Oracle Shared Pool Internals: List Chunk Position in the LRU List

2020-03-04

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.


NB! Check out 2020 online training classes here! Advanced Oracle Troubleshooting training, Advanced Oracle SQL Tuning training, Practical Linux Performance & Application Troubleshooting training. In addition to the online classes, all attendees will receive personal downloadable video recordings too!