ORACLE 10G Flash Back recommendations

Flashback Tips

The following tips and restrictions apply to using flashback features.

Flashback tips–performance

• For better performance, generate statistics on all tables involved in a Flashback Query DBMS_STATS by using the package, and Ke EP The statistics current. Flashback Query always uses the Cost-based optimizer, which relies on these statistics.

  For better performance, use Dbms_stats to gather statistics on all the tables to be applied in the flashback query and keep the information up to date. Flashback queries always use cost-based optimization, which relies on these statistics.

• The performance of a query into the past depends on how much undo data must be accessed. For better performance, the use queries to select small sets of the past data using indexes, not to scan entire tables. If You must does a full table scan, consider adding a parallel hint to the query.

  A query to the past depends on how much of the undo data needs to be accessed. To get better performance, use the index to query. If you have to do a full table scan, consider adding a parallel prompt query

• The performance cost in I/O is the cost of paging in data and undo blocks, which is not already in the buffer cache. The performance cost in CPU usage is the cost of applying undo information to affected data blocks. When operating on changes in the recent past, flashback features essentially CPU bound.

  The I/O performance cost is the cost of distributing data and the undo block is not already in the buffer cache block. The cost of the data block that the CPU uses for performance costs will undo information. When operating in recent past changes, the Flashback feature is basically CPU bound.

• Use index structures to Flashback Version query:the database keeps undo data for index changes as well as data changes. Performance of index lookup-based Flashback Version Query is an order of magnitude faster than the full table scans that a Re otherwise needed.

  Use the Flashback version query for the index structure: The database holds the index and the database is in Undo. Index lookup performance-based flashback version queries are an order of magnitude faster than others that require a full table scan.

• In a Flashback Transaction Query, the type of the xid column is RAW(8) . To take advantage of the index built on xid the column and use the HEXTORAW conversion function: HEXTORAW(xid) .

  In a flashback transaction query, the column name XID property is raw, and the XID column is used to index it, which can be converted with the Hextoraw function

• Flashback query against a materialized view does not take advantage of query rewrite optimizations.

  Flashback query materialized view is not conducive to query rewrite optimization

  See Also:

  Oracle Database Performance Tuning Guide

Flashback tips–general

• Use the package DBMS_FLASHBACK or other flashback features? Use ENABLE / DISABLE calls DBMS_FLASHBACK to the package around SQL code "do not control," or when you want to use the same past Time for several consecutive queries. Use Flashback query, Flashback Version query, or Flashback Transaction the query for SQL that you write, for convenience. A Flashback query, for example, was flexible enough to does comparisons and store results in a single Query.

• To obtain the SCN to use later with a flashback feature, use DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER .

• You can compute or retrieve a past time to use in a query by using a function return value as a timestamp or SCN argument. For example, you can perform date and time calculations by adding or subtracting a INTERVAL value to the value SYSTIMESTAMP of the function.

• You can query locally or remotely (Flashback query, Flashback Version query, or Flashback Transaction query). For example this is a remote Flashback Query:

  (SELECT * FROM [email protected]_remote_host         as of TIMESTAMP (Systimestamp-interval ' MINUTE);

  Flashback query, Flashback thing query, Flash back version query can be used for local or remote

• To ensure database consistency, always perform a COMMIT or ROLLBACK operation before querying past data.

  To ensure consistency of the database, always perform a commit or rollback before querying the data before the operation.

• Remember that all flashback processing are done using the current session settings, such as national language and character Set, not the settings this were in effect at the time being queried.

  Remember that all flashback is done by using the settings of the current session, such as the national language and character set, without being affected by the query at that time.

• Some DDLs that alter the structure of a table, such as drop/modify column, move table, drop partition, and truncate table/ Partition, invalidate any existing undo data for the table. It is not possible to retrieve data from a point earlier than the time such ddls were executed. Trying such a query results in error ORA-1466. This restriction does does apply to DDL operations that alter the storage attributes of a table, such as PCTFREE , INITRANS and MAXTRANS.

  Some operations that alter the structure of a table, such as deleting/modifying a column table, moving, deleting a partition, and truncating a table/partition, are not valid for any undo data on this table. It is not possible to restore a DLL operation from earlier than this point in time, and if this action occurs, a ora-1466 error will be reported. This restriction does not apply to the storage properties of DDL operation change tables, such as Pctfree,initrans, and Maxtrans.

• Use the SCN to query past data at a precise time. If you use a timestamp, the actual time queried might is up to 3 seconds earlier than the time you specify. Internally, Oracle Database uses Scns; These is mapped to timestamps at a granularity of every 3 seconds.

  Use a SCN to query past data at an exact time. If you use a timestamp, the real-time query may be up to 3 seconds longer than you specify. Internally, the Oracle database uses the SCN, which is mapped to a timestamp of granularity of every 3 seconds.

  For example, assume the SCN values and 1005 is mapped to the Times 8:41 and 8:46 AM respectively. A query for a time between 8:41:00 and 8:45:59 AM are mapped to SCN 1000; A Flashback Query for 8:46 AM was mapped to SCN 1005.

  For example, assume that the specified SCN values 1000 and 1005 map to times 41 8:46 a.m. and 8 point 46. A 8:41:00 and 8:45:59 are the time between queries mapped to SCN 1000; one is a 8:46 flashback query mapped to SCN 1005.

  Due to the TIME-TO-SCN mapping, if you specify a time that's slightly after a DDL operation (such as a table creation) T The He database might actually use an SCN, which is just before the DDL operation. This can result in error ORA-1466.

  On the time and SCN mappings, if you specify a time after the DDL operation the actual SCN is used before the DDL operation, then it is possible that the result will produce a ora-1466 operation

• You cannot retrieve past data from a v$ view in the data dictionary. Performing a query on such a view all returns the current data. You can, however, perform queries on past data in other views of the data dictionary, such as user_tables .

  You cannot retrieve historical data from a view in the data dictionary view. The query executed on the view is the current data returned. You can, however, perform a query on the data dictionary view for other past data, such as User_tables