使用SQL 优化集（SQL Tuning Set）优化SQL语句

原创听见风的声音 2024-12-11

645

1 使用场景

在数据库优化时，常常需要对多条SQL语句进行优化。比如DBA搜集了数据库的AWR报告，发现数据库的缓冲区读指标异常地高，十几条单次执行缓冲区读超过一定数量（比如100000）的SQL语句。这些语句能不能优化，优化后的收益有多大，如果全由人工来分析，费时费力，由SQL调优任务来完成，DBA根据调优任务来决定优化措施是更好的选择。

2 一次优化多条语句时，不能直接使用AWR报告作为输入

SQL调优任务可以加载Oracle AWR快照中的SQL语句。但是，如果试图加载AWR报告中的全部SQL语句，比如执行下面命令

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,
                           scope       => DBMS_SQLTUNE.scope_comprehensive,
                           time_limit  => 60,
                           task_name   => 'my_awr_task',
                           description => 'Tuning task for statement 43qef4f7g5h32  in AWR');
 END;

则会报以下错误

 l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                         *
ERROR at line 4:
ORA-06550: line 4, column 26:
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的AWR工作负载格式时，SQL_ID是强制选项，不能为空。批量优化SQL，应使用这个函数的STS（SQL tuning set）格式，从STS载入批量SQL。

3 使用STS（SQL tuning set）载入批量SQL语句

3.1 什么是STS

STS（SQL 优化集）是Oracle SQL优化里的一个重要的概念，在SQL优化任务中，STS是ORACLE SQL自动优化必须理解和掌握的工具。STS是Oracle数据库里的一个对象，包括一个或多个SQL语句负载信息，包括语句的执行信息统计，执行背景以及用户优先级排列。STS可以从多个数据源获取一条或多条语句的工作负载信息，包括AWR报告，共享SQL区，会话跟踪文件，也可以实时从游标缓冲区中实时捕捉SQL工作负载。一个STS中可以包括：

一条或多条SQL语句
语句的执行背景，包括用户模式、模块（Module）及动作（Action）、绑定变量值列表以及游标编译环境
语句执行基本统计信息，例如运行时间、CPU时间、缓冲区读、磁盘读、处理的行、游标获取、执行次数、完全执行次数、优化器成本、命令类型
每一条语句的和行源统计信息
STS可以作为SQL优化任务及SQL访问指导的输入，也可以从一个数据库导出到另一个数据库以在数据库之间传输工作负载。

3.3 工作负载的选择

STS的SQL负载可以由多个来源，DBMS_SQLTUNE提供了1个存储过程，5个函数用来获取一条或多条SQL语句的工作负载，如下表所示

子程序名	描述
CAPTURE_CURSOR_CACHE_SQLSET 存储过程	在一定的时间间隔内从游标缓冲区内持续捕获SQL语句的工作负载到STS
SELECT_CURSOR_CACHE 函数	从游标缓冲区内搜集SQL工作负载
SELECT_SQL_TRACE 函数	读取一个或多个跟踪文件内容，以sqlset_row格式返回找到的SQL语句
SELECT_SQLPA_TASK 函数	从SQL性能分析比较任务中收集SQL语句工作负载
SELECT_SQLSET 函数	从现有的STS中收集SQL工作负载
SELECT_WORKLOAD_REPOSITORY 函数	从AWR历史快照中收集SQL工作负载

上表中的存储过程和函数各自适用不同的场景，如果对历史SQL进行分析，可以使用SELECT_WORKLOAD_REPOSITORY函数或者是SELECT_SQL_TRACE，两者的区别是SELECT_WORKLOAD_REPOSITORY是周期性的采样信息，SQL_TRACE则包括了会话执行SQL的全部信息；对当前的SQL进行分析，使用SELECT_CURSOR_CACHE从游标缓冲池内获取SQL；也可以获取一定时间间隔内的游标缓冲区内累积的SQL负载，使用CAPTURE_CURSOR_CACHE_SQLSET存储过程。

3.4 创建和删除STS

在Oracle 11g中，使用DBMS_SQLTUNE包的CREATE_SQLSET存储过程创建STS，使用同一包的DROP_SQLSET存储过程删除STS。
使用下面的语句创建STS

EXEC DBMS_SQLTUNE.CREATE_SQLSET(-
  sqlset_name => 'my_workload', -
  description => 'complete application workload');

上面两个参数，sqlset_name是需要指定的，description不必指定，可以为空。创建之后，通过user_sqlset查看

select NAME,DESCRIPTION,CREATED,STATEMENT_COUNT from user_sqlset;

NAME                             DESCRIPTION                                      CREATED             STATEMENT_COUNT
-------------------------------- ------------------------------------------------ ------------------- ---------------
SQLT_WKLD_STS                    STS to store SQL from the private SQL area       2024-11-20 07:06:32               0
SWAT$AUTO_MV_ADV_STS             sql set for MVA                                  2024-04-24 11:15:16               0
SWAT$AUTO_MV_ADV_SURROGATE_STS   empty sql set for MVA                            2024-04-24 11:15:16               0
SWAT_ARM_STS_VER                 sql set for swat-ver                             2024-04-24 11:15:16               0
SYS_AUTO_STS                     System auto SQL Tuning Set                       2024-05-06 06:57:30             554
my_workload                      complete application workload                    2024-11-22 08:15:44            0

创建的STS如果不再用了，可以删除

exec DBMS_SQLTUNE.DROP_TUNING_TASK('my_sqltune_task')

如果STS被优化任务或者是访问指导使用，则不能删除，会报下面错误

ERROR at line 1:
ORA-13757: "SQL Tuning Set" "my_workload" owned by user "SYS" is active.
ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 15242
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 140

这时需要删除使用这个STS的优化任务或者是访问指导后才能删除STS。

3.5 装入SQL语句

刚创建的STS里面没有sql语句的信息，通过查询user_sqlset视图可以确认

SQL> select NAME,DESCRIPTION,CREATED,STATEMENT_COUNT from user_sqlset where name='my_workload';

NAME                             DESCRIPTION                                      CREATED             STATEMENT_COUNT
-------------------------------- ------------------------------------------------ ------------------- ---------------
my_workload                                                             2024-11-22 08:15:44            0

STS my_workl的STATEMENT_COUNT为0，要装入SQL语句后才可以在SQL优化任务及访问指导中使用。STS可以从不同的来源装入SQL语句，限于篇幅，这里演示从游标缓存内获取以及持续捕捉游标缓存内的工作负载两种方式。

3.5.1 从游标缓存内载入

游标缓存里存储的是当前正在执行的SQL语句的游标，使用SELECT_CURSOR_CACHE这个函数获取游标缓存里的SQL，这个函数的语法如下

DBMS_SQLTUNE.SELECT_CURSOR_CACHE (
  basic_filter        IN   VARCHAR2 := NULL,
  object_filter       IN   VARCHAR2 := NULL,
  ranking_measure1    IN   VARCHAR2 := NULL,
  ranking_measure2    IN   VARCHAR2 := NULL,
  ranking_measure3    IN   VARCHAR2 := NULL,
  result_percentage   IN   NUMBER   := 1,
  result_limit        IN   NUMBER   := NULL,
  attribute_list      IN   VARCHAR2 := NULL,
  recursive_sql       IN   VARCHAR2 := HAS_RECURSIVE_SQL)

上面列出了SELECT_CURSOR_CACHE的所有参数及默认值，其中object_filter参数现在不支持。basic_filter参数用来设置装入到STS中的语句的筛选条件，可以是SQL_ID,parsing_schema_name ,SQL_TEXT,module，action等。ranking_measureN指定sql排序的指标，如缓冲区读，执行时间等，result_percentage和result_limit用来指定根据ranking排序过滤出的语句，attribute_list用来指定导入到STS中的内容。我们这里导入全部游标缓存区内的SQL，执行下面的语句

 DECLARE
 cur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
 OPEN cur FOR
   SELECT VALUE(P)
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE()) P;

DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'my_workload',
                        populate_cursor => cur);
END;  2    3    4    5    6    7    8    9   10
 11  /

PL/SQL procedure successfully completed.

查询一下STS信息

SQL> select NAME,DESCRIPTION,CREATED,STATEMENT_COUNT from user_sqlset where NAME='my_workload';

NAME                             DESCRIPTION                                      CREATED             STATEMENT_COUNT
-------------------------------- ------------------------------------------------ ------------------- ---------------
my_workload                      complete application workload                    2024-12-02 07:40:36               180

STS里面已经有了180条语句的信息，看一下关于一条语句STS里都有哪些信息

SELECT * FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET('my_workload')) ;
SQL_ID       |FORCE_MATCHING_SIGNATURE|SQL_TEXT                                              |OBJECT_LIST|BIND_DATA|PARSING_SCHEMA_NAME|MODULE                                    |ACTION|ELAPSED_TIME|CPU_TIME|BUFFER_GETS|DISK_READS|DIRECT_WRITES|ROWS_PROCESSED|FETCHES|EXECUTIONS|END_OF_FETCH_COUNT|OPTIMIZER_COST|OPTIMIZER_ENV                                                                                                                                                                                                                                                  |PRIORITY|COMMAND_TYPE|FIRST_LOAD_TIME    |STAT_PERIOD|ACTIVE_STAT_PERIOD|OTHER|PLAN_HASH_VALUE|SQL_PLAN                                                                                                                                                                                                                                                       |BIND_LIST|CON_DBID  |LAST_EXEC_START_TIME|SHARABLE_MEM|EXACT_MATCHING_SIGNATURE|RESULT_CACHE_EXECUTIONS|SQL_PROFILE|SQL_PLAN_BASELINE|SQL_PATCH|AVG_HARD_PARSE_TIME|USER_IO_WAIT_TIME|IO_INTERCONNECT_BYTES|PARSING_USER_NAME|
-------------+------------------------+------------------------------------------------------+-----------+---------+-------------------+------------------------------------------+------+------------+--------+-----------+----------+-------------+--------------+-------+----------+------------------+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------+-------------------+-----------+------------------+-----+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+----------+--------------------+------------+------------------------+-----------------------+-----------+-----------------+---------+-------------------+-----------------+---------------------+-----------------+
8qyh8203pmvyc|    10526184842258109387|SELECT * FROM sales WHERE prod_id=113 AND PROMO_ID=999|NULL       |         |SH                 |DBeaver 24?1?0 ? SQLEditor ?Script?13?sql?|      |      444331|  213170|       3799|        52|            0|           200|      4|         1|                 0|          2167|â û Ð _ °   ®õÃâÏú3 VAEUQ ! UEU ETUXY UD e  U   UU UQQ"UT  ê UQEBeETTD  Vn   Æ£UE   T PD ýUqQU UU  U  !EEÑÃTD¡   YU å S5UUUUQé    [  e]VEa@U %$P ù¤   PQeU  PD  yU  T  A ÁA @d Q T P AZÐ _                     ,               8ø    ÌÌ      Ð _ edd  d2 2     |        |           3|2024-12-02/07:43:27|           |                  |     |     3521008416|['[null, null, 2024-12-02 07:43:27.0, null, SELECT STATEMENT, null, null, null, null, null, null, null, ALL_ROWS, 0, 0, null, 0, 2167, 2167, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, [NULL], null]','[null, n|NULL     |2195985998|2024-12-02/07:43:27 |       55911|      688626694708207893|                      0|           |                 |         |                   |           224965|               425984|SH               |

可以看到，STS里包含了这条语句的执行统计信息（执行时间，cpu时间等），绑定变量、执行计划（SQL PLAN）等我们上面所提到的内容。

3.5.2 持续捕捉游标缓存内的工作负载

使用CAPTURE_CURSOR_CACHE_SQLSET存储过程持续捕捉一定时间段内的SQL工作负载信息，这个存储过程的调用格式如下：

DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET (
    sqlset_name         IN VARCHAR2, 
    time_limit          IN POSITIVE := 1800,
    repeat_interval     IN POSITIVE := 300,
    capture_option      IN VARCHAR2 := 'MERGE',
    capture_mode        IN NUMBER   := MODE_REPLACE_OLD_STATS,
    basic_filter        IN VARCHAR2 := NULL,
    sqlset_owner        IN VARCHAR2 := NULL,
    recursive_sql       IN VARCHAR2 := HAS_RECURSIVE_SQL);

这里面的捕捉模式需要说明一下，捕捉模式由两个选项，

MODE_REPLACE_OLD_STATS - 如果执行的次数大于STS中的存储的值，替换旧值。
MODE_ACCUMULATE_STATS - 如果现在的SQL在STS中已经存在，将新值加到旧值上。
运行下面的语句

BEGIN
DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET( 
                         sqlset_name     => 'SQL_CAPTURE_STS', 
                         time_limit      => 180,
                         repeat_interval => 5,
                         capture_mode    => dbms_sqltune.MODE_ACCUMULATE_STATS);
END;

time_limit和repeat_interval的时间单位都是秒，这条语句每5秒一次，持续3分钟捕捉SQL工作负载到名为SQL_CAPTURE_STS的STS中，捕捉模式为MODE_ACCUMULATE_STATS。存储过过程运行完毕后，查询SQL_CAPTURE_STS表内容如下

SELECT * FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET('SQL_CAPTURE_STS'))；
SQL_ID       |FORCE_MATCHING_SIGNATURE|SQL_TEXT                                                                                                                                                                                                                                                       |OBJECT_LIST|BIND_DATA                                                                |PARSING_SCHEMA_NAME|MODULE                                    |ACTION                         |ELAPSED_TIME|CPU_TIME|BUFFER_GETS|DISK_READS|DIRECT_WRITES|ROWS_PROCESSED|FETCHES|EXECUTIONS|END_OF_FETCH_COUNT|OPTIMIZER_COST|OPTIMIZER_ENV                                                                                                                                                                                                                                                  |PRIORITY|COMMAND_TYPE|FIRST_LOAD_TIME    |STAT_PERIOD|ACTIVE_STAT_PERIOD|OTHER|PLAN_HASH_VALUE|SQL_PLAN                                                                                                                                                                                                                                                       |BIND_LIST|CON_DBID  |LAST_EXEC_START_TIME|SHARABLE_MEM|EXACT_MATCHING_SIGNATURE|RESULT_CACHE_EXECUTIONS|SQL_PROFILE|SQL_PLAN_BASELINE|SQL_PATCH|AVG_HARD_PARSE_TIME|USER_IO_WAIT_TIME|IO_INTERCONNECT_BYTES|PARSING_USER_NAME|
-------------+------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+-------------------------------------------------------------------------+-------------------+------------------------------------------+-------------------------------+------------+--------+-----------+----------+-------------+--------------+-------+----------+------------------+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------+-------------------+-----------+------------------+-----+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+----------+--------------------+------------+------------------------+-----------------------+-----------+-----------------+---------+-------------------+-----------------+---------------------+-----------------+
04kug40zbu4dm|     9752196975317558206|select policy#, action# from aud_object_opt$ where object# = :1 and type = 2                                                                                                                                                                                   |NULL       |¾Ú    gWâñ   À   Ã ?[                                                    |SYS                |                                          |                               |      247514|  253703|      43436|         0|            0|             9|   3111|      3102|              3064|             5|â û Ð _ °   ®õÃâÏú3 VAEUQ ! UEU ETUXY UD e  U   UU UQQ"UT  ê UQEBeETTD  Vn   Æ£UE   T PD ýUqQU UU  U  !EEÑÃTD¡   YU å S5UUUUQé    [  e]VEa@U %$P ù¤   PQeU  PD  yU ET  A ÁA @d Q T P AZÐ _                     ,               8ø    ÌÌ      Ð _  edd  d2 2    |        |           3|2024-12-10/01:45:48|           |                  |     |     4006480256|['[null, null, 2024-12-10 06:27:42.0, null, SELECT STATEMENT, null, null, null, null, null, null, null, CHOOSE, 0, 0, null, 0, 5, 5, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, [NULL], null]','[null, null, 202|NULL     |2195985998|2024-12-10/06:43:07 |      169509|    14514749038448292029|                      0|           |                 |         |               6791|                0|                    0|SYS              |
06gfrprr7w0r2|    17645000388822757149|select name,password,datats#,tempts#,type#,defrole,resource$, ptime, decode(defschclass,NULL,'DEFAULT_CONSUMER_GROUP',defschclass), spare1,spare4,ext_username,spare2,nvl(spare3,16382),spare9,spare10 from user$ where user#=:1                               |NULL       |¾Ú    gWâñ   À   ÁO                                                      |SYS                |                                          |                               |       17946|   10659|        229|         1|            0|           111|    118|       118|                 7|             1|â û Ð _ °   ®õÃâÏú3 VAEUQ ! UEU ETUXY UD e  U   UU UQQ"UT  ê UQEBeETTD  Vn   Æ£UE   T PD ýUqQU UU  U  !EEÑÃTD¡   YU å S5UUUUQé    [  e]VEa@U %$P ù¤   PQeU  PD  yU ET  A ÁA @d Q T P AZÐ _                     ,               8ø    ÌÌ      Ð _  edd  d2 2    |        |           3|2024-12-10/01:45:49|           |                  |     |     2709293936|['[null, null, 2024-12-10 06:42:58.0, null, SELECT STATEMENT, null, null, null, null, null, null, null, CHOOSE, 0, 0, null, 0, 1, 1, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, [NULL], null]','[null, null, 202|NULL     |2195985998|2024-12-10/06:42:57 |      109233|     6990732677435469110|                      0|           |                 |         |               3758|             6502|                 8192|SYS              |
0dzjhvmbd3wyx|    10333466378920749900|select count(*) from col$ where obj#=:1 and name=:2                                                                                                                                                                                                            |NULL       |¾Ú    gWâÞ   À   Â9Dð   i ORIGIN_CON_ID                                  |SYS                |                                          |                               |       29014|    6721|         33|         5|            0|            10|     10|        10|                10|             2|â û Ð _ °   ®õÃâÏú3 VAEUQ ! UEU ETUXY UD e  U   UU UQQ"UT  ê UQEBeETTD  Vn   Æ£UE   T PD ýUqQU UU  U  !EEÑÃTD¡   YU å S5UUUUQé    [  e]VEa@U %$P ù¤   PQeU  PD  yU  T  A ÁA @d Q T P AZÐ _                     ,               8ø    ÌÌ      Ð _  edd  d2 2    |        |           3|2024-12-10/01:46:02|           |                  |     |     2905815311|['[null, null, 2024-12-10 06:42:39.0, null, SELECT STATEMENT, null, null, null, null, null, null, null, CHOOSE, 0, 0, null, 0, 2, 2, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, [NULL], null]','[null, null, 202|NULL     |2195985998|2024-12-10/06:42:38 |      125076|    10333466378920749900|                      0|           |                 |         |              16817|            22710|                40960|SYS              |
0fdn56d88dns9|    14220952722813987031|INSERT ALL WHEN (:B42 = 1) THEN INTO WRI$_SQLSET_STATEMENTS (ID, CON_DBID, SQLSET_ID, SQL_ID, FORCE_MATCHING_SIGNATURE, PARSING_SCHEMA_NAME, MODULE, ACTION, COMMAND_TYPE, EXACT_MATCHING_SIGNATURE) VALUES (:B25 , :B23 , :B41 , :B40 , :B39 , :B32 , :B38 , :|NULL       |¾Ú    gWâ  <        À   Á À   Á À   Á                                    |SYS                |DBeaver 24?1?0 ? SQLEditor ?Script?13?sql?|                               |       89429|   31969|       3526|        10|            0|           723|      0|       181|                66|             2|â û Ð _ °   ®õÃâÏú3 VAEUQ ! UEU ETUXY UD e  U   UU UQQ"UT  ê UQEBeETTD  Vn   Æ£UE   T PD ýUqQU UU  U  !EEÑÃTD¡   YU å S5UUUUQé    [  e]VEa@U %$P ù¤   PQeU  PD  yU  T  A ÁA @d Q T P AZÐ _                     ,               8ø    ÌÌ      Ð _ edd  d2 2     |        |           2|2024-12-10/06:41:32|           |                  |     |     2939908344|['[null, null, 2024-12-10 06:41:32.0, null, INSERT STATEMENT, null, null, null, null, null, null, null, ALL_ROWS, 0, 0, null, 0, 2, 2, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, [NULL], null]','[null, null, 2|NULL     |2195985998|2024-12-10/06:43:54 |      286537|    15379768007029226471|                      0|           |                 |         |              20382|            44460|                81920|SYS              |
0fr8zhn4ymu3v|    18036339256563254873|select intcol#,type,flags,lobcol,objcol,extracol,schemaoid,  elemnum from opqtype$ where obj# = :1 order by intcol# asc                                                                                                                                        |NULL       |¾Ú    gWßb   À   Â                                                       |SYS                |                                          |                               |       12114|    6823|         89|         1|            0|             7|     82|        75|                58|             2|â û Ð _ °   ®õÃâÏú3 VAEUQ ! UEU ETUXY UD e  U   UU UQQ"UT  ê UQEBeETTD  Vn   Æ£UE   T PD ýUqQU UU  U  !EEÑÃTD¡   YU å S5UUUUQé    [  e]VEa@U %$P ù¤   PQeU  PD  yU ET  A ÁA @d Q T P AZÐ _                     ,               8ø    ÌÌ      Ð _  edd  d2 2    |        |           3|2024-12-10/01:45:48|           |                  |     |     1231101765|['[null, null, 2024-12-10 06:27:47.0, null, SELECT STATEMENT, null, null, null, null, null, null, null, CHOOSE, 0, 0, null, 0, 2, 2, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, [NULL], null]','[null, null, 202|NULL     |2195985998|2024-12-10/06:42:38 |      121712|    18036339256563254873|                      0|           |                 |         |               7100|             2981|                 8192|SYS              |
0gwaa0289rasu|    13579641042383274831|select /*+ rule */ pos#,intcol#,col#,spare1,bo#,spare2,spare3 from icol$ where obj#=:1                                                                                                                                                                         |NULL       |¾Ú    gWá¨   À   Ã  E                                                    |SYS                |                                          |                               |      131912|   98993|      21089|         9|            0|          7970|  10545|      2575|              2521|             1|â û Ð _ °   ®õÃâÏú3 VAEUQ ! UEU ETUXY UD e  U   UU UQQ"UTÕ ê UQEBeETTD  Vn   Æ£UE   T PD ýUqQU UU  U  !EEÑÃTD¡   YU å S5UUUUQé    [  e]VEa@U %$P ù¤   PQeU  PD  yU ET  A ÁA @d Q T P AZÐ _                     ,               8ø            Ð _  edd  d2 2    |        |           3|2024-12-10/01:45:48|           |                  |     |      299250003|['[null, null, 2024-12-10 01:46:02.0, null, SELECT STATEMENT, null, null, null, null, null, null, null, HINT: RULE, 0, 0, null, 0, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, [NULL], null]','[null,|NULL     |2195985998|2024-12-10/06:43:07 |       25215|    13579641042383274831|                      0|           |                 |         |                923|            32534|                73728|SYS              |
0h9ak104a3hpb|                       0|select pflags, xpflags, sflags, state, rowid, textlen, marker,  markerlen, hashval, hashval2, rorder, sorder, leafcnt,  orignode, nodeid, parent, opttyp, frompo, selcnt, flags,  numdetailtab, numaggregates, numkeycolumns, numjoins, numinlines,  numwhrnode|NULL       |¾Ú    gWâÝ   À   Ã @0                                                    |SYS                |                                          |                               |        7934|    2925|         60|         1|            0|             2|      4|         2|                 2|             2|â û Ð _ °   ®õÃâÏú3 VAEUQ ! UEU ETUXY UD e  U   UU UQQ"UT  ê UQEBeETTD  Vn   Æ£UE   T PD ýUqQU UU  U  !EEÑÃTD¡   YU å S5UUUUQé    [  e]VEa@U %$P ù¤   PQeU  PD  yU  T  A ÁA @d Q T P AZÐ _                     ,               8ø    ÌÌ      Ð _  edd  d2 2    |        |           3|2024-12-10/06:42:37|           |                  |     |     3662117323|['[null, null, 2024-12-10 06:42:37.0, null, SELECT STATEMENT, null, null, null, null, null, null, null, CHOOSE, 0, 0, null, 0, 2, 2, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, [NULL], null]','[null, null, 202|NULL     |2195985998|2024-12-10/06:42:37 |       35990|                       0|                      0|           |                 |         |               1444|             5852|                 8192|SYS              |
0k8522rmdzg4k|     5437523599453602356|select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege#>0                                                                                                                                                                             |NULL       |¾Ú    gWâ¡   À   Â '                                                     |SYS                |                                          |                               |       13330|    5683|        126|         2|            0|           102|    107|         5|                 4|             2|â û Ð _ °   ®õÃâÏú3 VAEUQ ! UEU ETUXY UD e  U   UU UQQ"UT  ê UQEBeETTD  Vn   Æ£UE   T PD ýUqQU UU  U  !EEÑÃTD¡   YU å S5UUUUQé    [  e]VEa@U %$P ù¤   PQeU  PD  yU  T  A ÁA @d Q T P AZÐ _                     ,               8ø    ÌÌ      Ð _  edd  d2 2    |        |           3|2024-12-10/01:46:08|           |                  |     |     2057665657|['[null, null, 2024-12-10 06:41:38.0, null, SELECT STATEMENT, null, null, null, null, null, null, null, CHOOSE, 0, 0, null, 0, 2, 2, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, [NULL], null]','[null, null, 202|NULL     |2195985998|2024-12-10/06:42:31 |       79051|    12966911773515429724|                      0|           |                 |         |               3770|             5975|                16384|SYS              |
0kgbytsnnjpva|     6502149215501505047|SELECT /*+ NO_STATEMENT_QUEUING RESULT_CACHE (SYSOBJ=TRUE) */ "OWNER","OWNERID","CONSTRAINT_NAME","CONSTRAINT_TYPE","TABLE_NAME","OBJECT_ID","OBJECT_TYPE#","ORIGIN_CON_ID" FROM NO_CROSS_CONTAINER("SYS"."INT$INT$DBA_CONSTRAINTS") "INT$INT$DBA_CONSTRAINTS" |NULL       |                                                                         |SYS                |DBeaver 24?1?0 ? Metadata                 |                               |      361485|  122374|       1763|        27|            0|          1041|      6|         1|                 1|           630|â û Ð _ °   ®õÃâÏú3 VAEUQ ! UEU ETUXY UD e  U   UU UQQ"UT  ê UQEBeETTD  Vn   Æ£UE   T PD ýUqQU UU  U  !EEÑÃTD¡   YU å S5UUUUQé    [  e]VEa@U %$P ù¤   PQeU  PD  yU  T  A ÁA @d Q T P AZÐ _                     ,               8ø    ÌÌ      Ð _ edd  d2 2     |        |           3|2024-12-10/06:42:58|           |                  |     |     1168831965|['[null, null, 2024-12-10 06:42:58.0, null, SELECT STATEMENT, null, null, null, null, null, null, null, ALL_ROWS, 0, 0, null, 0, 630, 630, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, [NULL], null]','[null, nul|NULL     |2195985998|2024-12-10/06:42:57 |      315439|     8523686897152595217|                      0|           |                 |         |             265336|            65183|               221184|SYS              |

4 创建SQL调优任务

创建SQL调优任务的SQLSET格式语法如下

DBMS_SQLTUNE.CREATE_TUNING_TASK(
  sqlset_name       IN VARCHAR2,
  basic_filter      IN VARCHAR2 :=  NULL,
  object_filter     IN VARCHAR2 :=  NULL,
  rank1             IN VARCHAR2 :=  NULL,
  rank2             IN VARCHAR2 :=  NULL,
  rank3             IN VARCHAR2 :=  NULL,
  result_percentage IN NUMBER   :=  NULL,
  result_limit      IN NUMBER   :=  NULL,
  scope             IN VARCHAR2 :=  SCOPE_COMPREHENSIVE,
  time_limit        IN NUMBER   :=  TIME_LIMIT_DEFAULT,
  task_name         IN VARCHAR2 :=  NULL,
  description       IN VARCHAR2 :=  NULL
  plan_filter       IN VARCHAR2 :=  'MAX_ELAPSED_TIME',
  sqlset_owner      IN VARCHAR2 :=  NULL)
RETURN VARCHAR2;

刚才在载入SQL语句时没有选择筛选条件，在创建优化任务时可以根据不同的条件进行筛选，rankn可是设置sql排序的指标，result_limit设置返回的top数量，result_percentage设置返回的语句负载占总体负载的百分比（以rank指标来衡量）。输入参数中，object_filter目前不支持，不能用于筛选SQL，basic_filter由多个条件可选，可以根据SQL_ID,SQL_TEXT,SQL解析用户名来筛选SQL语句。运行下面语句，创建调优任务

DECLARE
   l_sql_tune_task_id  VARCHAR2(100);
 BEGIN
   l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                           sqlset_name    => 'SQL_CAPTURE_STS',
                           scope          => DBMS_SQLTUNE.scope_comprehensive,
                           basic_filter  =>'parsing_schema_name=''SH''',
                           rank1          => 'BUFFER_GETS',
                           result_limit   => 20,
                           time_limit     => 3600,
                           task_name      => 'my_SQL_Capture_task',
                           description    => 'Tuning task for captured SQL');
 END;

创建的调优任务选择SQL_CAPTURE_STS中SH用户依据BUFFER_GETS排名的前20条语句进行调优。

5 执行SQL调优任务，查看调优建议报告

5.1 执行调优任务,查看调优报告

EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK('my_SQL_Capture_task');
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('my_SQL_Capture_task') FROM DUAL;

5.2 调优报告解析

5.2.1 通用信息部分

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : my_SQL_Capture_task
Tuning Task Owner                 : SYS
Workload Type                     : SQL Tuning Set
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 3600
Completion Status                 : COMPLETED
Started at                        : 12/10/2024 07:50:01
Completed at                      : 12/10/2024 07:50:46
SQL Tuning Set (STS) Name         : SQL_CAPTURE_STS
SQL Tuning Set Owner              : SYS
Number of Statements in the STS   : 180

通用信息部分包括调优任务的基本信息及执行情况，如负载类型，执行状态，STS的名称及SQL数量及执行的开始和结束时间等。

5.2.2 汇总部分

-------------------------------------------------------------------------------
                      Global SQL Tuning Result Statistics
-------------------------------------------------------------------------------
Number of SQLs Analyzed                      : 8
Number of SQLs in the Report                 : 3
Number of SQLs with Findings                 : 3
Number of SQLs with Alternative Plan Findings: 2
Number of SQLs with Index Findings           : 2

-------------------------------------------------------------------------------
    SQLs with Findings Ordered by Maximum (Profile/Index) Benefit, Object ID
-------------------------------------------------------------------------------
object ID  SQL ID        statistics profile(benefit) index(benefit) restructure
---------- ------------- ---------- ---------------- -------------- -----------
         8 527r01mtsw3sp                                     90.16%
         4 gc97qk9uycp2n                                     61.91%
         7 c5xnkyyhvzc7z

-------------------------------------------------------------------------------
 Tables with New Potential Indices (ordered by schema, number of times, table)
-------------------------------------------------------------------------------
Schema Name                 Table Name                  Index Name     Nb Time
--------------------------- --------------------------- -------------- --------
                         SH CUSTOMERS                   IDX$$_02C40002        1
                            SALES                       IDX$$_02C40003        1
                            SALES                       IDX$$_02C40005        1
                            TIMES                       IDX$$_02C40006        1

这部分是调优任务的汇总信息，可以看到分析了8条执行语句，这8条语句是调优任务从STS中选择出来的用户SH执行的语句。由3条语句可以优化，2条可以创建索引优化，2条可以通过更换执行计划优化。优化的收益，建议创建索引的表。

5.2.3 详细报告

这部分对汇总部分的每个对象进行详细的解释,这里摘取对象4的部分报告

bject ID     : 4
Schema Name   : SH
Container Name: FREEPDB1
SQL ID        : gc97qk9uycp2n
SQL Text      : SELECT prod_id, LISTAGG(cust_first_name||' '||cust_last_name,
                '; ')
                  WITHIN GROUP (ORDER BY amount_sold DESC) cust_list
                FROM sales, customers
                WHERE sales.cust_id = customers.cust_id AND cust_gender = 'M'

                  AND cust_credit_limit = 15000 AND prod_id BETWEEN 15 AND 18

                  AND channel_id = 2
                GROUP BY prod_id

-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------

1- Index Finding (see explain plans section below)
--------------------------------------------------
  The execution plan of this statement can be improved by creating one or more
  indices.

  Recommendation (estimated benefit: 61.91%)
  ------------------------------------------
  - Consider running the Access Advisor to improve the physical schema design
    or creating the recommended index.
    create index SH.IDX$$_02C40002 on SH.CUSTOMERS("CUST_CREDIT_LIMIT","CUST_GE
    NDER");

  - Consider running the Access Advisor to improve the physical schema design
    or creating the recommended index.  If you choose to create the
    recommended index, consider dropping the index "SH"."SALES_CHANNEL_BIX"
    because it is a prefix of the recommended index.
    create index SH.IDX$$_02C40003 on SH.SALES("CHANNEL_ID","PROD_ID");

  Rationale
  ---------
    Creating the recommended indices significantly improves the execution plan
    of this statement. However, it might be preferable to run "Access Advisor"
    using a representative SQL workload as opposed to a single statement. This
    will allow to get comprehensive index recommendations which takes into
    account index maintenance overhead and additional space consumption.

2- Alternative Plan Finding
---------------------------
  Some alternative execution plans for this statement were found by searching
  the system's real-time and historical performance data.

  The following table lists these plans ranked by their average elapsed time.
  See section "ALTERNATIVE PLANS SECTION" for detailed information on each
  plan.

  id plan hash  last seen            elapsed (s)  origin          note

  -- ---------- -------------------- ------------ --------------- --------------
--
   1 1231089132  2024-09-10/08:43:24        0.039 STS             not reproducib
le
   2 2504125178  2024-12-10/06:42:44        0.515 Cursor Cache

   3  353500715  2024-12-10/06:48:23        0.606 Cursor Cache    original plan


  Recommendation
  --------------
  - Consider creating a SQL plan baseline for the plan with the best average
    elapsed time.
    BEGIN
     dbms_sqltune.create_sql_plan_baseline(
      task_name => 'my_SQL_Capture_task',
      object_id => 4,
      owner_name => 'SYS',
      plan_hash_value => 2504125178);
    END;
    /
  - The plan with id 1 could not be reproduced in the current environment.
    For this reason, a SQL plan baseline cannot be created to instruct the
    Oracle optimizer to pick this plan in the future.

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

对于这条语句，由两条可以优化的措施，一是创建索引，而是创建执行计划基线，报告里给出了执行调优的命令。后面还有详细的解释，优化前后语句执行计划的对比等，限于篇幅，这里就不列出了。

6 写在后面

作为辅助工具，调优任务的批量优化确实可以提高数据库SQL优化的工作效率，提供优化的参考和依据。不过，在执行优化前，还需要DBA综合考虑各种因素，审慎执行。

最后修改时间：2024-12-12 10:09:44

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

使用SQL 优化集（SQL Tuning Set） 优化SQL语句