SQL Tuning Advisor的使用

最新推荐文章于 2024-01-12 08:55:41 发布
最新推荐文章于 2024-01-12 08:55:41 发布
阅读量104 收藏
点赞数

SQL> create user liushiming identified by liushiming;


User created.


SQL> grant connect,resource to liushiming;


Grant succeeded.


SQL> grant advisor to liushiming;


Grant succeeded.


SQL> conn liushiming/liushiming;

Connected.


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


Table created.


SQL> begin

  for i in 1..50000 loop

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

    end loop;

    end; 

    /


PL/SQL procedure successfully completed.


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


Table created.


SQL> begin

    for i in 1..1000 loop

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

    end loop;

    end; 

    /


PL/SQL procedure successfully completed.


SQL> commit;


Commit complete.


SQL> set autot on

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


        ID

----------

NAME

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

        ID

----------

NAME

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

      1000

test1000

      1000

test1000




Execution Plan

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

Plan hash value: 1703851322


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

-


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

|


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

-


|   0 | SELECT STATEMENT   |            |     3 |   390 |    60   (4)| 00:00:01

|


|*  1 |  HASH JOIN         |            |     3 |   390 |    60   (4)| 00:00:01

|


|*  2 |   TABLE ACCESS FULL| SMALLTABLE |     1 |    65 |     3   (0)| 00:00:01

|


|*  3 |   TABLE ACCESS FULL| BIGTABLE   |     3 |   195 |    57   (4)| 00:00:01

|


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

-



Predicate Information (identified by operation id):

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


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

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

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


Note

-----

   - dynamic sampling used for this statement


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


SQL> DECLARE

      my_task_name VARCHAR2(30);

      my_sqltext CLOB;

    BEGIN

      my_sqltext := 'select a.id,a.name,b.id,b.name from bigtable a,smalltable b where a.id=b.id and a.id=1000'; 

     my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(

                              sql_text => my_sqltext,

                              user_name => 'LIUSHIMING',

                             scope => 'COMPREHENSIVE',

                             time_limit => 60,

                             task_name => 'test_sql_tuning_task2',

                             description => 'Task to tune a query');

     DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'test_sql_tuning_task2');

  END; 

  /


PL/SQL procedure successfully completed.


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


SQL> conn / as sysdba

Connected.

SQL> select task_name,ADVISOR_NAME,STATUS from user_advisor_tasks;


SQL> conn liushiming/liushiming;

SQL> set long 10000

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


DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK2')

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

GENERAL INFORMATION SECTION

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

Tuning Task Name                  : test_sql_tuning_task2

Tuning Task Owner                 : LIUSHIMING

Scope                             : COMPREHENSIVE

Time Limit(seconds)               : 60

Completion Status                 : COMPLETED

Started at                        : 04/01/2014 15:05:26

Completed at                      : 04/01/2014 15:05:26

Number of Statistic Findings      : 2

Number of SQL Profile Findings    : 1


DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK2')

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

Number of Index Findings          : 1


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

Schema Name: LIUSHIMING

SQL ID     : 4ak4vqzrr1c1d

SQL Text   : select a.id,a.name,b.id,b.name from bigtable a,smalltable b

             where a.id=b.id and a.id=1000


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

FINDINGS SECTION (4 findings)

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


DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK2')

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


1- Statistics Finding

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

  Table "LIUSHIMING"."SMALLTABLE" was not analyzed.


  Recommendation

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

  - Consider collecting optimizer statistics for this table.

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

            'SMALLTABLE', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

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


DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK2')

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


  Rationale

  ---------

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

    select a good execution plan.


2- Statistics Finding

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

  Table "LIUSHIMING"."BIGTABLE" was not analyzed.


  Recommendation


DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK2')

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

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

  - Consider collecting optimizer statistics for this table.

    execute dbms_stats.gather_table_stats(ownname => 'LIUSHIMING', 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.



DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK2')

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

3- SQL Profile Finding (see explain plans section below)

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

  A potentially better execution plan was found for this statement.


  Recommendation (estimated benefit<=10%)

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

  - Consider accepting the recommended SQL profile.

    execute dbms_sqltune.accept_sql_profile(task_name =>

            'test_sql_tuning_task2', replace => TRUE);


4- Index Finding (see explain plans section below)


DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK2')

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

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

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

  indices.


  Recommendation (estimated benefit: 93.34%)

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

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

    or creating the recommended index.

    create index LIUSHIMING.IDX$$_00A80001 on LIUSHIMING.BIGTABLE("ID");


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


DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK2')

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

    or creating the recommended index.

    create index LIUSHIMING.IDX$$_00A80002 on LIUSHIMING.SMALLTABLE("ID");


  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('TEST_SQL_TUNING_TASK2')

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

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

EXPLAIN PLANS SECTION

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


1- Original With Adjusted Cost

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

Plan hash value: 1703851322


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

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

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


DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK2')

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

|   0 | SELECT STATEMENT   |            |     1 |    23 |    60   (4)| 00:00:01 |

|*  1 |  HASH JOIN         |            |     1 |    23 |    60   (4)| 00:00:01 |

|*  2 |   TABLE ACCESS FULL| SMALLTABLE |     1 |    10 |     3   (0)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL| BIGTABLE   |     1 |    13 |    57   (4)| 00:00:01 |

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


Predicate Information (identified by operation id):

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


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

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


DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK2')

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

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


2- Using SQL Profile

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

Plan hash value: 3277188744


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

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

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

|   0 | SELECT STATEMENT   |            |     1 |    23 |    60   (4)| 00:00:01 |

|*  1 |  HASH JOIN         |            |     1 |    23 |    60   (4)| 00:00:01 |


DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK2')

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

|*  2 |   TABLE ACCESS FULL| BIGTABLE   |     1 |    13 |    57   (4)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL| SMALLTABLE |     1 |    10 |     3   (0)| 00:00:01 |

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


Predicate Information (identified by operation id):

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


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

   2 - filter("A"."ID"=1000)

   3 - filter("B"."ID"=1000)



DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK2')

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

3- Using New Indices

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

Plan hash value: 1729327841


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

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

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

|   0 | SELECT STATEMENT              |                |     1 |    23 |     4  (0)| 00:00:01 |

|   1 |  MERGE JOIN CARTESIAN         |                |     1 |    23 |     4  (0)| 00:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID | BIGTABLE       |     1 |    13 |     2  (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN           | IDX$$_00A80001 |     1 |       |     1  (0)| 00:00:01 |


DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK2')

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

|   4 |   BUFFER SORT                 |                |     1 |    10 |     2  (0)| 00:00:01 |

|   5 |    TABLE ACCESS BY INDEX ROWID| SMALLTABLE     |     1 |    10 |     2  (0)| 00:00:01 |

|*  6 |     INDEX RANGE SCAN          | IDX$$_00A80002 |     1 |       |     1  (0)| 00:00:01 |

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


Predicate Information (identified by operation id):

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


   3 - access("A"."ID"=1000)

   6 - access("B"."ID"=1000)



DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK2')

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

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



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

转载于:http://blog.itpub.net/31448824/viewspace-2139376/

cuanlielian4510
关注
SQL Tuning Advisor使用总结
03-14
关于oracle的SQL Tuning Advisor全面的使用介绍，包括分析awr、cursor、sql_set、sql文本中获取sql并且建立、执行、获取结果的方法。并且介绍了sql_set，sql_profile的操作。里面涉及到的内容都有可执行的代码。
sql优化：使用sql profile优化sql语句
ckhwddy577380的博客
09-02 332
使用sql profile优化sql语句 对于复杂的SQL语句优化，可以借助sql 自动优化顾问生成sql profile，以下介绍使用sql优化顾问生成sql profile优化sql语句 1.低效的sql语句 ...
[terry笔记]Oracle SQL 优化之sql tuning advisor (STA)
weixin_30580341的博客
01-27 200
前言：经常可以碰到优化sql的需求，开发人员直接扔过来一个SQL让DBA优化，然后怎么办？ 当然，经验丰富的DBA可以从各种方向下手，有时通过建立正确索引即可获得很好的优化效果，但是那些复杂SQL错综复杂的表关联，却让DBA们满头大汗。 如下特别介绍一种oracle官方提供的科学优化方法STA，经过实践，不敢说此特性绝对有效，但是可以开阔思路，并且从中学到许多知识，不再用“猜”的方式去创建索引...
深入了解SQL Tuning Advisor
liujinwei2005的专栏
02-10 1367
1、前言：一直以来SQL调优都是DBA比较费力的技术活，而且很多DBA如果没有从事过开发的工作，那么调优更是一项头疼的工作，即使是SQL调优很厉害的高手，在SQL调优的过程中也要不停的分析执行计划、加HINT、分析统计信息等等。从ORACLE 10G开始，数据库采取了很多智能化的管理工作，其中SQL优化器(SQL Tuning Advisor：STA)，大大的提高了DBA进行SQL优化的效率； 2、原理介绍： When SQL statements are executed by the Orac.
SQL Tuning Advisor简介
473687880
10-23 372
1. 简介 一直以来，如何有效的诊断和监控高负载的SQL对于DBA来说不是件容易的事情.但是从Oracle 10G 开始,ADDM的引入使得由数据库自身参与监控成为可能.尽管通过ADDM发现出的高负载的SQL可能只会占全部SQL的很小一部分比例,但是如何对这些语句进行调优依然需要经验的积累. 2. 手工调优SQL的挑战 对SQL语句手工调优需要很多的经验和技巧,例如: . 对SQL语句...
使用SQL tuning advisor(STA)自动优化SQL
12-14
因此并不能保证SQL语句每次都是使用佳的执行计划。而tuning模式则将高负载的SQL语句直接扔给优化器，优化器来自动对其进行详细的分析，调试并给出建议，这是Oracle 提供的Automatic Tuning Optimizer，即自动调整...
使用ORACLE SQL Tuning advisor快速优化低效的SQL语句
12-19
使用ORACLE SQL Tuning advisor快速优化低效的SQL语句, 这里是一个使用范例，有9个步骤， 后面 其他语句，都按照这个步骤来做就可以了。
Oracle 12C SQL Tuning for Developers
05-30
通过Advisor工具，如SQL Tuning Advisor，系统会分析SQL语句并提供改进建议，如创建索引、重写查询或调整绑定变量。 3. **SQL Performance Analyzer**：这是一个强大的工具，用于在实际负载下测试SQL语句的性能变化...
oracle 10gADDM 和 SQL Tuning Advisor
09-02
oracle 10gADDM 和 SQL Tuning Advisor
禁用sql tuning advisor功能
最新发布
m0_38048955的博客
01-12 489
禁用方法
SQL Tuning Advisor(SQL调优顾问，STA)
20386053
12-05 341
SQL调优顾问需要一个或多个SQL语句作为输入，并调用自动优化器执行SQL调优。SQL调优顾问输出是以一种意见或者建议的形式，以及对每一项建议和期望效益的理由。该建议涉及对象的统计收集，新索引的创建，SQL语句的重组，或SQL概要的创建。你可以选择该建议来完成SQL语句的调优。 Oracle数据库可以自动优化有问题的SQL语句，并使用SQL调优顾问实现调优建议。也可以手动运行SQL调优顾问选择单...
使用SQL Tuning Advisor(STA)为sql提供优化建议
雅冰石的专栏
05-22 1210
在Oracle10g之前，优化SQL是个比较费力的技术活，不停的分析执行计划，加hint，分析统计信息等等。 在10g中，Oracle推出了自己的SQL优化辅助工具:SQL优化器(SQLTuningAdvisor：STA)，它可以给出优化建议。 使用STA一定要保证优化器是CBO模式下。 一 创建优化任务 可以通过传入文本来获取相应的优化建议，也可以通过sql_id方式获取。 1.1 传入文本方式 DECLARE my_task_nameVARCHAR2(30); ...
在EM中使用SQL Tuning Advisor（SQL优化建议）优化SQL
cssg91583的博客
04-03 507
同样是先选定SQL优化集，然后运行“SQL优化建议” 在结果页面，可以查看建议的改进操作所产生的新执行计划，以及与旧的执行计划的比较，但不能看到具体的建议操作。要查看，需要在SQL Developer中执行 ...
Sql优化(二十二) 自动调优工具：sql tuning advisor和sql profile介绍
cnyi15573的博客
08-01 345
Sql优化是有一定难度的工作，需要dba具有扎实的知识和较丰富的经验。在oracle10g以后，引入了一些自动调优技术，包括sql tuning advisor、sql access advisor、ADDM等，不需要了解JOI...
ORACLE SQL Tuning Advisor
weixin_42955647的博客
01-20 686
前言：一直以来SQL调优都是DBA比较费力的技术活，而且很多DBA如果没有从事过开发的工作，那么调优更是一项头疼的工作，即使是SQL调优很厉害的高手，在SQL调优的过程中也要不停的分析执行计划、加HINT、分析统计信息等等。从ORACLE 10G开始，数据库采取了很多智能化的管理工作，其中SQL优化器(SQL Tuning Advisor：STA)，大大的提高了DBA进行SQL优化的效率； 准备测试环境（创建两个表并插入数据） create table big_tab as select rownum as
如何用 SQL Tuning Advisor (STA) 优化SQL语句
热门推荐
David Dai -- Focus on Oracle
05-28 2万+
在Oracle10g之前，优化SQL是个比较费力的技术活，不停的分析执行计划，加hint，分析统计信息等等。在10g中，Oracle推出了自己的SQL优化辅助工具: SQL优化器(SQL Tuning Advisor ：STA)，它是新的DBMS_SQLTUNE包。使用STA一定要保证优化器是CBO模式下。执行DBMS_SQLTUNE包进行sql优化需要有advisor的权限：
SQL PROFILES
翔之天空的专栏
10-27 938
本次演示sql profile 的基本用法， 平台基于rhel6 +oracle11.2 版本   第一节建立测试用例 第二节查看sql的执行计划，本次所有试验也是通过sql profile 来使得第二节1部分的sql执行计划变成第二节2部分的执行计划 第三节是通过sql tuning advisor 生成sql profile ，使得sql的执行计划改变 第四节是通过sql tunin
sql tuning set/sql tuning advisor(待完善)
cuili9760的博客
01-22 180
创建sql tuning set sql tuning set是性能调整中重要的一个环节。以下我们先看该功能的使用，再来说明其用途。 （1）创建： [oracle@localhost ~]$ sqlplus /...
自动SQL优化：SQL Tuning Advisor与SQL Access Advisor解析
"这篇文档是关于自动SQL优化的讨论，主要涵盖了SQL Access Advisor和SQL Tuning Advisor的使用情景和功能，以及它们如何帮助减少手动调整的挑战和成本。" SQL优化是一个关键的数据库管理任务，涉及到对SQL语句的...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值