Appendix D: Direct SGA Access

You may wonder why there is a need to access the SGA directly when friendly SQL and powerful trace facility are available. There are many benefits to accessing the SGA directly. Besides the ability to do high frequency sampling, it is possible to get processes ‚ statistics even when the database or instance is hung and new connections cannot be established. This typically happens in very big sites with large SGAs and user populations, and the problem is normally caused by bugs in either the operating system or database. When this situation arises, the only way to get diagnostic statistics from Oracle is by accessing the SGA directly using external programs.

Overhead

Performance sampling with SQL has some overheads in the sense that the SQL statements are subject to Oracle ‚ s architecture and limits. The SQL statements must be parsed and optimized before execution. This involves dictionary lookups, latch acquisitions, etc. During the execution of the statements, the process must compete for resources as other Oracle processes. This involves latch and buffer lock acquisitions, consistent read image constructions, and various other potential waits. All these activities add to the overhead, and the overhead increases with higher sampling frequencies. But accessing the SGA directly from an external program does not introduce this kind of overhead to the Oracle instance. However, both types of access require CPU and memory to run. Performance monitoring always comes with a price, and accessing the SGA directly has a lower price tag. There is no such thing as overhead-free performance monitoring.