Oracle 10G强大的SQL优化工具：SQL Tuning Advisor
2016年11月14日
15:05
Oracle 10G推出了强大的SQL优化工具：SQL Tuning Advisor，使用该功能必须保证优化器是CBO模式，对SQL进行优化需要执行DBMS_SQLTUNE包，因而需要advisor权限。
举个例子介绍如何优化一条发现问题的语句
--1.创建测试环境
SQL> show user;
USER is "SYS"
--授予普通用户advisor的权限
SQL> grant advisor to owner;
Grant succeeded.
SQL> conn owner/owner
Connected.
SQL> create table test1114 as select * from dba_objects;
SQL> insert into test1114 select * from dba_objects;
SQL> set timing on
SQL> set autot on
SQL> select * from test1114;
Elapsed: 00:01:09.81
Execution Plan
----------------------------------------------------------
Plan hash value: 3447467757
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 110K| 21M| 588 (1)| 00:00:08 |
| 1 | TABLE ACCESS FULL| TEST1114 | 110K| 21M| 588 (1)| 00:00:08 |
------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
29 recursive calls
1 db block gets
11646 consistent gets
1080 physical reads
176 redo size
16498937 bytes sent via SQL*Net to client
106046 bytes received via SQL*Net from client
9595 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
143898 rows processed
--2.创建优化任务
步骤一: 调用函数CREATE_TUNING_TASK来创建优化任务；
步骤二: 调用存储过程EXECUTE_TUNING_TASK执行该任务；
SQL>set autotrace off
SQL>set timing off
SQL> declare
owner_sql varchar2(30);
sqltext_me clob;
begin
sqltext_me := 'select * from test1114';
--删除优化任务
dbms_sqltune.drop_tuning_task(task_name =>'tuning_owner_test');
--创建优化任务
owner_sql := dbms_sqltune.create_tuning_task (
sql_text => sqltext_me,
user_name => 'OWNER',
scope => 'comprehensive',
time_limit => 60,
task_name => 'tuning_owner_test',
description => 'task to tune a query on a specified table') ;
--执行优化任务
dbms_sqltune.execute_tuning_task( task_name => 'tuning_owner_test');
end;
/
PL/SQL procedure successfully completed.
--3.执行优化任务
调用dbms_sqltune.execute_tuning_task过程来执行前面创建好的优化任务。
SQL> exec dbms_sqltune.execute_tuning_task('tuning_owner_test');
PL/SQL procedure successfully completed.
--4.检查优化任务的状态
-通过查看dba_advisor_task或者suser_advisor_tasks可以检查优化任务的状态
SQL> SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name ='tuning_owner_test';
TASK_NAME STATUS
------------------------------ -----------
tuning_owner_test COMPLETED
--5.查看优化结果
查询dbms_sqltune.report_tning_task函数可以获得优化任务的结果
SQL> set long 888888
SQL> set serveroutput on size 888888
SQL> set lines 100
SQL> select dbms_sqltune.report_tuning_task('tuning_owner_test') from dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_OWNER_TEST1500')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : tuning_owner_test1500
Tuning Task Owner : OWNER
Workload Type : Single SQL Statement
Execution Count : 2
Current Execution : EXEC_146
Execution Type : TUNE SQL
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 11/14/2016 15:00:36
Completed at : 11/14/2016 15:00:37
-------------------------------------------------------------------------------
Schema Name: OWNER
SQL ID : dzrzmbsu9krk8
SQL Text : select * from test1114
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Table "OWNER"."TEST1114" was not analyzed.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'OWNER', tabname =>
'TEST1114', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');
Rationale
---------
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 3447467757
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 139K| 27M| 588 (1)| 00:00:08 |
| 1 | TABLE ACCESS FULL| TEST1114 | 139K| 27M| 588 (1)| 00:00:08 |
------------------------------------------------------------------------------
-------------------------------------------------------------------------------
分析语句为
execute dbms_stats.gather_table_stats(ownname => 'OWNER', tabname => 'TEST1114', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');
--6.删除优化任务
调用dbms_sqltuen.drop_tuning_task删除已经存在的优化任务
SQL> exec dbms_sqltune.drop_tuning_task('tuning_kingsql_test1500');
PL/SQL procedure successfully completed.
转载于:https://www.cnblogs.com/iyoume2008/p/6062441.html
相关资源：ORACLE性能优化工具整理