Oracle SQL優化之sql tuning advisor(STA)
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle SQL最佳化之sql tuning advisor(STA)OracleSQL
- oracle SQL最佳化器SQL Tuning Advisor （STA)OracleSQL
- Oracle SQL優化之sql tuning advisorOracleSQL優化
- sql tuning advisor(STA) 建議 建立sql profileSQL
- 在EM中使用SQL Tuning Advisor（SQL優化建議）優化SQLSQL優化
- Oracle優化案例-關閉auto space advisor和sql tuning advisor（十九）Oracle優化SQL
- oracle實用sql(3)--sql tuning advisorOracleSQL
- 使用sql tuning advisor最佳化sqlSQL
- Oracle自帶工具sql優化集-SQL Tuning Advisor （使用心得體會）OracleSQL優化
- SQL Access Advisor 與SQL Tuning AdvisorSQL
- SQL TUNING ADVISORSQL
- sql tuning set/sql tuning advisor(待完善)SQL
- SQL Access Advisor、SQL Tuning Advisor 測試SQL
- ORACLE SQL TUNING ADVISOR 使用方法OracleSQL
- 利用Oracle 10g SQL優化器（STA）優化語句Oracle 10gSQL優化
- sql tuning advisor和sql access advisor區別SQL
- 手工執行sql tuning advisor和sql access advisorSQL
- 【kingsql分享】Oracle 10G強大的SQL優化工具：SQL Tuning AdvisorSQLOracle 10g優化
- SQL Tuning Advisor簡介SQL
- Sql最佳化(二十二) 自動調優工具：sql tuning advisor和sql profile介紹SQL
- [原創]ORACLE SQL TUNING ADVISOR 使用方法OracleSQL
- Oracle 11 sql tuning advisor sql access advisor關閉以及job檢視與停止OracleSQL
- Sql Tuning Advisor 使用方法SQL
- 深入瞭解SQL Tuning AdvisorSQL
- SQL Tuning Advisor : dbms_sqltuneSQL
- SQL Tuning Advisor使用例項SQL
- oracle sql tuning 8--優化全表掃描OracleSQL優化
- Guideline of SQL Tuning AdvisorGUIIDESQL
- Oracle之sql語句優化OracleSQL優化
- oracle sql tuning 2--調優工具OracleSQL
- oracle優化一例之sql優化Oracle優化SQL
- oracle sql tuning 10 理解優化器訪問路徑OracleSQL優化
- Oracle12c中SQL最佳化（SQL TUNING）新特性之SQL計劃指令OracleSQL
- oracle11g中SQL最佳化（SQL TUNING）新特性之SQL Plan Management(SPM)OracleSQL
- Introduction to SQL Tuning Advisor zt自ITPUBSQL
- 使用SQL Profile及SQL Tuning Advisor固定執行計劃SQL
- 在EM中使用SQL Access Advisor（SQL訪問建議）優化SQLSQL優化
- SQL Server優化之SQL語句優化SQLServer優化