Thursday, March 28, 2013

Shared Pool - SQLA and KGLH0

SQLA


In the last Blog: Shared Pool - Memory Allocation - unbalanced, SQLA in Subpool_1 is desperately low allocated,

   Subpool_1 has   1'110 KB for SQLA.
   Subpool_2 has 588'943 KB for SQLA.


So if a statement requires more than 1'110 KB in Subpool_1, it will not be satisfied.

It is not clear why SQLA in Subpool_1 is extremely low. One possible guess is that KGLH0 in Subpool_1 is too high, and there is somehow a limitation on the whole SQL memory usage in each Subpool.

In fact, it was said that alert log contains some text like:

   Memory Notification: Library Cache Object loaded into SGA
   Heap size 74879K exceeds notification threshold (51200K)


actually this is controlled by:

   _kgl_large_heap_warning_threshold

with default setting 52428800 (52428800/1024 = 51200K), which is documented as:

 maximum heap size before KGL writes warnings to the alert log 

One can also monitor memory consumption by:

 select sql_id, alloc_class, sum(chunk_size)/1024 sum_mb, count(*) chunk_cnt 
 from v$sql_shared_memory 
 --where sql_id = ':sql_id'   -- not working with sql_id
 group by sql_id, alloc_class
 order by sum_mb desc;

As we observed by this incident, the low SQLA caused frequently cursor AgedOut, consequently, reloading/hard parsing, or eventually session dumps with:

   ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","SELECT MAX(XX) FROM...","SQLA","tmp")

or

   ORA-04031: unable to allocate 48 bytes of shared memory ("shared pool","select yy from tt whe...","TCHK^3fefd486","qcsqlpath: qcsAddSqlPath")

and the dump files contain some lines like:

     Chunk  700000088ff8000 sz=     4096    freeable  "SQLA^8b7ceb5a  "  ds=7000000a88fafc8
  
where "8b7ceb5a" is the hash value of sql_id (output of dbms_utility.sqlid_to_sqlhash, or mod by 131072 is hash bucket number in library cache).

It seems  that subpool id of shared pool is determined by:
     mod(mod(to_number('8b7ceb5a', 'xxxxxxxxx'), 131072), 7) + 1
or directly from sql_id
     mod(mod(dbms_utility.sqlid_to_sqlhash(:sql_id), 131072), 7) + 1
if all 7 subpools are used.

That means the subpool for one sql is chosen based on sql_id.


So it is worth of experimenting a littler more on SQLA area.

1. At first, make a heapdump of shared pool by:

   SQL> oradebug dump heapdump 2

  contains some lines like:
 
      Chunk  700000088ff8000 sz=     4096    freeable  "SQLA^8b7ceb5a  "  ds=7000000a88fafc8
      Chunk  700000088ff9000 sz=     4096    freeable  "SQLA^8b7ceb5a  "  ds=7000000a88fafc8

  
2. Then a heapdump_addr dump of SQLA by:

   SQL> oradebug dump heapdump_addr 1 0X7000000a88fafc8

    *** 2013-03-12 10:38:24.072
    Processing Oradebug command 'dump heapdump_addr 1 0X7000000a88fafc8'
    ******************************************************
    HEAP DUMP heap name="SQLA^8b7ceb5a"  desc=7000000a88fafc8
     extent sz=0xfe8 alt=32767 het=368 rec=0 flg=2 opc=2
     parent=700000000000198 owner=7000000a88fae88 nex=0 xsz=0xfe8 heap=0
     fl2=0x67, nex=0, dsxvers=1, dsxflg=0x0
     dsx first ext=0x8c5e2f90
    EXTENT 0 addr=7000000902996a0
      Chunk  7000000902996b0 sz=     4056    freeable  "TCHK^8b7ceb5a  "  ds=70000008c5e3a98
    ...
    EXTENT 85 addr=7000000902ee6a8
      Chunk  7000000902ee6b8 sz=     4056    freeable  "TCHK^8b7ceb5a  "  ds=70000008c5e3a98
 

3. Finally a heapdump_addr dump of TCHK ("Typecheck heap") by:

   SQL> oradebug dump heapdump_addr 1 0X70000008c5e3a98

    *** 2013-03-12 10:46:42.079
    Processing Oradebug command 'dump heapdump_addr 1 0X70000008c5e3a98'
    ******************************************************
    HEAP DUMP heap name="TCHK^8b7ceb5a"  desc=70000008c5e3a98
     extent sz=0xfc0 alt=32767 het=32767 rec=0 flg=2 opc=2
     parent=7000000a88fafc8 owner=7000000a88fae88 nex=0 xsz=0xfc0 heap=0
     fl2=0x67, nex=0, dsxvers=1, dsxflg=0x0
     dsx first ext=0x8c5a7b30
    EXTENT 0 addr=7000000902996c8
      Chunk  7000000902996d8 sz=      608    free      "               "
      Chunk  700000090299938 sz=       40    freeable  "chedef : qcuatc"

     
From last dump file, we can see top 5 memory consumers are with comments like:
     
  Chunk  700000094527010 sz=      112    freeable  "optdef: qcopCre"
  Chunk  7000000a16d2678 sz=      152    freeable  "opndef: qcopCre"
  Chunk  700000092c9e160 sz=      288    freeable  "kkojnp - infode"
  Chunk  7000000902bea50 sz=       40    freeable  "chedef : qcuatc" 
  Chunk  7000000902ebf58 sz=      184    freeable  "kggec.c.kggfa  "


Following queries can also get memory consumption:

select *
  from v$sql_shared_memory;

select sql_id, typecheck_mem, type_chk_heap, sql_text
  from v$sql
 where typecheck_mem > 0;


where 
 v$sql_shared_memory.HEAP_DESC points to
     ds=7000000a88fafc8 in "SQLA^8b7ceb5a  " 
    
 v$sql_shared_memory.SUBHEAP_DESC points to
     ds=70000008c5e3a98 in  "TCHK^8b7ceb5a  "

    
 v$sql.TYPECHECK_MEM gives the total memory of SQLA, which can be also obtained by sum(chunk_size) of v$sql_shared_memory.
    
so you can pick HEAP_DESC and SUBHEAP_DESC to make SQLA and TCHK dump.
    
As tested, the majority (>90%) of memory in SQLA is consumed by TCHK.

KGLH0


Similar to SQLA, one can find the ds for "KGLH0^8b7ceb5a " in shared pool heapdump:
   Chunk  7000000a1efce68 sz=     4096    freeable  "KGLH0^8b7ceb5a "  ds=7000000a1d9c450
and then make an address dump for "KGLH0^8b7ceb5a ":

  Total_size #Chunks  Chunk_size,        From_heap,       Chunk_type,  Alloc_reason
  ---------- ------- ------------ ----------------- ----------------- -----------------
        3296    1       3296 ,   KGLH0^8b7ceb5a,             perm,  perm          
        1960    1       1960 ,   KGLH0^8b7ceb5a,         freeable,  policy chain  
        1760    1       1760 ,   KGLH0^8b7ceb5a,             perm,  perm          
        1392    2        696 ,   KGLH0^8b7ceb5a,         freeable,  policy chain  
        1384    1       1384 ,   KGLH0^8b7ceb5a,             perm,  perm          
        1152    8        144 ,   KGLH0^8b7ceb5a,         freeable,  context chain 
         880    1        880 ,   KGLH0^8b7ceb5a,         freeable,  policy chain  
         760    5        152 ,   KGLH0^8b7ceb5a,         freeable,  kgltbtab      
         720    1        720 ,   KGLH0^8b7ceb5a,         freeable,  policy chain  
         712    1        712 ,   KGLH0^8b7ceb5a,         freeable,  policy chain  
         656    1        656 ,   KGLH0^8b7ceb5a,         freeable,  policy chain  
         608    1        608 ,   KGLH0^8b7ceb5a,         freeable,  policy chain  
         416    1        416 ,   KGLH0^8b7ceb5a,             free,                
         376    1        376 ,   KGLH0^8b7ceb5a,             free,                
          80    1         80 ,   KGLH0^8b7ceb5a,             perm,  perm          
          48    1         48 ,   KGLH0^8b7ceb5a,             free,

 
These seem about VPD. Following query:
     select * from v$vpd_policy v where sql_id = :sql_id; 
returns 8 rows, which confrims the above 8 policy chains and 8 context chains.

Discussion


A library_cache dump ("library_cache level 16") reveals that each child cursor is associated with one KGHL0 and one SQLA. It seems that KGHL0 stores environment information, whereas SQLA stores parsing tree and xplan. When memory is reclaimed under memory presssure, KGHL0 kept, whereas SQLA deallocated. The later re-use of the that child cursor will result in a hard-parsing by using the kept KGHL0 info.

Occasionally for certain statements, SQL Trace (10046) shows:

SQL ID: 67kamvx1dz051

SELECT * FROM XXX WHERE ID = :B1 

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute    233      0.01       0.85          0          0          0           0
Fetch      233      0.00       0.01          0       1494          0          78
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      466      0.02       0.86          0       1494          0          78

Misses in library cache during parse: 0
Misses in library cache during execute: 2

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  latch: shared pool                             12        0.00          0.03
  latch: row cache objects                        3        0.29          0.79
probably indicates such hard-parsing. Moreover, Wait Event "latch: shared pool" and "latch: row cache objects" also provides the evidence of hard-parsing.

It means that while executing the statement (233 times), the required child cursor could be always found in KGHL0, hence no Parse Call during parse; but occasionally xplan has been evicted (2 times), and should be newly created during execute.

Shared pool heapdump shows that both SQLA and KGLH0 are allocated in a unit of 4096 Bytes(Chunk_size). So the memory is only allocated starting from:
     Bucket 240 size=4096
in Free List (memory in Free List is partitioned into 255 Buckets from 0 to 254).

Big chunk size generates less fragmentation, however less efficient memory utilization, since certain ORA-04031 says:
    unable to allocate 32 bytes of shared memory ("shared pool","SELECT MAX(XX) FROM...","SQLA","tmp")
so a 32 Bytes memory demand would be boosted into a 4096 Bytes allocation.