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
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.
