This set of notes outlines the information you need and the problems you need to address if you want to write your own code to attach directly to the Oracle SGA so that you can extract information from it without creating a standard Oracle connection.
I was prompted to write it by a pair of questions that appeared at about the same time on discussion groups that I participate in. One was a question on the Oracle Developer Forum about finding the SQL that each session was currently running by using a direct attach to the shared memory, the other was a question on the Oracle-l mailing list asking why v$sysmetric reported the current “Session Count” with a value that was significantly higher than the count() of rows in v$session.
I gave a sketchy answer to the forum question with no intention of letting myself be dragged down into the convoluted and mind-bending details, but for the listserver question I decided to dig into x$ksuse (the main structure underneath v$session) to see if I could find an explanation for the discrepancy.
I’ve published the results of the listserver investigation on the blog, but after I’d finished poking around inside Oracle for a bit, I suddenly felt the urge to pull together a load of old notes and spend some time on a bit of new digging to see if I could produce a fairly detailed explanation of how to address the forum question.
I don’t know how many notes it will take to get through everything that I know, and I can’t guarantee that the description will be complete – after all I don’t have a machine that can start an instance with sessions=10000 and cpu_count=256 and there may be changes in memory segmentation details that appear only at that scale.
The notes I manage to produce will probably be of very little practical benefit to anyone – but they might be quite entertaining to read. At present I have a starting list of titles, which I’ll change to links as I manage to complete them.
- Outline, assumptions, and (current) limitations
- Attaching to the shared memory segment(s)
- Reviewing v$ and x$
- How to print the current SQL for each session (C code with caveats)
- x$kqfco column types and reporting more information from the session
- Walking v$session / x$ksuse without the cheat sheet
It’s quite possible that notes will be updated over time (especially the “limitations” one) as I get comments and suggestions from readers, or as I discover I’ve made a mistake. When things do change after initial publication I’ll put out a tweet and datestamp the index entry so that people can see that something has been updated.
So far I’ve discovered that some of the notes I wrote in 2002 are no longer relevant (which shouldn’t come as a surprise) and It’s quite likely that I will have to fill some gaps after initial publication.
Please feel free to add corrections, suggestions, and questions to the individual notes. The whole thing is really just a little bit of hackery, based largely on looking for things that seem to be related, so there may be critical details that I’ve overlooked or statements that are only correct for my particular setup or things that happen to work by accident.
Footnote
I’ve blocked comments on this introductory page, but only to make sure that if you have something to say you add it to the most appropriate page.
Oracle Scratchpad 