ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle10g新特性:使用DBMS_SQLTUNE优化SQL

Oracle10g新特性:使用DBMS_SQLTUNE优化SQL

原创 Linux操作系统 作者:NinGoo 时间:2019-05-25 16:18:05 0 删除 编辑

在Oracle10g之前,优化SQL是个比较费力的技术活,不停的分析执行计划,加hint,分析统计信息等等,当然也有SQL调优辅助工具可以使用,只是要么价格昂贵,要么效果不佳。在10g中,Oracle推出了自己的SQL优化辅助工具,这就是新的DBMS_SQLTUNE包。执行DBMS_SQLTUNE包进行sql优化需要有advisor的权限:

SYS@ning>grant advisor to ning;

Grant succeeded.

使用DBMS_SQLTUNE包进行SQL优化,大致可以分为四个步骤:

  • 创建优化任务
  • 执行优化任务
  • 显示优化任务的结果
  • 按照建议执行优化

一、创建优化任务
使用dbms_sqltune.create_tuning_task函数来创建优化任务,该参数的声明如下:

代码:
FUNCTION CREATE_TUNING_TASK RETURNS VARCHAR2
Argument Name Type In
/Out
Default?
---------------------- ------------- ------ --------
SQLSET_NAME VARCHAR2 IN
BASIC_FILTER VARCHAR2 IN
DEFAULT
OBJECT_FILTER VARCHAR2 IN
DEFAULT
RANK1 VARCHAR2 IN
DEFAULT
RANK2 VARCHAR2 IN
DEFAULT
RANK3 VARCHAR2 IN
DEFAULT
RESULT_PERCENTAGE NUMBER IN
DEFAULT
RESULT_LIMIT NUMBER IN
DEFAULT
SCOPE VARCHAR2 IN
DEFAULT
TIME_LIMIT NUMBER IN
DEFAULT
TASK_NAME VARCHAR2 IN
DEFAULT
DESCRIPTION VARCHAR2 IN
DEFAULT
PLAN_FILTER VARCHAR2 IN
DEFAULT
SQLSET_OWNER VARCHAR2 IN
DEFAULT


具体每个参数的含义请参考Oracle官方文档的说明。函数的返回值为创建的任务名。下面我们创建一个叫sql_tuning_test的任务:

代码:
NING@ning>DECLARE
2 my_task_name VARCHAR2 (30
);
3 my_sqltext CLOB
;
4 BEGIN 5 my_sqltext := 'SELECT e.last_name, d.department_name, d.department_id 6 FROM employees e, departments d 7 WHERE e.department_id = d.department_id 8 AND d.department_id = :bnd'; 9 my_task_name := dbms_sqltune.create_tuning_task (sql_text=> my_sqltext,
10 bind_list => sql_binds (anydata.convertnumber (9
)),
11 user_name => 'NING'
,
12 scope => 'COMPREHENSIVE'
,
13 time_limit => 60
,
14 task_name => 'sql_tuning_test'
,
15 description =>
'Tuning Task'
16
);
17 END
;
18
/
PL/SQL procedure successfully completed
.


二、执行优化任务

通过调用dbms_sqltune.execute_tuning_task过程来执行前面创建好的优化任务。

代码:

PROCEDURE EXECUTE_TUNING_TASK
Argument Name Type In
/Out Default?
----------------- ------------- ------ --------
TASK_NAME VARCHAR2 IN

NING
@ning>exec dbms_sqltune.execute_tuning_task('sql_tuning_test'
);
PL/SQL procedure successfully completed
.


三、检查优化任务的状态

通过查看user_advisor_tasks/dba_advisor_tasks视图可以查看优化任务的当前状态。

代码:

NING
@ning>SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'sql_tuning_test';
STATUS ----------- COMPLETED


四、得到优化任务执行的结果

通过dbms_sqltune.report_tning_task函数可以获得优化任务的结果。

代码:

NING
@ning>SET LONG 999999
NING
@ning>
set serveroutput on size 999999
NING
@ning>
SET LINESIZE 100
NING
@ning>SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('sql_tuning_test') FROM DUAL
;
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TEST'
)
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION ------------------------------------------------------------------------------- Tuning Task Name : sql_tuning_test
Tuning Task Owner
:
NING
Scope
:
COMPREHENSIVE
Time Limit
(seconds) :
60
Completion Status
:
COMPLETED
Started at
: 04/30/2007 10:42:
48
Completed at
: 04/30/2007 10:42:
49
Number of Statistic Findings
:
2
Number of SQL Profile Findings
:
1

DBMS_SQLTUNE
.REPORT_TUNING_TASK('SQL_TUNING_TEST'
)
----------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------
Schema Name:
NING
SQL ID
:
6rbh5v8smjpdq
SQL Text
: SELECT e.last_name, d.department_name, d.
department_id
FROM employees e
,
departments d
WHERE e
.department_id = d.
department_id
AND d.department_id = :
bnd
------------------------------------------------------------------------------- FINDINGS SECTION (3 findings)
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TEST'
)
----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
1-
Statistics Finding ---------------------
Table "NING"."DEPARTMENTS" was not analyzed
.

Recommendation
--------------
-
Consider collecting optimizer statistics for this table
.
execute dbms_stats.gather_table_stats(ownname => 'NING', tabname
=>
'DEPARTMENTS', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE
,
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TEST'
)
----------------------------------------------------------------------------------------------------
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-
Statistics Finding ---------------------
Table "NING"."EMPLOYEES" was not analyzed
.

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TEST'
)
----------------------------------------------------------------------------------------------------
Recommendation
--------------
-
Consider collecting optimizer statistics for this table
.
execute dbms_stats.gather_table_stats(ownname => 'NING', tabname
=>
'EMPLOYEES', 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
.
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TEST'
)
----------------------------------------------------------------------------------------------------
3- SQL Profile Finding (see explain plans section below
)
--------------------------------------------------------
A potentially better execution plan was found for this statement
.

Recommendation (estimated benefit<=10
%)
---------------------------------------
-
Consider accepting the recommended SQL profile
.
execute dbms_sqltune.accept_sql_profile(task_name => 'sql_tuning_test'
,
replace => TRUE
);

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TEST'
)
----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION -------------------------------------------------------------------------------
1-
Original With Adjusted Cost ------------------------------ Plan hash value: 2052257371
----------------------------------------------------------------------------------
|
Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
----------------------------------------------------------------------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TEST'
)
----------------------------------------------------------------------------------------------------
|
0 | SELECT STATEMENT | | 1 | 26 | 22 (5)| 00:00:01
|
|*
1 | HASH JOIN | | 1 | 26 | 22 (5)| 00:00:01
|
|*
2 | TABLE ACCESS FULL| DEPARTMENTS | 1 | 7 | 3 (0)| 00:00:01
|
|*
3 | TABLE ACCESS FULL| EMPLOYEES | 1 | 19 | 18 (0)| 00:00:01
|
----------------------------------------------------------------------------------
Predicate Information (identified by operation id
):
---------------------------------------------------

1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"
)
2 - filter("D"."DEPARTMENT_ID"=:BND
)
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TEST'
)
----------------------------------------------------------------------------------------------------
3 - filter("E"."DEPARTMENT_ID"=:BND
)
2-
Using SQL Profile -------------------- Plan hash value: 1782137809
----------------------------------------------------------------------------------
|
Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
----------------------------------------------------------------------------------
|
0 | SELECT STATEMENT | | 1 | 26 | 22 (5)| 00:00:01
|
|*
1 | HASH JOIN | | 1 | 26 | 22 (5)| 00:00:01
|
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TEST'
)
----------------------------------------------------------------------------------------------------
|*
2 | TABLE ACCESS FULL| EMPLOYEES | 1 | 19 | 18 (0)| 00:00:01
|
|*
3 | TABLE ACCESS FULL| DEPARTMENTS | 1 | 7 | 3 (0)| 00:00:01
|
----------------------------------------------------------------------------------
Predicate Information (identified by operation id
):
---------------------------------------------------

1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"
)
2 - filter("E"."DEPARTMENT_ID"=:BND
)
3 - filter("D"."DEPARTMENT_ID"=:BND
)

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TEST'
)
----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------


然后我们就可以根据Recommendation部分的建议来执行优化操作了。


五、删除优化任务

通过调用dbms_sqltuen.drop_tuning_task可以删除已经存在的优化任务

代码:

NING
@ning>exec dbms_sqltune.drop_tuning_task('sql_tuning_test');
PL/SQL procedure successfully completed
.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/193161/viewspace-50340/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2004-12-07

  • 博文量
    200
  • 访问量
    129142