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 分析背后做了什么