使用SQL调整顾问得到SQL优化建议

SQL调整顾问可以通过SQL*PLUS或EM调用。

一般分为3个步骤:
1. 创建调整任务。
2. 执行调整任务。
3. 查看结果。

创建调整任务:


点击(此处)折叠或打开

  1. DECLARE
  2.   tuning_task_name VARCHAR2(30);
  3.   tuning_sqltext CLOB;
  4. BEGIN
  5. tuning_sqltext := 'SELECT id FROM BOM WHERE MOD3=3';
  6. tuning_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
  7. sql_text => tuning_sqltext,
  8. bind_list => NULL,
  9. user_name => 'HK',
  10. scope => 'COMPREHENSIVE',
  11. time_limit => 60,
  12. task_name => 'first_tuning_task50',
  13. description => 'Tune T50 count');
  14. END;
  15. /
执行调整任务


点击(此处)折叠或打开

  1. BEGIN
  2. DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'first_tuning_task50' );
  3. END;
  4. /

上个步骤可能会耗时很长。

查看结果


点击(此处)折叠或打开

  1. SET SERVEROUTPUT ON
  2. BEGIN
  3.   DBMS_OUTPUT.PUT_LINE(dbms_sqltune.report_tuning_task('first_tuning_task60'));
  4. END;
  5. /


"DBMS_SQLTUNE.REPORT_TUNING_TAS"
"GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : first_tuning_task50
Tuning Task Owner                 : HK
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 60
Completion Status                 : COMPLETED
Started at                        : 09/26/2014 17:58:41
Completed at                      : 09/26/2014 17:59:14
Number of Statistic Findings      : 1
Number of Index Findings          : 1


-------------------------------------------------------------------------------
Schema Name: HK
SQL ID     : bx8zw8mfvh7u1
SQL Text   : SELECT id FROM BOM WHERE MOD3=3


-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------


1- Statistics Finding
---------------------
  Table ""HK"".""BOM"" and its indices were not analyzed.


  Recommendation
  --------------
  - Consider collecting optimizer statistics for this table and its indices.
    execute dbms_stats.gather_table_stats(ownname => 'HK', tabname => 'BOM',
            estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt =>
            'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);


  Rationale
  ---------
    The optimizer requires up-to-date statistics for the table and its indices
    in order to select a good execution plan.


2- Index Finding (see explain plans section below)
--------------------------------------------------
  The execution plan of this statement can be improved by creating one or more
  indices.


  Recommendation (estimated benefit: 100%)
  ----------------------------------------
  - Consider running the Access Advisor to improve the physical schema design
    or creating the recommended index.
    create index HK.IDX$$_00FE0001 on HK.BOM('MOD3');


  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.


-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------


1- Original
-----------
Plan hash value: 258718033


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  6008K|   148M|   242K  (1)| 00:48:31 |
|*  1 |  TABLE ACCESS FULL| BOM  |  6008K|   148M|   242K  (1)| 00:48:31 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(""MOD3""=:SYS_B_0)


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


-------------------------------------------------------------------------------------------
| Id  | Operation              | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                  |  6008K|   148M| 30552   (2)| 00:06:07 |
|*  1 |  VIEW                  | index$_join$_001 |  6008K|   148M| 30552   (2)| 00:06:07 |
|*  2 |   HASH JOIN            |                  |       |       |            |          |
|*  3 |    INDEX RANGE SCAN    | IDX$$_00FE0001   |  6008K|   148M|  2797   (1)| 00:00:34 |
|   4 |    INDEX FAST FULL SCAN| BOM_PK_ID        |  6008K|   148M| 20992   (2)| 00:04:12 |
-------------------------------------------------..."


观察执行结果,调整顾问会给出该语句的调整建议(上例是创建索引),并给出当前和使用调整建议后不同的执行计划。


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

转载于:http://blog.itpub.net/22621861/viewspace-1282023/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值