共计 12268 个字符，预计需要花费 31 分钟才能阅读完成。
>阅读导航
- 为什么需要 SQL Profile
- 遇到的问题
- Oracle 分析背后做了什么
- 优化器的问题
- 什么是 SQL Profile
为什么需要 SQL Profile
Why oracle need SQL Profiles,how it work and what are SQL Profiles…
使用 DBMS_XPLAN.DISPLAY 分析 SQL 执行计划，通常会看到 Note 中有类似下面这样的提示；
| Note | |
| ----- | |
| - SQL profile "SYS_SQLPROF_0158283a9b920000" used for this statement |
SQL profile 由人为手工创建或在 Automatic SQL Tunning 阶段由 SQL tuning advisor 创建，它看起来有如下的意思:
- 在优化器评估 SQL 时使用了额外的 对象 帮助完成评估；
- 对象 改变了优化器原先的评估计划；
当看到这些信息，比较关心的是这个对象 (SLQ profile) 是什么? 它做了什么? 是否真的需要它? 带着这些疑问学习和探索，最终决解了遇到的问题。
| SQL> @i | |
| USERNAME INST_NAME HOST_NAME SID SERIAL# VERSION STARTED SPID OPID CPID SADDR PADDR | |
| -------------------- -------------------- ------------------------- ----- -------- ---------- -------- ---------- ----- --------------- ---------------- ---------------- | |
| OPS$SYWU sydb sywu.com 288 22197 11.2.0.4.0 20160421 13736 46 3392:1312 0000000071FE0DA0 0000000072149F40 |
遇到的问题
假设有这样一张类似订单的表 orders；
| create table orders(order_no,order_date) | |
| as | |
| select | |
| level,cast(sysdate-level/24 as date) | |
| from | |
| dual | |
| connect by level<=5E5; | |
| SQL> @desc orders | |
| Name Null? Type | |
| ------------------------------- -------- ---------------------------- | |
| 1 ORDER_NO NUMBER | |
| 2 ORDER_DATE DATE |
保存订单信息，order_date 上创建了索引。
create index idx_orders_dt on orders(order_date);
在交易中可能经常遇到某些原因导致交易延期的情况，为了测试这个问题，开发人员添加了未来某一天这样的日期值测试；这里用一个清晰的时间来代替未来的日期；
INSERT INTO ORDERS VALUES (-1, DATE '9999-01-01');
和正常使用的一样，该表定期收集了统计信息；
exec dbms_stats.gather_table_stats(user,'orders', cascade => true);
当系统查询当天的交易记录时发现优化器使用全表扫描，并非索引扫描；
| ------------------------------------------------------------------------------------------------------------------------------- | |
| | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | | |
| ------------------------------------------------------------------------------------------------------------------------------- | |
| | 0 | SELECT STATEMENT | | 1 | | | 130 (100)| | 10 |00:00:00.23 | 329 | 323 | | |
| |* 1 | TABLE ACCESS FULL| ORDERS | 1 | 496K| 6302K| 130 (26)| 00:00:02 | 10 |00:00:00.23 | 329 | 323 | | |
| ------------------------------------------------------------------------------------------------------------------------------- | |
| Query Block Name / Object Alias (identified by operation id): | |
| ------------------------------------------------------------- | |
| 1 - SEL$1 / ORDERS@SEL$1 | |
| Outline Data | |
| ------------- | |
| /*+ | |
| BEGIN_OUTLINE_DATA | |
| IGNORE_OPTIM_EMBEDDED_HINTS | |
| OPTIMIZER_FEATURES_ENABLE('11.2.0.4') | |
| DB_VERSION('11.2.0.4') | |
| ALL_ROWS | |
| OUTLINE_LEAF(@"SEL$1") | |
| FULL(@"SEL$1" "ORDERS"@"SEL$1") | |
| END_OUTLINE_DATA | |
| */ | |
| Predicate Information (identified by operation id): | |
| --------------------------------------------------- | |
| 1 - filter("ORDER_DATE">=TRUNC(SYSDATE@!,'fmdd')) | |
| Column Projection Information (identified by operation id): | |
| ----------------------------------------------------------- | |
| 1 - "ORDERS"."ORDER_NO"[NUMBER,22], "ORDER_DATE"[DATE,7] |
显然对于这样一张交易记录表，实际当天的记录数据只占全表数据量的 4.1% 左右，使用索引扫描的方式开销小于全表扫描，但优化器对范围评估错误。接着使用 DBMS_SQLTUNE 分析 SQL；
| var task_name varchar2(30) | |
| BEGIN | |
| :task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => 'select * from orders where order_date>=trunc(sysdate,''DD'')', | |
| user_name => user, | |
| scope => 'COMPREHENSIVE', | |
| time_limit => 60, | |
| task_name => 'orders_tuning_task'); | |
| END; | |
| / |
执行分析；
| alter session set events '10046 trace name context forever,level 12'; | |
| exec DBMS_SQLTUNE.execute_tuning_task(task_name => :task_name); |
分析结果；
| col REPORT_TUNING format a200 | |
| select | |
| dbms_sqltune.report_tuning_task(:task_name) REPORT_TUNING | |
| from | |
| dual; | |
| REPORT_TUNING | |
| ---------------------------------------------------------------------------------------------------------------------- | |
| GENERAL INFORMATION SECTION | |
| ------------------------------------------------------------------------------- | |
| Tuning Task Name : orders_tuning_task | |
| Tuning Task Owner : OPS$SYWU | |
| Workload Type : Single SQL Statement | |
| Scope : COMPREHENSIVE | |
| Time Limit(seconds): 60 | |
| Completion Status : COMPLETED | |
| Started at : 11/07/2016 21:43:25 | |
| Completed at : 11/07/2016 21:43:27 | |
| ------------------------------------------------------------------------------- | |
| Schema Name: OPS$SYWU | |
| SQL ID : 9ybj4xdc5hsrb | |
| SQL Text : select * from orders where order_date>=trunc(sysdate,'DD') | |
| ------------------------------------------------------------------------------- | |
| FINDINGS SECTION (1 finding) | |
| ------------------------------------------------------------------------------- | |
| 1- SQL Profile Finding (see explain plans section below) | |
| -------------------------------------------------------- | |
| A potentially better execution plan was found for this statement. | |
| Recommendation (estimated benefit: 98.78%) | |
| ------------------------------------------ | |
| - Consider accepting the recommended SQL profile. | |
| execute dbms_sqltune.accept_sql_profile(task_name => | |
| 'orders_tuning_task', task_owner => 'OPS$SYWU', replace => TRUE); | |
| Validation results | |
| ------------------ | |
| The SQL profile was tested by executing both its plan and the original plan | |
| and measuring their respective execution statistics. A plan may have been | |
| only partially executed if the other could be run to completion in less time. | |
| Original Plan With SQL Profile % Improved | |
| ------------- ---------------- ---------- | |
| Completion Status: COMPLETE COMPLETE | |
| Elapsed Time (s): .041546 .000132 99.68 % | |
| CPU Time (s): .029895 .0001 99.66 % | |
| User I/O Time (s): .015204 .000032 99.78 % | |
| Buffer Gets: 328 4 98.78 % | |
| Physical Read Requests: 45 0 100 % | |
| Physical Write Requests: 0 0 | |
| Physical Read Bytes: 10682368 9830 99.9 % | |
| Physical Write Bytes: 0 0 | |
| Rows Processed: 10 10 | |
| Fetches: 10 10 | |
| Executions: 1 1 | |
| Notes | |
| ----- | |
| 1. Statistics for the original plan were averaged over 10 executions. | |
| 2. Statistics for the SQL profile plan were averaged over 10 executions. | |
| ------------------------------------------------------------------------------- | |
| EXPLAIN PLANS SECTION | |
| ------------------------------------------------------------------------------- | |
| 1- Original With Adjusted Cost | |
| ------------------------------ | |
| Plan hash value: 1275100350 | |
| ---------------------------------------------------------------------------- | |
| | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | |
| ---------------------------------------------------------------------------- | |
| | 0 | SELECT STATEMENT | | 18 | 234 | 130 (26)| 00:00:02 | | |
| |* 1 | TABLE ACCESS FULL| ORDERS | 18 | 234 | 130 (26)| 00:00:02 | | |
| ---------------------------------------------------------------------------- | |
| Predicate Information (identified by operation id): | |
| --------------------------------------------------- | |
| 1 - filter("ORDER_DATE">=TRUNC(SYSDATE@!,'fmdd')) | |
| 2- Using SQL Profile | |
| -------------------- | |
| Plan hash value: 3364688013 | |
| --------------------------------------------------------------------------------------------- | |
| | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | |
| --------------------------------------------------------------------------------------------- | |
| | 0 | SELECT STATEMENT | | 18 | 234 | 3 (0)| 00:00:01 | | |
| | 1 | TABLE ACCESS BY INDEX ROWID| ORDERS | 18 | 234 | 3 (0)| 00:00:01 | | |
| |* 2 | INDEX RANGE SCAN | IDX_ORDERS_DT | 10 | | 2 (0)| 00:00:01 | | |
| --------------------------------------------------------------------------------------------- | |
| Predicate Information (identified by operation id): | |
| --------------------------------------------------- | |
| 2 - access("ORDER_DATE">=TRUNC(SYSDATE@!,'fmdd')) | |
| ------------------------------------------------------------------------------- |
oracle 通过分析发现了问题，产生了新的执行计划，并对比两个执行计划，新的执行计划改善 90%+ 的性能，并且改善性能问题只需要同意使用 SQL Profile 即可；然后允许数据库使用 SQL Profile。
exec dbms_sqltune.ACCEPT_SQL_PROFILE(TASK_NAME=>:task_name);
再次执行 SQL 时，优化器使用了 SQL Profile 和新的执行计划。
| select * from orders where order_date>=trunc(sysdate,'DD'); | |
| SQL_ID 3zcvw1pxfcypm, child number 0 | |
| ------------------------------------- | |
| select * from orders where order_date>=trunc(sysdate,'DD') | |
| Plan hash value: 3364688013 | |
| --------------------------------------------------------------------------------------------------------------------------------------- | |
| | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | | |
| --------------------------------------------------------------------------------------------------------------------------------------- | |
| | 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 10 |00:00:00.01 | 6 | | |
| | 1 | TABLE ACCESS BY INDEX ROWID| ORDERS | 1 | 18 | 234 | 3 (0)| 00:00:01 | 10 |00:00:00.01 | 6 | | |
| |* 2 | INDEX RANGE SCAN | IDX_ORDERS_DT | 1 | 10 | | 2 (0)| 00:00:01 | 10 |00:00:00.01 | 3 | | |
| --------------------------------------------------------------------------------------------------------------------------------------- | |
| Query Block Name / Object Alias (identified by operation id): | |
| ------------------------------------------------------------- | |
| 1 - SEL$1 / ORDERS@SEL$1 | |
| 2 - SEL$1 / ORDERS@SEL$1 | |
| Outline Data | |
| ------------- | |
| /*+ | |
| BEGIN_OUTLINE_DATA | |
| IGNORE_OPTIM_EMBEDDED_HINTS | |
| OPTIMIZER_FEATURES_ENABLE('11.2.0.4') | |
| DB_VERSION('11.2.0.4') | |
| ALL_ROWS | |
| OUTLINE_LEAF(@"SEL$1") | |
| INDEX_RS_ASC(@"SEL$1" "ORDERS"@"SEL$1" ("ORDERS"."ORDER_DATE")) | |
| END_OUTLINE_DATA | |
| */ | |
| Predicate Information (identified by operation id): | |
| --------------------------------------------------- | |
| 2 - access("ORDER_DATE">=TRUNC(SYSDATE@!,'fmdd')) | |
| Column Projection Information (identified by operation id): | |
| ----------------------------------------------------------- | |
| 1 - "ORDERS"."ORDER_NO"[NUMBER,22], "ORDER_DATE"[DATE,7] | |
| 2 - "ORDERS".ROWID[ROWID,10], "ORDER_DATE"[DATE,7] | |
| Note | |
| ----- | |
| - SQL profile SYS_SQLPROF_01582d15092f0001 used for this statement | |
| SQL> @sql 2061925043 | |
| Show SQL text, child cursors and execution stats for SQL hash value 2061925043 child OPS$SYWU@sydb_111 report | |
| HASH_VALUE CH# PLAN_HASH SQL_TEXT SQL_PROFILE | |
| ---------- ----- ---------- -------------------------------------------------------------------------------------------------------------- ------------------------------ | |
| 2061925043 0 3364688013 select * from orders where order_date>=trunc(sysdate,'DD') SYS_SQLPROF_01582d15092f0001 | |
| CH# PARENT_HANDLE OBJECT_HANDLE PARSES H_PARSES EXECUTIONS FETCHES ROWS_PROCESSED LIOS PIOS SORTS CPU_MS ELA_MS USERS_EXECUTING | |
| ----- ---------------- ---------------- ---------- ---------- ---------- ---------- -------------- ---------- ---------- ---------- ---------- ---------- --------------- | |
| 0 0000000064618858 0000000063A03108 1 3 1 2 10 16 6 0 7.999 8.621 0 |
Oracle 分析背后做了什么
很惊奇，为什么分析后优化器就能找出问题所在，此时焦点都集中在 trace 文件了；分析 trace 文件，发现如下信息；
| SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring | |
| optimizer_features_enable(default) opt_param('parallel_execution_enabled', | |
| 'false') result_cache */ COUNT(C1) | |
| FROM | |
| (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS("ORDERS") */ 1 AS C1 FROM | |
| "ORDERS" SAMPLE BLOCK(56.8182, 2) SEED(1) "ORDERS") innerQuery | |
| SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring | |
| optimizer_features_enable(default) opt_param('parallel_execution_enabled', | |
| 'false') result_cache */ COUNT(C1) | |
| FROM | |
| (SELECT /*+ qb_name("innerQuery") INDEX_FFS("ORDERS" "IDX_ORDERS_DT") */ 1 | |
| AS C1 FROM "ORDERS" SAMPLE BLOCK(59.5238, 2) SEED(1) "ORDERS" WHERE ("ORDERS".ORDER_DATE IS NOT NULL)) innerQuery | |
| SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring | |
| optimizer_features_enable(default) opt_param('parallel_execution_enabled', | |
| 'false') result_cache */ COUNT(C1) | |
| FROM | |
| (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS("ORDERS") */ 1 AS C1 FROM | |
| "ORDERS" SAMPLE BLOCK(56.8182, 2) SEED(1) "ORDERS" WHERE | |
| ("ORDERS"."ORDER_DATE">=TRUNC(SYSDATE@!,'fmdd'))) innerQuery | |
| SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring | |
| optimizer_features_enable(default) opt_param('parallel_execution_enabled', | |
| 'false') result_cache OPT_ESTIMATE(@"innerQuery", TABLE, "ORDERS", | |
| SCALE_ROWS=3.545138895e-05) */ C1, C2, C3 | |
| FROM | |
| (SELECT /*+ qb_name("innerQuery") INDEX("ORDERS" "IDX_ORDERS_DT") */ | |
| COUNT(*) AS C1, 4294967295 AS C2, COUNT(*) AS C3 FROM "ORDERS" "ORDERS" | |
| WHERE ("ORDERS"."ORDER_DATE">=TRUNC(SYSDATE@!,'fmdd'))) innerQuery | |
| /* SQL Analyze(156,0) */ select * from orders where order_date>=trunc(sysdate,'DD') |
从 trace 文件分析得出，oracle 做了如下的操作:
- 动态采样分析全表数据(无索引)；
- 动态采样分析表中非空数据(无索引)；
- 用全表扫描的方式动态采样分析相关的数据；
- 用索引扫描的方式动态采样分析相关的数据；
- 对比旧的和新的执行计划；
Oracle 对比旧的和新的执行计划后，将消耗小的执行计划信息保存到 SQL profile 中。
优化器的问题
通常优化器相信:
- 数据值分布均匀(比如假设：表列数据中，数值 2 比数值 5 一样使用频繁)；
- 数据行分布均匀(比如假设：没有物理 cluster 或者数据排序)；
- 对于范围数据是连续的，没有漏缺。
优化器信任收集的统计信息，这些信息包括表行记录数,distinct value,max/min value, 直方图信息；换一种通俗的说法，统计信息捕获了表整体形状数据，但有些低级别的信息丢失了 。这种分析对于大多数数据来说往往工作得很好，但实际情况中， 不可避免的有违反规则的例外 ，比如对于一张大表，98% 的数据可能以随机的方式分配在整个段中(segment)，剩余的 2% 的数据可能只集中在几个数据块中；不幸的是 收集统计信息时没有记录这些细节；这就引发一个问题，已经有的统计信息不能完全有效的帮助优化器生成正确的执行计划，所以到了这里问题转变为什么可以弥补或纠正这些信息，让优化器面对这种特定的 SQL 时可以评估正确，生成好的执行计划。
什么是 SQL Profile
通过上面的实验大体将 SQL Profile 定义为：
- 为特定 SQL 创建和保存执行计划信息；
- 使用实际运行时的数据来帮助优化器为特定的 SQL 评估和生成更好的执行计划；
首先通过 dbms_sqltune.create_tunning_task 创建任务告诉数据库存在问题的可以改善的 SQL，这个操作在 11G 或以后的版本中可以通过 Automatic SQL Tuning 在对 ”most active” SQL 分析时创建；然后运行 dbms_sqltune.execute_tuning_task 评估，这个过程包括三个主要步骤:
- 动态采样分析表数据，获取到 真实的实数信息(最重要的比如，Cardinality)
- 提供这些真实的实数信息给优化器，让优化器重新评估；
- 如果优化器评估出新的执行计划，重复多次运行旧的执行计划和新的执行计划，最后对比性能；
如果优化器试运行得出的结果为:
- 优化器评估后产生了新的执行计划；
- 新的执行计划比旧的执行计划性能消耗更小，大幅提升性能；
则表明优化器证明旧的评估对于特定的 SQL 是错误的，一些低级的数据被分析出，进一步，oracle 会将这些信息保存供以后使用。但是如何保存这些信息呢? 不可能通过定期性的更新统计信息，因为统计信息不包括这些信息。所以，数据库使用一个独立的对象 (SQL Profile) 保存 SQL 和这些 (cardinality) 信息。oracle 以 opt_estimate hints 的格式保存 cardinality 信息；
/+ opt_estimate(table, orders, scale_rows=10) */
或者
/+ opt_estimate(index_scan, orders, IDX_ORDERS_DT, scale_rows=0.001) */
所以如果使用了 SQL Profile，评估时默认的 cardinality 将乘以这些数字，优化器会更真实的查看到表中的数据信息，然后做出评估。
更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12
本文永久更新链接地址：http://www.linuxidc.com/Linux/2016-11/137224.htm
正文完
星哥玩云-微信公众号
