自动内存调整中真正决定自动调整的参数

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26015009/viewspace-751757/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26015009/viewspace-751757/