SQL Tuning Advisor使用例項
在oracle10g之前，想要最佳化一個sql語句是比較麻煩，但是在oracle10g這個版本推出的SQL Tuning Advisor這個工具，能大大減少sql調優的工作量，不過要想使用SQL Tuning Advisor，一定要保證你的最佳化器是CBO模式。
1.首先需要建立一個用於調優的使用者bamboo,並授予advisor給建立的使用者
SQL> create user bamboo identified by bamboo;
User created.
SQL> grant connect,resource to bamboo;
Grant succeeded.
SQL> grant advisor to bamboo;
Grant succeeded.
2.建立使用者做測試的2張表,大表裡面插入500萬條資料，小表裡面插入10萬條資料，其建立方法如下
SQL> create table bigtable (id number(10),name varchar2(100));
Table created.
SQL> begin
2 for i in 1..5000000 loop
3 insert into bigtable values(i,'test'||i);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> commti;
SQL> create table smalltable (id number(10),name varchar2(100));
Table created.
SQL> begin
2 for i in 1..100000 loop
3 insert into smalltable values(i,'test'||i);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> commti;
3.然後對bigtable和smalltable做一個等連線查詢，然後跟蹤其執行計劃
SQL> select a.id,a.name,b.id,b.name from bigtable a,smalltable b where a.id=b.id and a.id=40000;
ID NAME ID NAME
---------- ---------------------------------------- ---------- ----------------------------------------
40000 test40000 40000 test40000
Execution Plan
----------------------------------------------------------
Plan hash value: 1703851322
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 839 | 106K| 3656 (5)| 00:00:44 |
|* 1 | HASH JOIN | | 839 | 106K| 3656 (5)| 00:00:44 |
|* 2 | TABLE ACCESS FULL| SMALLTABLE | 5 | 325 | 71 (3)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| BIGTABLE | 173 | 11245 | 3584 (5)| 00:00:44 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."ID"="B"."ID")
2 - filter("B"."ID"=40000)
3 - filter("A"."ID"=40000)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
16151 consistent gets
11469 physical reads
0 redo size
588 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
熟悉執行計劃的就可以看出，這個sql執行是很慢的，2個表都做的是全表掃描，並且其物理讀是11469，按照最佳化的經驗，給2個表的id建立索引，減少查詢時候的物理讀,下面我們就看看透過最佳化器，oracle能我們什麼樣的建議呢？
4.下面就透過DBMS_SQLTUNE包的CREATE_TUNING_TASK來建立一個最佳化任務，然後透過DBMS_SQLTUNE.EXECUTE_TUNING_TASK來執行調優任務，生成調優建議
SQL> DECLARE
2 my_task_name VARCHAR2(30);
3 my_sqltext CLOB;
4 BEGIN
5 my_sqltext := 'select a.id,a.name,b.id,b.name from bigtable a,smalltable b where a.id=b.id and a.id=40000';
6
7 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
8 sql_text => my_sqltext,
9 user_name => 'SCOTT',
10 scope => 'COMPREHENSIVE',
11 time_limit => 60,
12 task_name => 'test_sql_tuning_task1',
13 description => 'Task to tune a query');
14 DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'test_sql_tuning_task1');
15 END;
16 /
5.執行的過程中，也可以透過user_advisor_tasks或者dba_advisor_tasks來檢視調優任務執行的狀況
SQL> select task_name,ADVISOR_NAME,STATUS from user_advisor_tasks;
TASK_NAME ADVISOR_NAME STATUS
------------------------------ ---------------------------------------- ---------------------------------
test_sql_tuning_task1 SQL Tuning Advisor COMPLETED
如果status是EXECUTING，則表示任務正在執行，如果為COMPLETED，則任務已經執行完畢
6.透過呼叫dbms_sqltune.report_tuning_task可以查詢調優的結果，不過在查詢結果之前，得設定sqlplus的環境，如果不設定，則查詢的結果出不來
SQL> set long 999999
SQL> set LONGCHUNKSIZE 999999
SQL> set serveroutput on size 999999
SQL> set linesize 200
SQL> select dbms_sqltune.report_tuning_task('test_sql_tuning_task1') from dual;
SQL> select dbms_sqltune.report_tuning_task('test_sql_tuning_task1') from dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK1')
---------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : test_sql_tuning_task1
Tuning Task Owner : BAMBOO
Scope : COMPREHENSIVE
Time Limit(seconds) : 60
Completion Status : COMPLETED
Started at : 10/13/2011 05:07:53
Completed at : 10/13/2011 05:08:18
Number of Statistic Findings : 2
Number of Index Findings : 1
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK1')
----------------------------------------------------------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID : 7arau1k5a3mv1
SQL Text : select a.id,a.name,b.id,b.name from bigtable a,smalltable b
where a.id=b.id and a.id=40000
-------------------------------------------------------------------------------
FINDINGS SECTION (3 findings)
-------------------------------------------------------------------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK1')
----------------------------------------------------------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Table "SCOTT"."SMALLTABLE" was not analyzed.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>
'SMALLTABLE', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK1')
----------------------------------------------------------------------------------------------------------------------------------
Rationale
---------
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.
2- Statistics Finding
---------------------
Table "SCOTT"."BIGTABLE" was not analyzed.
Recommendation
--------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK1')
----------------------------------------------------------------------------------------------------------------------------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>
'BIGTABLE', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');
Rationale
---------
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.
3- Index Finding (see explain plans section below)
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK1')
---------------------------------------------------------------------------------------------------------------------------------
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 SCOTT.IDX$$_00790001 on SCOTT.SMALLTABLE('ID');
- Consider running the Access Advisor to improve the physical schema design
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK1')
----------------------------------------------------------------------------------------------------------------------------------
or creating the recommended index.
create index SCOTT.IDX$$_00790002 on SCOTT.BIGTABLE('ID');
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.
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK1')
----------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 1703851322
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK1')
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 839 | 106K| 3656 (5)| 00:00:44 |
|* 1 | HASH JOIN | | 839 | 106K| 3656 (5)| 00:00:44 |
|* 2 | TABLE ACCESS FULL| SMALLTABLE | 5 | 325 | 71 (3)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| BIGTABLE | 173 | 11245 | 3584 (5)| 00:00:44 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."ID"="B"."ID")
2 - filter("B"."ID"=40000)
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK1')
---------------------------------------------------------------------------------------------------------------------------------
3 - filter("A"."ID"=40000)
2- Using New Indices
--------------------
Plan hash value: 3720188830
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 130 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | BIGTABLE | 1 | 65 | 3 (0)| 00:00:01 |
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK1')
---------------------------------------------------------------------------------------------------------------------------------
| 2 | NESTED LOOPS | | 1 | 130 | 5 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| SMALLTABLE | 1 | 65 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX$$_00790001 | 1 | | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX$$_00790002 | 1 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("B"."ID"=40000)
5 - access("A"."ID"=40000)
從上面的結果可以看到oracle的調優顧問給我們3條建議：
（1）SCOTT.SMALLTABLE表沒有做分析，需要做一下表結構的分析，並且給出一個分析的建議，如下所示
execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>
'SMALLTABLE', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');
（2）SCOTT.BIGTABLE表沒有做分析，需要做一下表結構的分析，並且給出一個分析的建議，如下所示
execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>
'BIGTABLE', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');
（3）oracle建議我們在表SCOTT.SMALLTABLE，SCOTT.BIGTABLE的id列建立一個bitree索引，給的建議如下
create index SCOTT.IDX$$_00790002 on SCOTT.BIGTABLE('ID');
create index SCOTT.IDX$$_00790001 on SCOTT.SMALLTABLE('ID');
當然建立索引的名字可以改成別的名字
透過以上檢視oracle的調優顧問給的建議，基本和我們在前面給出的調優方案是一致，因此當我們給一個大的ＳＱＬ做最佳化的時候，可以先使用oracle調優顧問，得到一些調優方案，然後根據實際情況做一些調整就可以。
以下就是執行oracle調優顧問的建議，重新執行select a.id,a.name,b.id,b.name from bigtable a,smalltable b where a.id=b.id and a.id=40000這天語句得到的執行計劃，可以看出查詢時間和物理讀大大減少
SQL> select a.id,a.name,b.id,b.name from bigtable a,smalltable b where a.id=b.id and a.id=40000;
ID NAME ID NAME
---------- ---------------------------------------- ---------- ----------------------------------------
40000 test40000 40000 test40000
Execution Plan
----------------------------------------------------------
Plan hash value: 777647921
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 31 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | BIGTABLE | 1 | 17 | 3 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 31 | 5 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| SMALLTABLE | 1 | 14 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | I_ID_SAMLLTABLE | 1 | | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | I_ID_BIGTABLE | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("B"."ID"=40000)
5 - access("A"."ID"=40000)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
588 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
轉自網際網路
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/21601207/viewspace-715446/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- SQL Access Advisor、SQL Tuning Advisor 測試SQL
- 使用sql tuning advisor最佳化sqlSQL
- Oracle SQL優化之sql tuning advisorOracleSQL優化
- oracle SQL最佳化器SQL Tuning Advisor （STA)OracleSQL
- Oracle 11 sql tuning advisor sql access advisor關閉以及job檢視與停止OracleSQL
- Oracle優化案例-關閉auto space advisor和sql tuning advisor（十九）Oracle優化SQL
- Oracle自帶工具sql優化集-SQL Tuning Advisor （使用心得體會）OracleSQL優化
- SQL Access Advisor(zt)SQL
- 使用SSMS連線和查詢 SQL Server 例項SSMSQLServer
- msfvenom使用例項
- pinctrl使用例項
- expect使用例項
- SQL觸發器例項講解SQL觸發器
- [20191112]SQL Tuning by adding column alias (2).txtSQL
- tunna工具使用例項
- awk命令使用例項
- restorecon 命令使用例項REST
- Tee命令使用例項
- 14 個 SQL 拿來就用語句例項！SQL
- sql 注入越過登入驗證例項SQL
- Sql Server資料庫開窗函式Over()的使用例項詳解SQLServer資料庫函式
- cut命令的使用例項
- docker-compose 使用例項Docker
- C# IDispose使用例項C#
- Linux中printf命令使用例項Linux
- Memcached安裝與使用例項
- Composer 使用 JWT 生成 TOKEN 例項JWT
- iptables 常用規則使用例項
- AOP - Advisor
- [AlwaysOn] 建立SQL Server高可用性組T-SQL語法：例項SQLServer
- 自定義註解例項實現SQL語句生成SQL
- 例項詳解如何構建動態SQL語句SQL
- ActiveMQ的使用及整合spring的使用例項MQSpring
- Tee命令的幾個使用例項
- Linux 中 ss 命令的使用例項Linux
- Linux 中的 JQ 命令使用例項Linux
- Linux中ip命令的使用例項Linux
- Linux中的basename命令使用例項Linux
- 使用原生js實現選項卡功能例項教程JS