Sql Tuning Advisor 使用方法
Sql Tuning Advisor
1.使用步驟：
2.使用案例：
2.1 建立最佳化任務：
2.4
刪除任務的方法
案例分析：
我們現在建立一個測試表，看此時正確的執行計劃
13:11:53 scott@orcl> select * from t2 where empno=200;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2008370210
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 39 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T2 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=200)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1092 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
我們使用hint來強制走一個錯誤的執行計劃
13:11:58 scott@orcl> select /*+ full(t2) */ * from t2 where empno=200;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 15 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T2 | 1 | 39 | 15 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=200)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
47 consistent gets
0 physical reads
0 redo size
1088 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
現在我們使用sqltune advisor來進行調整
建立TUNING_TASK並執行
declare
l_task_name varchar2(30);
l_sql clob;
begin
l_sql := 'select /*+ full(t2) */ * from t2 where empno=200';
l_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => l_sql,
user_name => 'SCOTT',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'test01',
description => null);
end;
/
time_limit：執行的最長時間，預設是60。
scope：
LIMITED，用大概1秒時間去最佳化SQL語句，但是並不進行SQL Profiling分析。
COMPREHENSIVE，進行全面分析，包含SQL Profiling分析；比LIMITED用時更長。
**也可以用sql_id建立sql tunning任務，比用sql_text方便很多
FUNCTION CREATE_TUNING_TASK RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_ID VARCHAR2 IN
PLAN_HASH_VALUE NUMBER IN DEFAULT
SCOPE VARCHAR2 IN DEFAULT
TIME_LIMIT NUMBER IN DEFAULT
TASK_NAME VARCHAR2 IN DEFAULT
DESCRIPTION VARCHAR2 IN DEFAULT
DECLARE
my_task_name VARCHAR2(30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
SQL_ID => 'ddw7j6yfnw0vz',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'tunning_task_ddw7j6yfnw0vz',
description => 'Task to tune a query on ddw7j6yfnw0vz');
END;
/
我們檢視此時任務的狀態
13:27:53 scott@orcl> select task_name,EXECUTION_START,EXECUTION_END,STATUS from DBA_ADVISOR_LOG where task_name like 'test%';
TASK_NAME EXECUTION_START EXECUTION_END STATUS
------------------------------ ------------------- ------------------- -----------
test01 INITIAL
執行sql tuning任務
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'test01' );
END;
/
展示sql tunning結果
SET LONG 10000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('test01')
FROM DUAL;
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST01')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : test01
Tuning Task Owner : SCOTT
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 12/21/2014 13:29:11
Completed at : 12/21/2014 13:29:15
-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID : 3bgc9fc2fp597
SQL Text : select /*+ full(t2) */ * from t2 where empno=200
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 93.46%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'test01', task_owner
=> 'SCOTT', replace => TRUE);
Validation results
------------------
The SQL profile was tested by executing both its plan and the original plan
and measuring their respective execution statistics. A plan may have been
only partially executed if the other could be run to completion in less time.
Original Plan With SQL Profile % Improved
------------- ---------------- ----------
Completion Status: COMPLETE COMPLETE
Elapsed Time (s): .000378 .000098 74.07 %
CPU Time (s): .000299 .000099 66.88 %
User I/O Time (s): 0 0
Buffer Gets: 46 3 93.47 %
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 1 1
Fetches: 1 1
Executions: 1 1
Notes
-----
1. Statistics for the original plan were averaged over 10 executions.
2. Statistics for the SQL profile plan were averaged over 10 executions.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 15 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T2 | 1 | 39 | 15 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=200)
2- Using SQL Profile
--------------------
Plan hash value: 2008370210
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 39 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T2 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=200)
-------------------------------------------------------------------------------
我們可以看到他提供的建議，執行sql_profile，我們根據他的建議執行這個profile
execute dbms_sqltune.accept_sql_profile(task_name => 'test01',task_owner=> 'SCOTT',replace => TRUE);
然後我們再來執行下原來的帶hint的語句
select /*+ full(t2) */ * from t2 where empno=200;
13:39:32 scott@orcl> select /*+ full(t2) */ * from t2 where empno=200;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2008370210
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 39 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T2 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=200)
Note
-----
- SQL profile "SYS_SQLPROF_014a6b5a4a4a0000" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1092 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到使用了sql_profile 從而走了正確的執行計劃
現在我們再來看看其他的情況，我們原來的表上沒有索引，看看tune advisor能提供什麼樣的建議
13:42:44 scott@orcl> select * from t4 where empno=200;
Elapsed: 00:00:00.04
Execution Plan
----------------------------------------------------------
Plan hash value: 2560505625
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 100 | 15 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T4 | 1 | 100 | 15 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=200)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
14 recursive calls
0 db block gets
114 consistent gets
50 physical reads
0 redo size
1088 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
此時表是沒有索引的，走的全表掃描
我們使用sql_id的方式來建立task
13:45:41 scott@orcl> select sql_text,sql_id from v$sql where sql_text like 'select * from t4%';
SQL_TEXT SQL_ID
------------------------------------------------------------ -------------
select * from t4 where empno=200 5avs113b5fn8v
DECLARE
my_task_name VARCHAR2(30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
SQL_ID => '5avs113b5fn8v',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'tunning_task_5avs113b5fn8v',
description => 'Task to tune a query on 5avs113b5fn8v');
END;
/
啟動這個task
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'tunning_task_5avs113b5fn8v' );
END;
/
檢視report
SET LONG 10000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('tunning_task_5avs113b5fn8v')
FROM DUAL;
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNNING_TASK_5AVS113B5FN8V')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : tunning_task_5avs113b5fn8v
Tuning Task Owner : SCOTT
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 12/21/2014 13:48:02
Completed at : 12/21/2014 13:48:03
-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID : 5avs113b5fn8v
SQL Text : select * from t4 where empno=200
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Table "SCOTT"."T4" was not analyzed.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>
'T4', 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.
2- Index Finding (see explain plans section below)
--------------------------------------------------
The execution plan of this statement can be improved by creating one or more
indices.
Recommendation (estimated benefit: 86.7%)
-----------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index SCOTT.IDX$$_00540001 on SCOTT.T4("EMPNO");
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.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 2560505625
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 100 | 15 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T4 | 1 | 100 | 15 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=200)
2- Using New Indices
--------------------
Plan hash value: 3508715929
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 100 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T4 | 1 | 100 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX$$_00540001 | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=200)
-------------------------------------------------------------------------------
可以看到 sql_tune advisor提供了建議在empno 列上面建立索引，可見分析的還是很準確的
刪除tune_tast
EXEC DBMS_SQLTUNE.DROP_TUNING_TASK('test01');
其他
--sql tunning任務建立後，也可以修改引數
BEGIN
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(
task_name => 'test_sql_tuning',
parameter => 'TIME_LIMIT', value => 300);
END;
/
--檢視SQL Tuning Advisor的進展（task執行很久）
col opname for a20
col ADVISOR_NAME for a20
SELECT SID,SERIAL#,USERNAME,OPNAME,ADVISOR_NAME,TARGET_DESC,START_TIME SOFAR, TOTALWORK
FROM V$ADVISOR_PROGRESS
WHERE USERNAME = 'TEST';
1.使用步驟：
?建立最佳化任務
?執行最佳化任務
?檢視建議
?確認建議（實施建議）
2.使用案例：
2.1 建立最佳化任務：
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'SELECT * ' ||
'FROM sales ' || 'WHERE prod_id = 10 AND ' || 'cust_id = 100 ';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
user_name => 'SH',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'TEST_sql_tuning_task',
description => 'Sample Task');
END;
第二種方式：
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
select sql_fulltext into my_sqltext from v$sqlarea where sql_id='bhz4hquh8kf2a';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => my_sqltext,
user_name => 'UOP_CRM1',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'test_sql_tuning_task_ming',
description => 'Task to tune a query');
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'test_sql_tuning_task_ming');
END;
Execute dbms_sqltune.Execute_tuning_task (task_name => 'TEST_sql_tuning_task');
2.3 檢視最佳化建議set long 65536
set longchunksize 65536
set linesize 100
select dbms_sqltune.report_tuning_task('TEST_sql_tuning_task') from dual;
-------------------------------------------------------------------------------
SQL ID : 9bxw71yp99fr6
--------------------------------------------------------------------------------
SQL Text: SELECT * FROM sales WHERE prod_id = 10 AND cust_id = 100
-------------------------------------------------------------------------------
FINDINGS SECTION (5 findings)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Index "SH"."SALES_PROMO_BIX" was not analyzed.
Recommendation
Consider collecting optimizer statistics for this index.
execute dbms_stats.gather_index_stats(ownname => 'SH', indname =>
'SALES_PROMO_BIX', estimate_percent =>
2.4
刪除任務的方法
BEGIN dbms_sqltune.drop_tuning_task('SQL_TUNING_TEST'); END;
2.5 可以用到的檢視
SELECT * FROM USER_ADVISOR_TASKS T WHERE TASK_NAME='SQL_TUNING_TEST';
SELECT * FROM DBA_SQLTUNE_STATISTICS
SELECT * FROM DBA_SQLTUNE_BINDS
SELECT * FROM DBA_SQLTUNE_PLANS WHERE TASK_ID=13009
案例分析：
我們現在建立一個測試表，看此時正確的執行計劃
13:11:53 scott@orcl> select * from t2 where empno=200;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2008370210
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 39 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T2 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=200)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1092 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
我們使用hint來強制走一個錯誤的執行計劃
13:11:58 scott@orcl> select /*+ full(t2) */ * from t2 where empno=200;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 15 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T2 | 1 | 39 | 15 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=200)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
47 consistent gets
0 physical reads
0 redo size
1088 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
現在我們使用sqltune advisor來進行調整
建立TUNING_TASK並執行
declare
l_task_name varchar2(30);
l_sql clob;
begin
l_sql := 'select /*+ full(t2) */ * from t2 where empno=200';
l_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => l_sql,
user_name => 'SCOTT',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'test01',
description => null);
end;
/
time_limit：執行的最長時間，預設是60。
scope：
LIMITED，用大概1秒時間去最佳化SQL語句，但是並不進行SQL Profiling分析。
COMPREHENSIVE，進行全面分析，包含SQL Profiling分析；比LIMITED用時更長。
**也可以用sql_id建立sql tunning任務，比用sql_text方便很多
FUNCTION CREATE_TUNING_TASK RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_ID VARCHAR2 IN
PLAN_HASH_VALUE NUMBER IN DEFAULT
SCOPE VARCHAR2 IN DEFAULT
TIME_LIMIT NUMBER IN DEFAULT
TASK_NAME VARCHAR2 IN DEFAULT
DESCRIPTION VARCHAR2 IN DEFAULT
DECLARE
my_task_name VARCHAR2(30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
SQL_ID => 'ddw7j6yfnw0vz',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'tunning_task_ddw7j6yfnw0vz',
description => 'Task to tune a query on ddw7j6yfnw0vz');
END;
/
我們檢視此時任務的狀態
13:27:53 scott@orcl> select task_name,EXECUTION_START,EXECUTION_END,STATUS from DBA_ADVISOR_LOG where task_name like 'test%';
TASK_NAME EXECUTION_START EXECUTION_END STATUS
------------------------------ ------------------- ------------------- -----------
test01 INITIAL
執行sql tuning任務
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'test01' );
END;
/
展示sql tunning結果
SET LONG 10000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('test01')
FROM DUAL;
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST01')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : test01
Tuning Task Owner : SCOTT
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 12/21/2014 13:29:11
Completed at : 12/21/2014 13:29:15
-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID : 3bgc9fc2fp597
SQL Text : select /*+ full(t2) */ * from t2 where empno=200
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 93.46%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'test01', task_owner
=> 'SCOTT', replace => TRUE);
Validation results
------------------
The SQL profile was tested by executing both its plan and the original plan
and measuring their respective execution statistics. A plan may have been
only partially executed if the other could be run to completion in less time.
Original Plan With SQL Profile % Improved
------------- ---------------- ----------
Completion Status: COMPLETE COMPLETE
Elapsed Time (s): .000378 .000098 74.07 %
CPU Time (s): .000299 .000099 66.88 %
User I/O Time (s): 0 0
Buffer Gets: 46 3 93.47 %
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 1 1
Fetches: 1 1
Executions: 1 1
Notes
-----
1. Statistics for the original plan were averaged over 10 executions.
2. Statistics for the SQL profile plan were averaged over 10 executions.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 15 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T2 | 1 | 39 | 15 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=200)
2- Using SQL Profile
--------------------
Plan hash value: 2008370210
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 39 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T2 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=200)
-------------------------------------------------------------------------------
我們可以看到他提供的建議，執行sql_profile，我們根據他的建議執行這個profile
execute dbms_sqltune.accept_sql_profile(task_name => 'test01',task_owner=> 'SCOTT',replace => TRUE);
然後我們再來執行下原來的帶hint的語句
select /*+ full(t2) */ * from t2 where empno=200;
13:39:32 scott@orcl> select /*+ full(t2) */ * from t2 where empno=200;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2008370210
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 39 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T2 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=200)
Note
-----
- SQL profile "SYS_SQLPROF_014a6b5a4a4a0000" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1092 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到使用了sql_profile 從而走了正確的執行計劃
現在我們再來看看其他的情況，我們原來的表上沒有索引，看看tune advisor能提供什麼樣的建議
13:42:44 scott@orcl> select * from t4 where empno=200;
Elapsed: 00:00:00.04
Execution Plan
----------------------------------------------------------
Plan hash value: 2560505625
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 100 | 15 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T4 | 1 | 100 | 15 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=200)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
14 recursive calls
0 db block gets
114 consistent gets
50 physical reads
0 redo size
1088 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
此時表是沒有索引的，走的全表掃描
我們使用sql_id的方式來建立task
13:45:41 scott@orcl> select sql_text,sql_id from v$sql where sql_text like 'select * from t4%';
SQL_TEXT SQL_ID
------------------------------------------------------------ -------------
select * from t4 where empno=200 5avs113b5fn8v
DECLARE
my_task_name VARCHAR2(30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
SQL_ID => '5avs113b5fn8v',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'tunning_task_5avs113b5fn8v',
description => 'Task to tune a query on 5avs113b5fn8v');
END;
/
啟動這個task
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'tunning_task_5avs113b5fn8v' );
END;
/
檢視report
SET LONG 10000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('tunning_task_5avs113b5fn8v')
FROM DUAL;
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNNING_TASK_5AVS113B5FN8V')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : tunning_task_5avs113b5fn8v
Tuning Task Owner : SCOTT
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 12/21/2014 13:48:02
Completed at : 12/21/2014 13:48:03
-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID : 5avs113b5fn8v
SQL Text : select * from t4 where empno=200
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Table "SCOTT"."T4" was not analyzed.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>
'T4', 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.
2- Index Finding (see explain plans section below)
--------------------------------------------------
The execution plan of this statement can be improved by creating one or more
indices.
Recommendation (estimated benefit: 86.7%)
-----------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index SCOTT.IDX$$_00540001 on SCOTT.T4("EMPNO");
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.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 2560505625
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 100 | 15 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T4 | 1 | 100 | 15 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=200)
2- Using New Indices
--------------------
Plan hash value: 3508715929
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 100 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T4 | 1 | 100 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX$$_00540001 | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=200)
-------------------------------------------------------------------------------
可以看到 sql_tune advisor提供了建議在empno 列上面建立索引，可見分析的還是很準確的
刪除tune_tast
EXEC DBMS_SQLTUNE.DROP_TUNING_TASK('test01');
其他
--sql tunning任務建立後，也可以修改引數
BEGIN
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(
task_name => 'test_sql_tuning',
parameter => 'TIME_LIMIT', value => 300);
END;
/
--檢視SQL Tuning Advisor的進展（task執行很久）
col opname for a20
col ADVISOR_NAME for a20
SELECT SID,SERIAL#,USERNAME,OPNAME,ADVISOR_NAME,TARGET_DESC,START_TIME SOFAR, TOTALWORK
FROM V$ADVISOR_PROGRESS
WHERE USERNAME = 'TEST';
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25462274/viewspace-2120751/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- ORACLE SQL TUNING ADVISOR 使用方法OracleSQL
- [原創]ORACLE SQL TUNING ADVISOR 使用方法OracleSQL
- SQL TUNING ADVISORSQL
- SQL Access Advisor 與SQL Tuning AdvisorSQL
- sql tuning set/sql tuning advisor(待完善)SQL
- SQL Access Advisor、SQL Tuning Advisor 測試SQL
- sql tuning advisor和sql access advisor區別SQL
- SQL Tuning Advisor簡介SQL
- 手工執行sql tuning advisor和sql access advisorSQL
- 深入瞭解SQL Tuning AdvisorSQL
- SQL Tuning Advisor : dbms_sqltuneSQL
- SQL Tuning Advisor使用例項SQL
- 使用sql tuning advisor最佳化sqlSQL
- Oracle SQL優化之sql tuning advisorOracleSQL優化
- oracle實用sql(3)--sql tuning advisorOracleSQL
- sql tuning advisor(STA) 建議 建立sql profileSQL
- Oracle SQL優化之sql tuning advisor(STA)OracleSQL優化
- Guideline of SQL Tuning AdvisorGUIIDESQL
- oracle SQL最佳化器SQL Tuning Advisor （STA)OracleSQL
- Oracle SQL最佳化之sql tuning advisor(STA)OracleSQL
- Introduction to SQL Tuning Advisor zt自ITPUBSQL
- Sql Tuning Advisor的大致過程測試！SQL
- Oracle 11 sql tuning advisor sql access advisor關閉以及job檢視與停止OracleSQL
- 使用SQL Profile及SQL Tuning Advisor固定執行計劃SQL
- Oracle優化案例-關閉auto space advisor和sql tuning advisor（十九）Oracle優化SQL
- 在EM中使用SQL Tuning Advisor（SQL優化建議）優化SQLSQL優化
- Oracle自帶工具sql優化集-SQL Tuning Advisor （使用心得體會）OracleSQL優化
- Oracle OCP 1Z0 053 Q253(SQL Tuning Advisor)OracleSQL
- Oracle OCP IZ0-053 Q46(SQL Tuning Advisor Limited)OracleSQLMIT
- 【kingsql分享】Oracle 10G強大的SQL優化工具：SQL Tuning AdvisorSQLOracle 10g優化
- Oracle OCP 1Z0 053 Q406(sql tuning advisor)OracleSQL
- Sql最佳化(二十二) 自動調優工具：sql tuning advisor和sql profile介紹SQL
- SQL Tuning Advisor報錯ORA-00600: internal error code, arguments: [kesqsMakeBindValue:obj]SQLErrorOBJ
- [20130626]11GR2 SQL Tuning Advisor.txtSQL
- rac資料庫預設sql tuning advisor，導致大量library cache lock資料庫SQL
- Oracle OCP 1Z0 053 Q403(Automatic SQL Tuning Advisor task)OracleSQL
- sql tuningSQL
- Oracle OCP 1Z0 053 Q298(SQL Tuning Advisor&GATHER_STATS_JOB)OracleSQL