Optimal Undo Tablespace Size and Undo Retention

The undo tablespace and its size is related to the retention period and the transactional activity of the database.  These must be regularly reviewed and check to ensure that 1, you are not trying to have a retention period that the undo tablespace can not handle, 2 that you are size properly so that you do not encounter the dreaded ORA-01555 snapshot too old.

Optimal Undo Size and Retention
I have seen many environments where the undo tablespace is far to small for the undo retention setting and they target undo retention the environment requires.  This is due to the fact that the undo tablespace was size and undo retention set, but never rechecked or updated as the environment grew or activity changed.  Therefore if the expectation is that a flashback query can go back 2 hours, but the tablespace is too small the reality is that they really can not go back 2 hours.

First we need to check the Optimal Undo Size based on our desired retention as in most environments this will change regularly based on increases or decreases in the trasnactional activity.

The script below will ask for the desired undo retention, then will examine your database and tell you the optimal undo retention setting for the current environment as the undo tablespace size and transactional activity indicates, but will also show you what the size of the undo tablespace needs to be to be able to support the desired undo retention you have entered.

---------------------------------------------------------------------------------
-- Script: see_optimal_undo_retention.sql
-- Author: Michael Messina, Management Consultant TUSC a Rolta Company
--
-- Description:  Will calculate based on undo utilization and undo size
--               the optimal value for undo_retention.
---------------------------------------------------------------------------------
set serveroutput on size 1000000
set feedback off

ACCEPT desired_undo_retention PROMPT "Enter Desired Undo Retention in Seconds ->"

DECLARE
   v_desired_undo_retention          NUMBER := &desired_undo_retention ;
   v_block_size                      NUMBER ;
   v_undo_size                       NUMBER ;
   v_undo_blocks_per_sec             NUMBER ;
   v_optimal_undo_retention          NUMBER ;
   v_current_undo_retention          NUMBER ;
   v_undo_size_desired_ret           NUMBER ;
  
BEGIN
   -- get the current undo retention setting
   select TO_NUMBER(value)
   INTO v_current_undo_retention
   FROM v$parameter
   WHERE name = 'undo_retention' ;
  
   -- Calculate Actual Undo Size
   SELECT SUM(a.bytes)
   INTO v_undo_size
   FROM v$datafile a,
        v$tablespace b,
        dba_tablespaces c
   WHERE c.contents = 'UNDO'
     AND c.status = 'ONLINE'
     AND b.name = c.tablespace_name
     AND a.ts# = b.ts#;

   -- Calcuate the Undo Blocks per Second
   SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
   INTO v_undo_blocks_per_sec
   FROM v$undostat ;

   -- Get the database block size
   SELECT TO_NUMBER(value)
   INTO v_block_size
   FROM v$parameter
   WHERE name = 'db_block_size';

   v_optimal_undo_retention := v_undo_size/(v_block_size * v_undo_blocks_per_sec) ;
   v_undo_size_desired_ret := ((v_block_size * v_undo_blocks_per_sec) * (v_desired_undo_retention)) / 1024 / 1024 ;
  
   DBMS_OUTPUT.PUT_LINE ('              Current undo Size --> ' || TO_CHAR(v_undo_size/1024/1024) || 'MB') ;
   DBMS_OUTPUT.PUT_LINE (' Current undo_retention Setting --> ' || TO_CHAR(v_current_undo_retention) || ' Seconds') ;
   DBMS_OUTPUT.PUT_LINE (' Optimal undo_retention Setting --> ' || TO_CHAR(ROUND(v_optimal_undo_retention,2)) || ' Seconds') ;
   DBMS_OUTPUT.PUT_LINE (' Desired undo_retention Setting --> ' || TO_CHAR(ROUND(v_desired_undo_retention,2)) || ' Seconds') ;
   DBMS_OUTPUT.PUT_LINE ('Undo Size for desired retention --> ' || TO_CHAR(ROUND(v_undo_size_desired_ret,2)) || 'MB') ;
END ;
/

Example Execution of script:
SQL> @see_optimal_undo_retention.sql
Enter Desired Undo Retention in Seconds ->7200
old   2:    v_desired_undo_retention          NUMBER := &desired_undo_retention ;
new   2:    v_desired_undo_retention          NUMBER := 7200 ;
Current undo Size --> 1980MB
Current undo_retention Setting --> 900 Seconds
Optimal undo_retention Setting --> 100504.96 Seconds
Desired undo_retention Setting --> 7200 Seconds
Undo Size for desired retention --> 141.84MB

In this example on a very small database with no activity I want a retention of 2 hours, The output shows the current undo tablespace size, the current undo retention setting, the optimal undo retention setting indicates what I could make the undo retention based on the current activity and size of the undo tablespace, the desired undo retention that I entered and the undo tablespace size required to meet my desired undo retention setting. 

SQL Performance Analyzer, Step 2 Create Analysis Task, Make the Changes and Review Impact

I started this with the goal of being able to measure the impact of partitioning a table or set of tables.  The first Step is to get all the SQL I can from the system that executes against the table(s) that are intended to be partitioned so that we can ensure that we make a positive impact on the SQL and ensure that we are not going to make a negative impact on any existing SQL.

In this case I am choosing to use SQL Performance Analyzer to measure the impact on the SQL I gathered into my SQL Tuning Set  from

** Keep in mind I am using a test environment where I can capture the SQL, execute and measure my changes all in the same database, in same cases you may need to capture the SQL tuning set in production and then transfer the SQL Tuning Set to a test environment to execute the analysis make the changes and then make the comparison.  See Transfer SQL Tuning Sets between Databases post from October 2010.

Create an Analysis Task

In this case I will be using the dbms_sqlpa package using the create_analysis_task which will allow me to analyze the SQL in my SQL Tuning Set which is the SQL I captured that executes against the table(s) I am planning on partitioning.

dbms_sqlpa.create_analysis_task(sqlset_name => ‘STS_SPA_1‘,

task_name => ‘sts_spa1_task‘,

description => ‘test partitioning‘);

Execute the Analysis Task

Once the Analysis Task has been created we will need to execute the task to create the baseline that will be used to compare against after we make the changes.  We will be using the dbms_sqlpa package and the execute_analysis_task procedure.

dbms_sqlpa.execute_analysis_task(task_name => ‘STS_SPA1_task‘,

execution_type => ‘test execute‘,

execution_name => ‘before_partition_change‘);

Once the execution of the analysis task is complete we can make our changes.  Once the change has been made then we will need to execute another analysis task to get the information to compare against the base after the change.

dbms_sqlpa.execute_analysis_task(task_name => ‘STS_SPA1_task‘,

execution_type => ‘test execute‘,

execution_name => ‘after_partition_change‘);

Compare Analysis Task
Once the initial analysis task is done, the change is made the post analysis task is done we are read yo compare the initial to the after.  Again we will use the dbms_sqlpa package, this time we will use the execute_analysis task again but will set the analysis to compare performance.

dbms_sqlpa.execute_analysis_task(

task_name => ‘STS_SPA1_task‘,

execution_type => ‘compare performance‘,

execution_name => ‘analysis_results‘,

execution_params => dbms_advisor.arglist(‘execution_name1‘,‘before_partition_change‘,

‘execution_name2‘, ‘after_partition_change‘,

‘comparison_metric‘,‘buffer_gets‘));

Run Compare Report
Once the Compare Analysis is complete we can run a report that will show us the results of the compare analysis.  The follow short script will output to a file called SPA_COMPARE_REPORT.out 3 possible reports, the first being the full analysis report, the second being a summary report and the 3 being like a top offenders report called a finding report from the analysis.

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

-- Script: spa_compare.sql

-- Author; Michael R. Messina, Managing Consultant 

-- Rolta-TUSC, Infrastructure Services

--

-- Executes a SQL Tuning Set Comparison and then outputs a report

-- from Performance Analyzer. 

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

spool SPA_COMPARE_REPORT.out

-- Get the whole report for the single statement case.

SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('STS_SPA1_task') from dual;

-- Show me the summary for the sts case.

SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('STS_SPA1_task', 'TEXT', 'TYPICAL', 'SUMMARY')

FROM DUAL;

-- Show me the findings for the statement I'm interested in.

SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('STS_SPA1_task', 'TEXT', 'TYPICAL', 'FINDINGS', 5) from dual;

spool off

SQL Performance Analyzer, Step 1 Create a SQL Tuning Set

I was dealing with a performance issue for a particular application and wanted to partition the table then measure the impact of the partitioning based on the the SQL uses that table.  This got me thinking how could make use of SQL performance Analyzer for this that way I could have the before and after analysis.

First step would be to gather the SQL that executes against the table from the live production system.  We can do this by creating a SQL tuning set and putting that SQL into that SQL tuning set.  We would base this SQL tuning set on tuning set from the cache over a period time to capture the SQL into our SQL tuning set for the SQL that executes on the table. 

Create a SQL Tuning Set

We can use the DBMS_SQLTUNE package to create the SQL tuning set with the SQL that executes against the table.  For example if we want to capture the SQL execute against the table for a 24 hour period, take a sample at 5 minute intervals it would looks like the following.  We create a SQL Tuning Set called STS_SPA_1, then setup the capture of the SQL Tuning Set to come from the cursor cache over a the 24 hour period for just the SQL that contains ORDER for the ORDERS and ORDERS_ITEMS tables.

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

-- Script: spa_create_sts.sql

-- Author; Michael R. Messina, Managing Consultant 

-- Rolta-TUSC, Infrastructure Services

--

-- Setup a SQL Tuning Set to create a for the SQL

-- This will serve SQL Performance Analyzer well or allow us to create profiles.  

-- From there we can make generate before and after pictures

-- Then evaluate the impact of changes.

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

--

set feedback on

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

-- Set up the SQL Set and what sql we want to make

-- part of the sql tuning set

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

BEGIN

    -- Create the sql set

    DBMS_SQLTUNE.CREATE_SQLSET(sqlset_name => 'STS_SPA_1');

    -- Limit the sql in the set to Just on the ORDERS and ORDER_ITEMS

    -- The CAPTURE_CURSOR_CACHE_SQLSET will collect SQL statements

    -- over a peeriod of time from the cursor cache.  This helps build a more realistic

    -- set of SQL load from the system by capturing the live SQL for 24 hours at 5 min

    -- intervals

    DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET(

         sqlset_name => 'STS_SPA_1'

        ,basic_filter=> q'#UPPER(sql_text) LIKE '%ORDER%' #'

        ,time_limit  => 86400

        ,repeat_interval => 300

    );

END;

/

** I would show the steps for Grid Control except the blog would never save with the Grid 

Images. 

The next Step will be to use the SQL Tuning Set to compare a before and after our changes, which I will be doing with SQL Performance Analyzer.  To do this we Create an analysis task..... SQL Performance Analyzer, Step 2 Create Analysis Task