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

 
 Home
 E-mail Us
 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 


 

 

 


 

 

 
 

Size your Shared Pool with v$shared_pool_advice

Oracle Tips by Burleson Consulting

November 20, 2011

The v$shared_pool_advice advisory functionality has been extended in Oraclerelease 2 to include a new advice called v$shared_pool_advice, and there is talk to expending the advice facility to all SGA RAM areas in future releases of Oracle.

 

In Oraclerelease 2, the v$shared_pool_advice show the marginal difference in SQL parses as the shared pool changes in size from 10% of the current value to 200% of the current value.

 

The Oracle documentation contains a complete description for the set-up and use of shared pool advice, and it is very simple to configure.  Once it is installed, you can run a simple script to query the v$shared_pool_advice view and see the marginal changes in SQL parses for different shared_pool sizes.

 

-- ************************************************
-- Display shared pool advice
-- ************************************************
 
set lines  100
set pages  999
 
column        c1     heading 'Pool |Size(M)'
column        c2     heading 'Size|Factor'
column        c3     heading 'Est|LC(M)  '
column        c4     heading 'Est LC|Mem. Obj.'
column        c5     heading 'Est|Time|Saved|(sec)'
column        c6     heading 'Est|Parse|Saved|Factor'
column c7     heading 'Est|Object Hits'   format 999,999,999
 
 
SELECT
   shared_pool_size_for_estimate  c1,
   shared_pool_size_factor        c2,
   estd_lc_size                   c3,
   estd_lc_memory_objects         c4,
   estd_lc_time_saved                    c5,
   estd_lc_time_saved_factor             c6,
   estd_lc_memory_object_hits            c7
FROM
   v$shared_pool_advice;

 

                                                   Est        Est
                                                  Time      Parse
     Pool        Size        Est     Est LC      Saved      Saved          Est
   Size(M)     Factor    LC(M)    Mem. Obj.      (sec)     Factor  Object Hits
---------- ---------- ---------- ---------- ---------- ---------- ------------
        48         .5         48      20839    1459645          1  135,756,032
        64      .6667         63      28140    1459645          1  135,756,101
        80      .8333         78      35447    1459645          1  135,756,149
        96          1         93      43028    1459645          1  135,756,253
       112     1.1667        100      46755    1459646          1  135,756,842
       128     1.3333        100      46755    1459646          1  135,756,842
       144        1.5        100      46755    1459646          1  135,756,842
       160     1.6667        100      46755    1459646          1  135,756,842
       176     1.8333        100      46755    1459646          1  135,756,842
       192          2        100      46755    1459646          1  135,756,842

 

Here we see the statistics for the shared pool in a range from 50% of the current size to 200% of the current size.  These statistics can give you a great idea about the proper size for the shared_pool‑_size.  If you are automatic the SGA region sizes with automated ?alter system? commands, creating this output and writing a program to interpret the results is a great way to ensure that the shared pool and library cache always have enough RAM.

Tuning the Shared Pool for Oracle 11g

Oracle 11g provides the v$shared_pool_advice dynamic performance view that can be used to obtain tuning advice for the shared pool as shown in the following example.

SQL> desc v$shared_pool_advice

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------
 SHARED_POOL_SIZE_FOR_ESTIMATE                      NUMBER
 SHARED_POOL_SIZE_FACTOR                            NUMBER
 ESTD_LC_SIZE                                       NUMBER
 ESTD_LC_MEMORY_OBJECTS                             NUMBER
 ESTD_LC_TIME_SAVED                                 NUMBER
 ESTD_LC_TIME_SAVED_FACTOR                          NUMBER
 ESTD_LC_LOAD_TIME                                  NUMBER
 ESTD_LC_LOAD_TIME_FACTOR                           NUMBER
 ESTD_LC_MEMORY_OBJECT_HITS                         NUMBER

To obtain a truly useful value for sizing the shared pool for Oracle 11g, set the statistics_level parameter to either TYPICAL or ALL before querying the v$shared_pool_advice dynamic performance view.

So if one wants to look at recommended settings for the shared pool with Oracle 11g, issue the following query:

SQL> SELECT shared_pool_size_for_estimate "Size of Shared Pool in MB",

  2   shared_pool_size_factor "Size Factor",

  3   estd_lc_time_saved "Time Saved in sec"

  4  FROM v$shared_pool_advice;

Size of Shared Pool in MB Size Factor Time Saved in sec
------------------------- ----------- -----------------
                      136       .7727             17000
                      156       .8864             17009
                      176           1             17036
                      196      1.1136             17048
                      216      1.2273             17051
                      236      1.3409             17052
                      256      1.4545             17052
                      276      1.5682             17052
                      296      1.6818             17052
                      316      1.7955             17052
                      336      1.9091             17052

Size of Shared Pool in MB Size Factor Time Saved in sec
------------------------- ----------- -----------------
                      356      2.0227             17052 

12 rows selected.

An interesting observation can be noted here in the output from the v$shared_pool_advice query in the above figure.

The performance benefit varies little between a shared pool size of 136MB to 156MB. Only a real performance benefit can be seen when the shared pool size has been increased to 236MB. Also of note is that increasing the shared pool size beyond 236MB yields no additional performance benefit for the shared pool performance with Oracle 11g. In fact, Oracle support notes on Metalink (Metalink Note #255409.1) advise against increasing the size of the shared pool to a large value as it may cause latch contention or fragmentation problems.

Another dynamic performance view, v$rowcache, may also assist tuning efforts for the shared pool memory cache regions. While hit ratios are no longer gospel as far as performance tuning results, they can be helpful when used as part of a comprehensive performance tuning assessment.  In general, hit ratios should be above 95% for the library cache and data dictionary cache within the shared pool for Oracle 11g. Also, a performance report collected via the Oracle 11g ADDM (Automatic Database Diagnostic Monitor) and/or report from a recent snapshot with the Oracle 11g ADR (Automatic Diagnostic Repository) will yield outstanding performance issues with the shared pool cache areas.

 



 

 

��  
 
 

 
 
 
 
oracle dba poster
 

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

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.