这篇文章主要介绍“如何使用命令行来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 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 ON2.执行SQL语句，因此可以自动捕获它
执行下面的SQL语句
SQL> SELECT 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 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 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_name4.下面对SQL语句进行解析并验证优化器是否会使用SQL Plan Baseline中的执行计划
SQL> EXPLAIN PLAN FOR
2 SELECT 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 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 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 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 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_111.查看报告
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 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 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 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_name14.执行清除操作
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.到此，关于“如何使用命令行来evolve sql plan baselines”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！
