ORACLE 10G 闪回建议

时间：2023-03-08 17:12:21
ORACLE 10G 闪回建议

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 by using the DBMS_STATS package, and keep the statistics current. Flashback Query always uses the cost-based optimizer, which relies on these statistics.

    为了获得更好的性能，用dbms_stats收集统计所有在闪回查询中要应用的表并且保持这些信息最新。闪回查询总是使用基于成本的优化，这依赖于这些统计数据。

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

    一个查询到过去的取决于多少的undo数据需要访问。为了获得更好的性能，利用 索引来查询。如果你必须做一次全表扫描，可以考虑添加一个并行提示查询

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

    在I/O性能成本是分布数据的成本和undo块不是已经在缓冲区高速缓存块。在CPU使用性能成本将undo信息的数据块的成本的影响。操作时，在最近的过去的变化，闪回功能基本上CPU绑定。

  • Use index structures for 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 are otherwise needed.

    使用索引结构的闪回版本查询：数据库保存索引及数据库在 undo中。索引查找基于性能的闪回版本查询是一个数量级的速度比其他需要全表扫描要快。

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

    闪回事务查询 中,列名xid属性为RAW,利用 xid列建立索引,可以用hextoraw函数 转换

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

    闪回查询物化视图不利于查询重写优化

    See Also:

    Oracle Database Performance Tuning Guide

Flashback Tips – General

  • Use the DBMS_FLASHBACK package or other flashback features? Use ENABLE/DISABLE calls to the DBMS_FLASHBACK package around SQL code that you 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 Query for SQL that you write, for convenience. A Flashback Query, for example, is flexible enough to do comparisons and store results in a single query.

  • To obtain an 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 an INTERVAL value to the value of the SYSTIMESTAMP function.

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

    (SELECT * FROM employees@some_remote_host AS OF
    
        TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE);
    闪回查询,闪回事物查询,闪回版本查询可以用于本地或者是远程

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

    为了保证数据库的一致性，始终执行COMMIT或ROLLBACK操作前查询之前的数据。

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

    记住，所有的闪回操作是通过使用当前会话的设置，如国家语言和字符集，而不受当时被查询的影响。

  • 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 not apply to DDL operations that alter the storage attributes of a table, such as PCTFREE, INITRANS, and MAXTRANS.

    一些操作，改变表的结构，如删除/修改列的表，移动，删除分区，并截断表/分区，对于这张表上的任意undo数据是无效的。不可能从早于这个时间点恢复如dll操作,如果出现此操作,将会报ora-1466错误。该限制不适用于DDL操作变更表的存储属性，如pctfree，initrans，和maxtrans。

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

    使用一个SCN查询过去的数据在一个精确的时间。如果你使用一个时间戳，实时查询可能长达3秒，比您指定的时间。在内部，Oracle数据库使用SCN；这些被映射到时间戳的粒度为每3秒。

    For example, assume that the SCN values 1000 and 1005 are 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 is mapped to SCN 1000; a Flashback Query for 8:46 AM is mapped to SCN 1005.

    例如，假设指定SCN值1000和1005映射到次41 8:46上午和8点46。一种8:41:00和8:45:59是之间的时间查询映射到SCN 1000；一种是8:46闪回查询映射到SCN 1005。

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

    在时间和scn映射上,如果 你指定的时间在DDL操作之后实际使用的scn在DDL操作之前,那么有可能 结果会产生ora-1466操作

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

    你不能从一个视图在数据字典视图检索历史数据。对视图执行的查询是返回的当前数据。你可以，但是，执行对数据字典视图查询其他过去的数据，如user_tables

相关文章