Oracle 10G推出了强大的SQL优化工具:SQL Tuning Advisor,使用该功能必须保证优化器是CBO模式,对SQL进行优化需要执行DBMS_SQLTUNE包,因而需要advisor权限。
举个例子介绍如何优化一条发现问题的语句
1.创建测试环境
HZH@hzh%11gR2>create table hzh1 as select * from dba_objects;
HZH@hzh%11gR2>create table hzh2 as select * from dba_objects;
HZH@hzh%11gR2>insert into hzh1 select * from dba_objects;
HZH@hzh%11gR2>insert into hzh1 select * from dba_objects;
HZH@hzh%11gR2>insert into hzh1 select * from dba_objects;
HZH@hzh%11gR2>insert into hzh2 select * from dba_objects;
HZH@hzh%11gR2>insert into hzh2 select * from dba_objects;
HZH@hzh%11gR2>insert into hzh2 select * from dba_objects;
HZH@hzh%11gR2>set timing on
HZH@hzh%11gR2>set autot on
HZH@hzh%11gR2>select count(*) from hzh1 a,hzh2 b where a.object_name=b.object_name;
COUNT(*)
----------
2138860
Elapsed: 00:00:00.36
Execution Plan
----------------------------------------------------------
Plan hash value: 679460921
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 1 | 132 | | 4929 (2)| 00:01:
00 |
| 1 | SORT AGGREGATE | | 1 | 132 | | |
|
|* 2 | HASH JOIN | | 16M| 2124M| 22M| 4929 (2)| 00:01:
00 |
| 3 | TABLE ACCESS FULL| HZH1 | 302K| 19M| | 1204 (1)| 00:00:
15 |
| 4 | TABLE ACCESS FULL| HZH2 | 364K| 22M| | 1204 (1)| 00:00:
15 |
--------------------------------------------------------------------------------
----
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OBJECT_NAME"="B"."OBJECT_NAME")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
54 recursive calls
2 db block gets
8962 consistent gets
2178 physical reads
308 redo size
425 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
2.创建优化任务
调用函数CREATE_TUNING_TASK来创建优化任务;
调用存储过程EXECUTE_TUNING_TASK执行该任务;
HZH@hzh%11gR2> set autot off
HZH@hzh%11gR2> set timing off
HZH@hzh%11gR2>DECLARE
2 kingsql_me VARCHAR2(30);
3 sqltext_me CLOB;
4 BEGIN
5 sqltext_me := 'select count(*) from hzh1 a,hzh2 b where a.object_name=b.object_name';
6 kingsql_me := DBMS_SQLTUNE.CREATE_TUNING_TASK(
7 sql_text => sqltext_me,
8 user_name => 'HZH',
9 scope => 'COMPREHENSIVE',
10 time_limit => 60,
11 task_name => 'tuning_kingsql_test',
12 description => 'Task to tune a query on a specified table');
13 DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'tuning_kingsql_test');
14 END;
/
PL/SQL procedure successfully completed.
3.执行优化任务
调用dbms_sqltune.execute_tuning_task过程来执行前面创建好的优化任务。
HZH@hzh%11gR2>exec dbms_sqltune.execute_tuning_task('tuning_kingsql_test');
PL/SQL procedure successfully completed.
4.检查优化任务的状态
通过查看dba_advisor_task或者suser_advisor_tasks可以检查优化任务的状态
HZH@hzh%11gR2>SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name ='tuning_kingsql_test';
TASK_NAME STATUS
------------------------------ -----------
tuning_kingsql_test COMPLETED
5.查看优化结果
查询dbms_sqltune.report_tning_task函数可以获得优化任务的结果
HZH@hzh%11gR2>set long 888888
HZH@hzh%11gR2>set serveroutput on size 888888
HZH@hzh%11gR2>set lines 100
HZH@hzh%11gR2>select dbms_sqltune.report_tuning_task('tuning_kingsql_test') from dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_KINGSQL_TEST')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : tuning_kingsql_test
Tuning Task Owner : HZH
Workload Type : Single SQL Statement
Execution Count : 2
Current Execution : EXEC_16
Execution Type : TUNE SQL
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_KINGSQL_TEST')
--------------------------------------------------------------------------------
Started at : 10/16/2014 23:01:39
Completed at : 10/16/2014 23:01:40
-------------------------------------------------------------------------------
Schema Name: HZH
SQL ID : 4t3fargyx8syu
SQL Text : select count(*) from hzh1 a,hzh2 b where
a.object_name=b.object_name
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_KINGSQL_TEST')
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Table "HZH"."HZH2" was not analyzed.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'HZH', tabname =>
'HZH2', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_KINGSQL_TEST')
--------------------------------------------------------------------------------
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.
2- Statistics Finding
---------------------
Table "HZH"."HZH1" was not analyzed.
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_KINGSQL_TEST')
--------------------------------------------------------------------------------
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'HZH', tabname =>
'HZH1', 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.
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_KINGSQL_TEST')
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 679460921
--------------------------------------------------------------------------------
----
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_KINGSQL_TEST')
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 1 | 132 | | 4929 (2)| 00:01:
00 |
| 1 | SORT AGGREGATE | | 1 | 132 | | |
|
|* 2 | HASH JOIN | | 16M| 2124M| 22M| 4929 (2)| 00:01:
00 |
| 3 | TABLE ACCESS FULL| HZH1 | 302K| 19M| | 1204 (1)| 00:00:
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_KINGSQL_TEST')
--------------------------------------------------------------------------------
15 |
| 4 | TABLE ACCESS FULL| HZH2 | 364K| 22M| | 1204 (1)| 00:00:
15 |
--------------------------------------------------------------------------------
----
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OBJECT_NAME"="B"."OBJECT_NAME")
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_KINGSQL_TEST')
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
总结一下这个报告:
开头是一些简单的信息收集和介绍;
后面给出了2个建议,就是分析HZH1和HZH2这两个表;
分析语句为
execute dbms_stats.gather_table_stats(ownname => 'HZH', tabname =>
'HZH1', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');
execute dbms_stats.gather_table_stats(ownname => 'HZH', tabname =>
'HZH2', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');
6.删除优化任务
调用dbms_sqltuen.drop_tuning_task删除已经存在的优化任务
HZH@hzh%11gR2>exec dbms_sqltune.drop_tuning_task('tuning_kingsql_test');
PL/SQL procedure successfully completed.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28389881/viewspace-1301306/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28389881/viewspace-1301306/