实验目的：借助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

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 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

--------------------------------------------------------------------------

--------------------------------------------------------------------------

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

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 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

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 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

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 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

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 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行。

ITPub博客

AUTOMATIC SQL Profile稳定执行计划

最新文章