Know more about shared pool subpool

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

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: 432

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

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

Related posts about /Oracle

Categories cloud

.NET ASP.NET iphone linux python Windows mysql android sql windows-7 html c ruby-on-rails css objective-c ubuntu networking sql-server wpf asp.net-mvc ruby database Xml apache AJAX osx security regex django Performance 12.04 windows-xp mac web-development iphone-sdk vb.net Silverlight apache2 flash server perl best-practices email installation eclipse visual-studio algorithm windows-server-2008 winforms wcf firefox bash dns /Oracle