SQL> select x.ksppinm NAME,y.ksppstvl value,x.ksppdesc describ

2 from x$ksppi x, x$ksppcv y

3 where x.inst_id=USERENV('Instance')

4 and y.inst_id=USERENV('Instance')

5 and x.indx=y.indx

6 and x.ksppinm like '%pool_size%';

NAME VALUE DESCRIB

------------------------------ ------------------------- ---------------------------------------

_NUMA_pool_size Not specified aggregate size in bytes of NUMA pool

__shared_pool_size 1073741824 Actual size in bytes of shared pool

shared_pool_size 1073741824 size in bytes of shared pool

__large_pool_size 117440512 Actual size in bytes of large pool

large_pool_size 117440512 size in bytes of large pool

__java_pool_size 134217728 Actual size in bytes of java pool

java_pool_size 134217728 size in bytes of java pool

__streams_pool_size 0 Actual size in bytes of streams pool

streams_pool_size 0 size in bytes of the streams pool

_io_shared_pool_size 4194304 Size of I/O buffer pool from SGA

_backup_io_pool_size 1048576 memory to reserve from the large pool

global_context_pool_size Global Application Context Pool Size in Bytes

olap_page_pool_size 0 size of the olap page pool in bytes

13 rows selected

這些由兩個下劃線開頭的引數決定了當前的SGA的分配

這也是動態記憶體管理調整的引數,這些引數的更改也會

記錄到spfile檔案當中,在下一次資料庫啟動時仍然有效

透過create pfile from spfile我們可以看到如下內容:

jingyong.__db_cache_size=88080384

jingyong.__java_pool_size=4194304

jingyong.__large_pool_size=4194304

jingyong.__shared_pool_size=67108864

jingyong.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/jingyong/adump'

*.background_dump_dest='/u01/app/oracle/admin/jingyong/bdump'

*.compatible='10.2.0.1.0'

*.control_files='/u01/app/oracle/product/10.2.0/oradata/jingyong/control01.ctl','/u01/app/oracle/product/10.2.0/oradata/jingyong/control02.ctl','/u01/app/oracle/product/10.2.0/oradata/jingyong/control03.ctl'

*.core_dump_dest='/u01/app/oracle/admin/jingyong/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='jingyong'

*.db_recovery_file_dest='/u01/app/oracle/product/10.2.0/flash_recovery_area'

*.db_recovery_file_dest_size=2147483648

*.dispatchers='(PROTOCOL=TCP) (SERVICE=jingyongXDB)'

*.job_queue_processes=10

*.open_cursors=300

*.pga_aggregate_target=16777216

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=167772160

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/u01/app/oracle/admin/jingyong/udump'

還可以透過查詢v$sga_dynamic_components檢視來各動態記憶體元件的調整資訊

SQL> select component,current_size,min_size,last_oper_type,last_oper_mode,last_oper_time from v$sga_dynamic_components;

COMPONENT CURRENT_SIZE MIN_SIZE LAST_OPER_TYPE LAST_OPER_MODE LAST_OPER_TIME

---------------------------------------------------------------- ------------ ---------- -------------- -------------- --------------

shared pool 1124073472 1073741824 SHRINK DEFERRED 2012-12-27 16:

large pool 117440512 117440512 STATIC

java pool 134217728 134217728 STATIC

streams pool 0 0 STATIC

DEFAULT buffer cache 11442061312 1114007142 GROW DEFERRED 2012-12-27 16:

KEEP buffer cache 50331648 50331648 STATIC

RECYCLE buffer cache 0 0 STATIC

DEFAULT 2K buffer cache 0 0 STATIC

DEFAULT 4K buffer cache 0 0 STATIC

DEFAULT 8K buffer cache 0 0 STATIC

DEFAULT 16K buffer cache 0 0 STATIC

DEFAULT 32K buffer cache 0 0 STATIC

ASM Buffer Cache 0 0 STATIC

自動記憶體調整中真正決定自動調整的引數

相關文章