10gR2,Shared Pool里SubPool子分区问题

無限追云

环境：

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个。想不明白。请刘大帮忙解惑下

谢谢

無限追云

这是相应的trace

無限追云

谢谢Liu,刚从老家回来。
先看看你的解释

無限追云

Liu，恕我愚钝，看了你的解释和Google之后，对于 shared pool duration 还是存在疑惑。

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> set linesize 200 pagesize 200
SQL> show parameter "_enable_shared_pool_durations"

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_enable_shared_pool_durations        boolean     TRUE
SQL>
SQL> show parameter kgh

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_kghdsidx_count                      integer     7



SQL> alter system set "_kghdsidx_count"=2 scope=spfile;
System altered.
#将参数“_kghdsidx_count”设置为2，使它有2个subpool

SQL> startup force
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  2101808 bytes
Variable Size             264244688 bytes
Database Buffers          801112064 bytes
Redo Buffers                6283264 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL>
SQL> show parameter kgh

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_kghdsidx_count                      integer     2
SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump heapdump 2;
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/admin/vrh1/udump/vrh1_ora_5664.trc


[oracle@vrh1 ~]$ grep "sga heap" /u01/app/oracle/admin/vrh1/udump/vrh1_ora_5664.trc
HEAP DUMP heap name="sga heap"  desc=0x60000058
HEAP DUMP heap name="sga heap(1,0)"  desc=0x60036750
HEAP DUMP heap name="sga heap(1,1)"  desc=0x60037fa8
HEAP DUMP heap name="sga heap(1,2)"  desc=0x60039800
HEAP DUMP heap name="sga heap(1,3)"  desc=0x6003b058
HEAP DUMP heap name="sga heap(2,0)"  desc=0x6003ff78
HEAP DUMP heap name="sga heap(2,1)"  desc=0x600417d0
HEAP DUMP heap name="sga heap(2,2)"  desc=0x60043028

2个subpool的情况下，不是应该有8个shared pool durations吗？为什么只有7个？7个？还是8个？它和什么有关呢？

無限追云

無限追云

我在研究研究，谢谢Liu