Oracle Tips and Tricks — David Fitzjarrell

March 30, 2015

Sound Advice

Filed under: Performance — dfitzjarrell @ 09:33

"There is much worth noticing that often escapes the eye."
- Norton Juster, The Phantom Tollbooth

Oracle has provided views to assist in managing the SGA and PGA settings when you’re not using Automatic Memory Management (AMM) and a view to manage memory_target when you are using AMM. These views were designed to be fairly easy to interpret but it seems that there has been confusion of late in how to actually use the recommendations Oracle is presenting. Let’s fix that.

Starting with 10gR2 Oracle provided both V$SGA_TARGET_ADVICE and V$PGA_TARGET_ADVICE to assist the DBA in sizing the SGA and PGA based on how the database was using memory. The definitions of these views (as of are shown below:

SQL> desc v$sga_target_advice
 Name                                                                     Null?    Type
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 SGA_SIZE                                                                          NUMBER
 SGA_SIZE_FACTOR                                                                   NUMBER
 ESTD_DB_TIME                                                                      NUMBER
 ESTD_DB_TIME_FACTOR                                                               NUMBER
 ESTD_PHYSICAL_READS                                                               NUMBER

SQL> desc v$pga_target_advice
 Name                                                                     Null?    Type
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 PGA_TARGET_FOR_ESTIMATE                                                           NUMBER
 PGA_TARGET_FACTOR                                                                 NUMBER
 ADVICE_STATUS                                                                     VARCHAR2(3)
 BYTES_PROCESSED                                                                   NUMBER
 ESTD_TIME                                                                         NUMBER
 ESTD_EXTRA_BYTES_RW                                                               NUMBER
 ESTD_PGA_CACHE_HIT_PERCENTAGE                                                     NUMBER
 ESTD_OVERALLOC_COUNT                                                              NUMBER


Nothing unusual or esoteric here, however it appears that interpreting the provided advice can be confusing. Looking first at the contents of V$SGA_TARGET_ADVICE let’s see what Oracle reports:

SQL> select * From v$sga_target_advice
  2  /

---------- --------------- ------------ ------------------- -------------------
       520              .5        65793              5.8208             2579079
       780             .75        23911              2.1155             2068005
      1040               1        11303                   1             1134459
      1300            1.25         9162               .8106              970643
      1560             1.5         9098               .8049              970643
      1820            1.75         9058               .8014              970643
      2080               2         9058               .8014              970643

7 rows selected.


Based on resource usage since startup this view provides information on SGA memory settings that may improve performance if adopted. The key element in any of these views is the size factor, with a size factor of 1 being the current setting. Looking at the above output it’s possible that physical reads can be reduced a small amount if sga_target is increased from 1040 MB to 1300 MB; any further increase to sga_target will show no improvement. This is proven by the ESTD_PHYSICAL_READS column, the value of which stays constant for an SGA sized at 1300 MB or greater. In addition the ESTD_DB_TIME_FACTOR doesn’t improve much at greater SGA sizes indicating that further increases provide no real benefit. Ideally the goal is to find the setting providing substantial benefit and use it; the problem appears to be ‘where do I stop looking?’ With the above output we stopped looking for improvement at the first entry providing the lowest estimated physical reads. All entries below that provided no additional benefit.

If sga_target and sga_max_size are set diffently (to allow some degree of dynamic SGA management) it may not be necessary to restart the database since sga_target may be able to be set without adjusting sga_max_size. This view may, however, provide an SGA setting that requires both sga_target and sga_max_size be adjusted, and in that case the database will need to be restarted.

In a similar fashion the V$PGA_TARGET_ADVICE view can also be used to ‘fine tune’ overall PGA settings for the database:

SQL> select * From v$pga_target_advice;

----------------------- ----------------- --- --------------- ---------- ------------------- ----------------------------- --------------------
               73400320              .125 ON       7.4905E+10   37173880          1395450880                            98                   88
              146800640               .25 ON       7.4905E+10   36716537           456747008                            99                    6
              293601280                .5 ON       7.4905E+10   36643991           307845120                           100                    0
              440401920               .75 ON       7.4905E+10   36494007                   0                           100                    0
              587202560                 1 ON       7.4905E+10   36494007                   0                           100                    0
              704643072               1.2 ON       7.4905E+10   36494007                   0                           100                    0
              822083584               1.4 ON       7.4905E+10   36494007                   0                           100                    0
              939524096               1.6 ON       7.4905E+10   36494007                   0                           100                    0
             1056964608               1.8 ON       7.4905E+10   36494007                   0                           100                    0
             1174405120                 2 ON       7.4905E+10   36494007                   0                           100                    0
             1761607680                 3 ON       7.4905E+10   36494007                   0                           100                    0

----------------------- ----------------- --- --------------- ---------- ------------------- ----------------------------- --------------------
             2348810240                 4 ON       7.4905E+10   36494007                   0                           100                    0
             3523215360                 6 ON       7.4905E+10   36494007                   0                           100                    0
             4697620480                 8 ON       7.4905E+10   36494007                   0                           100                    0

14 rows selected.


Again it’s the target factor that identifies the current setting; working toward larger target factors may show improvement in the PGA cache hit percentage which means processes will see more optimized (one-pass) reads. Like the V$SGA_TARGET_ADVICE view the method is to find a setting that provides improvement. In this example the PGA setting is already optimal (the cache hit percentage is 100 and 0 extra bytes will need to be read). Not every database will be so lucky; this view is provided to allow the DBA to make as few changes as possible to achieve improved performance and throughput, eliminating the need for trial and error PGA management. In this case the database will need to be restarted after the parameter values have been changed.

To restate the methodology of using these two views:

1.	Find the row referencing the current setting; this will be the row where the size factor is equal to 1
2.	Scan the data for the entry just before the improvement estimates no longer change; this will be the setting
	Oracle has calculated to provide the most benefit
3.	Implement that setting, restarting the database, if necessary, to effect the change

In a similar fashion when using Automatic Memory Management, by setting memory_target and memory_max_target, the V$MEMORY_TARGET_ADVICE view can be used to tune the overall memory settings. Output from this view looks like this:

SQL> select * From v$memory_target_advice;

----------- ------------------ ------------ ------------------- ---------- ----------
        392                .25       143586              1.0063          0          0
        784                 .5       142687                   1          0          0
       1176                .75       142687                   1          0          0
       1568                  1       142687                   1          0          0
       1960               1.25       142687                   1          0          0
       2352                1.5       142687                   1          0          0
       2744               1.75       142687                   1          0          0
       3136                  2       142687                   1          0          0

8 rows selected.


The same methodology is used here, and if memory_max_target is larger than the suggested memory_target value the setting can be changed without restarting the database. Like the SGA settings if memory_max_target needs to also be adjusted this will require a database restart. Note that if you’re using Linux and hugepages using AMM is not supported.

Managing database memory settings doesn’t need to be a gruelling task of ‘try this, see if it works, it doesn’t so try another setting and repeat’. Using the provided memory advisors can make the DBA’s job much easier. Hopefully this has made that task easier, freeing the DBA to address more pressing issues.

Sometimes it’s good to take things under advisement.

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Blog at

%d bloggers like this: