Oracle 曆史SQL語句執行計畫的對比與分析

    基於CBO最佳化器的環境中，SQL執行計畫的產生依賴於統計資訊的真實與完整。如列的離散度，列上的長條圖，索引的可用性，索引上的聚簇因子。當這些資訊是真實完整的情況下，CBO最佳化器通常都可以制定最優的執行計畫。也正因此CBO最佳化器也靈活，難以控制，任一資訊的不真實或缺失都可能導致執行計畫發生變化而產生多個版本。經常碰到的情形是之前的某個SQL語句前陣子還不是TOP SQL，而最近變成了TOP SQL。或者說之前儘管是TOP SQL但，但最近盡然成了TOP 1。對於此情形，我們可以比對SQL語句的曆史執行計畫進行分析是何種原因導致SQL變慢或執行計畫發生變化。下面通過例子來類比SQL執行計畫變異的情形。
  
1、建立示範環境

--示範環境scott@SYBO2SZ> select * from v$version where rownum<2;BANNER----------------------------------------------------------------Oracle Database 10g Release 10.2.0.3.0 - 64bit Production--建立1000000萬記錄的表scott@SYBO2SZ> @cr_big_tbcheck total rows  for big_table====================================  COUNT(*)----------   1000000--為表建立索引scott@SYBO2SZ> create index i_big_tb_owner on big_table(owner);sys@SYBO2SZ> conn / as sysdba;sys@SYBO2SZ> select snap_id from dba_hist_snapshot order by snap_id;   SNAP_ID----------        30        31--清除awr的記錄,shared pool及buffer cache        sys@SYBO2SZ> exec dbms_workload_repository.drop_snapshot_range(30,31);sys@SYBO2SZ> alter system flush shared_pool;sys@SYBO2SZ> alter system flush buffer_cache;--清除dba_hist_sql_plan視圖,實際上清除wrh$_sql_plan,wrh$_sqltext,wrh$_sqlstatsys@SYBO2SZ> truncate table wrh$_sql_plan;--清除dba_hist_sql_sqltext以及dba_hist_sqlstat視圖sys@SYBO2SZ> truncate table wrh$_sqltext;sys@SYBO2SZ> truncate table wrh$_sqlstat;sys@SYBO2SZ> select count(*) from dba_hist_sql_plan;  COUNT(*)----------         0sys@SYBO2SZ> select count(*) from dba_hist_sqltext;  COUNT(*)----------         0

2、產生曆史SQL及其執行計畫

sys@SYBO2SZ> conn scott/tigerscott@SYBO2SZ> select count(*) from big_table where owner='GOEX_ADMIN';  COUNT(*)----------     43560scott@SYBO2SZ> @my_last_sqlADDRESS          HASH_VALUE SQL_ID        COMMAND_TYPE      PIECE SQL_TEXT---------------- ---------- ------------- ------------ ---------- ---------------------------------------------------------000000007B9BB7D0  243468085 4hqyjwh7861tp            3          0 select count(*) from big_table where owner='GOEX_ADMIN'--從awr中查詢sql的執行計畫，由於沒有產生快照，所以無其執行計畫scott@SYBO2SZ> @sql_plan_disp_awrEnter value for input_sqlid: 4hqyjwh7861tpno rows selected--建立快照scott@SYBO2SZ> exec dbms_workload_repository.create_snapshot();PL/SQL procedure successfully completed.--查看SQL的曆史執行計畫scott@SYBO2SZ> @sql_plan_disp_awrEnter value for input_sqlid: 4hqyjwh7861tpPLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------SQL_ID 4hqyjwh7861tp--------------------select count(*) from big_table where owner='GOEX_ADMIN'Plan hash value: 334839806------------------------------------------------------------------------------------| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |------------------------------------------------------------------------------------|   0 | SELECT STATEMENT  |                |       |       |   139 (100)|          ||   1 |  SORT AGGREGATE   |                |     1 |    17 |            |          ||   2 |   INDEX RANGE SCAN| I_BIG_TB_OWNER | 10073 |   167K|   139   (0)| 00:00:02 |------------------------------------------------------------------------------------

3、產生不同的曆史SQL並對比執行計畫

--對錶big_table進行move操作scott@SYBO2SZ> alter table big_table move;--檢查其表上的索引，如下，索引已經失效scott@SYBO2SZ> @idx_infoEnter value for owner: scottEnter value for table_name: big_tableTABLE_NAME                INDEX_NAME          CL_NAM               CL_POS STATUS   IDX_TYP         DSCD------------------------- ------------------- -------------------- ------ -------- --------------- ----BIG_TABLE                 BIG_TABLE_PK        ID                        1 UNUSABLE NORMAL          ASCBIG_TABLE                 I_BIG_TB_OWNER      OWNER                     1 UNUSABLE NORMAL          ASC--再次執行與之前相同的SQL語句scott@SYBO2SZ> select count(*) from big_table where owner='GOEX_ADMIN';  COUNT(*)----------     43560scott@SYBO2SZ> @my_last_sqlADDRESS          HASH_VALUE SQL_ID        COMMAND_TYPE      PIECE SQL_TEXT---------------- ---------- ------------- ------------ ---------- ----------------------------------------------------------000000007B9BB7D0  243468085 4hqyjwh7861tp            3          0 select count(*) from big_table where owner='GOEX_ADMIN'--建立一個新的快照，使之成為曆史SQLscott@SYBO2SZ> exec dbms_workload_repository.create_snapshot();--查看SQL的執行計畫scott@SYBO2SZ> @sql_plan_disp_awrEnter value for input_sqlid: 4hqyjwh7861tpPLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------SQL_ID 4hqyjwh7861tp--------------------select count(*) from big_table where owner='GOEX_ADMIN'Plan hash value: 334839806------------------------------------------------------------------------------------| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |------------------------------------------------------------------------------------|   0 | SELECT STATEMENT  |                |       |       |   139 (100)|          ||   1 |  SORT AGGREGATE   |                |     1 |    17 |            |          ||   2 |   INDEX RANGE SCAN| I_BIG_TB_OWNER | 10073 |   167K|   139   (0)| 00:00:02 |------------------------------------------------------------------------------------SQL_ID 4hqyjwh7861tp--------------------select count(*) from big_table where owner='GOEX_ADMIN'Plan hash value: 599409829--------------------------------------------------------------------------------| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------------|   0 | SELECT STATEMENT   |           |       |       |  3221 (100)|          ||   1 |  SORT AGGREGATE    |           |     1 |    17 |            |          ||   2 |   TABLE ACCESS FULL| BIG_TABLE | 10073 |   167K|  3221   (1)| 00:00:39 |--------------------------------------------------------------------------------28 rows selected.--從上面的查詢結果可以看到，同一條曆史SQL語句有不同的plan_hash_value 以及使用了不同的執行計畫--最早的一個是走索引範圍掃描，一個是全表掃描--下面直接從dba_hist_sql_plan查看sql語句的執行計畫--該視圖記錄了所有被awr快照捕獲的所有曆史sql的執行計畫以及執行計畫的產生時間scott@SYBO2SZ> run sql_plan_his  1  SELECT id,  2           operation,  3           options,  4           object_name,  5           bytes,  6           cpu_cost,                -----> Author : Robinson  7           io_cost,                 -----> Blog   : http://blog.csdn.net/robinson_0612  8           timestamp  9      FROM dba_hist_sql_plan 10     WHERE sql_id = '&input_sql_id' 11* ORDER BY timestamp,idEnter value for input_sql_id: 4hqyjwh7861tp ID OPERATION                 OPTIONS       OBJECT_NAME            BYTES   CPU_COST    IO_COST TIMESTAMP--- ------------------------- ------------- ----------------- ---------- ---------- ---------- -----------------  0 SELECT STATEMENT                                                                           20130517 11:23:20  1 SORT                      AGGREGATE                               17                       20130517 11:23:20  2 INDEX                     RANGE SCAN    I_BIG_TB_OWNER        171241    1789880        139 20130517 11:23:20  0 SELECT STATEMENT                                                                           20130517 11:27:16  1 SORT                      AGGREGATE                               17                       20130517 11:27:16  2 TABLE ACCESS              FULL          BIG_TABLE             171241  325825194       3203 20130517 11:27:166 rows selected.

4、修正SQL執行計畫

--如前面可知，由於索引不可用導致了SQL語句執行了全表掃描。--事實上導致全表掃描的問題很多，若使用謂詞列函數，謂詞列資料類型轉換，使用不等於，以及謂詞列參與計算等，不一一列出--針對上面的情形，我們應當收集統計資訊以及重建索引scott@SYBO2SZ> exec dbms_stats.gather_table_stats('SCOTT','BIG_TABLE',cascade=>true);BEGIN dbms_stats.gather_table_stats('SCOTT','BIG_TABLE',cascade=>true); END;*ERROR at line 1:ORA-20000: index "SCOTT"."BIG_TABLE_PK"  or partition of such index is in unusable stateORA-06512: at "SYS.DBMS_STATS", line 13182ORA-06512: at "SYS.DBMS_STATS", line 13202ORA-06512: at line 1--上面再收集統計資訊時，提示索引不可用，需要先rebulidscott@SYBO2SZ> alter index i_big_tb_owner rebuild nologging;scott@SYBO2SZ> alter index big_table_pk rebuild nologging;scott@SYBO2SZ> exec dbms_stats.gather_table_stats('SCOTT','BIG_TABLE',cascade=>true);--下面我們再次執行原SQL以及，由下可知，SQL已經使用了最優的執行計畫scott@SYBO2SZ> set autot trace exp;scott@SYBO2SZ> select count(*) from big_table where owner='GOEX_ADMIN';Execution Plan----------------------------------------------------------Plan hash value: 334839806------------------------------------------------------------------------------------| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |------------------------------------------------------------------------------------|   0 | SELECT STATEMENT  |                |     1 |     6 |   108   (1)| 00:00:02 ||   1 |  SORT AGGREGATE   |                |     1 |     6 |            |          ||*  2 |   INDEX RANGE SCAN| I_BIG_TB_OWNER | 44750 |   262K|   108   (1)| 00:00:02 |------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("OWNER"='GOEX_ADMIN')

5、後記
a、樣本中建立的big_table指令碼，請參考:Oracle 測試常用表BIG_TABLE
b、alter table move 方式用於實現段收縮，移動高水位，但不會釋放申請的空間，以及導致索引失效
c、對於曆史SQL語句，需要執行snapshot之後，才會被填充到DBA_HIST_SQL_PLAN、DBA_HIST_SQLSTAT、DBA_HIST_SNAPSHOT資料字典中
d、如果你的測試無法獲得曆史SQL語句及其執行計畫，通常是由於awr閥值設定所致，可參考：Oracle AWR 闕值影響曆史執行計畫
e、曆史SQL語句的執行計畫也可以通過$ORACLE_HOME/rdbms/admin/awrsqrpt.sql來產生txt或html檔案
f、引起同一SQL執行計畫發生變化的情形很多，如統計資訊的缺失，索引失效，不同層級的參數發生變化等
h、對於執行個體，會話，語句層級環境變化導致同一SQL執行計畫發變異，也可以對此跟蹤。參考:使用最佳化器效能檢視擷取SQL語句執行環境

 

更多參考

有關Oracle RAC請參考
     使用crs_setperm修改RAC資源的所有者及許可權
     使用crs_profile管理RAC資源設定檔
     RAC 資料庫的啟動與關閉
     再說 Oracle RAC services
     Services in Oracle Database 10g
     Migrate datbase from single instance to Oracle RAC
     Oracle RAC 串連到指定執行個體
     Oracle RAC 負載平衡測試(結合伺服器端與用戶端)
     Oracle RAC 伺服器端串連負載平衡(Load Balance)
     Oracle RAC 用戶端串連負載平衡(Load Balance)
     ORACLE RAC 下非預設連接埠監聽配置(listener.ora tnsnames.ora)
     ORACLE RAC 監聽配置 (listener.ora tnsnames.ora)
     配置 RAC 負載平衡與容錯移轉
     CRS-1006 , CRS-0215 故障一例 
     基於Linux (RHEL 5.5) 安裝Oracle 10g RAC
     使用 runcluvfy 校正Oracle RAC安裝環境

有關Oracle 網路設定相關基礎以及概念性的問題請參考：
     配置非預設連接埠的動態服務註冊
     配置sqlnet.ora限制IP訪問Oracle
     Oracle 監聽器日誌配置與管理
     設定 Oracle 監聽器密碼(LISTENER)
     配置ORACLE 用戶端串連到資料庫

有關基於使用者管理的備份和備份恢複的概念請參考
     Oracle 冷備份
     Oracle 熱備份
     Oracle 備份恢複概念
     Oracle 執行個體恢複
     Oracle 基於使用者管理恢複的處理
     SYSTEM 資料表空間管理及備份恢複
     SYSAUX資料表空間管理及恢複
     Oracle 基於備份控制檔案的恢複(unsing backup controlfile)

有關RMAN的備份恢複與管理請參考
     RMAN 概述及其體繫結構
     RMAN 配置、監控與管理
     RMAN 備份詳解
     RMAN 還原與恢複
     RMAN catalog 的建立和使用
     基於catalog 建立RMAN儲存指令碼
     基於catalog 的RMAN 備份與恢複
     RMAN 備份路徑困惑
     使用RMAN實現異機備份恢複(WIN平台)
     使用RMAN遷移檔案系統資料庫到ASM
     linux 下RMAN備份shell指令碼
     使用RMAN遷移資料庫到異機

有關ORACLE體繫結構請參考
     Oracle 資料表空間與資料檔案
     Oracle 密碼檔案
     Oracle 參數檔案
     Oracle 聯機重做記錄檔(ONLINE LOG FILE)
     Oracle 控制檔案(CONTROLFILE)
     Oracle 歸檔日誌
     Oracle 復原(ROLLBACK)和撤銷(UNDO)
     Oracle 資料庫執行個體啟動關閉過程
     Oracle 10g SGA 的自動化管理
     Oracle 執行個體和Oracle資料庫(Oracle體繫結構)