如何使用命令行来evolve sql plan baselines

这篇文章主要介绍"如何使用命令行来evolve sql plan baselines"，在日常操作中，相信很多人在如何使用命令行来evolve sql plan baselines问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答"如何使用命令行来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 sWHERE p.prod_id = s.prod_idAND p.prod_category_id =204GROUP 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_planNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------optimizer_capture_sql_plan_baselines boolean     TRUEoptimizer_use_sql_plan_baselines     boolean     TRUE

以sh用户登录到数据库，然后设置SQLPLUS的显示参数

[oracle@jytest1 ~]$ sqlplus sh/sh@jypdbSQL*Plus: Release 12.2.0.1.0 Production on Thu Feb 14 23:30:09 2019Copyright (c) 1982, 2016, Oracle.  All rights reserved.Last Successful login time: Thu Feb 14 2019 23:01:23 +08:00Connected to:Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit ProductionSQL> SET PAGES 10000 LINES 140SQL> SET SERVEROUTPUT ONSQL> COL SQL_TEXT FORMAT A20SQL> COL SQL_HANDLE FORMAT A20SQL> COL PLAN_NAME FORMAT A30SQL> COL ORIGIN FORMAT A12SQL> SET LONGC 60535SQL> SET LONG 60535SQL> 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                                        11526Model SM26273 Black Ink Cartridge                               15910PCMCIA modem/fax 28800 baud                                     19278Multimedia speakers- 3" cones                                   10969Internal 8X CD-ROM                                              11197Deluxe Mouse                                                    11609Model CD13272 Tricolor Ink Cartridge                            12321Model NM500X High Yield Toner Cartridge                          646618" Flat Panel Graphics Monitor                                  4415External 8X CD-ROM                                              13886SIMM- 8MB PCMCIAII card                                         17544PCMCIA modem/fax 19200 baud                                     20467Envoy External 8X CD-ROM                                        14850Envoy External Keyboard                                          2857External 6X CD-ROM                                              11732Model A3827H Black Image Cartridge                              17314Internal 6X CD-ROM                                               853317" LCD w/built-in HDTV Tuner                                    4874SIMM- 16MB PCMCIAII card                                        14191Multimedia speakers- 5" cones                                   10419Standard Mouse                                                   871421 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                                        11526Model SM26273 Black Ink Cartridge                               15910PCMCIA modem/fax 28800 baud                                     19278Multimedia speakers- 3" cones                                   10969Internal 8X CD-ROM                                              11197Deluxe Mouse                                                    11609Model CD13272 Tricolor Ink Cartridge                            12321Model NM500X High Yield Toner Cartridge                          646618" Flat Panel Graphics Monitor                                  4415External 8X CD-ROM                                              13886SIMM- 8MB PCMCIAII card                                         17544PCMCIA modem/fax 19200 baud                                     20467Envoy External 8X CD-ROM                                        14850Envoy External Keyboard                                          2857External 6X CD-ROM                                              11732Model A3827H Black Image Cartridge                              17314Internal 6X CD-ROM                                               853317" LCD w/built-in HDTV Tuner                                    4874SIMM- 16MB PCMCIAII card                                        14191Multimedia speakers- 5" cones                                   10419Standard Mouse                                                   871421 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 statement16 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                                        11526Model SM26273 Black Ink Cartridge                               15910PCMCIA modem/fax 28800 baud                                     19278Multimedia speakers- 3" cones                                   10969Internal 8X CD-ROM                                              11197Deluxe Mouse                                                    11609Model CD13272 Tricolor Ink Cartridge                            12321Model NM500X High Yield Toner Cartridge                          646618" Flat Panel Graphics Monitor                                  4415External 8X CD-ROM                                              13886SIMM- 8MB PCMCIAII card                                         17544PCMCIA modem/fax 19200 baud                                     20467Envoy External 8X CD-ROM                                        14850Envoy External Keyboard                                          2857External 6X CD-ROM                                              11732Model A3827H Black Image Cartridge                              17314Internal 6X CD-ROM                                               853317" LCD w/built-in HDTV Tuner                                    4874SIMM- 16MB PCMCIAII card                                        14191Multimedia speakers- 5" cones                                   10419Standard Mouse                                                   871421 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_nameSQL_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 statement16 rows selected.

上面的Note部分指示优化器使用了原始的没有索引的执行计划

9.以管理员用户登录数据库，然后创建一个evolve任务它包含未被接受执行计划相关的所有SQL语句

[oracle@jytest1 ~]$ sqlplus sys/xxzx7817600@jypdb as sysdbaSQL*Plus: Release 12.2.0.1.0 Production on Thu Feb 14 23:48:51 2019Copyright (c) 1982, 2016, Oracle.  All rights reserved.Connected to:Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit ProductionSQL> VARIABLE cnt NUMBERSQL> VARIABLE tk_name VARCHAR2(50)SQL> VARIABLE exe_name VARCHAR2(50)SQL> VARIABLE evol_out CLOBSQL> 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_1110.执行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_11Task Owner : SYSExecution Name : EXEC_1Execution Type       : SPM EVOLVEScope                : COMPREHENSIVEStatus               : COMPLETEDStarted              : 02/15/2019 17:49:32Finished             : 02/15/2019 17:49:35Last Updated         : 02/15/2019 17:49:35Global Time Limit    : 2147483646Per-Plan Time Limit  : UNUSEDNumber of Errors     : 0---------------------------------------------------------------------------SUMMARY SECTION---------------------------------------------------------------------------Number of plans processed : 1Number of findings : 1Number of recommendations : 1Number of errors : 0---------------------------------------------------------------------------DETAILS SECTION---------------------------------------------------------------------------Object ID : 2Test Plan Name : SQL_PLAN_0gwbcfvzskcu20135fd6cBase Plan Name : SQL_PLAN_0gwbcfvzskcu242949306SQL Handle : SQL_07f16c76ff893342Parsing Schema : SHTest Plan Creator : SHSQL Text : SELECT /*q1_group_by*/ prod_name,sum(quantity_sold)FROM products p, sales sWHERE p.prod_id=s.prod_id AND p.prod_category_id=203GROUP BY prod_nameExecution Statistics:-----------------------------Base Plan Test Plan---------------------------- ------------------------Elapsed Time (s): .044336 .012649CPU Time (s): .044003 .012445Buffer Gets: 360 99Optimizer Cost: 924 891Disk Reads: 341 82Direct Writes: 0 0Rows Processed: 4 2Executions: 5 9FINDINGS SECTION---------------------------------------------------------------------------Findings (1):-----------------------------1. The plan was verified in 2.18 seconds. It passed the benefit criterionbecause its verified performance was 2.01 times better than that of thebaseline plan.Recommendation:-----------------------------Consider accepting the plan. Executedbms_spm.accept_sql_plan_baseline(task_name => 'TASK_11', object_id => 2,task_owner => 'SYS');EXPLAIN PLANS SECTION---------------------------------------------------------------------------Baseline Plan-----------------------------Plan Id : 1Plan 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 : 2Plan 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_nameSQL_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 NUMBERSQL> 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.

到此，关于"如何使用命令行来evolve sql plan baselines"的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注网站，小编会继续努力为大家带来更多实用的文章！