SQL計劃指令是Oracle12c中自適應查詢最佳化的功能之一。SQL計劃指令就像“額外的提醒” ，用以提醒最佳化器你先前選擇了的計劃並不是最優的，典型的是因為錯誤的勢評估。錯誤的勢評估往往是由統計資訊缺失，統計資訊陳舊，複雜的謂詞或操作等引起的。SQL計劃指令和SQL輪廓（SQL profiles）不同，後者是針對特定語句的，而SQL計劃指令和查詢表示式關聯，因此，它們可以被多個包含匹配查詢表示式的語句共享。柱狀圖或擴充套件統計資訊缺失也許會導致SQL計劃指令的產生。

資料庫在內部自動管理SQL計劃指令。像自動重最佳化的情況也許會引起SQL計劃指令被寫入SGA且稍後寫到SYSAUX表空間，此時，可以透過DBA_SQL_PLAN_DIRECTIVES和DBA_SQL_PLAN_DIR_OBJECTS檢視來檢視SQL計劃指令。此外，已存在的SQL計劃指令也能透過DBMS_SPD包來儲存。

1. 設定

確信共享池已經被清空，因此，我們知道在一個乾淨的系統上進行測試。

CONN sys@pdb1 AS SYSDBA

ALTER SYSTEM FLUSH SHARED_POOL;

下面的測試表包含指示省和市存在的列。這些列上有檢查約束，以便告訴最佳化器這些列被允許的值，此外，這些列上還有索引。透過包括10行A省和90行B省的樣本來模擬傾斜嚴重的資料。

CONN test/test@pdb1

DROP TABLE tab1 PURGE;

CREATE TABLE tab1 (

id NUMBER,

Province VARCHAR2(10),

city VARCHAR2(10),

CONSTRAINT tab1_pk PRIMARY KEY (id),

CONSTRAINT tab1_prov_chk CHECK (province IN ('A', 'B')),

CONSTRAINTtab1_city_chk CHECK (city IN ('X', 'Y'))

);

INSERT /*+ APPEND */ INTO tab1

SELECT level, 'A', 'X'

FROM dual

CONNECT BY level <= 10;

COMMIT;

INSERT /*+ APPEND */ INTO tab1

SELECT 10+level, 'B', 'Y'

FROM dual

CONNECT BY level <= 90;

COMMIT;

CREATE INDEX tab1_prov_idx ON tab1(province);

CREATE INDEX tab1_city_idx ON tab1(city);

EXEC DBMS_STATS.gather_table_stats(USER,'TAB1');

無論資料都沒傾斜，當用預設設定收集統計資訊時，不會為這些列建立柱狀圖。

COLUMN column_name FORMAT A20

SELECT column_id,

column_name,

histogram

FROM user_tab_columns

WHERE table_name = 'TAB1'

ORDER BY column_id;

COLUMN_ID COLUMN_NAME HISTOGRAM

---------- -----------------------------------

1 ID NONE

2 PROVINCE NONE

3 CITY NONE

SQL>

2. 顯示錯誤的勢評估

大家都知道，所有省和市都是關聯的。由於最佳化器並不知道這種關聯關係，並且也沒柱狀圖來標示資料的傾斜，接著最佳化器會利用現有資訊盡力為謂詞的選擇性做出評估。於是，它會假設資料均勻分佈在各允許值間且一半行被標為A省且一半被標為B省。如果在兩個列之間沒任何關聯，預期100/2/2=25行被標為即是A省和X市。下列查詢查出所有符合該規則的行，且顯示返回資料的執行計劃，包括預期的和實際的勢。

CONN test/test@pdb1

SELECT /*+ GATHER_PLAN_STATISTICS */

*

FROM tab1

WHERE province= 'A'

AND city= 'X';

SET LINESIZE 200 PAGESIZE 100

SELECT * FROMTABLE(DBMS_XPLAN.display_cursor(format => 'allstats last'));

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------------------------------

SQL_ID 5t8y8p5mpb99j, child number 0

-------------------------------------

SELECT /*+ GATHER_PLAN_STATISTICS */ *FROM tab1 WHERE province

= 'A' AND city = 'X'

Plan hash value: 1552452781

-----------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 4 |

|* 1| TABLE ACCESS BY INDEX ROWID BATCHED|TAB1 | 1 | 25 | 10 |00:00:00.01 | 4 |

|* 2| INDEX RANGE SCAN | TAB1_PROV_IDX | 1 | 50 | 10 |00:00:00.01 | 2 |

-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified byoperation id):

---------------------------------------------------

1 -filter("CITY"='X')

2 -access("PROVINCE"='A')

SQL>

正如預期的那樣，最佳化器得到了一個錯誤的勢評估。這是一個經典的案例，其中，柱狀圖，或擴充套件統計資訊，或兩者存在將有助於最佳化器能更精確的對勢做出評估。

下列查詢檢查V$SQL檢視的IS_REOPTIMIZABLE列，看最佳化器是否已注意到錯誤的勢評估。

CONN sys@pdb1 AS SYSDBA

COLUMN sql_text FORMAT A40

COLUMN is_reoptimizable FORMAT A16

SELECT sql_text, is_reoptimizable

FROM v$sql

WHERE sql_id = '5t8y8p5mpb99j';

SQL_TEXT IS_REOPTIMIZABLE

---------------------------------------- ----------------

SELECT /*+ GATHER_PLAN_STATISTICS */

*FROM tab1 WHERE province = 'A' AN

D city= 'X'

SQL>

由於該語句被標作可重新最佳化的，因此，SQL計劃指令也很可能已經被建立了。

3. 顯示SQL計劃指令

至此，最佳化器已經把SQL計劃指令寫入了SGA，但是我們還不能看到它們，因為它們還沒被寫入SYSAUX表空間。

CONN sys@pdb1 AS SYSDBA

SET LINESIZE 200

COLUMN dir_id FORMAT A20

COLUMN owner FORMAT A10

COLUMN object_name FORMAT A10

COLUMN col_name FORMAT A10

SELECT TO_CHAR(d.directive_id) dir_id,o.owner, o.object_name,

o.subobject_name col_name, o.object_type, d.type, d.state, d.reason

FROM dba_sql_plan_directives d, dba_sql_plan_dir_objects o

WHERE d.directive_id=o.directive_id

AND o.owner = 'TEST'

ORDER BY 1,2,3,4,5;

no rows selected

SQL>

我們可以等資料庫把SQL計劃指令儲存，或用DBMS_SPD包手工儲存。

CONN sys@pdb1 AS SYSDBA

EXEC DBMS_SPD.flush_sql_plan_directive;

現在我們執行前面的查詢，將會看到SQL計劃指令。

CONN sys@pdb1 AS SYSDBA

SET LINESIZE 200

COLUMN dir_id FORMAT A20

COLUMN owner FORMAT A10

COLUMN object_name FORMAT A10

COLUMN col_name FORMAT A10

SELECT TO_CHAR(d.directive_id) dir_id,o.owner, o.object_name,

o.subobject_name col_name, o.object_type, d.type, d.state, d.reason

FROM dba_sql_plan_directives d, dba_sql_plan_dir_objects o

WHERE d.directive_id=o.directive_id

AND o.owner = 'TEST'

ORDER BY 1,2,3,4,5;

DIR_ID OWNER OBJECT_NAM COL_NAME OBJECTTYPE STATE REASON

-------------------- ---------- -------------------- ------ ---------------- ----------------------------------------------

12422623998396966202 TEST TAB1 PROVINCE COLUMN DYNAMIC_SAMPLING USABLE SINGLE TABLE CARDINALITY MISESTIMATE

12422623998396966202 TEST TAB1 TABLE DYNAMIC_SAMPLING USABLE SINGLE TABLE CARDINALITY MISESTIMATE

SQL>

4. 顯示SQL計劃指令的使用

下列查詢是剛才我們產生SQL計劃指令的那個。注意，勢評估發生了改變，且在DBMS_XPLAN輸出末尾出現了提醒。

CONN test/test@pdb1

SELECT /*+ GATHER_PLAN_STATISTICS */

*

FROM tab1

WHERE province= 'A'

AND city= 'X';

SET LINESIZE 200 PAGESIZE 100

SELECT * FROMTABLE(DBMS_XPLAN.display_cursor(format => 'allstats last'));

PLAN_TABLE_OUTPUT

-----------------------------------------------------------------------------------------------------------------

SQL_ID 5t8y8p5mpb99j, child number 1

-------------------------------------

SELECT /*+ GATHER_PLAN_STATISTICS */ *FROM tab1 WHERE province

= 'A' AND city = 'X'

Plan hash value: 1552452781

-----------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------

| 0 |SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 4 |

|* 1| TABLE ACCESS BY INDEX ROWID BATCHED|TAB1 | 1 | 10 | 10 |00:00:00.01 | 4 |

|* 2| INDEX RANGE SCAN | TAB1_PROV_IDX | 1 | 10 | 10 |00:00:00.01 | 2 |

-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified byoperation id):

---------------------------------------------------

1 -filter("CITY"='X')

2 -access("PROVINCE"='A')

Note

-----

-dynamic statistics used: dynamic sampling (level=2)

-statistics feedback used for this statement

- 1Sql Plan Directive used for this statement

SQL>

5. SQL 計劃指令和統計資訊

SQL計劃指令典型是一個“丟失資訊” 的指示，但這些“丟失的資訊”也許將來會變大的可用。如果我們重新收集表TAB1的統計資訊，我們會注意到存在傾斜資料的列已被收集了統計資訊。

EXEC DBMS_STATS.gather_table_stats(USER,'TAB1');

SELECT column_id,

column_name,

histogram

FROM user_tab_columns

WHERE table_name = 'TAB1'

ORDER BY column_id;

COLUMN_ID COLUMN_NAME HISTOGRAM

---------- -----------------------------------

1 ID NONE

2 PROVINCE FREQUENCY

3 CITY FREQUENCY

SQL>

如果收集了擴充套件統計資訊，可用下面的語句檢視它們。

COLUMN extension FORMAT A30

SELECT extension_name, extension

FROM user_stat_extensions;

一旦需要的柱狀圖或擴充套件資訊存在，或如果替代SQL計劃指令被建立，則該SQL計劃指令也許就會切換到一個替代狀態而不再被使用。

CONN sys@pdb1 AS SYDBA

SELECT state, COUNT(*)

FROM dba_sql_plan_directives

GROUP BY state

ORDER BY state;

STATE COUNT(*)

---------- ----------

SUPERSEDED 26

USABLE 44

SQL>

預設的，SQL計劃指令超過53周不被使用就會被刪除。而某些SQL計劃指令會對列組統計資訊的收集有影響。

6. DBMS_SPD包

DBMS_SPD包可以在一定程度上控制SQL計劃指令。

可以用以下過程來進行基本的SQL計劃指令管理。

1) FLUSH_SQL_PLAN_DIRECTIVE：將SGA中的SQL計劃指令存入SYSAUX表空間。

2) ALTER_SQL_PLAN_DIRECTIVE：更改確定SQL計劃指令的ENABLED和AUTO_DROP屬性。

3) DROP_SQL_PLAN_DIRECTIVE：刪除確定的SQL計劃指令。

SQL計劃指令不使用而被保留的最長時間透過 SPD_RETENTION_WEEKS確定，預設為53周。這可以用如下過程顯示和修改。

1) GET_PREFS ：顯示確定的偏好（preference）。目前只支援SPD_RETENTION_WEEKS。

2) SET_PREFS ：設定確定的偏好(preference)。目前只支援SPD_RETENTION_WEEKS。

SQL計劃執行的長期儲存和遷移可以用如下過程。

1) CREATE_STGTAB_DIRECTIVE：建立一個將SQL計劃指令匯出到的中間表。

2) PACK_STGTAB_DIRECTIVE：將SQL計劃指令匯出到指定表。

3) UNPACK_STGTAB_DIRECTIVE：從中間表匯入SQL計劃指令。

DBMS_SPD包的很多功能需要ADMINISTER SQL MANAGEMENTOBJECT許可權。

7. 註釋：

有時SQL計劃指令內部管理機制看起來有些混亂。測試期間，我遇到過本來希望它被建立但卻沒有。也遇到過本希望存在的SQL計劃指令標作被替代狀態但卻沒有。開始我認為這可能是因為我忘了儲存，但情況似乎並非如此。

記得查詢 DBA_SQL_PLAN_DIRECTIVES和DBA_SQL_PLAN_DIR_OBJECTS檢視前總是執行DBMS_SPD.FLUSH_SQL_PLAN_DIRECTIVE過程。

Oracle12c中SQL最佳化（SQL TUNING）新特性之SQL計劃指令

相關文章