利用Oracle 10g SQL優化器（STA）優化語句

jolly10發表於2008-07-01
Oracle 10gSQL優化

SQL優化器(SQL Tuning Advisor STA)是Oracle10g中推出的幫助DBA優化工具，它的特點是簡單、智慧，DBA值需要呼叫函式就可以給出一個效能很差的語句的優化結果。下面介紹一下它的使用。

使用STA一定要保證優化器是CBO模式下。

FROM: http://www.hellodba.com/Doc/how_to_oracle10_sta(1).htm

[@more@]

一、利用STA優化語句

對於問題語句的收集，可以通過Oracle10g的另一個自動化工具：資料庫自動診斷監視工具（ADDM）。它的使用可以參照我的另一篇文章《Oracle10g資料庫自動診斷監視工具(ADDM)使用指南》。

我們下面簡單介紹一下如何優化一條找到的問題語句。正如前面所述說的，STA是用起來很簡單（只要你會呼叫儲存過程，都能使用這個工具），三個步驟就可以完成一次語句調優。

測試環境建立：

SQL> CREATE TABLE bigtab AS SELECT rownum as "id", a.* FROM dba_objects a;
 
Table created.



SQL> create table smalltab as select rownum as "id", a.* FROM dba_tables a;




Table created.




SQL> ALTER TABLE bigtab MODIFY (empno NUMBER);
 
Table altered.





SQL> DECLARE

2       n NUMBER;

3    BEGIN

4       FOR n IN 1..100

5       LOOP

6           INSERT INTO bigtab SELECT rownum as "id", a.* FROM dba_objects a;

7           COMMIT;

8       END LOOP;

9   END;
/
 
PL/SQL procedure successfully completed.

這裡建立一張大表和一張小表，並且都沒有索引，下面執行一個查詢：

SQL> set timing on
SQL> set autot on
SQL> select count(*) from bigtab a, smalltab b where a.object_name=b.table_name;
 
 
  COUNT(*)
----------
    135000
 
Elapsed: 00:00:05.59
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3089226980
 
--------------------------------------------------------------------------------
 
| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
 
--------------------------------------------------------------------------------
 
|   0 | SELECT STATEMENT    |          |     1 |    36 |  3550   (2)| 00:00:43 |
|   1 |  SORT AGGREGATE     |          |     1 |    36 |            |          |
|*  2 |   HASH JOIN         |          |   155K|  5462K|  3550   (2)| 00:00:43 |
|   3 |    TABLE ACCESS FULL| SMALLTAB |  1223 | 22014 |    11   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| BIGTAB   |  1205K|    20M|  3526   (1)| 00:00:43 |
 
--------------------------------------------------------------------------------
 
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("A"."OBJECT_NAME"="B"."TABLE_NAME")
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      16013  consistent gets
      14491  physical reads
          0  redo size
        412  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

可以看出這個語句執行效能很差：16013 consistent gets。

第一步：建立優化任務並執行

通過呼叫函式CREATE_TUNING_TASK來建立優化任務，呼叫儲存過程EXECUTE_TUNING_TASK執行該任務：

SQL> set autot off
SQL> set timing 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 a.object_name=b.table_name';
  6    my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
  7            sql_text    => my_sqltext,
  8            user_name   => 'DEMO',
  9            scope       => 'COMPREHENSIVE',
 10            time_limit  => 60,
 11            task_name   => 'tuning_sql_test',
 12            description => 'Task to tune a query on a specified table');
 13
 14    DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'tuning_sql_test');
 15  END;
 16  /
 
PL/SQL procedure successfully completed.

在函式CREATE_TUNING_TASK，sql_text是需要優化的語句，user_name是該語句通過哪個使用者執行，scope是優化範圍（limited或comprehensive），time_limit優化過程的時間限制，task_name優化任務名稱，description優化任務描述。

可以通過檢視USER_ADVISOR_LOG和USER_ADVISOR_LOG來檢視建立過的優化任務。

SQL> select task_name, status from USER_ADVISOR_LOG where task_name='tuning_sql_
test';
 
TASK_NAME                      STATUS
------------------------------        -----------
tuning_sql_test                COMPLETED

第二步：檢視優化結果

通過函式可以檢視優化結果。

SQL> set long 10000
SQL> set longchunksize 1000
SQL> set linesize 100
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                 : DEMO
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 60
Completion Status                 : COMPLETED
Started at                        : 11/30/2005 13:16:43
Completed at                      : 11/30/2005 13:16:44
Number of Index Findings          : 1
 
Schema Name: DEMO
SQL ID     : 6p64dnnsqf9pm
SQL Text   : select count(*) from bigtab a, smalltab b where
             a.object_name=b.table_name
 
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
 
1- 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 DEMO.IDX$$_06C50001 on DEMO.SMALLTAB('TABLE_NAME');
 
  - Consider running the Access Advisor to improve the physical schema design
    or creating the recommended index.
    create index DEMO.IDX$$_06C50002 on DEMO.BIGTAB('OBJECT_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.
 
 
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
 
1- Original
-----------
Plan hash value: 3089226980
 
--------------------------------------------------------------------------------
| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
 
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |     1 |    36 |  3550   (2)| 00:00:43 |
|   1 |  SORT AGGREGATE     |          |     1 |    36 |            |          |
|*  2 |   HASH JOIN         |          |   155K|  5462K|  3550   (2)| 00:00:43 |
|   3 |    TABLE ACCESS FULL| SMALLTAB |  1223 | 22014 |    11   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| BIGTAB   |  1205K|    20M|  3526   (1)| 00:00:43 |
 
--------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("A"."OBJECT_NAME"="B"."TABLE_NAME")
 
2- Using New Indices
--------------------
Plan hash value: 494801882
 
-----------------------------------------------------------------------------------------
| Id  | Operation              | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                |     1 |    36 |  1108   (3)| 00:00:14 |
|   1 |  SORT AGGREGATE        |                |     1 |    36 |            |        |
|*  2 |   HASH JOIN            |                |   155K|  5462K|  1108   (3)| 00:00:14 |
|   3 |    INDEX FAST FULL SCAN| IDX$$_06C50001 |  1223 | 22014 |     3   (0)| 00:00:01 |
|   4 |    INDEX FAST FULL SCAN| IDX$$_06C50002 |  1205K|    20M|  1093   (2)| 00:00:14 |
-----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("A"."OBJECT_NAME"="B"."TABLE_NAME")
 
-------------------------------------------------------------------------------

看一下這個優化建議報告：

第一部分是關於這次優化任務的基本資訊：如任務名稱、執行時間、範圍、涉及到的語句等等。

第二部分是關於這次優化任務的所找到的問題以及給出的優化建議。前面先給出了問題描述：可以通過建立更多的所引來提高效能；然後是建議的具體內容：在表smalltab的欄位table_name上建立索引，在表bigtab的欄位object_name上建立索引；最後是相關注意事項：此次優化雖然給出了建立索引的建議，但是最好通過SQL訪問建議器(SQL Access Advisor SAA)結合整個資料庫的工作量來深入分析，那樣就能給出考慮了索引維護和空間消耗等因素的更加合理的建議。

最後，報告還給出了原有的查詢計劃，以及採用優化建議以後的查詢計劃的對比。可以看出COST值大大下降。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/271283/viewspace-1006425/，如需轉載，請註明出處，否則將追究法律責任。

相關文章