1 场景介绍
日常运维中经常会碰到性能有问题的SQL语句，比如这条语句：
SELECT * FROM sh.sales WHERE prod_id=113 AND PROMO_ID=999 AND to_char(TIME_ID,'YYYY-MM-DD')='2019-05-27' AND CHANNEL_ID=3 ;
复制上面这个形式的语句在数据库的生产环境中经常碰到，这条语句的执行计划如下：
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 86 | 2494 | 3823 (1)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL| | 86 | 2494 | 3823 (1)| 00:00:01 | 1 | 15 |
|* 2 | TABLE ACCESS FULL | SALES | 86 | 2494 | 3823 (1)| 00:00:01 | 1 | 15 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
2 - filter("PROD_ID"=113 AND "CHANNEL_ID"=3 AND
TO_CHAR(INTERNAL_FUNCTION("TIME_ID"),'YYYY-MM-DD')='2019-05-27' AND "PROMO_ID"=999)
复制从这条语句的执行计划可以看到，SALES表是个分区表，这条语句执行了全表扫描，扫描了全部从1-15共15个分区，共返回了86行数据。SALES表共有918843行数据，另外这条语句的where条件里出现了to_char()函数在等号左边的情况，访问表时不会用到普通的索引。
可以从两个方面着手对这条语句进行优化，一是改写to_char条件，二是创建索引。这个简单的语句优化起来不是难事，查询一下条件的各个组合的选择性，也就是几个group by的问题，然后同单个条件的选择性相比较，如果组合条件的选择性比单个条件选择性强很多，就创建组合索引，否则就在单列上创建索引，如果是多个where条件或者是多个表进行join的情况下，要确定在哪几个条件上创建联合索引就要费点功夫了。
这时，DBMS_SQLTUNE的作用就显现出来了，使用DBMS_SQLTUNE的SQL调优任务，这些工作数据库就可以完成，DBA只需要按照优化后的建议进行操作就可以了。
2 创建调优任务
这里只考虑对单条语句进行优化，这条语句可能是正在运行的（在SQL 共享区内存在这个语句的游标），将要运行的（语句还未在数据库里运行过），已经运行过的（语句的游标已经从SQL共享区清除了）。
2.1 优化当前正在运行的单条语句
2.1.1 查询sql语句的信息
对单个SQL语句进行优化，在创建SQL调优任务时，需要执行sql文本或者sql_id，这里选择sql_id，一是习惯，二是使用sql_id，语句看起来比较整齐，也不容易出现格式问题，如果是复杂的sql文本作为参数，不但看起来比较乱，也容易出格式问题。由于语句的游标还在SQL共享区内，sql_id可以使用下面语句查到
SELECT sql_id,sql_text FROM v$sql WHERE sql_text LIKE '%PROD_ID=113 AND PROMO_ID=999 AND to_char%';;
SQL_ID SQL_TEXT
-------------- ---------------------------------------------------------------
033jhgm9f3h5f SELECT * FROM sh.sales WHERE PROD_ID=113 AND PROMO_ID=999 AND to_char(TIME_ID,'YYYY-MM-DD')='2019-05-27' AND CHANNEL_ID=3 |
复制2.1.2 创建SQL调优任务
DECLARE
my_task_name VARCHAR2(30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => '033jhgm9f3h5f',
task_name => 'sales_sql_tuning_task');
END;
复制创建优化任务时，可以指定sql_id参数，Oracle会自动为任务执行一个名称。这里为了后续查询的方便，也指定了task_name参数。SQL优化任务有很多参数，可以在创建时指定，也可以在创建优化任务后进行更改。
2.2 调优尚未执行的单条SQL语句
这种情况偶尔会遇到，比如应用上线新功能时，语句还未在数据库里运行，需要提前了解一下语句的性能。这时，数据库的共享缓冲区内不存在尚未执行的语句的信息，最简单的是能够运行一下这条语句。但是，有些情况，某些语句是不可能手动运行一下的。如果任由其上线也不是比较好的办法。由于没有这条语句的SQL_ID,这种方式下可以使用SQL文本作为创建优化任务的参数，假设还是对上面的那条SQL进行优化，使用下面的语句创建优化任务
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'SELECT * FROM sh.sales '||
'WHERE PROD_ID=113 AND PROMO_ID=999 AND to_char(TIME_ID, ' ||
'''YYYY-MM-DD'''||')='||'''2019-05-27'''||
' AND CHANNEL_ID=3';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
user_name => 'SH',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'sales_sql_tuning_task',
description => 'Task to tune a query on a specified employee');
END;
复制上面的语句里包含单引号，进行语句的拼接时要注意对单引号进行转义，写成两个连续的单引号。另外要注意的是作为输入参数的sql语句不能包含结尾的分号（;）。
2.3 调优执行过的单条SQL语句
2.3.1 场景及基本步骤
有时候，在数据库的AWR报告里发现了存在性能问题的SQL语句，或者是看了数据库的ADDM报告，需要针对这条语句运行SQL优化建议。可是，这条SQL语句如果已经从SQL共享区里刷出去了，虽然知道语句的SQL_ID,也不能用来创建SQL优化任务，创建任务时会报下面的错误
DECLARE
stmt_task varchar2(64);
BEGIN
stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '8qyh8203pmvyc',
task_name => 'a_sqltune_task');
END; /
DECLARE
*
ERROR at line 1:
ORA-13780: SQL statement does not exist.
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.PRVT_SQLADV_INFRA", line 283
ORA-06512: at "SYS.DBMS_SQLTUNE", line 778
ORA-06512: at line 4
复制虽然在数据库的SQL共享区里已经不存在语句的游标了，我们可以从数据库的AWR历史快照里发现语句的游标信息，用来创建调优任务。
2.3.2创建调优任务
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
begin_snap => 394,
end_snap => 395,
sql_id => '033jhgm9f3h5f',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => 'my_awr_task',
description => 'Tuning task for statement 43qef4f7g5h32 in AWR');
END;
复制3 设置调优任务参数
调优任务的几个参数中，scope是一种最重要的几个参数中的一个，这个参数可以取2个值，一个是limit。SQL优化建议根据统计状态检查、访问路径分析和sql语句结构分析给出优化建议；参数的另一个值是COMPREHENSIVE，除了上述诸项之外，优化建议也执行sql profiling。优化任务还有一个参数，是时间限制，用来设置优化任务的执行时间，如下所示：
BEGIN
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER (
task_name => 'a_sqltune_task'
, parameter => 'TIME_LIMIT'
, value => 300
);
END;
/
复制设置完成后，使用下面语句检测设置是否生效
SELECT PARAMETER_NAME, PARAMETER_VALUE AS "VALUE"
FROM USER_ADVISOR_PARAMETERS
WHERE TASK_NAME = 'a_sqltune_task'
AND PARAMETER_NAME='TIME_LIMIT' ;
PARAMETER_NAME VALUE
------------------------- -------------------------
TIME_LIMIT 300
复制4 执行调优任务，检查执行任务状态
EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK('a_sqltune_task');复制
使用上面语句执行后，用下面语句检查任务执行状态
SELECT TASK_ID, TASK_NAME, STATUS, STATUS_MESSAGE
FROM USER_ADVISOR_LOG where TASK_NAME='a_sqltune_task';
TASK_ID TASK_NAME STATUS STATUS_MESSAGE
------- ------------------------- ----------- ---------------------------------
582 sqltune a single sql COMPLETED
复制如果任务迟迟没有完成，可以使用下面的语句查看执行进度
SELECT TASK_ID, ADVISOR_NAME, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$ADVISOR_PROGRESS
WHERE TASK_ID =579 ;
TASK_ID ADVISOR_NAME SOFAR TOTALWORK %_COMPLETE
------- -------------------- ----- --------- ----------
579 SQL Tuning Advisor 1 1 100
复制5 查看调优任务报告
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('a_sqltune_task') FROM DUAL;
复制上面三种方式产生的调优建议报告基本相同，内容如下：
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : sales_sql_tuning_task
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 12/06/2024 02:34:09
Completed at : 12/06/2024 02:34:10
-------------------------------------------------------------------------------
Schema Name : SH
Container Name: FREEPDB1
SQL ID : 1tm6szswmqsrq
SQL Text : SELECT * FROM sh.sales WHERE PROD_ID=113 AND PROMO_ID=999
AND to_char(TIME_ID, 'YYYY-MM-DD')='2019-05-27' AND
CHANNEL_ID=3
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- Index Finding (see explain plans section below)
--------------------------------------------------
通过创建一个或多个索引可以改进此语句的执行计划。
Recommendation (estimated benefit: 99.84%)
------------------------------------------
- 考虑运行可以改进物理方案设计的访问指导或者创建推荐的索引。
create index SH.IDX$$_029E0001 on SH.SALES(TO_CHAR("TIME_ID",'YYYY-MM-DD'),
"PROD_ID","CHANNEL_ID","PROMO_ID");
Rationale
---------
创建推荐的索引可以显著地改进此语句的执行计划。但是, 使用典型的 SQL 工作量运行 "访问指导"
可能比单个语句更可取。通过这种方法可以获得全面的索引建议案, 包括计算索引维护的开销和附加的空间消耗。
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 1550251865
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 86 | 2494 | 3823 (1)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL| | 86 | 2494 | 3823 (1)| 00:00:01 | 1 | 15 |
|* 2 | TABLE ACCESS FULL | SALES | 86 | 2494 | 3823 (1)| 00:00:01 | 1 | 15 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("PROD_ID"=113 AND "CHANNEL_ID"=3 AND
TO_CHAR(INTERNAL_FUNCTION("TIME_ID"),'YYYY-MM-DD')='2019-05-27' AND "PROMO_ID"=999)
2- Using New Indices
--------------------
Plan hash value: 3845943666
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 86 | 2494 | 6 (0)| 00:00:01 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| SALES | 86 | 2494 | 6 (0)| 00:00:01 | ROWID | ROWID |
|* 2 | INDEX RANGE SCAN | IDX$$_029E0001 | 6 | | 3 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SALES"."SYS_QSMMIX_VCOL_5001"='2019-05-27' AND "PROD_ID"=113 AND "CHANNEL_ID"=3 AND "PROMO_ID"=999)
-------------------------------------------------------------------------------
复制报告由三个部分组成
- 通用信息部分–包括任务的名称、所有者、负载类型、调优范围、时间限制、任务开始结束时间，语句的模式、容器、文本等信息。
- findings 部分–调优报告包括几个调优建议，每个调优建议的基本信息。这个报告只给出了一条建议，创建联合索引，并给出了创建联合索引的语句及建议的一句。
- 解释部分–对于每条建议的详细解释。这个报告里给出了创建索引前后的执行计划，通过比较执行计划，可以看到创建联合索引的性能上的提高幅度，从而确定是否采用这个建议。
6 注意事项
在创建优化任务时，不能写成下面的形式
BEGIN
DBMS_SQLTUNE.create_tuning_task (
begin_snap => 385,
end_snap => 386,
sql_id => '5b1numrquu7qv');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
复制执行上面的语句，汇报以下错误
DBMS_SQLTUNE.create_tuning_task (
*
ERROR at line 4:
ORA-06550: line 4, column 4:
PLS-00306: wrong number or types of arguments in call to 'CREATE_TUNING_TASK'
ORA-06550: line 4, column 4:
PL/SQL: Statement ignored
Help: https://docs.oracle.com/error-help/db/ora-06550/
复制报上述错误的原因是DBMS_SQLTUNE.create_tuning_task返回一个varchar2类型的参数，需要将它付给一个varchar2类型的变量，否则就会报PLS-00306: wrong number or types of arguments的参数错误。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
目录
