oracle 的執行計劃經常會遭遇繫結變數偷窺問題，導致執行計劃變更，帶來效能問題

同事發在內網的一個帖子轉這裡了。

感謝分享。

SQL執行計劃變更導致資料庫負載突升,讓我們措手不及,有沒有好的處理辦法呢?

讓我們來查查這個語句的歷史執行資訊,看看是否發生變化,何時發生了變化.

如果發生了變化,找出以前的執行計劃,與當前的執行計劃進行對比,有什麼不同.

oracle 10G中我們可以透過下面的三個檢視查詢到語句的歷史執行資訊.

DBA_HIST_SQL_PLAN DBA_HIST_SQLSTAT DBA_HIST_SNAPSHOT

我在生產庫sunha5上做個測試,查詢it商城的sql:

1.查詢當前在活動的會話獲得SQL_ID值

1SYS AS SYSDBA at v880 >select USERNAME,SQL_ID from v$session where status='ACTIVE' AND SCHEMA#>0;
2 
3USERNAME                       SQL_ID
4------------------------------ -------------
5CYP_NW_APP                     dxx8pvcttf5qv
6PRODUCT_PUB                    5c53uzwqswhtb

我們可以獲得一個sql_id='dxx8pvcttf5qv'

2.獲得此sql_id對應的sql語句

1	select sql_id,sql_fulltext from v$sql where sql_id='dxx8pvcttf5qv';

從查詢結果sql_fulltext,我們可以獲得sql語句.

3.查詢此sql_id歷史執行資訊

01select a.INSTANCE_NUMBER,
02      a.snap_id,
03      a.sql_id,
04      a.plan_hash_value,
05      b.begin_interval_time
06from dba_hist_sqlstat a, dba_hist_snapshot b
07where sql_id = 'dxx8pvcttf5qv'
08  and a.snap_id = b.snap_id
09order by instance_number, snap_id;
10 
11INSTANCE_NUMBER    SNAP_ID SQL_ID        PLAN_HASH_VALUE BEGIN_INTERVAL_TIME
12---------------
 ---------- ------------- --------------- 
---------------------------------------------------------------------------
13              1      17370 dxx8pvcttf5qv      2125777269 24-6月 -10 11.00.44.900 上午
14              1      17371 dxx8pvcttf5qv      2125777269 24-6月 -10 12.00.46.879 下午
15              1      17372 dxx8pvcttf5qv      2125777269 24-6月 -10 01.00.48.962 下午
16              1      17373 dxx8pvcttf5qv      1904478120 24-6月 -10 02.00.50.872 下午
17              1      17374 dxx8pvcttf5qv      1904478120 24-6月 -10 03.00.52.840 下午
18              1      17375 dxx8pvcttf5qv      1904478120 24-6月 -10 04.00.54.780 下午

擷取了一段查詢資訊,可以看到sql歷史執行資訊中,在6月24日時執行計劃有變更.

我們具體查檢視變更前後的執行計劃有什麼區別.

4.查詢變更前後的執行計劃

01select sql_id,
02        plan_hash_value,
03        id,
04        operation,
05        options,
06        object_owner,
07        object_name,
08        depth,
09        cost,
10        timestamp
11   from DBA_HIST_SQL_PLAN
12  where sql_id = 'dxx8pvcttf5qv'
13  and plan_hash_value in (1904478120,2125777269);
14 
15SQL_ID        PLAN_HASH_VALUE  ID OPERATION            OPTIONS    OBJECT_OWN OBJECT_NAME     DEPTH   COST TIMESTAMP
16dxx8pvcttf5qv 1904478120 11 TABLE ACCESS BY INDEX ROWID CYP_NW_APP ENT_PRODUCT 11 14780 2010-06-24 14-14-36
17dxx8pvcttf5qv 1904478120 12 INDEX RANGE SCAN CYP_NW_APP IDX_ENT_PRODUCT_2 12 14767 2010-06-24 14-14-36
18dxx8pvcttf5qv 1904478120 13 TABLE ACCESS BY INDEX ROWID CYP_NW_APP ENT_USER 9 1 2010-06-24 14-14-36
19dxx8pvcttf5qv 1904478120 14 INDEX UNIQUE SCAN CYP_NW_APP SYS_C00124956 10 0 2010-06-24 14-14-36
20 
21SQL_ID        PLAN_HASH_VALUE  ID OPERATION            OPTIONS    OBJECT_OWN OBJECT_NAME     DEPTH   COST TIMESTAMP
22dxx8pvcttf5qv 2125777269 11 TABLE ACCESS FULL CYP_NW_APP ENT_PRODUCT 11 21329 2010-06-17 03-14-15
23dxx8pvcttf5qv 2125777269 12 TABLE ACCESS BY INDEX ROWID CYP_NW_APP ENT_USER 9 1 2010-06-17 03-14-15
24dxx8pvcttf5qv 2125777269 13 INDEX UNIQUE SCAN CYP_NW_APP SYS_C00124956 10 0 2010-06-17 03-14-15

我們從查詢結果中可以看到變更前後執行計劃除了一個索引不同外,其他都一樣
plan_hash_value = 1904478120 ---此執行計劃多走一個索引IDX_ENT_PRODUCT_2
plan_hash_value = 2125777269

5.根據執行計劃的不同點查詢原因

1	select * from dba_objects where object_name='IDX_ENT_PRODUCT_2';

從索引'IDX_ENT_PRODUCT_2'的資訊中看到, last_ddl_time='2010-06-24 14-01-56' ,應該是這個原因導致執行計劃的改變.

我上面是舉個例子,當資料庫突然有異常sql時,排除程式更新的原因,我們可以按照這個思路去查詢異常sql的執行計劃是否變更.

oracle 執行計劃變更

相關文章