Chinese

question from oracler:


环境：

DB:10.2.0.1
虚拟机内存：2G
CPU:1

sga信息：

Total System Global Area 1207959552 bytes
Fixed Size 2020384 bytes
Variable Size 352324576 bytes
Database Buffers 838860800 bytes
Redo Buffers 14753792 bytes


最近在学习eygle的 《深入解析Oracle》的“Oracle 10g共享池管理的增强”，有个地方不是很清楚。

原文如下：
http://www.eygle.com/archives/20 ... kghdsidx_count.html

当使用两个子缓冲池时，Shared Pool则被划分为8个子分区进行管理如下：
[oracle@eygle udump]$ grep "sga heap" eygle_ora_13618.trc
HEAP DUMP heap name="sga heap" desc=0x2000002c
HEAP DUMP heap name="sga heap(1,0)" desc=0x2001b550
HEAP DUMP heap name="sga heap(1,1)" desc=0x2001c188
HEAP DUMP heap name="sga heap(1,2)" desc=0x2001cdc0
HEAP DUMP heap name="sga heap(1,3)" desc=0x2001d9f8
HEAP DUMP heap name="sga heap(2,0)" desc=0x20020640
HEAP DUMP heap name="sga heap(2,1)" desc=0x20021278
HEAP DUMP heap name="sga heap(2,2)" desc=0x20021eb0
HEAP DUMP heap name="sga heap(2,3)" desc=0x20022ae8


我的实验：

SQL> oradebug setmypid
Statement processed.
SQL> alter session set events 'immediate trace name heapdump level 2';
Session altered.
SQL> oradebug tracefile_name
/u01/app/oracle/admin/test/udump/test_ora_11552.trc

分析得到的日志，当仅有一个子缓冲时，Shared Pool被划分为sga heap(1，0)-sga heap(1，3) 共4个子分区

[oracle@vrhtest udump]$ grep "sga heap" test_ora_11552.trc
HEAP DUMP heap name="sga heap" desc=0x60000058
HEAP DUMP heap name="sga heap(1,0)" desc=0x60032940
HEAP DUMP heap name="sga heap(1,1)" desc=0x60034198
HEAP DUMP heap name="sga heap(1,2)" desc=0x600359f0
HEAP DUMP heap name="sga heap(1,3)" desc=0x60037248

当使用两个子缓冲时，Shared Pool则被划分为8个子分区进行管理:

SQL> alter system set "_kghdsidx_count"=2 scope=spfile;
System altered.
SQL> startup force
ORACLE instance started.
Total System Global Area 1207959552 bytes
Fixed Size 2020384 bytes
Variable Size 352324576 bytes
Database Buffers 838860800 bytes
Redo Buffers 14753792 bytes
Database mounted.
Database opened.

SQL> oradebug setmypid
Statement processed.
SQL> alter session set events 'immediate trace name heapdump level 2';
Session altered.
SQL> oradebug tracefile_name
/u01/app/oracle/admin/test/udump/test_ora_12100.trc

[oracle@vrhtest udump]$ grep "sga heap" test_ora_12100.trc
HEAP DUMP heap name="sga heap" desc=0x60000058
HEAP DUMP heap name="sga heap(1,0)" desc=0x60032940
HEAP DUMP heap name="sga heap(1,1)" desc=0x60034198
HEAP DUMP heap name="sga heap(1,2)" desc=0x600359f0
HEAP DUMP heap name="sga heap(1,3)" desc=0x60037248
HEAP DUMP heap name="sga heap(2,0)" desc=0x6003bcb8
HEAP DUMP heap name="sga heap(2,1)" desc=0x6003d510
HEAP DUMP heap name="sga heap(2,2)" desc=0x6003ed68

我的Shared Pool被划分成7个子分区，而不是书上所说的8个。

shared_pool_size的大小：

SQL> select name,value from v$parameter
2 where name in ('large_pool_size','java_pool_size',
3 'shared_pool_size','streams_pool_size','db_cache_size');

NAME VALUE
-------------------- --------------------
shared_pool_size 318767104
large_pool_size 16777216
java_pool_size 16777216
streams_pool_size 0
db_cache_size 838860800

10g的subpool至少为256M，现在的shared pool为300多M。

我以为是shared pool不够大，所以虚拟机内存增大到4G，把shared pool改为700M：

SQL> alter system set sga_max_size=2g scope=spfile;

System altered.

SQL> alter system set sga_target=2g scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size 2022184 bytes
Variable Size 352322776 bytes
Database Buffers 1778384896 bytes
Redo Buffers 14753792 bytes
Database mounted.
Database opened.


SQL> alter system set "shared_pool_size"=700M scope=memory;
System altered.

SQL> select name,value from v$parameter
2 where name in ('large_pool_size','java_pool_size',
3 'shared_pool_size','streams_pool_size','db_cache_size');

NAME VALUE
-------------------- --------------------
shared_pool_size 738197504
large_pool_size 16777216
java_pool_size 16777216
streams_pool_size 0
db_cache_size 838860800

SQL> oradebug setmypid
Statement processed.
SQL> alter session set events 'immediate trace name heapdump level 2';
Session altered.
SQL> oradebug tracefile_name
/u01/app/oracle/admin/test/udump/test_ora_3087.trc


[oracle@vrhtest udump]$ grep "sga heap" test_ora_3087.trc
HEAP DUMP heap name="sga heap" desc=0x60000058
HEAP DUMP heap name="sga heap(1,0)" desc=0x60033040
HEAP DUMP heap name="sga heap(1,1)" desc=0x60034898
HEAP DUMP heap name="sga heap(1,2)" desc=0x600360f0
HEAP DUMP heap name="sga heap(1,3)" desc=0x60037948
HEAP DUMP heap name="sga heap(2,0)" desc=0x6003c3b8
HEAP DUMP heap name="sga heap(2,1)" desc=0x6003dc10
HEAP DUMP heap name="sga heap(2,2)" desc=0x6003f468

它还是被划分成7个子分区，不是8个。想不明白。请刘大帮忙解惑下