Manually Evolve and Accept SQL PLAN BASELINES in Oracle 12c
這裡將介紹如何使用命令列來evolve sql plan baselines。為了evolve一個特定的sql執行計劃執行以下操作:
1.建立一個evolve任務
2.設定evolve任務引數
3.執行evolve任務
4.實現任務中給出的建議
5.顯示任務執行的結果
下面將舉例來說明，假設滿足以下條件
.資料庫沒有啟用自動evolve任務
.對下面的查詢建立一個SQL Plan Baseline
SELECT /* q2_group_by */ prod_name, sum(quantity_sold) FROM products p, sales s WHERE p.prod_id = s.prod_id AND p.prod_category_id =204 GROUP BY prod_name;
.想要建立兩個索引來提高查詢語句的效能，如果使用索引的效能比SQL Plan Baseline中的當前執行計劃的效能好那麼就evolve該執行計劃
為了evolve一個特定的執行計劃需要執行以下操作
1.執行初始化設定操作
清空共享池與緩衝區快取
SQL> ALTER SYSTEM FLUSH SHARED_POOL; System altered. SQL> ALTER SYSTEM FLUSH BUFFER_CACHE; System altered.
啟用自動捕獲SQL Plan Baselines
SQL> ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true; System altered. SQL> show parameter sql_plan NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_capture_sql_plan_baselines boolean TRUE optimizer_use_sql_plan_baselines boolean TRUE
以sh使用者登入到資料庫，然後設定SQLPLUS的顯示引數
[oracle@jytest1 ~]$ sqlplus sh/sh@jypdb SQL*Plus: Release 12.2.0.1.0 Production on Thu Feb 14 23:30:09 2019 Copyright (c) 1982, 2016, Oracle. All rights reserved. Last Successful login time: Thu Feb 14 2019 23:01:23 +08:00 Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> SET PAGES 10000 LINES 140 SQL> SET SERVEROUTPUT ON SQL> COL SQL_TEXT FORMAT A20 SQL> COL SQL_HANDLE FORMAT A20 SQL> COL PLAN_NAME FORMAT A30 SQL> COL ORIGIN FORMAT A12 SQL> SET LONGC 60535 SQL> SET LONG 60535 SQL> SET ECHO ON
2.執行SQL語句，因此可以自動捕獲它
執行下面的SQL語句
SQL> SELECT /* q1_group_by */ prod_name, sum(quantity_sold) 2 FROM products p, sales s 3 WHERE p.prod_id = s.prod_id 4 AND p.prod_category_id =203 5 GROUP BY prod_name; PROD_NAME SUM(QUANTITY_SOLD) -------------------------------------------------- ------------------ Envoy External 6X CD-ROM 11526 Model SM26273 Black Ink Cartridge 15910 PCMCIA modem/fax 28800 baud 19278 Multimedia speakers- 3" cones 10969 Internal 8X CD-ROM 11197 Deluxe Mouse 11609 Model CD13272 Tricolor Ink Cartridge 12321 Model NM500X High Yield Toner Cartridge 6466 18" Flat Panel Graphics Monitor 4415 External 8X CD-ROM 13886 SIMM- 8MB PCMCIAII card 17544 PCMCIA modem/fax 19200 baud 20467 Envoy External 8X CD-ROM 14850 Envoy External Keyboard 2857 External 6X CD-ROM 11732 Model A3827H Black Image Cartridge 17314 Internal 6X CD-ROM 8533 17" LCD w/built-in HDTV Tuner 4874 SIMM- 16MB PCMCIAII card 14191 Multimedia speakers- 5" cones 10419 Standard Mouse 8714 21 rows selected.
查詢資料字典確認在SQL Plan Baseline中不存在執行計劃，因為只有重複執行的SQL語句才會被捕獲
SQL> SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, 2 ACCEPTED, FIXED, AUTOPURGE 3 FROM DBA_SQL_PLAN_BASELINES 4 WHERE SQL_TEXT LIKE '%q1_group%'; no rows selected
再次執行SQL語句
SQL> SELECT /* q1_group_by */ prod_name, sum(quantity_sold) 2 FROM products p, sales s 3 WHERE p.prod_id = s.prod_id 4 AND p.prod_category_id =203 5 GROUP BY prod_name; PROD_NAME SUM(QUANTITY_SOLD) -------------------------------------------------- ------------------ Envoy External 6X CD-ROM 11526 Model SM26273 Black Ink Cartridge 15910 PCMCIA modem/fax 28800 baud 19278 Multimedia speakers- 3" cones 10969 Internal 8X CD-ROM 11197 Deluxe Mouse 11609 Model CD13272 Tricolor Ink Cartridge 12321 Model NM500X High Yield Toner Cartridge 6466 18" Flat Panel Graphics Monitor 4415 External 8X CD-ROM 13886 SIMM- 8MB PCMCIAII card 17544 PCMCIA modem/fax 19200 baud 20467 Envoy External 8X CD-ROM 14850 Envoy External Keyboard 2857 External 6X CD-ROM 11732 Model A3827H Black Image Cartridge 17314 Internal 6X CD-ROM 8533 17" LCD w/built-in HDTV Tuner 4874 SIMM- 16MB PCMCIAII card 14191 Multimedia speakers- 5" cones 10419 Standard Mouse 8714 21 rows selected.
3.查詢資料字典來確保執行計劃已經被載入到SQL Plan Baseline中了，下面的查詢顯示執行計劃已經被接受，這意味著執行計劃已經儲存在SQL Plan Baselines中了。origin列顯示為AUTO-CAPTURE，這意味著執行計劃是被自動捕獲的
SQL> SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, 2 ORIGIN, ENABLED, ACCEPTED, FIXED 3 FROM DBA_SQL_PLAN_BASELINES 4 WHERE SQL_TEXT LIKE '%q1_group%'; SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC FIX -------------------- -------------------- ------------------------------ ------------ --- --- --- SQL_07f16c76ff893342 SELECT /* q1_group_b SQL_PLAN_0gwbcfvzskcu242949306 AUTO-CAPTURE YES YES NO y */ prod_name, sum( quantity_sold) FROM products p, sal es s WHERE p.prod_id = s. prod_id AND p.prod_category_ id =203 GROUP BY prod_name
4.下面對SQL語句進行解析並驗證最佳化器是否會使用SQL Plan Baseline中的執行計劃
SQL> EXPLAIN PLAN FOR 2 SELECT /* q1_group_by */ prod_name, sum(quantity_sold) 3 FROM products p, sales s 4 WHERE p.prod_id = s.prod_id 5 AND p.prod_category_id =203 6 GROUP BY prod_name; Explained. SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null, null, 'basic +note')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 3535171836 ------------------------------------------ | Id | Operation | Name | ------------------------------------------ | 0 | SELECT STATEMENT | | | 1 | HASH GROUP BY | | | 2 | HASH JOIN | | | 3 | TABLE ACCESS FULL | PRODUCTS | | 4 | PARTITION RANGE ALL| | | 5 | TABLE ACCESS FULL | SALES | ------------------------------------------ Note ----- - SQL plan baseline "SQL_PLAN_0gwbcfvzskcu242949306" used for this statement 16 rows selected.
從執行計劃的Note部分可以看到SQL Plan Baseline已經應用到這個SQL語句了
5.建立兩個索引用來提高上面SQL語句的效能
SQL> CREATE INDEX ind_prod_cat_name ON products(prod_category_id, prod_name, prod_id); Index created. SQL> CREATE INDEX ind_sales_prod_qty_sold ON sales(prod_id, quantity_sold); Index created.
6.再次執行SQL語句，因為啟用了自動捕獲功能，所以新的執行計劃會被載入到SQL Plan Baseline中
SQL> SELECT /* q1_group_by */ prod_name, sum(quantity_sold) 2 FROM products p, sales s 3 WHERE p.prod_id = s.prod_id 4 AND p.prod_category_id =203 5 GROUP BY prod_name; PROD_NAME SUM(QUANTITY_SOLD) -------------------------------------------------- ------------------ Envoy External 6X CD-ROM 11526 Model SM26273 Black Ink Cartridge 15910 PCMCIA modem/fax 28800 baud 19278 Multimedia speakers- 3" cones 10969 Internal 8X CD-ROM 11197 Deluxe Mouse 11609 Model CD13272 Tricolor Ink Cartridge 12321 Model NM500X High Yield Toner Cartridge 6466 18" Flat Panel Graphics Monitor 4415 External 8X CD-ROM 13886 SIMM- 8MB PCMCIAII card 17544 PCMCIA modem/fax 19200 baud 20467 Envoy External 8X CD-ROM 14850 Envoy External Keyboard 2857 External 6X CD-ROM 11732 Model A3827H Black Image Cartridge 17314 Internal 6X CD-ROM 8533 17" LCD w/built-in HDTV Tuner 4874 SIMM- 16MB PCMCIAII card 14191 Multimedia speakers- 5" cones 10419 Standard Mouse 8714 21 rows selected.
7.查詢資料字典來確保新的執行計劃被載入到SQL Plan Baseline中了
SQL> SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED
2 FROM DBA_SQL_PLAN_BASELINES
3 WHERE SQL_HANDLE IN ('SQL_07f16c76ff893342')
4 ORDER BY SQL_HANDLE, ACCEPTED;
SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC
-------------------- -------------------- ------------------------------ ------------ --- ---
SQL_07f16c76ff893342 SELECT /* q1_group_b SQL_PLAN_0gwbcfvzskcu20135fd6c AUTO-CAPTURE YES NO
y */ prod_name, sum(
quantity_sold)
FROM products p, sal
es s
WHERE p.prod_id = s.
prod_id
AND p.prod_category_
id =203
GROUP BY prod_name
SQL_07f16c76ff893342 SELECT /* q1_group_b SQL_PLAN_0gwbcfvzskcu242949306 AUTO-CAPTURE YES YES
y */ prod_name, sum(
quantity_sold)
FROM products p, sal
es s
WHERE p.prod_id = s.
prod_id
AND p.prod_category_
id =203
GROUP BY prod_name
上面的查詢結果顯示新的執行計劃是為被接受的。
8.再次解析SQL語句並驗證最佳化器是不是使用原始沒有索引的執行計劃
SQL> EXPLAIN PLAN FOR 2 SELECT /* q1_group_by */ prod_name, sum(quantity_sold) 3 FROM products p, sales s 4 WHERE p.prod_id = s.prod_id 5 AND p.prod_category_id =203 6 GROUP BY prod_name; Explained. SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null, null, 'basic +note')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 3535171836 ------------------------------------------ | Id | Operation | Name | ------------------------------------------ | 0 | SELECT STATEMENT | | | 1 | HASH GROUP BY | | | 2 | HASH JOIN | | | 3 | TABLE ACCESS FULL | PRODUCTS | | 4 | PARTITION RANGE ALL| | | 5 | TABLE ACCESS FULL | SALES | ------------------------------------------ Note ----- - SQL plan baseline "SQL_PLAN_0gwbcfvzskcu242949306" used for this statement 16 rows selected.
上面的Note部分指示最佳化器使用了原始的沒有索引的執行計劃
9.以管理員使用者登入資料庫，然後建立一個evolve任務它包含未被接受執行計劃相關的所有SQL語句
[oracle@jytest1 ~]$ sqlplus sys/xxzx7817600@jypdb as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Thu Feb 14 23:48:51 2019 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> VARIABLE cnt NUMBER SQL> VARIABLE tk_name VARCHAR2(50) SQL> VARIABLE exe_name VARCHAR2(50) SQL> VARIABLE evol_out CLOB SQL> EXECUTE :tk_name := DBMS_SPM.CREATE_EVOLVE_TASK(sql_handle => 'SQL_07f16c76ff893342',plan_name => 'SQL_PLAN_0gwbcfvzskcu20135fd6c'); PL/SQL procedure successfully completed. SQL> SELECT :tk_name FROM DUAL; :TK_NAME -------------------------------------------------------------------------------------------------------------------------------- TASK_11 10.執行evolve任務 SQL>EXECUTE :exe_name :=DBMS_SPM.EXECUTE_EVOLVE_TASK(task_name=>:tk_name); PL/SQL procedure successfully completed. SQL>SELECT :exe_name FROM DUAL; :EXE_NAME --------------------------------------------------------------------------- EXEC_1
11.檢視報告
EXECUTE :evol_out := DBMS_SPM.REPORT_EVOLVE_TASK( task_name=>:tk_name,
execution_name=>:exe_name );
SELECT :evol_out FROM DUAL;
GENERAL INFORMATION SECTION
--------------------------------------------------------------------------
Task Information:
---------------------------------------------
Task Name : TASK_11
Task Owner : SYS
Execution Name : EXEC_1
Execution Type : SPM EVOLVE
Scope : COMPREHENSIVE
Status : COMPLETED
Started : 02/15/2019 17:49:32
Finished : 02/15/2019 17:49:35
Last Updated : 02/15/2019 17:49:35
Global Time Limit : 2147483646
Per-Plan Time Limit : UNUSED
Number of Errors : 0
---------------------------------------------------------------------------
SUMMARY SECTION
---------------------------------------------------------------------------
Number of plans processed : 1
Number of findings : 1
Number of recommendations : 1
Number of errors : 0
---------------------------------------------------------------------------
DETAILS SECTION
---------------------------------------------------------------------------
Object ID : 2
Test Plan Name : SQL_PLAN_0gwbcfvzskcu20135fd6c
Base Plan Name : SQL_PLAN_0gwbcfvzskcu242949306
SQL Handle : SQL_07f16c76ff893342
Parsing Schema : SH
Test Plan Creator : SH
SQL Text : SELECT /*q1_group_by*/ prod_name,
sum(quantity_sold)
FROM products p, sales s
WHERE p.prod_id=s.prod_id AND p.prod_category_id=203
GROUP BY prod_name
Execution Statistics:
-----------------------------
Base Plan Test Plan
---------------------------- ------------------------
Elapsed Time (s): .044336 .012649
CPU Time (s): .044003 .012445
Buffer Gets: 360 99
Optimizer Cost: 924 891
Disk Reads: 341 82
Direct Writes: 0 0
Rows Processed: 4 2
Executions: 5 9
FINDINGS SECTION
---------------------------------------------------------------------------
Findings (1):
-----------------------------
1. The plan was verified in 2.18 seconds. It passed the benefit criterion
because its verified performance was 2.01 times better than that of the
baseline plan.
Recommendation:
-----------------------------
Consider accepting the plan. Execute
dbms_spm.accept_sql_plan_baseline(task_name => 'TASK_11', object_id => 2,
task_owner => 'SYS');
EXPLAIN PLANS SECTION
---------------------------------------------------------------------------
Baseline Plan
-----------------------------
Plan Id : 1
Plan Hash Value : 1117033222
---------------------------------------------------------------------------
| Id| Operation | Name | Rows | Bytes |Cost | Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 21 | 861 | 924 | 00:00:12|
| 1 | HASH GROUP BY | | 21 | 861 | 924 | 00:00:12|
| *2| HASH JOIN | |267996|10987836 | 742 | 00:00:09|
| *3| TABLE ACCESS FULL | PRODUCTS | 21 | 714 | 2 | 00:00:01|
| 4 | PARTITION RANGE ALL | |918843| 6431901 | 662 | 00:00:08|
| 5 | TABLE ACCESS FULL | SALES |918843| 6431901 | 662 | 00:00:08|
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("P"."PROD_ID"="S"."PROD_ID")
* 3 - filter("P"."PROD_CATEGORY_ID"=203)
Test Plan
-----------------------------
Plan Id : 2
Plan Hash Value : 20315500
---------------------------------------------------------------------------
|Id| Operation | Name | Rows | Bytes | Cost| Time |
---------------------------------------------------------------------------
| 0|SELECT STATEMENT | | 21| 861| 891|00:00:11|
| 1| SORT GROUP BY NOSORT| | 21| 861| 891|00:00:11|
| 2| NESTED LOOPS | |267996|10987836| 891|00:00:11|
|*3| INDEX RANGE SCAN |IND_PROD_CAT_NAME | 21| 714| 1|00:00:01|
|*4| INDEX RANGE SCAN |IND_SALES_PROD_QTY| 12762| 89334| 42|00:00:01|
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 3 - access("P"."PROD_CATEGORY_ID"=203)
* 4 - access("P"."PROD_ID"="S"."PROD_ID")
報告顯示使用兩個索引的執行計劃比原始執行計劃效能更好
12.實現evolve任務所給出的建議
SQL>EXECUTE :cnt := DBMS_SPM.IMPLEMENT_EVOLVE_TASK( task_name=>:tk_name,execution_name=>:exe_name ); PL/SQL procedure successfully completed.
13.查詢資料字典來確保新的執行計劃已經是接受狀態
SQL> SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED
2 FROM DBA_SQL_PLAN_BASELINES
3 WHERE SQL_HANDLE IN ('SQL_07f16c76ff893342')
4 ORDER BY SQL_HANDLE, ACCEPTED;
SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC
-------------------- -------------------- ------------------------------ -------------------- --- ---
SQL_07f16c76ff893342 SELECT /* q1_group_b SQL_PLAN_0gwbcfvzskcu242949306 AUTO-CAPTURE YES YES
y */ prod_name, sum(
quantity_sold)
FROM products p, sal
es s
WHERE p.prod_id = s.
prod_id
AND p.prod_category_
id =203
GROUP BY prod_name
SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC
-------------------- -------------------- ------------------------------ -------------------- --- ---
SQL_07f16c76ff893342 SELECT /* q1_group_b SQL_PLAN_0gwbcfvzskcu2ae9b4305 AUTO-CAPTURE YES YES
y */ prod_name, sum(
quantity_sold)
FROM products p, sal
es s
WHERE p.prod_id = s.
prod_id
AND p.prod_category_
id =203
GROUP BY prod_name
14.執行清除操作
SQL> VARIABLE cnt NUMBER
SQL> EXEC :cnt := DBMS_SPM.DROP_SQL_PLAN_BASELINE('SQL_07f16c76ff893342');
PL/SQL procedure successfully completed.
SQL> DELETE FROM SQLLOG$;
13 rows deleted.
SQL> commit;
Commit complete.
SQL> DROP INDEX IND_SALES_PROD_QTY_SOLD;
Index dropped.
SQL> DROP INDEX IND_PROD_CAT_NAME;
Index dropped.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26015009/viewspace-2636109/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Manage SQL Plan Baselines in Oracle 12cSQLOracle
- sql plan baselines（一）SQL
- SQL Plan Baselines 實驗01SQL
- Oracle OCP IZ0-053 Q478(SQL Plan Baselines)OracleSQL
- Oracle OCP 1Z0 053 Q246(SQL Plan Baselines)OracleSQL
- oracle 11g sql plan baseline(2)調整baselines屬性OracleSQL
- Disable the Evolve Job SYS_AUTO_SPM_EVOLVE_TASK in Oracle 12cOracle
- Oracle OCP 1Z0 053 Q201(DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE)OracleSQL
- Oracle SQL Plan Baseline 學習OracleSQL
- 【SQL_PLAN】Oracle 透過檢視sql_plan 格式化執行計劃SQLOracle
- How to drop Oracle RAC database manually?OracleDatabase
- Oracle11g中SQL Plan Management （一）OracleSQL
- Oracle11g中SQL Plan Management （二）OracleSQL
- Oracle11g中SQL Plan Management （三）OracleSQL
- SQL Plan ManagementSQL
- Oracle 11g 新特性 -- SQL Plan Management 示例OracleSQL
- ORACLE SQL調優之'PLAN_TABLE' is old versionOracleSQL
- How to Deinstall Oracle Clusterware Home ManuallyOracle
- Oracle 11g SQL效能的新特性（三）- SQL Plan ManagementOracleSQL
- SQL Plan Management(SPM)SQL
- Oracle 12c 新SQL提示（hint）OracleSQL
- oracle execution planOracle
- SQL Plan Management介紹SQL
- (轉):學習Oracle動態效能表-(6)-V$SQL,V$SQL_PLANOracleSQL
- oracle 11g sql plan baseline(1)基本使用OracleSQL
- 【DBA】Oracle 11g 針對SQL效能的新特性（三）- SQL Plan ManagementOracleSQL
- Oracle EXPLAIN PLAN用法OracleAI
- Oracle SYSTEM_PLANOracle
- Oracle simple resource planOracle
- Oracle Performance Tune PlanOracleORM
- PL/SQL Developer連線到Oracle 12cSQLDeveloperOracle
- sql_plan_baselineSQL
- SQL PLAN Management的測試SQL
- WRH$_SQL_PLAN 被鎖SQL
- 11g-sql plan managementSQL
- sql plan baseline（二）SQL
- oracle11g中SQL最佳化（SQL TUNING）新特性之SQL Plan Management(SPM)OracleSQL
- Oracle -- 深入體會PLAN_TABLE、PLAN_TABLE$Oracle