sql turning advise（sta）
公司主營業(yè)務(wù)：網(wǎng)站制作、成都網(wǎng)站設(shè)計(jì)、移動(dòng)網(wǎng)站開發(fā)等業(yè)務(wù)。幫助企業(yè)客戶真正實(shí)現(xiàn)互聯(lián)網(wǎng)宣傳，提高企業(yè)的競(jìng)爭能力。成都創(chuàng)新互聯(lián)公司是一支青春激揚(yáng)、勤奮敬業(yè)、活力青春激揚(yáng)、勤奮敬業(yè)、活力澎湃、和諧高效的團(tuán)隊(duì)。公司秉承以“開放、自由、嚴(yán)謹(jǐn)、自律”為核心的企業(yè)文化，感謝他們對(duì)我們的高要求，感謝他們從不同領(lǐng)域給我們帶來的挑戰(zhàn)，讓我們激情的團(tuán)隊(duì)有機(jī)會(huì)用頭腦與智慧不斷的給客戶帶來驚喜。成都創(chuàng)新互聯(lián)公司推出瀏陽免費(fèi)做網(wǎng)站回饋大家。
SQL優(yōu)化器SQL Tuning Advisor（STA)，是oracle的sql優(yōu)化補(bǔ)助工具。
其實(shí)優(yōu)化sql主要有兩個(gè)方案：
其一是改寫sql本身，改寫sql需要對(duì)sql語法、數(shù)據(jù)庫的執(zhí)行方式都要有較好地理解。
其二就是這個(gè)STA，它屬于DBMS_SQLTUNE包，它的主要作用是對(duì)于sql使用到的表創(chuàng)建正確的索引。
使用STA前提：
要保證優(yōu)化器是CBO模式下。
show parameterOPTIMIZER_MODE
all_rows /*CBO，sql所有返回行都采用基于成本的方式運(yùn)行*/
first_rows /*CBO，使用成本和試探法相結(jié)合的方法，查找一種可以最快返回前面少數(shù)行*/
first_rows_n /*CBO，全部采用基于成本的優(yōu)化方法CBO，并以最快的速度，返回前N行記錄*/
choose /*如果有統(tǒng)計(jì)信息，采用CBO，否則采用RBO*/
rule /*RBO*/
執(zhí)行DBMS_SQLTUNE包進(jìn)行sql優(yōu)化需要有advisor的權(quán)限：
grant advisor toscott;
下面通過案例詳細(xì)介紹該工具的具體使用：
1：創(chuàng)建案例用戶并授權(quán)
SQL> createuser lanniao identified by lanniao;
用戶已創(chuàng)建。
SQL> grantconnect,resource to lanniao;
授權(quán)成功。
SQL> grantadvisor to lanniao;
授權(quán)成功。
2：創(chuàng)建測(cè)試表
SQL> createtable bigtab as select rownum as id,a.* from sys.all_objects a;
表已創(chuàng)建。
SQL> createtable smalltab as select rownum as id,a.* from sys.all_tables a;
表已創(chuàng)建。
然后多運(yùn)行幾次下面的腳本，增加表里的數(shù)據(jù)：
SQL> insertinto bigtab select rownum as id,a.* fromsys.all_objects a;
已創(chuàng)建55637行。
SQL> insertinto bigtab select rownum as id,a.* fromsys.all_objects a;
已創(chuàng)建55637行。
SQL> insertinto bigtab select rownum as id,a.* fromsys.all_objects a;
已創(chuàng)建55637行。
SQL> commit;
提交完成。
這里創(chuàng)建一張大表和一張小表，并且都沒有索引，下面執(zhí)行一個(gè)查詢：
SQL> conn /as sysdba
已連接。
SQL> grantdba to lanniao;
授權(quán)成功。
SQL>conn lanniao/lanniao
已連接。
SQL> settiming on
SQL> setautot on
SQL> select count(*) from bigtab a, smalltab bwhere a.object_name=b.table_name;
COUNT(*)
----------
752
已用時(shí)間: 00: 00: 00.32
執(zhí)行計(jì)劃
----------------------------------------------------------
Plan hashvalue: 3089226980
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 34 | 966 (1)| 00:00:12 |
| 1 | SORT AGGREGATE | | 1 | 34 | | |
|* 2 | HASH JOIN | | 155K| 5152K| 966 (1)| 00:00:12 |
| 3 | TABLE ACCESS FULL| SMALLTAB | 2542 | 43214 | 32 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| BIGTAB | 227K| 3778K| 932 (1)| 00:00:12 |
--------------------------------------------------------------------------------
PredicateInformation (identified by operation id):
---------------------------------------------------
2 -access("A"."OBJECT_NAME"="B"."TABLE_NAME")
Note
-----
- dynamic sampling used for this statement(level=2)
統(tǒng)計(jì)信息
----------------------------------------------------------
73 recursive calls
1 db block gets
3683 consistent gets
942 physical reads
132 redo size
535 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
以上可以看到，在執(zhí)行以上兩個(gè)查詢的時(shí)候，兩張表走的全表掃和hash join。
3:使用STA對(duì)sql進(jìn)行分析
3.1：創(chuàng)建優(yōu)化任務(wù)
通過調(diào)用函數(shù)CREATE_TUNING_TASK來創(chuàng)建優(yōu)化任務(wù)，調(diào)用存儲(chǔ)過程EXECUTE_TUNING_TASK執(zhí)行該任務(wù)：
SQL> setautot off
SQL> settiming 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
6 a.object_name=b.table_name';
7 my_task_name:=dbms_sqltune.create_tuning_task(
8 sql_text =>my_sqltext,
9 user_name =>'LANNIAO',
10 scope =>'COMPREHENSIVE',
11 time_limit =>60,
12 task_name =>'tuning_sql_test',
13 description =>'Task to tune a query on specified table');
14 dbms_sqltune.execute_tuning_task(task_name =>'tuning_sql_test');
15 end;
16 /
PL/SQL 過程已成功完成。
函數(shù)CREATE_TUNING_TASK，
sql_text是需要優(yōu)化的語句，
user_name是該語句通過哪個(gè)用戶執(zhí)行，用戶名大寫，
scope是優(yōu)化范圍（limited或comprehensive），
time_limit優(yōu)化過程的時(shí)間限制，
task_name優(yōu)化任務(wù)名稱，
description優(yōu)化任務(wù)描述。
3.2:執(zhí)行優(yōu)化任務(wù)
通過調(diào)用dbms_sqltune.execute_tuning_task過程來執(zhí)行前面創(chuàng)建好的優(yōu)化任務(wù)。
SQL> execdbms_sqltune.execute_tuning_task('tuning_sql_test');
PL/SQL過程已成功完成。
3.3：檢查優(yōu)化任務(wù)的狀態(tài)
通過查看user_advisor_tasks/dba_advisor_tasks視圖可以查看優(yōu)化任務(wù)的當(dāng)前狀態(tài)。
SQL> setlinesize 1000
SQL> SELECTtask_name,status FROM USER_ADVISOR_TASKS WHERE task_name ='tuning_sql_test';
TASK_NAME STATUS
----------------------------- -------------------
tuning_sql_test COMPLETED
3.4:查看優(yōu)化結(jié)果
通過dbms_sqltune.report_tning_task函數(shù)可以獲得優(yōu)化任務(wù)的結(jié)果。
SQL> set long999999
SQL> setserveroutput on size 999999
SQL> set line120
SQL> selectDBMS_SQLTUNE.REPORT_TUNING_TASK( 'tuning_sql_test') from dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
GENERALINFORMATION SECTION
-------------------------------------------------------------------------------
Tuning TaskName : tuning_sql_test
Tuning TaskOwner : LANNIAO
WorkloadType : Single SQL Statement
ExecutionCount : 2
CurrentExecution : EXEC_1056
Execution Type : TUNE SQL
Scope : COMPREHENSIVE
TimeLimit(seconds): 60
CompletionStatus : COMPLETED
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
Started at : 04/14/2014 16:42:18
Completedat : 04/14/2014 16:42:19
-------------------------------------------------------------------------------
Schema Name:LANNIAO
SQL ID : 9n5grk4kh8ndq
SQL Text : select count(*) from bigtab a,smalltab bwhere
a.object_name=b.table_name
-------------------------------------------------------------------------------
FINDINGS SECTION(3 findings)
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
1- StatisticsFinding
---------------------
尚未分析表"LANNIAO"."SMALLTAB"。
Recommendation
--------------
-考慮收集此表的優(yōu)化程序統(tǒng)計(jì)信息。
execute dbms_stats.gather_table_stats(ownname=> 'LANNIAO', tabname =>
'SMALLTAB', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
method_opt => 'FOR ALL COLUMNSSIZE AUTO');
Rationale
---------
為了選擇好的執(zhí)行計(jì)劃,優(yōu)化程序需要此表的最新統(tǒng)計(jì)信息。
2- StatisticsFinding
---------------------
尚未分析表"LANNIAO"."BIGTAB"。
Recommendation
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
--------------
-考慮收集此表的優(yōu)化程序統(tǒng)計(jì)信息。
executedbms_stats.gather_table_stats(ownname => 'LANNIAO', tabname =>
'BIGTAB', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNSSIZE AUTO');
Rationale
---------
為了選擇好的執(zhí)行計(jì)劃,優(yōu)化程序需要此表的最新統(tǒng)計(jì)信息。
3- Index Finding(see explain plans section below)
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
--------------------------------------------------
通過創(chuàng)建一個(gè)或多個(gè)索引可以改進(jìn)此語句的執(zhí)行計(jì)劃。
Recommendation (estimated benefit: 90.48%)
------------------------------------------
-考慮運(yùn)行可以改進(jìn)物理方案設(shè)計(jì)的訪問指導(dǎo)或者創(chuàng)建推薦的索引。
create index LANNIAO.IDX$$_04050001 onLANNIAO.SMALLTAB("TABLE_NAME");
-考慮運(yùn)行可以改進(jìn)物理方案設(shè)計(jì)的訪問指導(dǎo)或者創(chuàng)建推薦的索引。
create index LANNIAO.IDX$$_04050002 onLANNIAO.BIGTAB("OBJECT_NAME");
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
Rationale
---------
創(chuàng)建推薦的索引可以顯著地改進(jìn)此語句的執(zhí)行計(jì)劃。但是,使用典型的 SQL工作量運(yùn)
行 "訪問指導(dǎo)"
可能比單個(gè)語句更可取。通過這種方法可以獲得全面的索引建議案,包括計(jì)算索引維護(hù)
的開銷和附加的空間消耗。
-------------------------------------------------------------------------------
EXPLAIN PLANSSECTION
-------------------------------------------------------------------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
1- Original
-----------
Plan hash value:3089226980
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 34 | 966 (1)| 00:00:12 |
| 1 | SORT AGGREGATE | | 1 | 34 | | |
|* 2 | HASH JOIN | | 155K| 5152K| 966 (1)| 00:00:12 |
| 3 | TABLE ACCESS FULL| SMALLTAB | 2542 | 43214 | 32 (0)| 00:00:01 |
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
| 4 | TABLE ACCESS FULL| BIGTAB | 227K| 3778K| 932 (1)| 00:00:12 |
--------------------------------------------------------------------------------
PredicateInformation (identified by operation id):
---------------------------------------------------
2 -access("A"."OBJECT_NAME"="B"."TABLE_NAME")
2- Using NewIndices
--------------------
Plan hash value:2901183249
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
ime |
--------------------------------------------------------------------------------
---------
| 0 | SELECT STATEMENT | | 1 | 34 | 92 (4)| 0
0:00:0
2 |
| 1 | SORT AGGREGATE | | 1 | 34 | |
|
|* 2 | HASH JOIN | | 155K| 5152K| 92 (4)| 0
0:00:02 |
| 3 | INDEX FAST FULL SCAN| IDX$$_04050001 | 2542 | 43214 | 12 (0)| 0
0:00:01 |
| 4 | INDEX FAST FULL SCAN| IDX$$_04050002 | 227K| 3778K| 78 (2)| 0
0:00:0
1 |
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------
PredicateInformation (identified by operation id):
---------------------------------------------------
2 -access("A"."OBJECT_NAME"="B"."TABLE_NAME")
-------------------------------------------------------------------------------
看一下這個(gè)優(yōu)化建議報(bào)告：
第一部分是關(guān)于這次優(yōu)化任務(wù)的基本信息：如任務(wù)名稱、執(zhí)行時(shí)間、范圍、涉及到的語句等等。
第二部分是關(guān)于這次優(yōu)化任務(wù)的所找到的問題以及給出的優(yōu)化建議。前面先給出了問題描述：收集表的統(tǒng)計(jì)信息及可以通過建立更多的索引來提高性能；然后是建議的具體內(nèi)容：在表smalltab的字段table_name上創(chuàng)建索引，在表bigtab的字段object_name上創(chuàng)建索引；最后是相關(guān)注意事項(xiàng)：此次優(yōu)化雖然給出了創(chuàng)建索引的建議，但是最好通過SQL訪問建議器(SQL Access Advisor SAA)結(jié)合整個(gè)數(shù)據(jù)庫的工作量來深入分析，那樣就能給出考慮了索引維護(hù)和空間消耗等因素的更加合理的建議。
最后，報(bào)告還給出了原有的查詢計(jì)劃，以及采用優(yōu)化建議以后的查詢計(jì)劃的對(duì)比?？梢钥闯鯟OST值大大下降。
3.5：刪除優(yōu)化任務(wù)
通過調(diào)用dbms_sqltuen.drop_tuning_task可以刪除已經(jīng)存在的優(yōu)化任務(wù)
SQL>execdbms_sqltune.drop_tuning_task('tuning_sql_test');
3.6：按照優(yōu)化建議進(jìn)行優(yōu)化
首先要說明一點(diǎn)的是，最好不要直接按照優(yōu)化器給出的建議直接優(yōu)化。因?yàn)橄窠ㄋ饕@種操作影響可不是這一條語句，我們這里只是驗(yàn)證一下優(yōu)化建議的效果。
按照建議，創(chuàng)建兩個(gè)索引：
SQL> createindex smalltab_idx1 on smalltab(table_name);
索引已創(chuàng)建。
SQL> createindex bigtab_idx1 on bigtab(object_name);
索引已創(chuàng)建。
SQL> analyzetable smalltab compute statistics;
表已分析。
SQL> analyzetable bigtab compute statistics;
表已分析。
SQL> settiming on
SQL> setautot on
SQL> selectcount(*) from bigtab a, smalltab b where a.object_name=b.table_name;
COUNT(*)
----------
752
已用時(shí)間: 00: 00: 00.05
執(zhí)行計(jì)劃
----------------------------------------------------------
Plan hash value:2594317117
--------------------------------------------------------------------------------
--------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti
me |
--------------------------------------------------------------------------------
--------
| 0 | SELECT STATEMENT | | 1 | 44 | 321 (2)| 00
:00:04 |
| 1 | SORT AGGREGATE | | 1 | 44 | |
|
|* 2 | HASH JOIN | | 20689 | 888K| 321 (2)| 00
:00:04 |
| 3 | INDEX FAST FULL SCAN| SMALLTAB_IDX1 | 2869 | 54511 | 5 (0)| 00
:00:01 |
| 4| INDEX FAST FULL SCAN|BIGTAB_IDX1 | 222K| 5433K| 313 (1)| 00
:00:04 |
--------------------------------------------------------------------------------
--------
PredicateInformation (identified by operation id):
---------------------------------------------------
2 -access("A"."OBJECT_NAME"="B"."TABLE_NAME")
統(tǒng)計(jì)信息
----------------------------------------------------------
1 recursive calls
0 db block gets
1176 consistent gets
3 physical reads
0 redo size
535 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可以看出，consistentgets比優(yōu)化前大大下降了，優(yōu)化建議確實(shí)提高了性能。Oracle10g讓優(yōu)化變得如此簡單。
重慶分公司
重慶分公司
