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