Oracle SQL優化之sql tuning advisor(STA)

xulongxc發表於2014-10-22
OracleSQL優化
Oracle SQL優化之sql tuning advisor(STA)
 
SQL優化器SQL Tuning Advisor （STA)，是oracle的sql優化補助工具。
其實優化sql主要有兩個方案，其一是改寫sql本身，改寫sql需要對sql語法、資料庫的執行方式都要有較好地理解。
其二就是這個STA，它屬於DBMS_SQLTUNE包，它的主要作用是對於sql使用到的表建立正確的索引。
 
使用STA前提：
 
要保證優化器是CBO模式下。
show parameter OPTIMIZER_MODE
all_rows  /*CBO，sql所有返回行都採用基於成本的方式執行*/
first_rows  /*CBO，使用成本和試探法相結合的方法，查詢一種可以最快返回前面少數行*/
first_rows_n  /*CBO，全部採用基於成本的優化方法CBO，並以最快的速度，返回前N行記錄*/
choose  /*如果有統計資訊，採用CBO，否則採用RBO*/
rule  /*RBO*/
 
執行DBMS_SQLTUNE包進行sql優化需要有advisor的許可權：
grant advisor to scott;
 
例子：
 
 
 
SQL> create table obj as select * from dba_objects;
 
表已建立。
 
SQL> create table ind as select * from dba_indexes;
 
表已建立。
 
SQL> insert into obj select * from obj;
 
已建立 74603 行。
 
SQL> insert into obj select * from obj;
 
已建立 149206 行。
 
SQL> insert into obj select * from obj;
 
已建立 298412 行。
 
SQL> insert into ind select * from ind;
 
已建立 5134 行。
 
SQL> insert into ind select * from ind;
 
已建立 10268 行。
 
SQL> insert into ind select * from ind;
 
已建立 20536 行。
 
這裡建立兩張表，下面執行一個查詢：
 
 
 
SQL> set timing on
SQL> set autot trace
SQL> select count(*) from obj o, ind i where o.object_name=i.index_name;
 
已用時間:  00: 00: 00.15
 
執行計劃
----------------------------------------------------------
Plan hash value: 380737209
 
------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    83 |       |  5063   (1)| 00:01:01 |
|   1 |  SORT AGGREGATE     |      |     1 |    83 |       |            |          |
|*  2 |   HASH JOIN         |      |  5861K|   463M|  1272K|  5063   (1)| 00:01:01 |
|   3 |    TABLE ACCESS FULL| IND  | 44789 |   743K|       |   379   (1)| 00:00:05 |
|   4 |    TABLE ACCESS FULL| OBJ  |   577K|    36M|       |  2472   (1)| 00:00:30 |
------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("O"."OBJECT_NAME"="I"."INDEX_NAME")
 
Note
-----
   - dynamic sampling used for this statement (level=2)
 
 
統計資訊
----------------------------------------------------------
          9  recursive calls
          4  db block gets
      10406  consistent gets
          0  physical reads
          0  redo size
        425  bytes sent via SQL*Net to client
        415  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。
使用STA：
第一步：建立優化任務
通過呼叫函式CREATE_TUNING_TASK來建立優化任務，呼叫儲存過程EXECUTE_TUNING_TASK執行該任務：
 
 
SQL> set autot off
SQL> set timing off
DECLARE
my_task_name VARCHAR2(30);
my_sqltext  CLOB;
BEGIN
my_sqltext := 'select count(*) from obj o, ind i where o.object_name=i.index_name';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text    => my_sqltext,
user_name   => 'SCOTT', 
scope       => 'COMPREHENSIVE',
time_limit  => 30,
task_name   => 'tuning_sql_test',
description => 'tuning');
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'tuning_sql_test');
END;
/
PL/SQL 過程已成功完成。
 
函式CREATE_TUNING_TASK，
sql_text是需要優化的語句，
user_name是該語句通過哪個使用者執行，使用者名稱大寫，
scope是優化範圍（limited或comprehensive），
time_limit優化過程的時間限制，
task_name優化任務名稱，
description優化任務描述。
 
第二步： 執行優化任務
通過呼叫dbms_sqltune.execute_tuning_task過程來執行前面建立好的優化任務。
 
SQL> exec dbms_sqltune.execute_tuning_task('tuning_sql_test');
PL/SQL 過程已成功完成。
 
 
第三步：檢查優化任務的狀態
通過檢視user_advisor_tasks/dba_advisor_tasks檢視可以檢視優化任務的當前狀態。
 
SQL> SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name ='tuning_sql_test';
 
TASK_NAME         STATUS
------------------------------ -----------
tuning_sql_test         COMPLETED
 
 
 
第四步：檢視優化結果
通過dbms_sqltune.report_tning_task函式可以獲得優化任務的結果。
 
SQL> set long 999999
SQL> set serveroutput on size 999999
SQL> set line 120
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  : SCOTT
Workload Type      : Single SQL Statement
Execution Count    : 2
Current Execution  : EXEC_112
Execution Type     : TUNE SQL
Scope              : COMPREHENSIVE
Time Limit(seconds): 30
Completion Status  : COMPLETED
Started at         : 08/29/2013 11:10:10
Completed at       : 08/29/2013 11:10:12
 
-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID     : 6wruu2mxyu8g3
SQL Text   : select count(*) from obj o, ind i where
             o.object_name=i.index_name
 
-------------------------------------------------------------------------------
FINDINGS SECTION (3 findings)
-------------------------------------------------------------------------------
 
1- Statistics Finding
---------------------
  尚未分析表 "SCOTT"."IND"。
 
  Recommendation
  --------------
  - 考慮收集此表的優化程式統計資訊。
    execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname => 'IND', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO');
 
  Rationale
  ---------
    為了選擇好的執行計劃, 優化程式需要此表的最新統計資訊。
 
2- Statistics Finding
---------------------
  尚未分析表 "SCOTT"."OBJ"。
 
  Recommendation
  --------------
  - 考慮收集此表的優化程式統計資訊。
    execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname => 'OBJ', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO');
 
  Rationale
  ---------
    為了選擇好的執行計劃, 優化程式需要此表的最新統計資訊。
 
3- Index Finding (see explain plans section below)
--------------------------------------------------
  通過建立一個或多個索引可以改進此語句的執行計劃。
 
  Recommendation (estimated benefit: 75.74%)
  ------------------------------------------
  - 考慮執行可以改進物理方案設計的訪問指導或者建立推薦的索引。
    create index SCOTT.IDX$$_00790001 on SCOTT.OBJ("OBJECT_NAME");
 
  - 考慮執行可以改進物理方案設計的訪問指導或者建立推薦的索引。
    create index SCOTT.IDX$$_00790002 on SCOTT.IND("INDEX_NAME");
 
  Rationale
  ---------
    建立推薦的索引可以顯著地改進此語句的執行計劃。但是, 使用典型的 SQL 工作量運
行 "訪問指導"
    可能比單個語句更可取。通過這種方法可以獲得全面的索引建議案, 包括計算索引維護
的開銷和附加的空間消耗。
 
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
 
1- Original
-----------
Plan hash value: 380737209
 
------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time
|
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    83 |       |  5063   (1)| 00:01:01 |
|   1 |  SORT AGGREGATE     |      |     1 |    83 |       |            |          |
|*  2 |   HASH JOIN         |      |  5861K|   463M|  1272K|  5063   (1)| 00:01:01 |
|   3 |    TABLE ACCESS FULL| IND  | 44789 |   743K|       |   379   (1)| 00:00:05 |
|   4 |    TABLE ACCESS FULL| OBJ  |   577K|    36M|       |  2472   (1)| 00:00:30 |
------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("O"."OBJECT_NAME"="I"."INDEX_NAME")
 
2- Using New Indices
--------------------
Plan hash value: 4048334321
 
---------------------------------------------------------------------------------------------
-----
| Id  | Operation               | Name           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
---
|   0 | SELECT STATEMENT        |                |     1 |    83 |       |  1228   (2)| 00:00:15 |
|   1 |  SORT AGGREGATE         |                |     1 |    83 |       |            |
|
|   2 |   MERGE JOIN            |                |  5861K|   463M|       |  1228   (2)| 00:00:15 |
|   3 |    INDEX FULL SCAN      | IDX$$_00790001 |   577K|    36M|       |   944   (1)| 00:00:12 |
|*  4 |    SORT JOIN            |                | 44789 |   743K|  2120K|   268
   (1)| 00:00:04 |
|   5 |     INDEX FAST FULL SCAN| IDX$$_00790002 | 44789 |   743K|       |    18   (0)| 00:00:01 |
----------------------------------------------------------------------------------
----------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("O"."OBJECT_NAME"="I"."INDEX_NAME")
       filter("O"."OBJECT_NAME"="I"."INDEX_NAME")
 
報告如上，此報告主要包含建議的索引和前後執行計劃對比，可見效果還是可以的。
 
五、刪除優化任務
通過呼叫dbms_sqltuen.drop_tuning_task可以刪除已經存在的優化任務
SQL>exec dbms_sqltune.drop_tuning_task('tuning_sql_test');

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

相關文章