sql> create table t1 (
2 n number);
2、生成测试数据
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
3、收集统计信息
sql>exec dbms_stats.gather_table_stats(ownname=>'TEST',tabname=>'T1',method_opt=>'for all columns size 1',CASCADE=>true);
4、创建索引
sql> create index idx_t1 on t1(n);
索引已创建。
5 、查询语句，使用no_index 使语句不走索引
sql> select /*+ no_index(t1 idx_t1)*/ * from t1 where n=1;
N
----------
1
6、查看执行计划
sql> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
sql_ID au4x8czp461nu,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行。
6、创建自动调整任务
sql>declare
my_task_name varchar2(30);
my_sqltext clob;
begin
my_sqltext:=' select /*+ no_index(t1 idx_t1)*/ * from t1 where n=1';
my_task_name:=dbms_sqltune.create_tuning_task(
sql_text => my_sqltext,
user_name => 'TEST',
scope =>'COMPREHENSIVE',
time_limit=>60,
task_name =>'my_sql_tuning_task_2',
description=>'Task to tune a query on table t1');
end;
/
7、执行自动调整任务
sql>BEGIN
dbms_sqltune.execute_tuning_task(task_name=>'my_sql_tuning_task_2');
end;
/
8、查看自动调整任务的结果
sql>SET LONG 9000
sql>SET LONGCHUNKSIZE 1000
sql>SET LINESIZE 200
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 : 09/03/2016 01:24:36
Completed at : 09/03/2016 01:24:38
DBMS_sqlTUNE.REPORT_TUNING_TASK('MY_sql_TUNING_TASK_2')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Schema Name: TEST
sql ID : 21qpu3xh964v1
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): .000138 .000016 88.4 %
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)
-------------------------------------------------------------------------------
9、接受更好的执行计划建议
sql> execute dbms_sqltune.accept_sql_profile(task_name=>'my_sql_tuning_task_2',task_owner=>' TEST',replace=>TRUE);
PL/sql 过程已成功完成。
10、再次执行sql语句，验证结果
sql> select /*+ no_index(t1 idx_t1)*/ * from t1 where n=1;
N
----------
1
11、查看执行计划
sql> select * from table(dbms_xplan.display_cursor(null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
sql_ID au4x8czp461nu,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_0156ebfbb3b00000 used for this statement
已选择46行。
12、稳定的执行计划是不能有变动的，如果变动将不再生效，如当条件中n=2时，还是继续走全表扫描。
sql> select /*+ no_index(t1 idx_t1)*/ * from t1 where n=2;
N
----------
2
sql> select * from table(dbms_xplan.display_cursor(null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
sql_ID 1hnvc732gf7qu,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 |
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行。
13、通过调用dbms_sqltuen.drop_tuning_task可以删除已经存在的优化任务
sql>execdbms_sqltune.drop_tuning_task('my_sql_tuning_task_2');
原文链接：https://www.f2er.com/oracle/212791.html