使用SQL profile优化特定语句的执行计划

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

1016

1 SQL profile原理

SQL profile是常用的干预SQL语句执行计划的技术手段，大概也是在SQL语句执行计划出现问题时DBA第一时间想到的技术措施。
根据Oracle官方的定义，SQL profile是一个或多个hints的集合，以内部格式存储在数据库数据字典中，hints存储的是一个查询所引用表和列的辅助统计信息的集合。Oracle优化器使用这些统计信息来生成更好的执行计划。
除了其它统计信息之外，profile还包括一系列基数调整信息。调整信息的基数基于查询语句的where条件而不是查询的列来度量。例如存在
SELECT * FROM t WHERE x=5 AND y=10的SQL profile，profile存储的是这个语句实际返回的行的数量。SQL profile和执行计划的关系可以由下图看到

SQL profile的hints对整个查询语句生效，不针对具体的执行计划，不产生特定的执行计划。相反，SQL profile的hints纠正优化器评估到的错误的统计信息（这些统计信息产生次优化的执行计划）。如上图所示，优化器在产生执行计划时，同时使用SQL profile里的hints和语句执行环境，其中任何一个发生变化，都会导致执行计划的变化。

2 SQL profile还是SQL baseline

针对查询语句执行计划的优化措施除了SQL profile外，还有SQL outline和SQL baseline，SQL outline在以后的版本中会取消，取代它的是SQL baseline。同样可以固定查询语句的执行计划，SQL profile和SQL baseline的有什么不同，不同场景怎样选择？具体来说，二者的区别有以下两点：

通常来说，SQL baseline是主动的，提前的，先发的预防措施，SQL profile是事后的应对措施。通常，DBA或者开发在sql语句性能变差之前提前设置语句的SQL 执行计划基线，SQL执行计划基线阻止优化器以后产生次优化的执行计划。SQL profile则是在语句的性能出现问题之后，通过运行SQL优化任务产生（也可以手动生成，不过不是官方推荐的）。因为SQL profile是响应式的，它不能保证数据发生剧烈变化时稳定的查询性能。
SQL baseline重建一个特定的执行计划，SQL profile纠正优化器错误的评估。SQL profile在执行计划产生的过程中起作用，影响执行计划的产生。SQL baseline在新的执行计划产生后起作用，影响执行计划的选择。
具体的特定的场景，如何在这两者之间进行选择，Oracle的建议是听 SQL Tuning Advisor的，它推荐哪个就我们就使用哪个。个人的想法是，如果当前语句有更好的执行计划，则使用SQL baseline设置这个执行计划为固定基线，如果当前语句没有更好的执行计划，则使用SQL profile。另有一种情形比较常见，即语句使用不恰当的hint，则使用SQL profile取消此hint。下面是SQL优化建议分别推荐SQL baseline和SQL profile的例子：

SQL profile

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  为此语句找到了性能更好的执行计划。

  Recommendation (estimated benefit: 98.75%)
  ------------------------------------------
  - 系统中存在手动创建的 SQL profile。
    Name:   SYS_SQLPROF_0193bdc335b10000
    Status: ENABLED

SQL baseline

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  为此语句找到了性能更好的执行计划。

  Recommendation (estimated benefit: 98.75%)
  ------------------------------------------
  - 系统中存在手动创建的 SQL profile。
    Name:   SYS_SQLPROF_0193bdc335b10000
    Status: ENABLED
----省略多行
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;

3 SQL profile基本操作

，

3.1 接受SQL profile

SQL优化建议推荐的SQL profile，需要接受才能生效，下面是建议中接受SQL profile的脚本。

BEGIN    
     dbms_sqltune.accept_sql_profile(
      task_name => 'sales_sql_tuning_task',
      task_owner => 'SYS',
      replace => TRUE);
END;

运行上面的脚本，SQL profile就会生效，生效的profile的名称是Oracle自动产生的，这个存储或者是函数(accept_sql_profile过程和函数的区别是accept_sql_profile函数返回接受的profile 名称，这个在不指定名称时非常有用）的语法如下

dbms_sqltune.accept_sql_profile(task_name   => 'TASK_3401',
                                task_owner  => user,
                                name        => 'opt_estimate',
                                description => NULL,
                                category    => NULL,
                                force_match => TRUE,
                                replace     => TRUE);

从上面语法上来看，在接受profile时，可以设置profile的名称，类别，强制匹配等属性，默认的类别是default，default类别是数据库里生效的默认类别，这个类别里的profile在接受后会立即生效。


##### 3.2 更改属性
接受SQL profile之后，可以使用alter_sql_profile存储过程更改它的属性，可以接受的参数包括name，description，category以及status。例如，使用下面命令更改profile的状态。
```sql
dbms_sqltune.alter_sql_profile(name           => 'opt_estimate',
                               attribute_name => 'status',
                               value          => 'disabled');

3.3 激活

SQL profile的激活由 sqltune_category参数控制，这个参数可以在系统级或者会话级设置。系统级的默认设置是default，这个类别的profile会激活。如果在激活profile之前需要先在一个会话测试一下，在接受profile时可以设置为其他的类别，比如test，然后在当前会话激活此类别

ALTER SESSION SET sqltune_category = test;

sqltune_category参数只接受一个类别值，即每次只能激活一个类别。测试后如果确定接受这个profile，更改其类别值为default即可。

3.4 查询创建的SQL profile

接受的profile的信息可以在DBA_SQL_PROFILES查到，DBA_SQL_PROFILES里面可以查到除hint外的所有信息。

SELECT * FROM DBA_SQL_PROFILES;-- 查询SQL profile 
NAME                        |CATEGORY|SIGNATURE           |SQL_TEXT                                    |CREATED                |LAST_MODIFIED          |DESCRIPTION|TYPE  |STATUS |FORCE_MATCHING|TASK_ID|TASK_EXEC_NAME|TASK_OBJ_ID|TASK_FND_ID|TASK_REC_ID|TASK_CON_DBID|
----------------------------+--------+--------------------+--------------------------------------------+-----------------------+-----------------------+-----------+------+-------+--------------+-------+--------------+-----------+-----------+-----------+-------------+
SYS_SQLPROF_0193bdc335b10000|DEFAULT |17059993427489620168|SELECT /*+ FULL(s)*/  count(*) FROM SALES s |2024-12-13 02:05:07.386|2024-12-13 02:05:07.000|           |MANUAL|ENABLED|NO            |    727|EXEC_834      |          1|          1|          1|            0|
SELECT * FROM user_advisor_actions WHERE TASK_ID='727';--相关优化任务信息
TASK_ID|TASK_NAME            |EXECUTION_NAME|REC_ID|ACTION_ID|OBJECT_ID|COMMAND           |COMMAND_ID|FLAGS|ATTR1                |ATTR2|ATTR3      |ATTR4|ATTR5|ATTR6|NUM_ATTR1|NUM_ATTR2|NUM_ATTR3|NUM_ATTR4|NUM_ATTR5|MESSAGE          |MESSAGE_MSG_GID|FILTERED|RESULT_STATUS|RESULT_LAST_MODIFIED|RESULT_MESSAGE|RESULT_MSG_GID|
-------+---------------------+--------------+------+---------+---------+------------------+----------+-----+---------------------+-----+-----------+-----+-----+-----+---------+---------+---------+---------+---------+-----------------+---------------+--------+-------------+--------------------+--------------+--------------+
727|sales_sql_tuning_task|EXEC_834      |     1|        1|        1|ACCEPT SQL PROFILE|        32|    0|sales_sql_tuning_task|1    |SQL PROFILE|     |     |     |        0|        0|        0|        0|        0|考虑接受推荐的 SQL 概要文件。|           5136|N       |NOT AUTOMATED|                    |              |

3.5 查询SQL profile的hints

SQL profile 的hint信息可以从数据库内部数据字典表里查到，也可以利用中间表将SQL转入后通过查询中间表。

3.5.1 查询内部数据字典表

SELECT so.name,so.CATEGORY,extractValue(value(h),'.') AS hint
 FROM sys.sqlobj$data od, sys.sqlobj$ so,
  table(xmlsequence(extract(xmltype(od.comp_data),'/outline_data/hint'))) h
  WHERE so.signature = od.signature
  AND so.category = od.category
  AND so.obj_type = od.obj_type
  AND so.plan_id = od.plan_id AND so.name='SYS_SQLPROF_0193bdc335b10000';
NAME                        |CATEGORY|HINT                              |
----------------------------+--------+----------------------------------+
SYS_SQLPROF_0193bdc335b10000|DEFAULT |OPTIMIZER_FEATURES_ENABLE(default)|
SYS_SQLPROF_0193bdc335b10000|DEFAULT |IGNORE_OPTIM_EMBEDDED_HINTS       |

可以看到profile的两个hint，OPTIMIZER_FEATURES_ENABLE(default)和IGNORE_OPTIM_EMBEDDED_HINTS。

3.5.2 通过中间表查询

创建中间表，不能创建在SYS模式下

BEGIN 
dbms_sqltune.create_stgtab_sqlprof(table_name      => 'MYSTGTAB');
END;

拷贝SQL profile 至中间表

BEGIN
dbms_sqltune.pack_stgtab_sqlprof(profile_name         => 'SYS_SQLPROF_0193bdc335b10000',
                                 profile_category     => 'DEFAULT',
                                 staging_table_name   => 'MYSTGTAB',
                                 staging_schema_owner =>  'SH');
END;

查询中间表

SELECT SQL_HANDLE,OBJ_NAME,SQL_TEXT,CATEGORY,COMP_DATA,OPERATION,OPTIONS,OBJECT_NAME ,OBJECT_ALIAS,OPTIMIZER,CARDINALITY FROM sh.MYSTGTAB;
SQL_HANDLE          |OBJ_NAME                    |SQL_TEXT                                    |CATEGORY|COMP_DATA                                                                                                                                   |OPERATION        |OPTIONS  |OBJECT_NAME      |OBJECT_ALIAS|OPTIMIZER|CARDINALITY|
--------------------+----------------------------+--------------------------------------------+--------+--------------------------------------------------------------------------------------------------------------------------------------------+-----------------+---------+-----------------+------------+---------+-----------+
SQL_ecc145a2740480c8|SYS_SQLPROF_0193bdc335b10000|SELECT /*+ FULL(s)*/  count(*) FROM SALES s |DEFAULT |<outline_data><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE(default)]]></hint><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint></outline_data>|SELECT STATEMENT |         |                 |            |ALL_ROWS |          1|
SQL_ecc145a2740480c8|SYS_SQLPROF_0193bdc335b10000|SELECT /*+ FULL(s)*/  count(*) FROM SALES s |DEFAULT |<outline_data><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE(default)]]></hint><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint></outline_data>|SORT             |AGGREGATE|                 |            |         |          1|
SQL_ecc145a2740480c8|SYS_SQLPROF_0193bdc335b10000|SELECT /*+ FULL(s)*/  count(*) FROM SALES s |DEFAULT |<outline_data><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE(default)]]></hint><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint></outline_data>|PARTITION RANGE  |ALL      |                 |            |         |     918843|
SQL_ecc145a2740480c8|SYS_SQLPROF_0193bdc335b10000|SELECT /*+ FULL(s)*/  count(*) FROM SALES s |DEFAULT |<outline_data><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE(default)]]></hint><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint></outline_data>|BITMAP CONVERSION|COUNT    |                 |"S"@"SEL$1" |         |     918843|
SQL_ecc145a2740480c8|SYS_SQLPROF_0193bdc335b10000|SELECT /*+ FULL(s)*/  count(*) FROM SALES s |DEFAULT |<outline_data><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE(default)]]></hint><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint></outline_data>|BITMAP INDEX     |FULL SCAN|SALES_CHANNEL_BIX|            |         |           |

中间表里<outline_data>列显示了SQL profile的hint，CARDINALITY列可以看到查询涉及到的每个对象的基数。

4 SQL profile的效果检验

4.1 接受SQL profile之后语句的执行计划

接受SQL profile后不执行语句检查语句当前的执行计划

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8rypxbhx7569n, child number 0
-------------------------------------
SELECT /*+ FULL(s)*/  count(*) FROM SALES s

Plan hash value: 3519235612

--------------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |       |  3820 (100)|          |       |       |
|   1 |  SORT AGGREGATE      |       |     1 |            |          |       |       |
|   2 |   PARTITION RANGE ALL|       |   918K|  3820   (1)| 00:00:01 |     1 |    15 |
|   3 |    TABLE ACCESS FULL | SALES |   918K|  3820   (1)| 00:00:01 |     1 |    15 |
--------------------------------------------------------------------------------------

查询语句并没有使用SQL profile，原因是SQL profile只给优化器提供相应的信息，不会生成新的执行计划。

4.2 运行一下语句后再看执行计划

select * from table(dbms_xplan.display_cursor('8rypxbhx7569n',null,'outline'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8rypxbhx7569n, child number 0
-------------------------------------
SELECT /*+ FULL(s)*/  count(*) FROM SALES s

Plan hash value: 4022732600

-------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name              | Rows  | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                   |       |    64 (100)|          |       |       |
|   1 |  SORT AGGREGATE           |                   |     1 |            |          |       |       |
|   2 |   PARTITION RANGE ALL     |                   |   918K|    64   (0)| 00:00:01 |     1 |    15 |
|   3 |    BITMAP CONVERSION COUNT|                   |   918K|    64   (0)| 00:00:01 |       |       |
|   4 |     BITMAP INDEX FULL SCAN| SALES_CHANNEL_BIX |       |            |          |     1 |    15 |
-------------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('23.1.0')
      DB_VERSION('23.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      BITMAP_TREE(@"SEL$1" "S"@"SEL$1" AND(("SALES"."CHANNEL_ID")))
      END_OUTLINE_DATA
  */

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------

   3 -  SEL$1 / "S"@"SEL$1"
         U -  FULL(s) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS

Note
-----
   - SQL profile SYS_SQLPROF_0193bdc335b10000 used for this statement

从执行计划的Note可以看到，语句使用了SQL profile，优化器在生成语句的执行计划时使用了SQL profile提供的hints。

5 写在后面

作为干预语句执行计划的技术手段，SQL profile可以在不更改应用的情况下通过hints使优化器生成性能更优的执行计划，这种更改对应用是透明的。选择SQL profile或者是SQL baseline除了dba的判断外，SQL 优化建议任务也会提供一种比较好的判断依据，

最后修改时间：2024-12-17 10:01:38

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

关注作者