【kingsql分享】Oracle 10G强大的SQL优化工具：SQL Tuning Advisor

 

    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.

$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

我的QQ 1749160152

我的邮箱 hongzhuohui@kingsql.com

我的百科 http://baike.sogou.com/v82076725.htm?sp=SST洪卓辉

$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28389881/viewspace-1301306/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28389881/viewspace-1301306/