Simon Krenger

This week and next week I am participating in an Oracle Architecture and Internals training at Trivadis in Berne. Today, we received an overview of Oracle (Editions, Licensing, Support) and took a first look into the Oracle Architecture. This first chunk of information included Parameters, Memory and Process Structures. While looking at the PGA structures, I noticed a graph showing “PGA Cache Hit Percentage” in relation to the sizing of the PGA.

The term “PGA Cache Hit Percentage” does not make sense, since there is no cache in the PGA. According to my training material, the PGA consists of the following structures:

• User Global Area (UGA)
• Stack Space
• User Session Status
• Cursor Status
• Sort Area

It can be noted that the Sort Area is definitely the most important structure in an average database environment. But there is still no cache. In the SGA we have the Database Buffer Cache and various areas in the Shared Pool. So we can calculate the buffer pool hit ratio with the following query (Source):

SELECT NAME, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS,
      1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS)) "Hit Ratio"
  FROM V$BUFFER_POOL_STATISTICS;

It turns out that the “PGA Cache Hit %” is the percentage of one-pass sorts that could be done in memory. So if your PGA is appropriately sized, most sorts can be done one-pass in memory and do not require multiple passes.
Please let me know if I got this wrong or missed something.