ORACLE 10G 閃回建議

標籤：http   使用   os   io   資料   for   ar   art   

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 [email protected]_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