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

Maclean Liu(刘相兵

很好的一个问题 ：

已在http://www.oracledatabase12g.com ... d-pool-subpool.html 这篇博客中回复


有同学在T.askmaclean.com上求助Shared Pool里SubPool子分区问题，这里我们来搞清楚_kghdsidx_count 与 subpool 以及subpool中的分区( 实际是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 1400

SQL> show parameter kgh

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

SQL> oradebug setmypid;
Statement processed.

SQL> oradebug dump heapdump 536870914;
Statement processed.

SQL> oradebug tracefile_name
/s01/admin/G10R25/udump/g10r25_ora_11783.trc

[oracle@vrh8 dbs]$ grep "sga heap"  /s01/admin/G10R25/udump/g10r25_ora_11783.trc
HEAP DUMP heap name="sga heap"  desc=0x60000058
HEAP DUMP heap name="sga heap(1,0)"  desc=0x60036110
FIVE LARGEST SUB HEAPS for heap name="sga heap(1,0)"   desc=0x60036110
HEAP DUMP heap name="sga heap(2,0)"  desc=0x6003f938
FIVE LARGEST SUB HEAPS for heap name="sga heap(2,0)"   desc=0x6003f938
HEAP DUMP heap name="sga heap(3,0)"  desc=0x60049160
FIVE LARGEST SUB HEAPS for heap name="sga heap(3,0)"   desc=0x60049160
HEAP DUMP heap name="sga heap(4,0)"  desc=0x60052988
FIVE LARGEST SUB HEAPS for heap name="sga heap(4,0)"   desc=0x60052988
HEAP DUMP heap name="sga heap(5,0)"  desc=0x6005c1b0
FIVE LARGEST SUB HEAPS for heap name="sga heap(5,0)"   desc=0x6005c1b0
HEAP DUMP heap name="sga heap(6,0)"  desc=0x600659d8
FIVE LARGEST SUB HEAPS for heap name="sga heap(6,0)"   desc=0x600659d8
HEAP DUMP heap name="sga heap(7,0)"  desc=0x6006f200
FIVE LARGEST SUB HEAPS for heap name="sga heap(7,0)"   desc=0x6006f200

SQL> alter system set "_kghdsidx_count"=6 scope=spfile;

System altered.

SQL> startup force;
ORACLE instance started.

Total System Global Area  859832320 bytes
Fixed Size                  2100104 bytes
Variable Size             746587256 bytes
Database Buffers          104857600 bytes
Redo Buffers                6287360 bytes
Database mounted.
Database opened.
SQL>

SQL> oradebug setmypid;
Statement processed.
SQL> oradebug dump heapdump 536870914;
Statement processed.
SQL> oradebug tracefile_name
/s01/admin/G10R25/udump/g10r25_ora_11908.trc

[oracle@vrh8 dbs]$ grep "sga heap"  /s01/admin/G10R25/udump/g10r25_ora_11908.trc
HEAP DUMP heap name="sga heap"  desc=0x60000058
HEAP DUMP heap name="sga heap(1,0)"  desc=0x600360f0
FIVE LARGEST SUB HEAPS for heap name="sga heap(1,0)"   desc=0x600360f0
HEAP DUMP heap name="sga heap(2,0)"  desc=0x6003f918
FIVE LARGEST SUB HEAPS for heap name="sga heap(2,0)"   desc=0x6003f918
HEAP DUMP heap name="sga heap(3,0)"  desc=0x60049140
FIVE LARGEST SUB HEAPS for heap name="sga heap(3,0)"   desc=0x60049140
HEAP DUMP heap name="sga heap(4,0)"  desc=0x60052968
FIVE LARGEST SUB HEAPS for heap name="sga heap(4,0)"   desc=0x60052968
HEAP DUMP heap name="sga heap(5,0)"  desc=0x6005c190
FIVE LARGEST SUB HEAPS for heap name="sga heap(5,0)"   desc=0x6005c190
HEAP DUMP heap name="sga heap(6,0)"  desc=0x600659b8
FIVE LARGEST SUB HEAPS for heap name="sga heap(6,0)"   desc=0x600659b8

SQL>
SQL> alter system set "_kghdsidx_count"=2 scope=spfile;

System altered.

SQL>
SQL> startup force;
ORACLE instance started.

Total System Global Area  851443712 bytes
Fixed Size                  2100040 bytes
Variable Size             738198712 bytes
Database Buffers          104857600 bytes
Redo Buffers                6287360 bytes
Database mounted.
Database opened.

SQL> oradebug setmypid;
Statement processed.
SQL> oradebug dump heapdump 2;
Statement processed.
SQL> oradebug tracefile_name
/s01/admin/G10R25/udump/g10r25_ora_12003.trc

[oracle@vrh8 ~]$ grep "sga heap"  /s01/admin/G10R25/udump/g10r25_ora_12003.trc
HEAP DUMP heap name="sga heap"  desc=0x60000058
HEAP DUMP heap name="sga heap(1,0)"  desc=0x600360b0
HEAP DUMP heap name="sga heap(2,0)"  desc=0x6003f8d

SQL> alter system set cpu_count=16 scope=spfile;

System altered.

SQL> startup force;
ORACLE instance started.

Total System Global Area  851443712 bytes
Fixed Size                  2100040 bytes
Variable Size             738198712 bytes
Database Buffers          104857600 bytes
Redo Buffers                6287360 bytes
Database mounted.
Database opened.

SQL> oradebug setmypid;
Statement processed.
SQL>  oradebug dump heapdump 2;
Statement processed.
SQL> oradebug tracefile_name
/s01/admin/G10R25/udump/g10r25_ora_12065.trc

[oracle@vrh8 ~]$ grep "sga heap"  /s01/admin/G10R25/udump/g10r25_ora_12065.trc
HEAP DUMP heap name="sga heap"  desc=0x60000058
HEAP DUMP heap name="sga heap(1,0)"  desc=0x600360b0
HEAP DUMP heap name="sga heap(2,0)"  desc=0x6003f8d8

SQL> show parameter sga_target

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
sga_target                           big integer                      0

SQL> alter system set sga_target=1000M scope=spfile;

System altered.

SQL> startup force;
ORACLE instance started.

Total System Global Area 1048576000 bytes
Fixed Size                  2101544 bytes
Variable Size             738201304 bytes
Database Buffers          301989888 bytes
Redo Buffers                6283264 bytes
Database mounted.
Database opened.

SQL> alter system set sga_target=1000M scope=spfile;

System altered.

SQL> startup force;
ORACLE instance started.

Total System Global Area 1048576000 bytes
Fixed Size                  2101544 bytes
Variable Size             738201304 bytes
Database Buffers          301989888 bytes
Redo Buffers                6283264 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug dump heapdump 2;
Statement processed.
SQL>  oradebug tracefile_name
/s01/admin/G10R25/udump/g10r25_ora_12148.trc
SQL>
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@vrh8 dbs]$ grep "sga heap"  /s01/admin/G10R25/udump/g10r25_ora_12148.trc
HEAP DUMP heap name="sga heap"  desc=0x60000058
HEAP DUMP heap name="sga heap(1,0)"  desc=0x60036690
HEAP DUMP heap name="sga heap(1,1)"  desc=0x60037ee8
HEAP DUMP heap name="sga heap(1,2)"  desc=0x60039740
HEAP DUMP heap name="sga heap(1,3)"  desc=0x6003af98
HEAP DUMP heap name="sga heap(2,0)"  desc=0x6003feb8
HEAP DUMP heap name="sga heap(2,1)"  desc=0x60041710
HEAP DUMP heap name="sga heap(2,2)"  desc=0x60042f68

_enable_shared_pool_durations:该参数控制是否启用10g中特有的shared pool duration特性，当我们设置sga_target为0时该参数为false；
同时在10.2.0.5前若cursor_space_for_time设置为true时该参数也为false，不过在10.2.0.5以后cursor_space_for_time参数被废弃

SQL> alter system set "_enable_shared_pool_durations"=false scope=spfile;

System altered.

SQL>
SQL> startup force;
ORACLE instance started.

Total System Global Area 1048576000 bytes
Fixed Size                  2101544 bytes
Variable Size             738201304 bytes
Database Buffers          301989888 bytes
Redo Buffers                6283264 bytes
Database mounted.
Database opened.
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug dump heapdump 2;
Statement processed.
SQL> oradebug tracefile_name
/s01/admin/G10R25/udump/g10r25_ora_12233.trc
SQL>
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options\

[oracle@vrh8 dbs]$ grep "sga heap"   /s01/admin/G10R25/udump/g10r25_ora_12233.trc
HEAP DUMP heap name="sga heap"  desc=0x60000058

HEAP DUMP heap name="sga heap(1,0)"  desc=0x60036690
HEAP DUMP heap name="sga heap(2,0)"  desc=0x6003feb8





结论：
1.
_kghdsidx_count 控制了 shared pool subpool的数量， _kghdsidx_count的最大允许值是7 即最多 7个 shared pool subpool

2.
为什么会在一个 subpool中还有4个 sub partition 如：

sga heap(1,0) sga heap(1,1) sga heap(1,2) sga heap(1,3)

这不是因为 cpu的数目 也不是因为_kghdsidx_count， 而是因为 在10g 中AUTO SGA 引入了 shared pool duration的概念，

duration 分成4类：

    Session duration
    Instance duration (never freed)
    Execution duration (freed fastest)
    Free memory

引入了 shared pool duration的目的是

在10gR1中Shared Pool的shrink收缩操作存在一些缺陷，造成缺陷的原因是在该版本中Buffer Cache还没有能力共享使用一个granule，这是因为Buffer Cache的granule的尾部由granule header和Metadata(可能是buffer header或者RAC中的Lock Elements)拼接组成，在其尾部不容许存在空洞。另一个原因是当时的shared pool允许不同生命周期duration(以后会介绍)的chunk存放在同一个granule中，这造成共享池无法完全释放granule。到10gR2中通过对Buffer Cache Granule结构的修改允许在granule header和buffer及Metadata(buffer header或LE)存在缝隙，同时shared pool中不同duration的chunk将不在共享同一个granule，通过以上改进buffer cache与shared pool间的内存交换变得可行。此外在10gr2中streams pool也开始支持内存交换(实际根据不同的streams pool duration存在限制)

reference : http://www.oracledatabase12g.com ... ory-management.html

yanshoupeng

hello,
maclean:

SQL> oradebug dump heapdump 536870914;
Statement processed.


heapdump后的 536870914  是代表什么，这么大的数字记起来也不好记。
应该有啥含义吧。

Maclean Liu(刘相兵

是不好记，  但是请记住它 ，并 尝试经常去读读 这个heapdump level的内容

yanshoupeng

好的，谢谢。我觉得ORACLE应该搞些字符常量来代替这样的数字。

無限追云

谢谢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个？它和什么有关呢？

無限追云

Maclean Liu(刘相兵

抱歉是 前面的内容没贴全 在我的测试中 2个subpool 的情况下是 8个duration 分区

[oracle@vrh8 ~]$ grep "sga heap"  /s01/admin/G10R25/udump/g10r25_ora_12148.trc
HEAP DUMP heap name="sga heap"  desc=0x60000058
HEAP DUMP heap name="sga heap(1,0)"  desc=0x60036690
HEAP DUMP heap name="sga heap(1,1)"  desc=0x60037ee8
HEAP DUMP heap name="sga heap(1,2)"  desc=0x60039740
HEAP DUMP heap name="sga heap(1,3)"  desc=0x6003af98
HEAP DUMP heap name="sga heap(2,0)"  desc=0x6003feb8
HEAP DUMP heap name="sga heap(2,1)"  desc=0x60041710
HEAP DUMP heap name="sga heap(2,2)"  desc=0x60042f68
HEAP DUMP heap name="sga heap(2,3)"  desc=0x600447c0

warmbreeze

os:redhat 5(32bit)
db:10203

_kghdsidx_count =2的时候我这里也是7个shared pool durations

sga heap(1,0)
sga heap(1,1)
sga heap(1,2)
sga heap(1,3)
sga heap(2,0)
sga heap(2,1)
sga heap(2,2)


_kghdsidx_count =3的时候10个shared pool durations

sga heap(1,0)
sga heap(1,1)
sga heap(1,2)
sga heap(2,0)
sga heap(2,1)
sga heap(2,2)
sga heap(2,3)
sga heap(3,0)
sga heap(3,1)
sga heap(3,2)

無限追云

我在研究研究，谢谢Liu