Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

v$pga_target_advice Tips

Oracle Tips by Burleson Consulting

November 20. 2011

Question:  I need to understand the v$memory_target_advice view and see how it is used to size my pga_aggregate_target and memory_target parameters.

Answer:  For tuning the PGA memory within Oracle 11g, either the memory advisors can be used as shown previously or automatic memory management can be deployed to tune the sizes of the PGA memory buffers via the v$pga_target_advice or query v$memory_target_advice as shown in the figure listed below.

SQL> select pga_target_for_estimate, pga_target_factor, estd_time
  2  from v$pga_target_advice;

PGA_TARGET_FOR_ESTIMATE PGA_TARGET_FACTOR  ESTD_TIME
----------------------- ----------------- ----------
               20447232              .125       8754
               40894464               .25       8754
               81788928                .5       8754
              122683392               .75       8754
              163577856                 1       8754
              196292608               1.2       8754
              229008384               1.4       8754
              261724160               1.6       8754
              294439936               1.8       8754
              327155712                 2       8754
              490733568                 3       8754

Tuning the size of the PGA using the above view for v$pga_target_advice operates in a similar fashion to that of the SGA query (v$sga_target_advice) in that one needs to look at the PGA_TARGET_FACTOR column and compare to the ESTD_TIME column from the query result to determine the best fit size for the PGA with Oracle 11g.

Another option is to experiment with the new 11g dynamic performance view v$memory_target_advice as shown below.

SQL> select * from v$memory_target_advice;

MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR    VERSION
----------- ------------------ ------------ ------------------- ----------
        380                  1         2283                   1          3
        475               1.25         2284                   1          3
        570                1.5         2222               .9729          3
        665               1.75         2222               .9729          3
        760                  2         2222               .9729          3

So what can be ascertained from the output of the above query with v$memory_target_advice is that with a memory size between 570 megabytes and 665 megabytes, there is about the same performance estimate for the  memory_target parameter. This is beneficial because you can avoid wasting memory allocation by using 570 megabytes instead of the larger 760 megabytes maximum value available.

PGA target advice

 

Oracle first introduced the new advisory utility dubbed v$pga_target_advice.  This utility will show the marginal changes in optimal, one-pass, and multipass PGA execution for different sizes of pga_aggregate_target, ranging from 10% to 200% of the current value.

 

 

-- ************************************************
-- Display pga target advice
-- ************************************************
 
column c1     heading 'Target(M)'
column c2     heading 'Estimated|Cache Hit %'
column c3     heading 'Estimated|Over-Alloc.'
 
SELECT
   ROUND(pga_target_for_estimate /(1024*1024)) c1,
   estd_pga_cache_hit_percentage         c2,
   estd_overalloc_count                  c3
FROM
   v$pga_target_advice;
 
Here is the output.
 
 
             Estimated   Estimated
 Target(M) Cache Hit % Over-Alloc.
---------- ----------- -----------
       188          93           0
       376          95           0
       752          96           0
      1128          97           0
      1504          98           0
      1805          98           0
      2106          98           0
      2406          98           0
      2707          98           0
      3008          98           0
      4512          98           0
      6016          98           0
      9024          98           0
     12032          98           0
 
 
 
pga_target_advice_histogram.sql
 
-- ************************************************
-- Display pga target advice histogram
-- ************************************************
 
 
SELECT
   low_optimal_size/1024 "Low(K)",
   (high_optimal_size+1)/1024 "High(K)",
   estd_optimal_executions "Optimal",
   estd_onepass_executions "One Pass",
   estd_multipasses_executions "Multi-Pass"
FROM
   v$pga_target_advice_histogram
WHERE
   pga_target_factor = 2
AND
   estd_total_executions != 0
ORDER BY
   1;
 
 
    Low(K)    High(K)    Optimal   One Pass Multi-Pass
---------- ---------- ---------- ---------- ----------
         8         16   19218271          0          0
        16         32       8190          0          0
        32         64       1787          0          0
        64        128    1274804          0          0
       128        256        204          0          0
       256        512        267          0          0
       512       1024    3271389          0          0
      1024       2048        260          0          0
      2048       4096        247          0          0
      4096       8192        166          0          0
      8192      16384        876          0          0
     16384      32768       1492          0          0
     32768      65536       2506          0          0
     65536     131072        180         41          0
    131072     262144         11        102          0
    262144     524288          9         13          0
    524288    1048576          2         33          0

As we can see, these utilities make it easy to size your PGA regions in Oracle.  If you want more details on PGA sizing you might enjoy my latest book 'Oracle Tuning: The Definitive Reference' by Rampant TechPress. 


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational