PGA Cache Hit Ratio

dbacore

Hi,

In DWH DB ( 12c)  OEM reports PGA cache Hit Ratio 76% .

Values from Enterprise Manager ( 12c )

Current Allocated  :  20G  

Max allocated       :   116 G

pga_aggregate_target  :  100G

pga_aggregate_limit     :   200G

Server has 700 GB Memory.  Only about 60% is used at the moment.  Why Hit Ratio is so small if the limit is 200G and max allocated 116G ?

Jonathan Lewis

There are various soft and hard limit to what an individual process can do that may stop it from using as much of the available PGA memory as you might expect.

You could check v$sql_workarea_histogram to see what your worst case workareas have looked like (the view reports a range of sizes and number of executions, optimal, one-pass and multi-pass); you could also check v$sql_workarea to see if you can find any SQL_IDs (hence statements) which have large values for non-zero max_tempseg_size.

As Martin Preiss pointed out, checking the AWR reports for snapshot intervals is a better approach than picking up current values from v$ objects, but these views may give you a quick clue about your current state.

Regards

Jonathan Lewis

Updated to fix typos

AndrewSayer

user12052255 wrote:Hi,In DWH DB ( 12c) OEM reports PGA cache Hit Ratio 76% .Values from Enterprise Manager ( 12c )Current Allocated : 20G Max allocated : 116 G
pga_aggregate_target : 100G 
pga_aggregate_limit : 200G 
Server has 700 GB Memory. Only about 60% is used at the moment. Why Hit Ratio is so small if the limit is 200G and max allocated 116G ? 

From the docs

https://docs.oracle.com/database/121/TGDBA/tune_pga.htm#TGDBA346

cache hit percentage

This metric is computed by Oracle Database to reflect the performance of the PGA memory component. It is cumulative from instance startup. A value of 100% means that all work areas executed by the system since instance startup are using an optimal amount of PGA memory. This is ideal but rarely happens except for pure OLTP systems. Typically, some work areas run one-pass or even multi-pass, depending on the overall size of the PGA memory. When a work area cannot run optimally, one or more extra passes are performed over the input data. This reduces the cache hit percentage in proportion to the size of the input data and the number of extra passes performed. For an example of how this metric is calculated, see Example 16-1

Do you have a real performance problem? What real investigation have you done?

Martin Preiss

looking at global hit ratios is seldom a good idea. You could check the workarea statistics to determine wich queries use one-pass or multi-pass operations - and decide if this is actually unexpected.

dbacore

First of all I wanted to be sure that I understand the PGA memory allocation correctly. But your docu explains it well.

Secondly  the biggest wait event I got is direct path read . So I was wondering if its releated to this PGA cache hit Ratio.

dbacore

Its all optimal.  So I suppose I can ignore the cache hit ratio.

Jonathan Lewis

There are various soft and hard limit to what an individual process can do that may stop it from using as much of the available PGA memory as you might expect.

You could check v$sql_workarea_histogram to see what your worst case workareas have looked like (the view reports a range of sizes and number of executions, optimal, one-pass and multi-pass); you could also check v$sql_workarea to see if you can find any SQL_IDs (hence statements) which have large values for non-zero max_tempseg_size.

As Martin Preiss pointed out, checking the AWR reports for snapshot intervals is a better approach than picking up current values from v$ objects, but these views may give you a quick clue about your current state.

Regards

Jonathan Lewis

Updated to fix typos

AndrewSayer

user12052255 wrote:First of all I wanted to be sure that I understand the PGA memory allocation correctly. But your docu explains it well. Secondly the biggest wait event I got is direct path read . So I was wondering if its releated to this PGA cache hit Ratio.

Direct path read or direct path read temp?

Have you seen which SQLs are doing these? Do you have diagnostics pack licensed and access to v$active_session_history?

dbacore

Direct path read . Yes we have diagnostic pack so I'm able to identify the SQL.  I will have a closer look at them.