你的位置:首页 > 数据库

[数据库]为什么需要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创建,它看起来有如下的意思:

  1. 在优化器评估SQL时使用了额外的对象帮助完成评估;
  2. 对象改变了优化器原先的评估计划;

当看到这些信息,比较关心的是这个对象(SLQ profile)是什么?它做了什么?是否真的需要它?带着这些疑问学习和探索,最终决解了遇到的问题。

SQL> @iUSERNAME       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)asselect  level,cast(sysdate-level/24 as date) from   dualconnect 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$1Outline 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 a200select  dbms_sqltune.report_tuning_task(:task_name) REPORT_TUNINGfrom  dual;REPORT_TUNING----------------------------------------------------------------------------------------------------------------------GENERAL INFORMATION SECTION-------------------------------------------------------------------------------Tuning Task Name  : orders_tuning_taskTuning Task Owner : OPS$SYWUWorkload Type   : Single SQL StatementScope       : COMPREHENSIVETime Limit(seconds): 60Completion Status : COMPLETEDStarted at     : 11/07/2016 21:43:25Completed at    : 11/07/2016 21:43:27-------------------------------------------------------------------------------Schema Name: OPS$SYWUSQL ID   : 9ybj4xdc5hsrbSQL 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$1Outline 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 statementSQL> @sql 2061925043Show SQL text, child cursors and execution stats for SQL hash value 2061925043 child OPS$SYWU@sydb_111 reportHASH_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") innerQuerySELECT /* 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)) innerQuerySELECT /* 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'))) innerQuerySELECT /* 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, C3FROM (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做了如下的操作:

  1. 动态采样分析全表数据(无索引);
  2. 动态采样分析表中非空数据(无索引);
  3. 用全表扫描的方式动态采样分析相关的数据;
  4. 用索引扫描的方式动态采样分析相关的数据;
  5. 对比旧的和新的执行计划;

Oracle对比旧的和新的执行计划后,将消耗小的执行计划信息保存到SQL profile中。

优化器的问题

通常优化器相信:

  1. 数据值分布均匀(比如假设:表列数据中,数值2比数值5一样使用频繁);
  2. 数据行分布均匀(比如假设:没有物理cluster或者数据排序);
  3. 对于范围数据是连续的,没有漏缺。

优化器信任收集的统计信息,这些信息包括表行记录数,distinct value,max/min value,直方图信息;换一种通俗的说法,统计信息捕获了表整体形状数据,但有些低级别的信息丢失了。这种分析对于大多数数据来说往往工作得很好,但实际情况中,不可避免的有违反规则的例外,比如对于一张大表,98%的数据可能以随机的方式分配在整个段中(segment),剩余的2%的数据可能只集中在几个数据块中;不幸的是收集统计信息时没有记录这些细节;这就引发一个问题,已经有的统计信息不能完全有效的帮助优化器生成正确的执行计划,所以到了这里问题转变为什么可以弥补或纠正这些信息,让优化器面对这种特定的SQL时可以评估正确,生成好的执行计划。

什么是SQL Profile

通过上面的实验大体将SQL Profile定义为:

  1. 为特定SQL创建和保存执行计划信息;
  2. 使用实际运行时的数据来帮助优化器为特定的SQL评估和生成更好的执行计划;

首先通过dbms_sqltune.create_tunning_task创建任务告诉数据库存在问题的可以改善的SQL,这个操作在11G或以后的版本中可以通过Automatic SQL Tuning在对"most active" SQL 分析时创建;然后运行dbms_sqltune.execute_tuning_task评估,这个过程包括三个主要步骤:

  1. 动态采样分析表数据,获取到真实的实数信息(最重要的比如,Cardinality)
  2. 提供这些真实的实数信息给优化器,让优化器重新评估;
  3. 如果优化器评估出新的执行计划,重复多次运行旧的执行计划和新的执行计划,最后对比性能;

如果优化器试运行得出的结果为:

  1. 优化器评估后产生了新的执行计划;
  2. 新的执行计划比旧的执行计划性能消耗更小,大幅提升性能;

则表明优化器证明旧的评估对于特定的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将乘以这些数字,优化器会更真实的查看到表中的数据信息,然后做出评估。