Know more about shared pool subpool

Posted by Liu Maclean(???) on Oracle Blogs See other posts from Oracle Blogs or by Liu Maclean(???)
Published on Fri, 12 Oct 2012 13:20:58 +0000 Indexed on 2012/10/12 15:44 UTC
Read the original article Hit count: 441

Filed under:

????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/archives/understanding-automatic-sga-memory-management.html

© Oracle Blogs or respective owner

Related posts about /Oracle