SQL Tuning Advisor使用实例

来源：互联网发布：红中麻将源码教程编辑：程序博客网时间：2024/04/29 09:31

在oracle10g之前，想要优化一个sql语句是比较麻烦，但是在oracle10g这个版本推出的SQLTuning Advisor这个工具，能大大减少sql调优的工作量，不过要想使用SQL TuningAdvisor，一定要保证你的优化器是CBO模式。

1.首先需要创建一个用于调优的用户bamboo,并授予advisor给创建的用户
SQL> create user bamboo identified by bamboo;
User created.

SQL> grant connect,resource tobamboo;
Grant succeeded.

SQL> grant advisor to bamboo;
Grant succeeded.

2.创建用户做测试的2张表,大表里面插入500万条数据，小表里面插入10万条数据，其创建方法如下
SQL> create table bigtable (id number(10),namevarchar2(100));
Table created.

SQL> begin
2 for i in 1..5000000loop
3 insert into bigtablevalues(i,'test'||i);
4 end loop;
5 end;
6 /

PL/SQL procedure successfully completed.

SQL> commit;

SQL> create table smalltable (idnumber(10),name varchar2(100));
Table created.

SQL> begin
2 for i in 1..100000loop
3 insert into smalltablevalues(i,'test'||i);
4 end loop;
5 end;
6 /

PL/SQL procedure successfully completed.

SQL> commit;

3.然后对bigtable和smalltable做一个等连接查询，然后跟踪其执行计划
SQL> select a.id,a.name,b.id,b.name from bigtablea,smalltable b where a.id=b.id and a.id=40000;

ID NAME ID NAME
---------- -------------------- -------------------------------------
40000 test40000 40000 test40000

Execution Plan
----------------------------------------------------------
Plan hash value: 1703851322

---------------------------------------------------------------------------------
| Id |Operation         |Name      | Rows | Bytes | Cost (%CPU)|Time    |
---------------------------------------------------------------------------------
|   0 | SELECTSTATEMENT  |           |   839|   106K| 3656   (5)| 00:00:44 |
|* 1 | HASHJOIN        |           |   839|   106K| 3656   (5)| 00:00:44 |
|* 2 |   TABLEACCESS FULL| SMALLTABLE|    5 |   325|   71   (3)| 00:00:01 |
|* 3 |   TABLEACCESS FULL| BIGTABLE  |   173 | 11245| 3584   (5)|00:00:44 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 -access("A"."ID"="B"."ID")
   2 -filter("B"."ID"=40000)
   3 -filter("A"."ID"=40000)

Note
-----
- dynamic sampling used forthis statement

Statistics
----------------------------------------------------------
         9 recursive calls
         0 db block gets
     16151 consistent gets
     11469 physical reads
         0 redo size
       588 bytes sent via SQL*Net to client
       385 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         2 sorts (memory)
         0 sorts (disk)
         1 rows processed

熟悉执行计划的就可以看出，这个sql执行是很慢的，2个表都做的是全表扫描，并且其物理读是11469，按照优化的经验，给2个表的id创建索引，减少查询时候的物理读,下面我们就看看通过优化器，oracle能我们什么样的建议呢？

4.下面就通过DBMS_SQLTUNE包的CREATE_TUNING_TASK来创建一个优化任务，然后通过DBMS_SQLTUNE.EXECUTE_TUNING_TASK来执行调优任务，生成调优建议

SQL> DECLARE
2   my_task_name VARCHAR2(30);
3    my_sqltextCLOB;
4 BEGIN
5    my_sqltext:= 'select a.id,a.name,b.id,b.name from bigtable a,smalltable bwhere a.id=b.id and a.id=40000';
6
7   my_task_name :=DBMS_SQLTUNE.CREATE_TUNING_TASK(
8                           sql_text => my_sqltext,
9                           user_name => 'SCOTT',
10                            scope => 'COMPREHENSIVE',
11                            time_limit => 60,
12                            task_name =>'test_sql_tuning_task1',
13                            description => 'Task to tune aquery');
14    DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name =>'test_sql_tuning_task1');
15 END;
16 /

5.执行的过程中，也可以通过user_advisor_tasks或者dba_advisor_tasks来查看调优任务执行的状况

SQL> select task_name,ADVISOR_NAME,STATUSfrom user_advisor_tasks;

TASK_NAME ADVISOR_NAME STATUS
------------------ -------------------------------------------------------------------------
test_sql_tuning_task1 SQL TuningAdvisor COMPLETED

如果status是EXECUTING，则表示任务正在执行，如果为COMPLETED，则任务已经执行完毕

6.通过调用dbms_sqltune.report_tuning_task可以查询调优的结果，不过在查询结果之前，得设置sqlplus的环境，如果不设置，则查询的结果出不来
SQL> set long 999999
SQL> set LONGCHUNKSIZE 999999
SQL> set serveroutput on size 999999
SQL> set linesize 200
SQL> selectdbms_sqltune.report_tuning_task('test_sql_tuning_task1') fromdual;

SQL> selectdbms_sqltune.report_tuning_task('test_sql_tuning_task1') fromdual;

DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK1')
---------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning TaskName                 : test_sql_tuning_task1
Tuning TaskOwner                : BAMBOO
Scope                            : COMPREHENSIVE
TimeLimit(seconds)              : 60
CompletionStatus                : COMPLETED
Startedat                       : 10/13/2011 05:07:53
Completedat                     : 10/13/2011 05:08:18
Number of StatisticFindings     : 2
Number of IndexFindings         : 1

DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK1')
----------------------------------------------------------------------------------------------------------------------------------
Schema Name: SCOTT
SQLID    : 7arau1k5a3mv1
SQL Text   : selecta.id,a.name,b.id,b.name from bigtable a,smalltable b
            where a.id=b.id and a.id=40000

-------------------------------------------------------------------------------
FINDINGS SECTION (3 findings)
-------------------------------------------------------------------------------

Recommendation
--------------
- Consider collecting optimizer statistics forthis table.
    executedbms_stats.gather_table_stats(ownname=> 'SCOTT', tabname =>
           'SMALLTABLE', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,
           method_opt => 'FOR ALL COLUMNS SIZEAUTO');

DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK1')
----------------------------------------------------------------------------------------------------------------------------------
Rationale
---------
Theoptimizer requires up-to-date statistics for the table in orderto
select agood execution plan.

2- Statistics Finding
---------------------
Table "SCOTT"."BIGTABLE" was notanalyzed.

Recommendation
--------------

DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK1')
----------------------------------------------------------------------------------------------------------------------------------
- Consider collecting optimizer statistics forthis table.
    executedbms_stats.gather_table_stats(ownname => 'SCOTT',tabname =>
           'BIGTABLE', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,
           method_opt => 'FOR ALL COLUMNS SIZEAUTO');

Rationale
---------
Theoptimizer requires up-to-date statistics for the table in orderto
select agood execution plan.

3- Index Finding (see explain plans sectionbelow)

Recommendation (estimated benefit:100%)
----------------------------------------
- Consider running the Access Advisor to improvethe physical schema design
or creatingthe recommended index.
create indexSCOTT.IDX$$_00790001 on SCOTT.SMALLTABLE('ID');

- Consider running the Access Advisorto improve the physical schema design

DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK1')
----------------------------------------------------------------------------------------------------------------------------------
or creatingthe recommended index.
create indexSCOTT.IDX$$_00790002 on SCOTT.BIGTABLE('ID');

Rationale
---------
    Creating therecommended indices significantly improves the execution plan
    of thisstatement. However, it might be preferable to run "AccessAdvisor"
    using arepresentative SQL workload as opposed to a single statement.This
    will allowto get comprehensive index recommendations which takes into
    accountindex maintenance overhead and additional spaceconsumption.

1- Original
-----------
Plan hash value: 1703851322

DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK1')
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECTSTATEMENT  |           |   839|   106K| 3656   (5)| 00:00:44 |
|* 1 | HASHJOIN        |           |   839|   106K| 3656   (5)| 00:00:44 |
|* 2 |   TABLEACCESS FULL| SMALLTABLE|    5 |   325|   71   (3)| 00:00:01 |
|* 3 |   TABLEACCESS FULL| BIGTABLE  |   173 | 11245| 3584   (5)|00:00:44 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 -access("A"."ID"="B"."ID")
2 -filter("B"."ID"=40000)

2- Using New Indices
--------------------
Plan hash value: 3720188830

------------------------------------------------------------------------------------------------
| Id |Operation                    |Name          | Rows | Bytes | Cost (%CPU)|Time    |
------------------------------------------------------------------------------------------------
|   0 | SELECTSTATEMENT             |               |    1 |   130|    5   (0)| 00:00:01 |
|   1 | TABLEACCESS BY INDEX ROWID |BIGTABLE      |    1 |    65|    3   (0)| 00:00:01 |

DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK1')
---------------------------------------------------------------------------------------------------------------------------------
|   2|   NESTEDLOOPS               |               |    1 |   130|    5   (0)| 00:00:01 |
|   3|    TABLEACCESS BY INDEX ROWID|SMALLTABLE    |    1 |    65|    2   (0)| 00:00:01 |
|* 4|    INDEX RANGESCAN         | IDX$$_00790001|    1|      |    1   (0)| 00:00:01 |
|* 5|    INDEX RANGESCAN          | IDX$$_00790002|    1|      |    2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 -access("B"."ID"=40000)
5 -access("A"."ID"=40000)

从上面的结果可以看到oracle的调优顾问给我们3条建议：
（1）SCOTT.SMALLTABLE表没有做分析，需要做一下表结构的分析，并且给出一个分析的建议，如下所示
    execute dbms_stats.gather_table_stats(ownname =>'SCOTT', tabname =>
           'SMALLTABLE', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,
           method_opt => 'FOR ALL COLUMNS SIZE AUTO');
（2）SCOTT.BIGTABLE表没有做分析，需要做一下表结构的分析，并且给出一个分析的建议，如下所示
    execute dbms_stats.gather_table_stats(ownname =>'SCOTT', tabname =>
           'BIGTABLE', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,
           method_opt => 'FOR ALL COLUMNS SIZE AUTO');
（3）oracle建议我们在表SCOTT.SMALLTABLE，SCOTT.BIGTABLE的id列创建一个bitree索引，给的建议如下
     create index SCOTT.IDX$$_00790002 onSCOTT.BIGTABLE('ID');
     create index SCOTT.IDX$$_00790001 on SCOTT.SMALLTABLE('ID');
   当然创建索引的名字可以改成别的名字

通过以上查看oracle的调优顾问给的建议，基本和我们在前面给出的调优方案是一致，因此当我们给一个大的ＳＱＬ做优化的时候，可以先使用oracle调优顾问，得到一些调优方案，然后根据实际情况做一些调整就可以。

以下就是执行oracle调优顾问的建议，重新执行selecta.id,a.name,b.id,b.name from bigtable a,smalltable b wherea.id=b.id and a.id=40000这条语句得到的执行计划，可以看出查询时间和物理读大大减少
SQL> selecta.id,a.name,b.id,b.name from bigtable a,smalltable b wherea.id=b.id and a.id=40000;

       ID  NAME              ID NAME
-------------------    ---------- ----------------------------------------
    40000test40000          40000   test40000

Execution Plan
----------------------------------------------------------
Plan hash value: 777647921

-------------------------------------------------------------------------------------------------
| Id |Operation                    |Name           | Rows | Bytes | Cost (%CPU)|Time    |
-------------------------------------------------------------------------------------------------
|   0 | SELECTSTATEMENT             |                |    1 |    31|    5   (0)| 00:00:01 |
|   1 | TABLEACCESS BY INDEX ROWID |BIGTABLE       |    1 |    17|    3   (0)| 00:00:01 |
|   2|   NESTEDLOOPS               |                |    1 |    31|    5   (0)| 00:00:01 |
|   3|    TABLEACCESS BY INDEX ROWID|SMALLTABLE     |    1 |    14|    2   (0)| 00:00:01 |
|* 4|    INDEX RANGESCAN         | I_ID_SAMLLTABLE|    1|      |    1   (0)| 00:00:01 |
|* 5|    INDEX RANGESCAN          | I_ID_BIGTABLE  |    1|      |    2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 -access("B"."ID"=40000)
5 -access("A"."ID"=40000)

Statistics
----------------------------------------------------------
         0 recursive calls
         0 db block gets
         9 consistent gets
         0 physical reads
         0 redo size
       588 bytes sent via SQL*Net to client
       385 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         0 sorts (memory)
         0 sorts (disk)
         1 rows processed

转自：http://www.cnblogs.com/einyboy/archive/2012/08/14/2637659.html

0 0

SQL&nbsp;Tuning&nbsp;Advisor使用实例

SQL Tuning Advisor使用实例