小编给大家分享一下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用时更长。
/*2014-4-8日增加 begin*/
**也可以用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
——————————
t免费主机域名est_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 designDBMS_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)
——————————————————————————-建议报告总结:
收集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’);
创建索引
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整理
看完了这篇文章,相信你对“SQL调优工具包DBMS_SQLTUNE怎么用”有了一定的了解,如果想了解更多相关知识,欢迎关注云技术行业资讯频道,感谢各位的阅读!
相关推荐: Oracle 12c R2中ADG会话保留特性的示例分析
这篇文章给大家分享的是有关Oracle 12c R2中ADG会话保留特性的示例分析的内容。小编觉得挺实用的,因此分享给大家做个参考,一起跟随小编过来看看吧。Oracle 12c R2中有一个不错的特性,那就是Active Data Guard会话保留,原本的叫…