DBMS_SQLTUNE使用方法
SQL調優工具包DBMS_SQLTUNE的使用方法
oracle 提供了最佳化建議功能包DBMS_SQLTUNE，該包可以幫助我們分析SQL，並提供最佳化建議。
原有執行計劃
alter session set statistics_level=all;
set serveroutput off
select * from test.emp where ename='SCOTT' and DEPTNO=20;
SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'runstats_last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID 8k1gbrapm7zpd, child number 0
-------------------------------------
select * from test.emp where ename='SCOTT' and DEPTNO=20
Plan hash value: 3956160932
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 4 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 1 | 1 |00:00:00.01 | 4 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("ENAME"='SCOTT' AND "DEPTNO"=20))
下面就用DBMS_SQLTUNE最佳化該SQL
--1.賦予使用者ADVISOR許可權
grant ADVISOR to test;
--2.建立sql tuning任務
conn test/test
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'select * from emp where ename= :name and DEPTNO= :deptno';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
bind_list => sql_binds(anydata.convertvarchar2(10),anydata.convertnumber(2)),
user_name => 'TEST',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'test_sql_tuning',
description => 'Task to tune a query on emp');
END;
/
引數說明：
bind_list：多個繫結變數以','逗號分隔。引數值一定要根據繫結變數對應的列的型別書寫.
如：emp.ename型別是VARCHAR2(10)，那麼就要寫成
bind_list =>sql_binds(anydata.convertvarchar2(10)),
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;
/
/*2014-4-8日增加 end*/
--3.檢視任務名 SELECT TASK_NAME
FROM DBA_ADVISOR_LOG
WHERE OWNER = 'TEST';
TASK_NAME
------------------------------
test_sql_tuning
--4.執行sql tuning任務
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'test_sql_tuning' );
END;
/
--5.檢視sql tunning任務狀態
SELECT status
FROM USER_ADVISOR_TASKS
WHERE task_name = 'test_sql_tuning';
STATUS
-----------
COMPLETED
--6.展示sql tunning結果
SET LONG 10000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('test_sql_tuning')
FROM DUAL;
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : test_sql_tuning
Tuning Task Owner : TEST
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 04/01/2014 16:45:16
Completed at : 04/01/2014 16:45:17
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING')
----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Schema Name: TEST
SQL ID : 95fv6dbj64d0f
SQL Text : select * from emp where ename= :name and DEPTNO= :deptno
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING')
----------------------------------------------------------------------------------------------------
Table "TEST"."EMP" was not analyzed.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'TEST', tabname =>
'EMP', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');
Rationale
---------
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING')
----------------------------------------------------------------------------------------------------
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: 66.67%)
------------------------------------------
- Consider running the Access Advisor to improve the physical schema design
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING')
----------------------------------------------------------------------------------------------------
or creating the recommended index.
create index TEST.IDX$$_00D80001 on TEST.EMP("ENAME","DEPTNO");
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')
----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING')
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 87 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ENAME"=:NAME AND "DEPTNO"=:DEPTNO)
2- Using New Indices
--------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING')
----------------------------------------------------------------------------------------------------
Plan hash value: 2106247215
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 87 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX$$_00D80001 | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING')
----------------------------------------------------------------------------------------------------
---------------------------------------------------
2 - access("ENAME"=:NAME AND "DEPTNO"=:DEPTNO)
-------------------------------------------------------------------------------
建議報告總結：
<1>收集EMP表的統計資訊
execute dbms_stats.gather_table_stats(ownname => 'TEST', tabname =>'EMP', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO');
<2>建立索引
create index TEST.IDX$$_00D80001 on TEST.EMP("ENAME","DEPTNO");
最佳化後執行計劃
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 2 | INDEX RANGE SCAN | IDX$$_00D80001 | 1 | 1 | 1 |00:00:00.01 | 2 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ENAME"='SCOTT' AND "DEPTNO"=20)
--7.完成後刪除sql tunning任務
EXEC DBMS_SQLTUNE.DROP_TUNING_TASK('test_sql_tuning');
--8.其他
--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';
以上根據oracle doc整理
轉載：
http://blog.chinaunix.net/uid-23284114-id-4192029.html
oracle 提供了最佳化建議功能包DBMS_SQLTUNE，該包可以幫助我們分析SQL，並提供最佳化建議。
原有執行計劃
alter session set statistics_level=all;
set serveroutput off
select * from test.emp where ename='SCOTT' and DEPTNO=20;
SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'runstats_last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID 8k1gbrapm7zpd, child number 0
-------------------------------------
select * from test.emp where ename='SCOTT' and DEPTNO=20
Plan hash value: 3956160932
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 4 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 1 | 1 |00:00:00.01 | 4 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("ENAME"='SCOTT' AND "DEPTNO"=20))
下面就用DBMS_SQLTUNE最佳化該SQL
--1.賦予使用者ADVISOR許可權
grant ADVISOR to test;
--2.建立sql tuning任務
conn test/test
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'select * from emp where ename= :name and DEPTNO= :deptno';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
bind_list => sql_binds(anydata.convertvarchar2(10),anydata.convertnumber(2)),
user_name => 'TEST',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'test_sql_tuning',
description => 'Task to tune a query on emp');
END;
/
引數說明：
bind_list：多個繫結變數以','逗號分隔。引數值一定要根據繫結變數對應的列的型別書寫.
如：emp.ename型別是VARCHAR2(10)，那麼就要寫成
bind_list =>sql_binds(anydata.convertvarchar2(10)),
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;
/
/*2014-4-8日增加 end*/
--3.檢視任務名 SELECT TASK_NAME
FROM DBA_ADVISOR_LOG
WHERE OWNER = 'TEST';
TASK_NAME
------------------------------
test_sql_tuning
--4.執行sql tuning任務
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'test_sql_tuning' );
END;
/
--5.檢視sql tunning任務狀態
SELECT status
FROM USER_ADVISOR_TASKS
WHERE task_name = 'test_sql_tuning';
STATUS
-----------
COMPLETED
--6.展示sql tunning結果
SET LONG 10000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('test_sql_tuning')
FROM DUAL;
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : test_sql_tuning
Tuning Task Owner : TEST
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 04/01/2014 16:45:16
Completed at : 04/01/2014 16:45:17
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING')
----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Schema Name: TEST
SQL ID : 95fv6dbj64d0f
SQL Text : select * from emp where ename= :name and DEPTNO= :deptno
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING')
----------------------------------------------------------------------------------------------------
Table "TEST"."EMP" was not analyzed.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'TEST', tabname =>
'EMP', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');
Rationale
---------
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING')
----------------------------------------------------------------------------------------------------
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: 66.67%)
------------------------------------------
- Consider running the Access Advisor to improve the physical schema design
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING')
----------------------------------------------------------------------------------------------------
or creating the recommended index.
create index TEST.IDX$$_00D80001 on TEST.EMP("ENAME","DEPTNO");
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')
----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING')
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 87 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ENAME"=:NAME AND "DEPTNO"=:DEPTNO)
2- Using New Indices
--------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING')
----------------------------------------------------------------------------------------------------
Plan hash value: 2106247215
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 87 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX$$_00D80001 | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING')
----------------------------------------------------------------------------------------------------
---------------------------------------------------
2 - access("ENAME"=:NAME AND "DEPTNO"=:DEPTNO)
-------------------------------------------------------------------------------
建議報告總結：
<1>收集EMP表的統計資訊
execute dbms_stats.gather_table_stats(ownname => 'TEST', tabname =>'EMP', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO');
<2>建立索引
create index TEST.IDX$$_00D80001 on TEST.EMP("ENAME","DEPTNO");
最佳化後執行計劃
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 2 | INDEX RANGE SCAN | IDX$$_00D80001 | 1 | 1 | 1 |00:00:00.01 | 2 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ENAME"='SCOTT' AND "DEPTNO"=20)
--7.完成後刪除sql tunning任務
EXEC DBMS_SQLTUNE.DROP_TUNING_TASK('test_sql_tuning');
--8.其他
--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';
以上根據oracle doc整理
轉載：
http://blog.chinaunix.net/uid-23284114-id-4192029.html
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29135257/viewspace-2146249/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- SQL調優工具包DBMS_SQLTUNE的使用方法SQL
- oracle 11g DBMS_SQLTUNE 包的使用方法介紹OracleSQL
- dbms_sqltuneSQL
- dbms_sqltune包的使用SQL
- DBMS_SQLTUNE優化SQLSQL優化
- 利用DBMS_SQLTUNE優化SQLSQL優化
- 使用shell來定製dbms_sqltuneSQL
- SQL優化----dbms_sqltune詳解(1)SQL優化
- DBMS_SQLTUNE詳解(2)---sql_profileSQL
- SQL Tuning Advisor : dbms_sqltuneSQL
- 使用dbms_sqltune進行SQL優化SQL優化
- 使用dbms_sqltune獲得SQL調整建議SQL
- 使用dbms_sqltune調優sql的步驟SQL
- 關於Oracle Baseline和DBMS_SQLTUNE工具OracleSQL
- Oracle10g新特性：使用DBMS_SQLTUNE最佳化SQLOracleSQL
- Oracle 11g系統調優之dbms_sqltune包的使用OracleSQL
- SQL效能的度量 - 利用Hints和dbms_sqltune進行SQL監控SQL
- VNC viewer使用方法，win10VNC使用方法VNCViewWin10
- webpack 使用方法。Web
- EntityManager使用方法
- Alfred使用方法Alfred
- WebView使用方法WebView
- XQuartz 使用方法quartz
- plotly使用方法
- chown 命令使用方法
- scp命令使用方法
- Java DES 使用方法Java
- API文件使用方法API
- Swiper Animate使用方法
- jQuery datatable 使用方法jQuery
- FCKeditor使用方法(轉)
- git命令使用方法Git
- Jquery datatables 使用方法jQuery
- FMDatabase 的使用方法Database
- Properties使用方法解析
- HttpClient初步使用方法HTTPclient
- ****phpredis的使用方法PHPRedis
- ieHTTPHeaders使用方法HTTPHeader