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

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

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

| 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

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

|

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

| 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

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

-----

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

---

| 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');

Oracle SQL最佳化之sql tuning advisor(STA)

相關文章