Monitoring and Tuning Oracle Flashback Capabilities

Determining the Current Flashback Database Window
At any given time, the earliest point in time to which you can actually rewind your database by using Flashback Database can be determined by querying the V$FLASHBACK_DATABASE_LOG view as shown in this example:

SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME
FROM V$FLASHBACK_DATABASE_LOG;


Monitoring Flashback Logs
Monitoring the flashback logs can be monitored to help understand the overhead and the nature of flashback log generation.

SQL> select * from v$flashback_database_stat ;


Determine Transactional Flashback

Definitions
Current Undo Size – Current space for the undo tablespace in MB

Current undo_retention Setting – value of undo_retention database initialization parameter

Optimal undo_retention Setting – current capability of undo retention in seconds based on undo transactional activity and current undo size.

Desired undo_retention Setting – value in seconds to meeting undo/transactional flashback requirements

Steps to Gather Data for undo/transactional Flashback
1. Locate/Create see_optimal_undo_retention.
See Appendix A: see_optimal_undo_retention.sql

2. Execute see_optimal_undo_retention.sql
SQL> @see_optimal_undo_retention.sql

3. Enter Desired Undo Retention in Seconds
259200 -- 72 hours or 3 days in seconds

4. Evaluate output results

Output from see_optimal_undo_retention.sql
Enter Desired Undo Retention in Seconds ->259200
old 2: v_desired_undo_retention NUMBER := &desired_undo_retention ;
new 2: v_desired_undo_retention NUMBER := 259200 ;

Current undo Size --> 16912MB
Current undo_retention Setting --> 25200 Seconds
Optimal undo_retention Setting --> 221129.88 Seconds
Desired undo_retention Setting --> 259200 Seconds
Undo Size for desired retention --> 10021.36MB


Evaluating output results from see_optimal_undo_retention.sql

Is the current undo_retention optimal?
To evaluate if the current undo_retention is being met and see if undo_retention can be increased look at the current undo_retention setting and the optimal undo_retention_setting over several executions over a period of time. If the optimal undo_retention_setting is consistently more then the current undo_retention_setting then undo_retention can be increased.

Can the database meet the desired undo_retention?
The main consideration from the output is boiled down to the current value of the desired undo_retention setting and the optimal undo retention setting. When the optimal undo retention setting is lower then the desired undo_retention setting it means that the database is not able to meet the desired retention. Evaluate the undo size for desired retention to determine the undo tablespace size require to minimally meet the desired undo_retention.


Monitor Undo Retention

With undo_retention set we need to ensure that the undo_retention is being met by the database. Therefore the undo_retention capability must be consistently monitored.

1. Locate/create verify_undo_retention_target.sql
See Appendix B: verify_undo_retention_target.sql

2. Execute the verify_undo_retention.sql script
SQL> @verify_undo_retention_target


3. Evaluate output from script

Example Output when Undo Retention is being met
SQL> @verify_undo_retention_target

Example Output when Undo Retention is no being met
SQL> @verify_undo_retention_target
Undo Retention Target can not be Met! Undo Adjustments Needed!
---------------------------------------------------------------
Current undo Size --> 30MB
Current undo_retention Setting --> 2000 Seconds
Current undo_retention Capable --> 1106.1 Seconds
Undo Size for desired retention --> 54.24MB




Performance Tuning for Flashback Database

Changed blocks are written from memory to the flashback logs at relatively infrequent, regular intervals, to limit processing and I/O overhead.

To achieve good performance for large production databases with Flashback Database enabled, Oracle recommends:


· Use a fast file system for your flash recovery area, preferably without operating system file caching. Files the database creates in the flash recovery area, including flashback logs, are typically large. Operating system file caching is typically not effective for these files, and may actually add CPU overhead for reading from and writing to these files. Thus, it is recommended to use a file system that avoids operating system file caching, such as ASM, or the Solaris 2.8 file system with direct I/O.

· Configure enough disk spindles for the file system that will hold the flash recovery area. For large production databases, multiple disk spindles may be needed to support the required disk throughput for the database to write the flashback logs effectively.


· If the storage system used to hold the flash recovery area does not have non-volatile RAM, try to configure the file system on top of striped storage volumes, with a relatively small stripe size such as 128K. This will allow each write to the flashback logs to be spread across multiple spindles, improving performance


· For large, production databases, set the init.ora parameter LOG_BUFFER to be at least 8MB. This makes sure the database allocates maximum memory (typically 16MB) for writing flashback database logs.


The overhead of turning on logging for Flashback Database depends on the read-write mix of the database workload. The more write-intensive the workload, the higher the overhead caused by turning on logging for Flashback Database. (Queries do not change data and thus do not contribute to logging activity for Flashback Database.)




Appendix A: see_optimal_undo_retention.sql

---------------------------------------------------------------------------------
-- Script: see_optimal_undo_retention.sql
-- Author: Michael Messina
--
-- 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 ;
/


Appendix B: verify_undo_retention_target.sql

---------------------------------------------------------------------------------
-- Script: verify_undo_retention_target.sql
-- Author: Michael Messina

--
-- Description: Will calculate based on undo utilization and undo size
-- the optimal value for undo_retention and compare against
-- current undo_retention value.
---------------------------------------------------------------------------------
set serveroutput on size 1000000
set feedback off

DECLARE
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#;

-- Calculate 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) ;

if v_current_undo_retention > v_optimal_undo_retention then
v_undo_size_desired_ret := ((v_block_size * v_undo_blocks_per_sec) * (v_current_undo_retention)) / 1024
/ 1024 ;
DBMS_OUTPUT.PUT_LINE ('Undo Retention Target can not be Met! Undo Adjustments Needed!') ;
DBMS_OUTPUT.PUT_LINE ('---------------------------------------------------------------') ;
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) ' S
econds') ;
DBMS_OUTPUT.PUT_LINE (' Current undo_retention Capable --> ' TO_CHAR(ROUND(v_optimal_undo_retention,2
)) ' Seconds') ;
DBMS_OUTPUT.PUT_LINE ('Undo Size for desired retention --> ' TO_CHAR(ROUND(v_undo_size_desired_ret,2)
) 'MB') ;
end if ;

END ;
/