流同步機制優化（一）

流同步機制建立已經快要一年半了，原本執行五、六個小時的過程，目前執行時間已經需要十多個小時左右。如果不對其進行優化，將會影響正常業務。

首先需要定位最主要的問題，經過檢查發現，流的應用階段佔用的時間並不長，和以前相比也沒有很明顯的變化。而主要的時間消耗在一個檢查流執行狀態的SQL上。

SQL> EXPLAIN PLAN FOR
2 SELECT DISTINCT (A.CKPT_SCN)
3 FROM SYSTEM.LOGMNR_RESTART_CKPT$ A
4 WHERE A.CKPT_SCN <= :1
5 AND A.VALID = 1
6 AND EXISTS
7 (
8 SELECT *
9 FROM SYSTEM.LOGMNR_LOG$ L
10 WHERE A.CKPT_SCN BETWEEN L.FIRST_CHANGE# AND L.NEXT_CHANGE#
11 )
12 ORDER BY A.CKPT_SCN DESC
13 ;

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | SORT UNIQUE | | | | |
| 2 | NESTED LOOPS | | | | |
| 3 | TABLE ACCESS FULL | LOGMNR_RESTART_CKPT$ | | | |
| 4 | TABLE ACCESS FULL | LOGMNR_LOG$ | | | |
------------------------------------------------------------------------------

Note: rule based optimization, PLAN_TABLE' is old version

12 rows selected.

從執行計劃上可以看出，NESTED LOOP連線且兩個表均為全表掃描，等價於一個笛卡兒積。

而且兩張表的資料量都不小：

SQL> SELECT COUNT(*) FROM SYSTEM.LOGMNR_RESTART_CKPT$;

COUNT(*)
----------
4120512

SQL> SELECT COUNT(*) FROM SYSTEM.LOGMNR_LOG$;

COUNT(*)
----------
10230

SQL> SELECT 4120512*10230 FROM DUAL;

4120512*10230
-------------
4.2153E+10

正是由於大資料量加上很不理想的執行計劃，導致這個SQL成為整個流同步機制的瓶頸。目前，這個SQL的執行已經需要八、九個小時。

這個SQL是一個流狀態檢查過程的一部分，是METALINK上提供的。懷疑作者在寫這個SQL的時候沒有考慮效能的因素，或者沒有考慮資料量增長的情況。

現在問題變得比較簡單了，只需要優化這個SQL就可以了。

簡單分析之後，存在以下幾種辦法：

一、去掉這部分SQL語句：由於是狀態檢查SQL，和流同步本身關係不是很大，直接去掉也不會造成太大的影響。不過，這個狀態檢查資訊還是比較有用的，只要是有優化的可能，還是儘可能保留，這只是最後迫不得已的辦法。

二、優化這個查詢：

優化查詢也有很多方案：比如重寫這個查詢，改變優化模式為CBO等。

這個查詢已經很簡單了，而且查詢連線條件都不是單純的等於連線，所以重寫查詢的意義不大。由於沒有可用的索引，改變優化模式的意義也不大，最多是將NESTED LOOP改變為MERGE JOIN。

目前主要問題是執行計劃的問題，兩張表進行NESTED LOOP，且都是全表掃描。如果建立合適的索引是的索引掃描來替代全表掃描，應該可以較大的提高效能。

但是對SYSTEM使用者的資料字典表建立索引，仍然是一個相對比較危險的動作。

三、減少資料量

這兩張表的資料庫目前很大，尤其是SYSTEM.LOGMNR_RESTART_CKPT$表，資料量已經達到了400萬，而這張表記錄的是自STREAM建立以來所有的CHECKPOINT資訊。通過減少表中資料量的方法，同樣可以使得查詢速度提高。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4227/viewspace-69353/，如需轉載，請註明出處，否則將追究法律責任。