使用SQL調整顧問得到SQL優化建議

hooca發表於2014-09-26
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章