使用SQL Tuning Advisor(STA)优化SQL语句

   
         Oracle10g之前,优化SQL是个比较费力的技术活,不停的分析执行计划,加hint,分析统计信息等等。在Oracle10g中推出了自己的SQL优化辅助工具: SQL优化器(SQL Tuning Advisor STA),它是新的DBMS_SQLTUNE包。一定要保证你的优化器是CBO模式。

1.执行DBMS_SQLTUNE包进行sql优化需要有advisor的权限

SQL> create user shall identified by shall;

SQL> grant connect,resource to shall;

SQL> grant advisor to shall;

 

----实验

2.创建做测试表2张,大表500万条数据,小表10万条数据,如下:

SQL> conn shall/shall

SQL> create table bigtable (id number(10),name varchar2(100));

begin

for i in 1..5000000 loop

insert into bigtable values(i,'test'||i);

end loop;

commit;

end;

/

PL/SQL procedure successfully completed.

 

SQL> create table smalltable (id number(10),name varchar2(100));

begin

for i in 1..100000 loop

insert into smalltable values(i,'test'||i);

end loop;

commit;

end;

/

PL/SQL procedure successfully completed.

 

3.然后对bigtable和smalltable做一个等连接查询,然后跟踪其执行计划

SQL> set autotrace trace

SQL> select a.id,a.name,b.id,b.name  from bigtable a,smalltable b  where a.id=b.id and a.id=40000;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1703851322

--------------------------------------------------------------------------------

| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |            |   721 | 93730 |  4488   (1)| 00:00:54

|*  1 |  HASH JOIN         |            |   721 | 93730 |  4488   (1)| 00:00:54

|*  2 |   TABLE ACCESS FULL| SMALLTABLE |     4 |   260 |   103   (1)| 00:00:02

|*  3 |   TABLE ACCESS FULL| BIGTABLE   |   177 | 11505 |  4385   (1)| 00:00:53

--------------------------------------------------------------------------------

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 for this statement (level=2)

Statistics

----------------------------------------------------------

         30  recursive calls

          2  db block gets

      31462  consistent gets

       8282  physical reads

    1096960  redo size

        739  bytes sent via SQL*Net to client

        520  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          6  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

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

第一步:创建优化任务

4.通过DBMS_SQLTUNE包的CREATE_TUNING_TASK来创建一个优化任务。

5.通过DBMS_SQLTUNE.EXECUTE_TUNING_TASK来执行前面创建好的优化任务,生成调优建议。

SQL> exec DBMS_SQLTUNE.execute_tuning_task('tuning_sql_test');

PL/SQL procedure successfully completed

 

第三步:检查优化任务的状态

6.通过user_advisor_tasks或者dba_advisor_tasks来查看调优任务执行的状况

SQL> select task_name,advisor_name,status   from user_advisor_tasks;

TASK_NAME                ADVISOR_NAME            STATUS

----------------------- -------------------------- ---------------------------------

tuning_sql_test            SQL Tuning Advisor         COMPLETED

 

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

第四步:查看优化结果

7.通过调用dbms_sqltune.report_tuning_task查询调优的结果,不过在查询结果之前,得设置sqlplus的环境,如果不设置,则查询的结果出不来。

 set long 999999

 set LONGCHUNKSIZE 999999

 set serveroutput on size 999999

 set linesize 200

SQL> select dbms_sqltune.report_tuning_task('tuning_sql_test') from dual;

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')

-----------------------------------------------------------------------

GENERAL INFORMATION SECTION

-------------------------------------------------------------------------------

Tuning Task Name   : tuning_sql_test

Tuning Task Owner  : SHALL

Workload Type      : Single SQL Statement

Scope              : COMPREHENSIVE

Time Limit(seconds): 60

Completion Status  : COMPLETED

Started at         : 04/23/2016 02:32:18

Completed at       : 04/23/2016 02:32:47

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')

-------------------------------------------------------------------------------

Schema Name: SHALL

SQL ID     : 7arau1k5a3mv1

SQL Text   : select a.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)

-------------------------------------------------------------------------------

 

1- Statistics Finding

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')

-----------------------------------------------------------------------

  Table "SHALL"."SMALLTABLE" was not analyzed.

 

  Recommendation

  --------------

  - Consider collecting optimizer statistics for this table.

    execute dbms_stats.gather_table_stats(ownname => 'SHALL', tabname =>

            'SMALLTABLE', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

            method_opt => 'FOR ALL COLUMNS SIZE AUTO');

 

  Rationale

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')

-----------------------------------------------------------------------

    The optimizer requires up-to-date statistics for the table in order to

    select a good execution plan.

 

2- Statistics Finding

---------------------

  Table "SHALL"."BIGTABLE" was not analyzed.

 

  Recommendation

  --------------

  - Consider collecting optimizer statistics for this table.

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')

-----------------------------------------------------------------------

    execute dbms_stats.gather_table_stats(ownname => 'SHALL', tabname =>

            'BIGTABLE', 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.

 

3- Index Finding (see explain plans section below)

--------------------------------------------------

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')

-----------------------------------------------------------------------

  The execution plan of this statement can be improved by creating one or more

  indices.

 

  Recommendation (estimated benefit: 99.88%)

  ------------------------------------------

  - Consider running the Access Advisor to improve the physical schema design

    or creating the recommended index.

    create index SHALL.IDX$$_00200001 on SHALL.BIGTABLE("ID","NAME");

 

  - Consider running the Access Advisor to improve the physical schema design

    or creating the recommended index.

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')

-----------------------------------------------------------------------

    create index SHALL.IDX$$_00200002 on SHALL.SMALLTABLE("ID","NAME");

 

  Rationale

  ---------

    Creating the recommended indices significantly improves the execution plan

    of this statement. However, it might be preferable to run "Access Advisor"

    using a representative SQL workload as opposed to a single statement. This

    will allow to get comprehensive index recommendations which takes into

    account index maintenance overhead and additional space consumption.

-------------------------------------------------------------------------------

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')

-----------------------------------------------------------------------

EXPLAIN PLANS SECTION

-------------------------------------------------------------------------------

 

1- Original

-----------

Plan hash value: 1703851322

---------------------------------------------------------------------------------

| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |            |   721 | 93730 |  4488   (1)| 00:00:54 |

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')

---------------------------------------------------------------------------------------------------------------------------------

|*  1 |  HASH JOIN         |            |   721 | 93730 |  4488   (1)| 00:00:54 |

|*  2 |   TABLE ACCESS FULL| SMALLTABLE |     4 |   260 |   103   (1)| 00:00:02 |

|*  3 |   TABLE ACCESS FULL| BIGTABLE   |   177 | 11505 |  4385   (1)| 00:00:53 |

---------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("A"."ID"="B"."ID")

   2 - filter("B"."ID"=40000)

   3 - filter("A"."ID"=40000)

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')

---------------------------------------------------------------------------------------------------------------------------------

2- Using New Indices

--------------------

Plan hash value: 1578481415

---------------------------------------------------------------------------------------

| Id  | Operation            | Name       | Rows  | Bytes | Cost (%CPU)| Time    

---------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |            |     1 |   130 |     5   (0)| 00:00:01 |

|   1 |  MERGE JOIN CARTESIAN|             |     1 |   130 |     5   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN   | IDX$$_00200001 |   1 |    65 |     3   (0)| 00:00:01 |

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')

-----------------------------------------------------------------------

|   3 |   BUFFER SORT        |            |     1 |    65 |     2   (0)| 00:00:01 |

|*  4 |    INDEX RANGE SCAN  | IDX$$_00200002 |  1 |    65 |     2   (0)| 00:00:01 |

---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("A"."ID"=40000)

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

-------------------------------------------------------------------------------

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')

---------------------------------------------------------------------------------------------------------------------------------

 

  从上面的结果可以看到oracle的调优顾问给我们3条建议:

(1)SHALL.SMALLTABLE表没有做分析,需要做一下表结构的分析,并且给出一个分析的建议,如下所示:

    execute dbms_stats.gather_table_stats(ownname => 'SHALL', tabname => 'SMALLTABLE', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');

 

(2)SHALL.BIGTABLE表没有做分析,需要做一下表结构的分析,并且给出一个分析的建议,如下所示:

    execute dbms_stats.gather_table_stats(ownname => 'SHALL', tabname => 'BIGTABLE', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,  method_opt => 'FOR ALL COLUMNS SIZE AUTO');

 

(3)oracle建议我们在表SHALL.SMALLTABLE,SHALL.BIGTABLE的id列创建一个bitree索引,给的建议如下:

    create index SHALL.IDX$$_00200002 on SHALL.SMALLTABLE("ID","NAME");

    create index SHALL.IDX$$_00200001 on SHALL.BIGTABLE("ID","NAME");

当然创建索引的名字可以改成别的名字。

    create index SHALL.IDX_SMALLTABLE_ID on SHALL.SMALLTABLE("ID","NAME");

    create index SHALL.IDX_BIGTABLE_ID on SHALL.BIGTABLE("ID","NAME");

 

最后,报告还给出了原有的查询计划,以及采用优化建议以后的查询计划的对比。可以看出COST值大大下降。

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

五、删除优化任务

8.通过调用dbms_sqltuen.drop_tuning_task可以删除已经存在的优化任务

SQL> select task_name,advisor_name,status   from user_advisor_tasks;

TASK_NAME                      ADVISOR_NAME                   STATUS

------------------------------ ------------------------------ -----------

tuning_sql_test                SQL Tuning Advisor             COMPLETED

 

SQL>exec dbms_sqltune.drop_tuning_task('tuning_sql_test');

PL/SQL procedure successfully completed.

        

 

 

 

 

 

--------------------------------------reference  http://blog.sina.com.cn/s/blog_4bde435b01017urb.html

http://www.linuxidc.com/Linux/2013-05/85156.htm

 

sql调优脚本

 

使用到的视图:

DBA_ADVISOR_LOG

DBA_ADVISOR_TASKS

DBA_ADVISOR_FINDINGS

DBA_ADVISOR_RECOMMENDATIONS

DBA_ADVISOR_RATIONALE

DBA_SQLTUNE_STATISTICS

DBA_SQLTUNE_BINDS

DBA_SQLTUNE_PLANS

 

 

脚本:sta.sql 内容如下:

SET ECHO OFF TERMOUT ON FEEDBACK OFF VERIFY OFF 

SET SCAN ON

SET LONG 1000000 LINESIZE 180

COL recs FORMAT a135

VARIABLE tuning_task VARCHAR2(30)

DECLARE

  l_sql_id v$session.prev_sql_id%TYPE;

BEGIN

  SELECT prev_sql_id INTO l_sql_id

  FROM v$session

  WHERE audsid = userenv('SESSIONID');

  :tuning_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id);

  dbms_sqltune.execute_tuning_task(:tuning_task);

END;

/

 

SELECT dbms_sqltune.report_tuning_task(:tuning_task) as recs

FROM dual;

 

SET VERIFY ON FEEDBACK ON

 

调用调优脚本

sql>@sta.sql

 

 

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

转载于:http://blog.itpub.net/30130773/viewspace-2120907/

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值