Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation







 Oracle Memory Advisors (SGA and PGA)
Oracle Tips by Burleson Consulting

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by the top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

These advisors are straightforward. There is some debate as to the validity of the findings they produce, both in a tabular format showing increase or decrease in reads as memory is increased or decreased. The tabular/graph-like data can be queried directly via canned scripts. An example of what the SGA Advisor generates in a graph is shown in Figure 8.8.

Figure 8.8:  Memory Advice

Sample queries for SGA target advice include those which replicate what a graph shows and what the history has been at a certain size (does the SGA size advice vary during the day?) . In this example, it can be seen that the advice has held steady at 508MB for the snapshots shown.


select snap_id, sga_size

from dba_hist_sga_target_advice

 where sga_size_factor = 1

order by snap_id asc



---------- ----------

         4        508

         5        508

         6        508

         7        508

         8        508

         9        508

        10        508

        11        508


The relevant data dictionary views have ADVICE in them and it is a trivial matter to query those based on a snapshot ID to see what the advice was at that time.

Undo Advisor

The Automated Undo Advisor is also simple to access and query. This advisor evaluates the space needed to ensure a guaranteed retention time. The retention time comes into play when considering flashback. Since flashback goes back in time, a sufficient amount of undo space, which supports the amount of time, needs to be set aside for the undo tablespace. Undo data cannot be overwritten in order for flashback to work.

Figure 8.9:  Undo Tablespace Growth Line

Calculating the current optimal setting can be done via a query like that below:


col "ACTUAL UNDO SIZE [MByte]" for 999999999

col "UNDO RETENTION [Sec]" for a20

col "OPTIMAL UNDO RETENTION [Sec]" for 999999999

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MB]",

       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",

       (TO_NUMBER(e.value) * TO_NUMBER(f.value) *

       g.undo_block_per_sec) / (1024*1024)


  FROM (

       SELECT SUM(a.bytes) undo_size

         FROM v$datafile a,

              v$tablespace b,

              dba_tablespaces c

        WHERE c.contents = 'UNDO'

          AND c.status = 'ONLINE'

          AND = c.tablespace_name

          AND a.ts# = b.ts#

       ) d,

      v$parameter e,

       v$parameter f,


       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))


         FROM v$undostat

       ) g

 WHERE = 'undo_retention'

  AND = 'db_block_size';


The results for a small system running on a laptop:



--------------------- -------------------- ---------------------

                  190 900                                43.5234375 

MTTR  Advisor

The MTTR Advisor (Mean Time To Recover Advisor) is enabled when two parameters are set to certain values: STATISTICS_LEVEL and FAST_START_MTTR_TARGET. The required values for STATISTICS_LEVEL are TYPICAL or ALL. When the FAST_START_MTTR_TARGET is set to a non-zero value, combined with STATISTICS_LEVEL being appropriately set, the advisor is started.


Several other parameters should not be set because they will interfere with mechanisms used to meet the value or setting of FAST_START_MTTR_TARGET.  These other parameters are:




The possible settings for FAST_START_MTTR_TARGET range between 0 and 3600 seconds. Any setting above 3600 defaults back to 3600.


Due to what the parameter represents, the two-phase instance recovery process of roll forward and roll back, some amount of time is going to be spent on startup to apply committed transactions not yet written to datafiles and to roll back the uncommitted ones. Therefore, a setting of 0 and obviously anything less than that is not realistic. Whether the time is 10 seconds, 30 seconds, or 2 minutes, examples depend, to a degree, on how the redo logs are sized.


So, in conjunction with the redo log size and the time it takes for the database to start up regardless of what the logs have to deal with, a query from V$INSTANCE_RECOVERY can be used to calibrate the optimal or practical time for the mean time to recovery.  The results of the following query show the system is pretty much calibrated.






----------- --------------

         37             35


Another ADVICE type view, V$MTTR_TARGET_ADVICE, shows statistics and advisories collected by this advisor. If the advisor is not started, the following graph will not appear under the Instance Recovery section of the Recovery Settings page in OEM.


Figure 8.10:   MTTR Advice


For more details on Oracle utilities, see the book "Advanced Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.

You can buy it direct from the publisher for 30% off directly from Rampant TechPress.

Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software










BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.