There was a discussion at Oracle-L about shared pool chunk eviction and I thought I’d show how to see a shared pool memory chunk’s position in the LRU list yourself. This is a post mostly about Oracle internals and less about practical every day troubleshooting.
There’s a view X$KGHLU (@kghlu.sql script) that shows you a summary of shared pool (sub)heap allocations and how many of these chunks are in the “transient list” (used once) vs. “recurrent list” (used more than once). There’s just a single LRU list per shared pool (sub)heap, but chunks can reside in either “half” of the list, separated by a special chunk called SEPARATOR in shared pool heap dumps.
SQL> @kghlu
SUB SSUB FLUSHED LRU LIST RECURRENT TRANSIENT FREE UNPIN LAST FRUNP RESERVED RESERVED RESERVED RESERVED
POOL POOL CHUNKS OPERATIONS CHUNKS CHUNKS UNSUCCESS UNSUCC SIZE SCANS MISSES MISS SIZE MISS MAX SZ
------ ---------- ---------- ---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- -----------
1 0 6055332 17794835 10384 14474 0 0 9391 0 0 0
When the KGH heap allocator doesn’t find usable free chunks in the heap freelist, it will start scanning and evicting unpinned recreatable chunks from the transient part of the LRU list, to avoid flushing out frequently used stuff.
Now let’s run a simple query (first hard parse):
SQL> SELECT * FROM dual /* tanel2 */;
D
-
X
Check the hash value of the previously executed SQL:
SQL> @hash
HASH_VALUE SQL_ID CHILD_NUMBER PLAN_HASH_VALUE SQL_EXEC_START SQL_EXEC_ID
---------- ------------- ------------ --------------- ------------------- -----------
4247540029 89yp91gyksn9x 0 272002086 2020-03-04 01:08:14 16777216
I convert the SQL hash value to hex as the heap dump contains hexadecimal library cache object hash values of the “allocating client” as part of the 16 char comments in shared pool chunk headers:
SQL> @dec 4247540029
DEC HEX
----------------------------------- --------------------
4247540029.000000 FD2C513D
So far my demo query has been executed only once. In a different session, I issued a shared pool heap dump and made sure that the dump went to a separate tracefile (0021) from my previous experiments:
SQL> @ti
New tracefile_identifier=/u01/app/oracle/diag/rdbms/lin183/LIN183/trace/LIN183_ora_16826_0021.trc
SQL> @evi heapdump 2
alter session set events 'immediate trace name heapdump level 2';
Session altered.
Note that the shared pool heap dump is a heavy operation that may make your shared pool latching problems even worse and in older versions may even hang your instance for a while. Don’t use this (lightly) in production.
And then I ran my demo query one more time (now it’s been executed twice):
SQL> SELECT * FROM dual /* tanel2 */;
D
-
X
And I ran one more heap dump (to a new tracefile 0022):
SQL> @ti
New tracefile_identifier=/u01/app/oracle/diag/rdbms/lin183/LIN183/trace/LIN183_ora_16826_0022.trc
SQL> @evi heapdump 2
alter session set events 'immediate trace name heapdump level 2';
Session altered.
And now it’s time to see what’s in the trace files. The shared pool dump files are very big (depending on your shared pool size), so I’ll just grep for our chunks of interest and the shared pool SEPARATOR object.
This is the output we get from the 1st SQL execution shared pool dump. All chunks belonging to the library cache object with hash value 0xFD2C513D in the sga heap(1,0) (subheap 1, duration 0) are above the SEPARATOR marker within that subheap. So when the heap manager searches for space to free (top-down in the tracefile LRU layout terms), then it would find these chunks first, before going to the recurrent end.
$ egrep -ie "HEAP DUMP|SEPARATOR|FD2C513D" LIN183_ora_16826_0021.trc HEAP DUMP heap name="sga heap" desc=0x6010b4f8 HEAP DUMP heap name="sga heap(1,0)" desc=0x6010ce50 Chunk 06eac3938 sz= 4096 recreatPC "KGLH0^fd2c513d " Chunk 07221f5f8 sz= 4096 recreatUC "KGLH0^fd2c513d " Chunk 0688d5e90 sz= 4096 recreatUC "SQLA^fd2c513d " Chunk 07221f5f8 sz= 4096 recreatUC "KGLH0^fd2c513d " SEPARATOR HEAP DUMP heap name="sga heap(1,3)" desc=0x60111758 Chunk 066eb5fc0 sz= 4096 freeableU "SQLA^fd2c513d " ds=0x6eac4120 Chunk 0688d5e90 sz= 4096 recreatUC "SQLA^fd2c513d " Chunk 06bddb958 sz= 4096 freeableU "SQLA^fd2c513d " ds=0x6eac4120 SEPARATOR HEAP DUMP heap name="IMCA_RO" desc=0x60001030 HEAP DUMP heap name="IMCA_RW" desc=0x60001178
After the 2nd execution, one of the chunks has moved to below the SEPARATOR in the LRU list:
$ egrep -ie "HEAP DUMP|SEPARATOR|FD2C513D" LIN183_ora_16826_0022.trc HEAP DUMP heap name="sga heap" desc=0x6010b4f8 HEAP DUMP heap name="sga heap(1,0)" desc=0x6010ce50 Chunk 06eac3938 sz= 4096 recreatPC "KGLH0^fd2c513d " Chunk 07221f5f8 sz= 4096 recreatPR "KGLH0^fd2c513d " Chunk 07221f5f8 sz= 4096 recreatPR "KGLH0^fd2c513d " SEPARATOR Chunk 0688d5e90 sz= 4096 recreatUR "SQLA^fd2c513d " HEAP DUMP heap name="sga heap(1,3)" desc=0x60111758 Chunk 066eb5fc0 sz= 4096 freeableU "SQLA^fd2c513d " ds=0x6eac4120 Chunk 0688d5e90 sz= 4096 recreatUR "SQLA^fd2c513d " Chunk 06bddb958 sz= 4096 freeableU "SQLA^fd2c513d " ds=0x6eac4120 SEPARATOR HEAP DUMP heap name="IMCA_RO" desc=0x60001030 HEAP DUMP heap name="IMCA_RW" desc=0x60001178
So, this chunk should stay in the shared pool more resiliently now as it is “further down the LRU list” below the SEPARATOR chunk now.
There’s no X$ that would tell you an individual chunk’s position in the shared pool heap LRU list, but you can get this info from heapdumps. Also, I didn’t see the behavior (of a chunk moving to the recurrent part of the LRU list) when the shared pool had plenty of free space - I had to first “pollute” the shared pool with lots of cursors using literal values (like in my @lotshparses2.sql script).
In practice, thanks to this behavior I tend to run my demo queries 2-3 times in a row, if I want them to stay in the shared pool long enough for examining in later parts of the demo.
Update: If you are interested in Shared Pool layout and concepts in general, then this whitepaper by Oracle is still a very good starting point.
