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 statementSQL 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')) -------------------------------------------------------------------------------