在Oracle10g之前,优化SQL是个比较费力的技术活,不停的分析执行计划,加hint,分析统计信息等等。在10g中,Oracle推出了自己的SQL优化辅助工具: SQL优化器(SQL Tuning Advisor :STA),它是新的DBMS_SQLTUNE包。使用STA一定要保证优化器是CBO模式下。
执行DBMS_SQLTUNE包进行sql优化需要有advisor的权限:
SQL> create user dave identified by dave;
用户已创建。
SQL> grant connect,resource to dave;
授权成功。
SQL> grant advisor to dave;
授权成功。
下面简单介绍一下如何优化一条找到的问题语句。
createtable bigtab asselectrownumas"id",a.* from sys.all_objects a;
createtable smalltab asselectrownumas"id",a.* FROM sys.all_tables a;
然后多运行几次下面的脚本,增加表里的数据:
insertintobigtabselectrownumas"id",a.* from sys.all_objects a;
insertintosmalltabselectrownumas"id",a.* FROM sys.all_tables a;
这里创建一张大表和一张小表,并且都没有索引,下面执行一个查询:
SQL> set timing on
SQL> set autot on
SQL> select count(*) from bigtab a, smalltab b where a.object_name=b.table_name;
COUNT(*)
----------
2141537
已用时间: 00: 00: 20.05
执行计划
----------------------------------------------------------
Plan hash value: 3089226980
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 45 | 3146 (1)| 00:00:38 |
| 1 | SORT AGGREGATE | | 1 | 45 | | |
|* 2 | HASH JOIN | | 447K| 19M| 3146 (1)| 00:00:38 |
| 3 | TABLE ACCESS FULL| SMALLTAB | 27327 | 533K| 264 (1)| 00:00:04 |
| 4 | TABLE ACCESS FULL| BIGTAB | 712K| 16M| 2878 (1)| 00:00:35 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OBJECT_NAME"="B"."TABLE_NAME")
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
31149 consistent gets
21058 physical reads
0 redo size
426 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
第一步:创建优化任务
通过调用函数CREATE_TUNING_TASK来创建优化任务,调用存储过程EXECUTE_TUNING_TASK执行该任务:
SQL> set autot off
SQL> set timing off
SQL> DECLARE
2 my_task_name VARCHAR2(30);
3 my_sqltext CLOB;
4 BEGIN
5 my_sqltext := 'select count(*) from bigtab a, smalltab b where a.object_name=b.table_name';
6 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
7 sql_text => my_sqltext,
8 user_name => 'DAVE',-- 注意是大写,不然会报错,用户无效
9 scope => 'COMPREHENSIVE',
10 time_limit => 60,
11 task_name => 'tuning_sql_test',
12 description => 'Task to tune a query on a specified table');
13
14 DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'tuning_sql_test');
15 END;
16 /
PL/SQL procedure successfully completed.
在函数CREATE_TUNING_TASK,sql_text是需要优化的语句,user_name是该语句通过哪个用户执行,scope是优化范围(limited或comprehensive),time_limit优化过程的时间限制,task_name优化任务名称,description优化任务描述。
第二步:执行优化任务
通过调用dbms_sqltune.execute_tuning_task过程来执行前面创建好的优化任务。
SQL> exec dbms_sqltune.execute_tuning_task('tuning_sql_test');
PL/SQL 过程已成功完成。
第三步:检查优化任务的状态
通过查看user_advisor_tasks/dba_advisor_tasks视图可以查看优化任务的当前状态。
SQL> SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name ='tuning_sql_test';
TASK_NAME STATUS
------------------------------ -----------
tuning_sql_test COMPLETED
第四步:查看优化结果
通过dbms_sqltune.report_tning_task函数可以获得优化任务的结果。
SQL> SET LONG 999999
SQL> set serveroutput on size 999999
SQL> SET LINESIZE 100
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'tuning_sql_test') from DUAL;
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : tuning_sql_test
Tuning Task Owner : DEMO
Scope : COMPREHENSIVE
Time Limit(seconds) : 60
Completion Status : COMPLETED
Started at : 5/28/2010 13:16:43
Completed at : 5/28/2010 13:16:44
Number of Index Findings : 1
Schema Name: DEMO
SQL ID : 6p64dnnsqf9pm
SQL Text : select count(*) from bigtab a, smalltab b where
a.object_name=b.table_name
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- 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: 100%)
----------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index DEMO.IDX$$_06C50001 on SYS.SMALLTAB('TABLE_NAME');
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index DEMO.IDX$$_06C50002 on SYS.BIGTAB('OBJECT_NAME');
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: 3089226980
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | 3550 (2)| 00:00:43 |
| 1 | SORT AGGREGATE | | 1 | 36 | | |
|* 2 | HASH JOIN | | 155K| 5462K| 3550 (2)| 00:00:43 |
| 3 | TABLE ACCESS FULL| SMALLTAB | 1223 | 22014 | 11 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| BIGTAB | 1205K| 20M| 3526 (1)| 00:00:43 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OBJECT_NAME"="B"."TABLE_NAME")
2- Using New Indices
--------------------
Plan hash value: 494801882
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | 1108 (3)| 00:00:14 |
| 1 | SORT AGGREGATE | | 1 | 36 | | |
|* 2 | HASH JOIN | | 155K| 5462K| 1108 (3)| 00:00:14 |
| 3 | INDEX FAST FULL SCAN| IDX$$_06C50001 | 1223 | 22014 | 3 (0)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN| IDX$$_06C50002 | 1205K| 20M| 1093 (2)| 00:00:14 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OBJECT_NAME"="B"."TABLE_NAME")
------------------------------------------------------------------------------
看一下这个优化建议报告:
第一部分是关于这次优化任务的基本信息:如任务名称、执行时间、范围、涉及到的语句等等。
第二部分是关于这次优化任务的所找到的问题以及给出的优化建议。前面先给出了问题描述:可以通过建立更多的所引来提高性能;然后是建议的具体内容:在表smalltab的字段table_name上创建索引,在表bigtab的字段object_name上创建索引;最后是相关注意事项:此次优化虽然给出了创建索引的建议,但是最好通过SQL访问建议器(SQL Access Advisor SAA)结合整个数据库的工作量来深入分析,那样就能给出考虑了索引维护和空间消耗等因素的更加合理的建议。
最后,报告还给出了原有的查询计划,以及采用优化建议以后的查询计划的对比。可以看出COST值大大下降。
五、删除优化任务
通过调用dbms_sqltuen.drop_tuning_task可以删除已经存在的优化任务
SQL>exec dbms_sqltune.drop_tuning_task('tuning_sql_test');
PL/SQL procedure successfully completed.
注: 整理自网络
相关推荐
使用ORACLE SQL Tuning advisor快速优化低效的SQL语句, 这里是一个使用范例,有9个步骤, 后面 其他语句,都按照这个步骤来做就可以了。
关于oracle的SQL Tuning Advisor全面的使用介绍,包括分析awr、cursor、sql_set、sql文本中获取sql并且建立、执行、获取结果的方法。并且介绍了sql_set,sql_profile的操作。里面涉及到的内容都有可执行的代码。
而tuning模式则将高负载的SQL语句直接扔给优化器,优化器来自动对其进行详细的分析,调试并给出建议,这是Oracle 提供的Automatic Tuning Optimizer,即自动调整优化器。Oracle 自动调整优化器通过SQL调优建
oracle 10gADDM 和 SQL Tuning Advisor
SQL Tuning使用指南 QCO的SQL Tuning模块是一种功能强大的SQL语句分析、测试、优化工具,它可以贯穿Oracle数据库应用的整个生命周期,在开发阶段、试运行阶段和生产阶段帮助获得最佳SQL语句,以提高应用系统的...
Top-SQL Tuning SQL语句调整
SQL Tuning sqlServer,数据库教程, chm格式 英文版
SQL Tuning Advisor用一个调整工具集作为输入,检查其中的语句并为它们提出优化建议。Oracle 10g第二版(release 2)中增加了移动SQL Tuning Set的功能。使用一个中转表和DBMS_SQLTUNE类中的过程,你就可以从创建SQL...
一款针对Oracle SQL语句优化的工具,对于某些耗时的查询语句有很好的优化效果。该软件的优化手段是引入Oracle的hint功能,手动给查询语句指定更优的执行计划来达到优化效果。
Tosska SQL Tuning Expert (TSE™) 是一款优秀的SQL 调优工具,无需用户参与即可优化 SQL 语句。该产品将为您提供最终的 SQL 性能解决方案,只需点击鼠标即可。在整个 SQL 调优过程中,您不必进行分析、猜测或手工...
针对这种情况,人工智能自动SQL优化工具应运而生。现在我就向大家介绍这样一款工具:SQLTuning for SQL Server。 1. SQL Tuning 简介 SQL Turning是Quest公司出品的Quest Central软件中的一个工具。 QuestCentral...
oracle的sqltuning 课程,但资源有些老,04年的
sql tuning oracle sql tuning
Oracel SQL tuning Oracel SQL tuning Oracel SQL tuning Oracel SQL tuning Oracel SQL tuning Oracel SQL tuning
Oracle SQL Tuning with Oracle SQLTXPLAIN: Oracle Database 12c Edition by Stelios Charalambides English | 8 May 2017 | ISBN: 1484224353 | 408 Pages | PDF | 24.39 MB Learn through this practical guide ...
1. Database Tuning advisor (DTA) Enhancements 2. Column store indexes 3. Online index operations
SQL Tuning author Dan Tow outlines a timesaving method he's developed for finding the optimum execution plan--rapidly and systematically--regardless of the complexity of the SQL or the database ...
来源oracle官方ppt,讲解sql tuning技巧
自动SQL优化工具,用于解决SQL性能问题
sqltuning 英文版 sqltuning 英文版 sqltuning 英文版