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

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

Leave a Reply

Your email address will not be published.

Proudly powered by WordPress | Indrajeet by Sus Hill.