温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

sql turning advise的使用

发布时间:2020-07-27 21:00:26 来源:网络 阅读:1035 作者:lanniaode 栏目:关系型数据库

             sql turning advisesta

SQL优化器SQL Tuning Advisor STA),是oraclesql优化补助工具。

其实优化sql主要有两个方案:

其一是改写sql本身,改写sql需要对sql语法、数据库的执行方式都要有较好地理解。

其二就是这个STA,它属于DBMS_SQLTUNE包,它的主要作用是对于sql使用到的表创建正确的索引。

 

使用STA前提:

 要保证优化器是CBO模式下。

show parameterOPTIMIZER_MODE

all_rows /*CBOsql所有返回行都采用基于成本的方式运行*/

first_rows /*CBO,使用成本和试探法相结合的方法,查找一种可以最快返回前面少数行*/

first_rows_n /*CBO,全部采用基于成本的优化方法CBO,并以最快的速度,返回前N行记录*/

choose  /*如果有统计信息,采用CBO,否则采用RBO*/

rule /*RBO*/

 

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

grant advisor toscott;

 

下面通过案例详细介绍该工具的具体使用:

 

1:创建案例用户并授权

SQL> createuser lanniao identified by lanniao;

 

用户已创建。

 

SQL> grantconnect,resource to lanniao;

 

授权成功。

 

SQL> grantadvisor to lanniao;

 

授权成功。

2创建测试表

 

SQL> createtable bigtab as select rownum as id,a.* from sys.all_objects a;

 

表已创建。

 

SQL> createtable smalltab as select rownum as id,a.* from sys.all_tables a;

 

表已创建。

 

然后多运行几次下面的脚本,增加表里的数据:

 

SQL> insertinto bigtab  select rownum as id,a.* fromsys.all_objects a;

 

已创建55637行。

 

SQL> insertinto bigtab  select rownum as id,a.* fromsys.all_objects a;

 

已创建55637行。

 

SQL> insertinto bigtab  select rownum as id,a.* fromsys.all_objects a;

 

已创建55637行。

 

SQL> commit;

 

提交完成。

 

     这里创建一张大表和一张小表,并且都没有索引,下面执行一个查询:

 

SQL> conn /as sysdba

已连接。

SQL> grantdba to lanniao;

 

授权成功。

 

SQL>conn  lanniao/lanniao

已连接。

SQL> settiming on

SQL> setautot on

SQL>  select count(*) from bigtab a, smalltab bwhere a.object_name=b.table_name;

 

  COUNT(*)

----------

       752

 

已用时间:  00: 00: 00.32

 

执行计划

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

Plan hashvalue: 3089226980

 

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

 

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

 

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

 

|   0 | SELECT STATEMENT    |         |     1 |    34 |  966   (1)| 00:00:12 |

 

|   1 | SORT AGGREGATE     |          |    1 |    34 |            |          |

 

|*  2 |  HASH JOIN         |          |  155K|  5152K|   966  (1)| 00:00:12 |

 

|   3 |   TABLE ACCESS FULL| SMALLTAB | 2542 | 43214 |    32   (0)| 00:00:01 |

 

|   4 |   TABLE ACCESS FULL| BIGTAB   |   227K| 3778K|   932   (1)| 00:00:12 |

 

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

 

 

PredicateInformation (identified by operation id):

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

 

   2 -access("A"."OBJECT_NAME"="B"."TABLE_NAME")

 

Note

-----

   - dynamic sampling used for this statement(level=2)

 

 

统计信息

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

         73 recursive calls

          1 db block gets

       3683 consistent gets

        942 physical reads

        132 redo size

        535 bytes sent via SQL*Net to client

        523 bytes received via SQL*Net from client

          2 SQL*Net roundtrips to/from client

          0 sorts (memory)

          0 sorts (disk)

          1 rows processed

 

以上可以看到,在执行以上两个查询的时候,两张表走的全表扫和hash join

 

3: 使用STAsql进行分析

 

3.1创建优化任务

通过调用函数CREATE_TUNING_TASK来创建优化任务,调用存储过程EXECUTE_TUNING_TASK执行该任务:

 

 

SQL> setautot off

SQL> settiming off

SQL> declare

  2 my_task_name varchar2(30);

  3 my_sqltext clob;

  4 begin

  5 my_sqltext:='select count(*) from bigtab a,smalltab b where

  6 a.object_name=b.table_name';

  7 my_task_name:=dbms_sqltune.create_tuning_task(

  8 sql_text =>my_sqltext,

  9 user_name =>'LANNIAO',

 10 scope =>'COMPREHENSIVE',

 11 time_limit =>60,

 12 task_name =>'tuning_sql_test',

 13 description =>'Task to tune a query on specified table');

 14 dbms_sqltune.execute_tuning_task(task_name =>'tuning_sql_test');

 15  end;

 16  /

PL/SQL 过程已成功完成。

 

函数CREATE_TUNING_TASK

sql_text是需要优化的语句,

user_name是该语句通过哪个用户执行,用户名大写,

scope是优化范围(limitedcomprehensive),

time_limit优化过程的时间限制,

task_name优化任务名称,

description优化任务描述。

 

3.2: 执行优化任务

通过调用dbms_sqltune.execute_tuning_task过程来执行前面创建好的优化任务。

 

SQL> execdbms_sqltune.execute_tuning_task('tuning_sql_test');

PL/SQL 过程已成功完成。

 

3.3:检查优化任务的状态

通过查看user_advisor_tasks/dba_advisor_tasks视图可以查看优化任务的当前状态。

SQL> setlinesize 1000

SQL> SELECTtask_name,status FROM USER_ADVISOR_TASKS WHERE task_name ='tuning_sql_test';

 

TASK_NAME                  STATUS

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

tuning_sql_test                  COMPLETED

 

3.4: 查看优化结果

通过dbms_sqltune.report_tning_task函数可以获得优化任务的结果。

 

SQL> set long999999

SQL> setserveroutput on size 999999

SQL> set line120

SQL> selectDBMS_SQLTUNE.REPORT_TUNING_TASK( 'tuning_sql_test') from dual;

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')

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

 

GENERALINFORMATION SECTION

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

Tuning TaskName   : tuning_sql_test

Tuning TaskOwner  : LANNIAO

WorkloadType      : Single SQL Statement

ExecutionCount    : 2

CurrentExecution  : EXEC_1056

Execution Type     : TUNE SQL

Scope              : COMPREHENSIVE

TimeLimit(seconds): 60

CompletionStatus  : COMPLETED

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')

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

 

Started at         : 04/14/2014 16:42:18

Completedat       : 04/14/2014 16:42:19

 

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

Schema Name:LANNIAO

SQL ID     : 9n5grk4kh8ndq

SQL Text   : select count(*) from bigtab a,smalltab bwhere

             a.object_name=b.table_name

 

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

FINDINGS SECTION(3 findings)

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')

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

 

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

 

1- StatisticsFinding

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

  尚未分析表"LANNIAO"."SMALLTAB"

 

  Recommendation

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

  - 考虑收集此表的优化程序统计信息。

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

            'SMALLTAB', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')

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

 

            method_opt => 'FOR ALL COLUMNSSIZE AUTO');

 

  Rationale

  ---------

    为了选择好的执行计划, 优化程序需要此表的最新统计信息。

 

2- StatisticsFinding

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

  尚未分析表"LANNIAO"."BIGTAB"

 

  Recommendation

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')

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

 

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

  - 考虑收集此表的优化程序统计信息。

    executedbms_stats.gather_table_stats(ownname => 'LANNIAO', tabname =>

            'BIGTAB', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,

            method_opt => 'FOR ALL COLUMNSSIZE AUTO');

 

  Rationale

  ---------

    为了选择好的执行计划, 优化程序需要此表的最新统计信息。

 

3- Index Finding(see explain plans section below)

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')

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

 

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

  通过创建一个或多个索引可以改进此语句的执行计划。

 

  Recommendation (estimated benefit: 90.48%)

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

  - 考虑运行可以改进物理方案设计的访问指导或者创建推荐的索引。

    create index LANNIAO.IDX$$_04050001 onLANNIAO.SMALLTAB("TABLE_NAME");

 

  - 考虑运行可以改进物理方案设计的访问指导或者创建推荐的索引。

    create index LANNIAO.IDX$$_04050002 onLANNIAO.BIGTAB("OBJECT_NAME");

 

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')

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

 

  Rationale

  ---------

    创建推荐的索引可以显著地改进此语句的执行计划。但是, 使用典型的 SQL 工作量运

"访问指导"

    可能比单个语句更可取。通过这种方法可以获得全面的索引建议案, 包括计算索引维护

 

的开销和附加的空间消耗。

 

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

EXPLAIN PLANSSECTION

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

 

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')

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

 

1- Original

-----------

Plan hash value:3089226980

 

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

 

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

 

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

 

|   0 | SELECT STATEMENT    |         |     1 |    34 |  966   (1)| 00:00:12 |

 

|   1 | SORT AGGREGATE     |          |    1 |    34 |            |          |

 

|*  2 |  HASH JOIN         |          |  155K|  5152K|   966  (1)| 00:00:12 |

 

|   3 |   TABLE ACCESS FULL| SMALLTAB | 2542 | 43214 |    32   (0)| 00:00:01 |

 

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')

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

 

|   4 |   TABLE ACCESS FULL| BIGTAB   |   227K| 3778K|   932   (1)| 00:00:12 |

 

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

 

 

PredicateInformation (identified by operation id):

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

 

   2 -access("A"."OBJECT_NAME"="B"."TABLE_NAME")

 

2- Using NewIndices

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

Plan hash value:2901183249

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')

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

 

 

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

---------

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

ime     |

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

---------

|   0 | SELECT STATEMENT       |                |     1 |   34 |    92   (4)| 0

0:00:0

2 |

|   1 | SORT AGGREGATE        |                |     1 |   34 |            |

        |

|*  2 |  HASH JOIN            |                |   155K| 5152K|    92   (4)| 0

0:00:02 |

|   3 |   INDEX FAST FULL SCAN| IDX$$_04050001 | 2542 | 43214 |    12   (0)| 0

0:00:01 |

|   4 |   INDEX FAST FULL SCAN| IDX$$_04050002 |  227K|  3778K|    78  (2)| 0

0:00:0

1 |

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')

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

 

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

---------

 

PredicateInformation (identified by operation id):

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

 

   2 -access("A"."OBJECT_NAME"="B"."TABLE_NAME")

 

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

看一下这个优化建议报告:

第一部分是关于这次优化任务的基本信息:如任务名称、执行时间、范围、涉及到的语句等等。

 

第二部分是关于这次优化任务的所找到的问题以及给出的优化建议。前面先给出了问题描述:收集表的统计信息及可以通过建立更多的索引来提高性能;然后是建议的具体内容:在表smalltab的字段table_name上创建索引,在表bigtab的字段object_name上创建索引;最后是相关注意事项:此次优化虽然给出了创建索引的建议,但是最好通过SQL访问建议器(SQL Access Advisor SAA)结合整个数据库的工作量来深入分析,那样就能给出考虑了索引维护和空间消耗等因素的更加合理的建议。

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

 

3.5:删除优化任务

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

SQL>execdbms_sqltune.drop_tuning_task('tuning_sql_test');

 

3.6:按照优化建议进行优化

首先要说明一点的是,最好不要直接按照优化器给出的建议直接优化。因为像建索引这种操作影响可不是这一条语句,我们这里只是验证一下优化建议的效果。

按照建议,创建两个索引:

SQL> createindex smalltab_idx1 on smalltab(table_name);

 

索引已创建。

 

SQL> createindex bigtab_idx1 on bigtab(object_name);

 

索引已创建。

 

SQL> analyzetable smalltab compute statistics;

 

表已分析。

 

SQL> analyzetable bigtab compute statistics;

 

表已分析。

 

SQL> settiming on

SQL> setautot on

SQL> selectcount(*) from bigtab a, smalltab b where a.object_name=b.table_name;

 

 

  COUNT(*)

----------

       752

 

已用时间:  00: 00: 00.05

 

执行计划

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

Plan hash value:2594317117

 

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

--------

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

me     |

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

--------

|   0 | SELECT STATEMENT       |               |     1 |   44 |   321   (2)| 00

:00:04 |

|   1 | SORT AGGREGATE        |               |     1 |   44 |            |

       |

|*  2 |  HASH JOIN            |               | 20689 |   888K|  321   (2)| 00

:00:04 |

|   3 |   INDEX FAST FULL SCAN| SMALLTAB_IDX1 | 2869 | 54511 |     5   (0)| 00

:00:01 |

|   4|    INDEX FAST FULL SCAN|BIGTAB_IDX1   |   222K| 5433K|   313   (1)| 00

:00:04 |

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

--------

 

PredicateInformation (identified by operation id):

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

 

   2 -access("A"."OBJECT_NAME"="B"."TABLE_NAME")

 

 

统计信息

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

          1 recursive calls

          0 db block gets

       1176 consistent gets

          3 physical reads

          0 redo size

        535 bytes sent via SQL*Net to client

        523 bytes received via SQL*Net from client

          2 SQL*Net roundtrips to/from client

          0 sorts (memory)

          0 sorts (disk)

          1 rows processed

 

 

可以看出,consistentgets比优化前大大下降了,优化建议确实提高了性能。Oracle10g让优化变得如此简单。

 


向AI问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI