7 Replies Latest reply on Feb 28, 2017 11:38 AM by dbacore

    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 ?

        • 1. Re: PGA Cache Hit Ratio
          Andrew Sayer

          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?

          1 person found this helpful
          • 2. Re: PGA Cache Hit Ratio
            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.

            1 person found this helpful
            • 3. Re: PGA Cache Hit Ratio
              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.

              • 4. Re: PGA Cache Hit Ratio
                dbacore

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

                • 5. Re: PGA 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

                  • 6. Re: PGA Cache Hit Ratio
                    Andrew Sayer

                    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?

                    • 7. Re: PGA Cache Hit Ratio
                      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.