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
        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?

        1 位用户发现它有用
        • 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 位用户发现它有用
          • 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
                  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?

                  • 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.