Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.4K Development
- 17 Developer Projects
- 139 Programming Languages
- 293.1K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 161 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 475 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
PGA Cache Hit Ratio

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
-
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
-
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-1Do you have a real performance problem? What real investigation have you done?
-
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.
-
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.
-
Its all optimal. So I suppose I can ignore the cache hit ratio.
-
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
-
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?
-
Direct path read . Yes we have diagnostic pack so I'm able to identify the SQL. I will have a closer look at them.