AUTOMATIC SQL Profile稳定执行计划

来源：这里教程网时间：2026-03-03 11:52:49 作者：

实验目的：借助SQL调优工具进行对改变执行计划,从而达到稳定执行计划。关键词： dbms_stats.gather_table_stats、 DBMS_SQLTUNE.EXECUTE_TUNING_TASK、 DBMS_SQLTUNE.EXECUTE_TUNING_TASK、 DBMS_SQLTUNE.REPORT_TUNING_TASK、 EXECUTE DBMS_SQLTUNE.ACCEPT_SQL_PROFILE 一、创建表，收集表统计信息 SQL> create table t1( n number); 表已创建。 SQL> declare 2 begin 3 for i in 1 .. 10000 4 loop 5 insert into t1 values(i); 6 commit; 7 end loop; 8 end; 9 / PL/SQL 过程已成功完成。 SQL> select count(*) from t1; COUNT(*) ---------- 10000 SQL> create index idx_t1 on t1(n); 索引已创建。 SQL> exec dbms_stats.gather_table_stats( ownname =>'TEST' , tabname =>'T1' , method_opt =>'for all columns size 1', CASCADE => TRUE); PL/SQL 过程已成功完成。二、查询SQL，并收集执行计划 SQL> SELECT /*+ NO_INDEX(T1 IDX_T1) */ * FROM T1 WHERE N=1; N ---------- 1 SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'advanced')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID gn8zuq00kd86g, child number 0 ------------------------------------- SELECT /*+ NO_INDEX(T1 IDX_T1) */ * FROM T1 WHERE N=1 Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 7 (100)| | |* 1 | TABLE ACCESS FULL| T1 | 1 | 4 | 7 (0)| 00:00:01 | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- -------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / T1@SEL$1 Outline Data ------------- /*+ PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- 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" "T1"@"SEL$1") END_OUTLINE_DATA */ Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- --------------------------------------------------- 1 - filter("N"=1) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "N"[NUMBER,22] 已选择42行。三、使用SQL调优工具，创建任务、执行优化 SQL> DECLARE 2 MY_TASK_NAME VARCHAR2(30); 3 MY_SQLTEXT CLOB; 4 BEGIN 5 MY_SQLTEXT := 'SELECT /*+ NO_INDEX(T1 IDX_T1) */ * FROM T1 WHERE N=1'; 6 MY_TASK_NAME := DBMS_SQLTUNE.CREATE_TUNING_TASK( 7 SQL_TEXT => MY_SQLTEXT, 8 USER_NAME =>'TEST', 9 SCOPE =>'COMPREHENSIVE', 10 TIME_LIMIT =>'60', 11 TASK_NAME =>'my_sql_tuning_task_2', 12 description =>'Task to tune a query on table t1'); 13 END; 14 / PL/SQL 过程已成功完成。 SQL> BEGIN 2 DBMS_SQLTUNE.EXECUTE_TUNING_TASK( TASK_NAME =>'my_sql_tuning_task_2'); 3 END; 4 / PL/SQL 过程已成功完成。 SQL> SET LONG 9000 SQL> SET LONGCHUNKSIZE 1000 SQL> SET LINESIZE 800 SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('my_sql_tuning_task_2') from dual; DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2') -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- GENERAL INFORMATION SECTION ------------------------------------------------------------------------------- Tuning Task Name : my_sql_tuning_task_2 Tuning Task Owner : TEST Workload Type : Single SQL Statement Scope : COMPREHENSIVE Time Limit(seconds): 60 Completion Status : COMPLETED Started at : 03/27/2018 14:46:51 Completed at : 03/27/2018 14:46:52 DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2') -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------- Schema Name: TEST SQL ID : agf66q2by2qvp SQL Text : SELECT /*+ NO_INDEX(T1 IDX_T1) */ * FROM T1 WHERE N=1 ------------------------------------------------------------------------------- FINDINGS SECTION (1 finding) ------------------------------------------------------------------------------- 1- SQL Profile Finding (see explain plans section below) -------------------------------------------------------- DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2') -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 为此语句找到了性能更好的执行计划。 Recommendation (estimated benefit: 90.9%) ----------------------------------------- - 考虑接受推荐的 SQL 概要文件。 execute dbms_sqltune.accept_sql_profile(task_name => 'my_sql_tuning_task_2', task_owner => 'TEST', replace => TRUE); Validation results ------------------ 已对 SQL profile 进行测试, 方法为执行其计划和原始计划并测量与计划相对应的执行统计信息。如果其中一个计划运行在很短的时间内就完成, DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2') -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 则另一计划可能只执行了一部分。 Original Plan With SQL Profile % Improved ------------- ---------------- ---------- Completion Status: COMPLETE COMPLETE Elapsed Time (s): .000121 .000013 89.25 % CPU Time (s): 0 0 User I/O Time (s): 0 0 Buffer Gets: 22 2 90.9 % Physical Read Requests: 0 0 Physical Write Requests: 0 0 DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2') -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Physical Read Bytes: 0 0 Physical Write Bytes: 0 0 Rows Processed: 1 1 Fetches: 1 1 Executions: 1 1 Notes ----- 1. the original plan 的统计信息是 10 执行的平均值。 2. the SQL profile plan 的统计信息是 10 执行的平均值。 DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2') -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original With Adjusted Cost ------------------------------ Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2') -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4 | 7 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T1 | 1 | 4 | 7 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("N"=1) 2- Using SQL Profile -------------------- DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2') -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 1369807930 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 | |* 1 | INDEX RANGE SCAN| IDX_T1 | 1 | 4 | 1 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2') -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 - access("N"=1) ------------------------------------------------------------------------------- 四、改变执行计划 SQL> EXECUTE DBMS_SQLTUNE.ACCEPT_SQL_PROFILE( TASK_NAME =>'my_sql_tuning_task_2',task_owner=> 'TEST' ,REPLACE => TRUE); PL/SQL 过程已成功完成。 SQL> SELECT /*+ NO_INDEX(T1 IDX_T1) */ * FROM T1 WHERE N=1; N ---------- 1 SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'advanced')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID gn8zuq00kd86g, child number 0 ------------------------------------- SELECT /*+ NO_INDEX(T1 IDX_T1) */ * FROM T1 WHERE N=1 Plan hash value: 1369807930 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 (100)| | |* 1 | INDEX RANGE SCAN| IDX_T1 | 1 | 4 | 1 (0)| 00:00:01 | --已查看到走索引 PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ --------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / T1@SEL$1 Outline Data ------------- /*+ PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 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(@"SEL$1" "T1"@"SEL$1" ("T1"."N")) END_OUTLINE_DATA */ Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ --------------------------------------------------- 1 - access("N"=1) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "N"[NUMBER,22] Note ----- PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ - SQL profile SYS_SQLPROF_0162663bdb700000 used for this statement 已选择46行。 SQL> SELECT /*+ NO_INDEX(T1 IDX_T1) */ * FROM T1 WHERE N=2; N ---------- 2 SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'advanced')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID c7rgfm3dqkzz7, child number 0 ------------------------------------- SELECT /*+ NO_INDEX(T1 IDX_T1) */ * FROM T1 WHERE N=2 Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 7 (100)| | |* 1 | TABLE ACCESS FULL| T1 | 1 | 4 | 7 (0)| 00:00:01 | ---发现当条件改变，执行计划仍然是全表扫描，可以通过 DBMS_SQLTUNE.ACCEPT_SQL_PROFILE 添加 --- force_match => true 实现SQL脚本具体输入值变化而执行计划不变 PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / T1@SEL$1 Outline Data ------------- /*+ PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 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" "T1"@"SEL$1") END_OUTLINE_DATA */ Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ --------------------------------------------------- 1 - filter("N"=2) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "N"[NUMBER,22] 已选择42行。 SQL> EXECUTE DBMS_SQLTUNE.ACCEPT_SQL_PROFILE( TASK_NAME =>'my_sql_tuning_task_2',task_owner => 'TEST' ,REPLACE => TRUE, force_match => true ); PL/SQL 过程已成功完成。 SQL> SELECT /*+ NO_INDEX(T1 IDX_T1) */ * FROM T1 WHERE N=2; N ---------- 2 SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'advanced')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID c7rgfm3dqkzz7, child number 0 ------------------------------------- SELECT /*+ NO_INDEX(T1 IDX_T1) */ * FROM T1 WHERE N=2 Plan hash value: 1369807930 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 (100)| | |* 1 | INDEX RANGE SCAN| IDX_T1 | 1 | 4 | 1 (0)| 00:00:01 | ---这里具体输入值变化之后，执行计划不变 PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ --------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / T1@SEL$1 Outline Data ------------- /*+ PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 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(@"SEL$1" "T1"@"SEL$1" ("T1"."N")) END_OUTLINE_DATA */ Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ --------------------------------------------------- 1 - access("N"=2) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "N"[NUMBER,22] Note ----- PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ - SQL profile SYS_SQLPROF_01626643a6130001 used for this statement 已选择46行。