26-Jun-2008: Oracle X$KSMLRU fixed table

Regarding question on Oracle X$KSMLRU fixed table.

The x$ksmlru table keeps track of the current shared pool objects and the corresponding number of objects flushed out of the shared pool to allocate space for the load. These objects are stored and flushed out based on the Least Recently Used (LRU) algorithm. .... Note: This is a fixed table: once you query the table, the database will automatically reset the table. Make sure that you spool the output to a file so you can capture it for analysis.

I cannot yet answer why this table reset after each SELECT, but at least, I know at which point. Information in this table is constructed using function ksmlrs() (at least for 10.2), which also calls ksmsplu(). The last function allocate memory chunks for the table, marking them as x$\$$ksmsp lrstat and x$\$$ksmsp lru, then copy the table, then calls memset() C function to zero it. If to bypass this last call to memset(), by debugger for example, it is possible to have such Oracle instance which will not reset this table after each SELECT. However, if you want to do this, I hope you'll backup your database first.

Update: The story about how did I find it, I added to my "Quick introduction to reverse engineering for beginners" book: //yurichev.com/RE-book.html.

This open sourced site and this page in particular is hosted on GitHub. Patches, suggestions and comments are welcome.

→ [list of blog posts]