Forum Stats

  • 3,734,702 Users
  • 2,247,028 Discussions
  • 7,857,459 Comments

Discussions

PGA Cache Hit Ratio

dbacore
dbacore Member Posts: 26
edited Mar 1, 2017 2:09AM in General Database Discussions

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 ?

Best Answer

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,611 Gold Crown
    edited Feb 28, 2017 6:33AM Accepted Answer

    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

Answers

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited Feb 28, 2017 4:58AM
    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?

    dbacore
  • Martin Preiss
    Martin Preiss Member Posts: 2,381 Gold Trophy
    edited Feb 28, 2017 5:03AM

    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
  • dbacore
    dbacore Member Posts: 26
    edited Feb 28, 2017 5:27AM

    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
    dbacore Member Posts: 26
    edited Feb 28, 2017 5:28AM

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

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,611 Gold Crown
    edited Feb 28, 2017 6:33AM Accepted Answer

    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
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited Feb 28, 2017 6:15AM
    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
    dbacore Member Posts: 26
    edited Feb 28, 2017 6:38AM

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

This discussion has been closed.