使用SQLTuningAdvisorSTA 优化SQL语句 如何优化sql语句

在Oracle10g之前,优化SQL是个比较费力的技术活,不停的分析执行计划,加hint,分析统计信息等等。在Oracle10g中推出了自己的SQL优化辅助工具:SQL优化器(SQLTuningAdvisor:STA),它是新的DBMS_SQLTUNE包。一定要保证你的优化器是CBO模式。

1.执行DBMS_SQLTUNE包进行sql优化需要有advisor的权限

SQL> grant advisor tozy;
Grant succeeded.

下面简单介绍一下如何优化一条找到的问题语句

2.创建用户做测试的2张表,大表里面插入500万条数据,小表里面插入10万条数据,其创建方法如下:

SQL> create table bigtable(id number(10),name varchar2(100));
Table created.

SQL> begin
2for i in 1..5000000 loop
3insertinto bigtable values(i,'test'||i);
4end loop;
5 end;
6 /
PL/SQL procedure successfully completed.

SQL> commti;

SQL> create table smalltable(id number(10),name varchar2(100));
Table created.

SQL> begin
2 for i in 1..100000loop
3 insert into smalltablevalues(i,'test'||i);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.

SQL> commti;

3.然后对bigtable和smalltable做一个等连接查询,然后跟踪其执行计划。

SQL> set autotracetrace
SQL>select a.id,a.name,b.id,b.name
2from bigtable a,smalltableb
3where a.id=b.id anda.id=40000;
执行计划
----------------------------------------------------------
Plan hash value:1703851322
---------------------------------------------------------------------------------
|Id | Operation| Name| Rows | Bytes | Cost (%CPU)|Time|
---------------------------------------------------------------------------------
|0 | SELECTSTATEMENT||582 | 75660| 3708(6)|00:00:45|
|* 1 | HASH JOIN||582 | 75660| 3708(6)|00:00:45|
|* 2 |TABLE ACCESS FULL|SMALLTABLE |3 |195 |73(6)|00:00:01|
|* 3 |TABLE ACCESS FULL|BIGTABLE|173 | 11245| 3635(6)|00:00:44|
---------------------------------------------------------------------------------
PredicateInformation (identified by operation id):
---------------------------------------------------
1- access("A"."ID"="B"."ID")
2- filter("B"."ID"=40000)
3- filter("A"."ID"=40000)
Note
-----
-dynamic sampling used for this statement
统计信息
----------------------------------------------------------
134 recursivecalls
2 db block gets
32073 consistentgets
8368 physical reads
1135576 redo size
585 bytes sent via SQL*Net toclient
385 bytes received via SQL*Net fromclient
2 SQL*Net roundtrips to/fromclient
2 sorts (memory)
0 sorts (disk)
1 rows processed

熟悉执行计划的就可以看出,这个sql执行是很慢的,2个表都做的是全表扫描,并且其物理读是8368,按照优化的经验,给2个表的id创建索引,减少查询时候的物理读,下面我们就看看通过优化器,oracle能我们什么样的建议呢?

第一步:创建优化任务

4.通过DBMS_SQLTUNE包的CREATE_TUNING_TASK来创建一个优化任务。

SQL> DECLARE
2my_task_namevarchar2(30);
3my_sqltextclob;
4BEGIN
5my_sqltext:='select a.id,a.name,b.id,b.name from bigtablea,smalltable b where a.id=b.id and a.id=40000'; --需要优化的语句
6my_task_name:=DBMS_SQLTUNE.create_tuning_task(
7sql_text =>my_sqltext,
8user_name => 'ZY', --该语句通过哪个用户执行。注意是大写,不然会报错,用户无效
9scope => 'COMPREHENSIVE', --优化范围(limited或comprehensive)
10time_limit => 60, --优化过程的时间限制
11task_name => 'tuning_sql_test', --优化任务名称,自己拟定
12description => 'Task to tune a query on a specified table');--优化任务描述,自己拟定
13 END;
14 /
PL/SQL procedure successfullycompleted

第二步:执行优化任务

5.通过DBMS_SQLTUNE.EXECUTE_TUNING_TASK来执行前面创建好的优化任务,生成调优建议。

SQL> execDB【】MS_SQLTUNE.execute_tuning_task('tuning_sql_test');
PL/SQLprocedure successfully completed

第三步:检查优化任务的状态

6.通过user_advisor_tasks或者dba_advisor_tasks来查看调优任务执行的状况

SQL> selecttask_name,advisor_name,status
2from user_advisor_tasks;
TASK_NAMEADVISOR_NAMESTATUS
----------------------------------------------------------------------------------
tuning_sql_testSQL Tuning AdvisorCOMPLETED

如果status是EXECUTING,则表示任务正在执行,如果为COMPLETED,则任务已经执行完毕。

第四步:查看优化结果

7.通过调用dbms_sqltune.report_tuning_task查询调优的结果,不过在查询结果之前,得设置sqlplus的环境,如果不设置,则查询的结果出不来。

SQL> set long 999999
SQL> set LONGCHUNKSIZE 999999
SQL> set serveroutput on size 999999
SQL> set linesize 200
SQL> selectdbms_sqltune.report_tuning_task('tuning_sql_test') fromdual;
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
-----------------------------------------------------------------------------------------
GENERAL INFORMATIONSECTION
-------------------------------------------------------------------------------
Tuning TaskName:tuning_sql_test
Tuning Task Owner:ZY
Scope :COMPREHENSIVE
TimeLimit(seconds):60
CompletionStatus : COMPLETED
Started at :09/12/2012 11:06:15
Completed at : 09/12/201211:06:48
Numberof Statistic Findings :2
Number ofIndex Findings : 1
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
SchemaName:ZY
SQLID:7arau1k5a3mv1
SQL Text:select a.id,a.name,b.id,b.name from bigtable a,smalltableb
where a.id=b.id anda.id=40000
-------------------------------------------------------------------------------
FINDINGS SECTION (3findings)
-------------------------------------------------------------------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
1- StatisticsFinding
---------------------
尚未分析表"ZY"."SMALLTABLRecommendation
--------------
- 考虑收集此表的优
executedbms_stats.gather_table_stats(ownname => 'ZY', tabname=>
'SMALLTABLE', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZEAUTO');

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
Rationale
---------
为了选择好的执行计划, 优化程序需
2- StatisticsFinding
---------------------
尚未分析表"ZY"."BIGTABL
Recommendation
--------------
- 考虑收集此表的优
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
executedbms_stats.gather_table_stats(ownname => 'ZY', tabname=>
'BIGTABLE', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZEAUTO');

Rationale
---------
为了选择好的执行计划, 优化程序需

3- Index Finding (see explain plans sectionbelow)
--------------------------------------------------
通过创建一个或多个索引可以

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
Recommendation(estimated benefit: 100%)
----------------------------------------
-考虑运行可以改进物理方案设计的Access Advi
create index ZY.IDX$$_30CA0001 onZY.BIGTABLE('ID');
-考虑运行可以改进物理方案设计的Access Advi
create index ZY.IDX$$_30CA0002 onZY.SMALLTABLE('ID');

Rationale
---------
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
创建推荐的索引可以显著地改进此语句的执行计划。但是, 使用典型的
可能比单个语句更可取。通过这种方法可以获得全面的索引建
-------------------------------------------------------------------------------
EXPLAIN PLANSSECTION
-------------------------------------------------------------------------------
1-Original
-----------
Plan hash value:1703851322
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
---------------------------------------------------------------------------------
|Id | Operation| Name| Rows | Bytes | Cost (%CPU)|Time|
---------------------------------------------------------------------------------
|0 | SELECTSTATEMENT||582 | 75660| 3708 (6)| 00:00:45|
|* 1 | HASH JOIN||582 | 75660| 3708(6)|00:00:45|
|* 2 |TABLE ACCESS FULL|SMALLTABLE |3 |195 |73(6)|00:00:01|
|* 3 |TABLE ACCESS FULL|BIGTABLE|173 | 11245| 3635(6)|00:00:44|
--------------------------------------------------------------------------------
PredicateInformation (identified by operation id):
---------------------------------------------------

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
----------------------------------------
1- access("A"."ID"="B"."ID")
2- filter("B"."ID"=40000)
3- filter("A"."ID"=40000)

2-Using New Indices
--------------------
使用SQLTuningAdvisor(STA)优化SQL语句 如何优化sql语句
Planhash value: 57768072

------------------------------------------------------------------------------------------------
|Id | Operation| Name|Rows | Bytes | Cost (%CPU)|Time|
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
|0 | SELECTSTATEMENT||1 |130 | 3(0)| 00:00:01 |
|1 | TABLE ACCESS BY INDEXROWID | SMALLTABLE | 1 | 65 | 1(0)| 00:00:01 |
|2 |NESTEDLOOPS|| 1 |130 | 3(0)| 00:00:01 |
|3 |TABLE ACCESS BY INDEXROWID| BIGTABLE |1 | 65 |2 (0)| 00:00:01|
|* 4 |INDEX RANGE SCAN|IDX$$_30CA0001| 1 || 1 (0)| 00:00:01|
|* 5 |INDEX RANGESCAN|IDX$$_30CA0002| 1 || 0 (0)| 00:00:01|
------------------------------------------------------------------------------------------------
PredicateInformation (identified by operation id):
---------------------------------------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
4- access("A"."ID"=40000)
5- access("B"."ID"=40000)
-------------------------------------------------------------------------------

从上面的结果可以看到oracle的调优顾问给我们3条建议:

(1)SCOTT.SMALLTABLE表没有做分析,需要做一下表结构的分析,并且给出一个分析的建议,如下所示:

execute dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname =>
'SMALLTABLE', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');

(2)SCOTT.BIGTABLE表没有做分析,需要做一下表结构的分析,并且给出一个分析的建议,如下所示:

execute dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname =>
'BIGTABLE', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');

(3)oracle建议我们在表SCOTT.SMALLTABLE,SCOTT.BIGTABLE的id列创建一个bitree索引,给的建议如下:

create index SCOTT.IDX$$_00790002 onSCOTT.BIGTABLE('ID');
create index SCOTT.IDX$$_00790001 onSCOTT.SMALLTABLE('ID');

当然创建索引的名字可以改成别的名字。

最后,报告还给出了原有的查询计划,以及采用优化建议以后的查询计划的对比。可以看出COST值大大下降。

通过以上查看oracle的调优顾问给的建议,基本和我们在前面给出的调优方案是一致,因此当我们给一个大的SQL做优化的时候,可以先使用oracle调优顾问,得到一些调优方案,然后根据实际情况做一些调整就可以。

五、删除优化任务

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

SQL>execdbms_sqltune.drop_tuning_task('tuning_sql_test');
PL/SQLproceduresuccessfullycompleted.

  

爱华网本文地址 » http://www.413yy.cn/a/25101015/252658.html

更多阅读

在word中如何使用超链接 word如何使用超链接

在word的编辑中为了能够更形象的展示一些物品,可能会用到超链接。那么怎么使用呢?在word中如何使用超链接——链接电脑上到其他文本在word中如何使用超链接 1、打开word。在word中如何使用超链接 2、选中需要加链接的文字。在word

★i实用★如何使用投影仪 明基投影仪如何使用

投影仪现在很多公司企业都会用到,所以看看怎么使用,有哪些注意要点吧~~★i实用★如何使用投影仪——步骤/方法★i实用★如何使用投影仪 1、第一次接触到投影仪先要知道电源开关在哪里★i实用★如何使用投影仪 2、这时候,就不得不知道在

Mac使用教程Dashboard篇 mac如何关闭dashboard

不是所有 Mac 上的工作都需要使用大型应用程式,有些简单的工作不妨让 [tiger] 上的新功能 Dashboard 来为您完成。只要按下一个键,半透明的 Dashboard 就会浮现在电脑桌面上,供您快速取用多种有趣又实用的迷你应用程式 widget。需要加

如何使用VPN上网 精 ipad如何使用vpn上网

如何使用VPN上网 精——简介使用VPN的原因很多,比如被限制上网,被封IP,加速网页的浏览等等,很多人为了使用VPN都安装上了软件,其实VPN的使用,除了一些专用的网络外,并不需要安装软件的如何使用VPN上网 精——方法/步骤如何使用VPN

惠普系统恢复使用说明 惠普电脑如何恢复系统

惠普系统恢复使用说明——简介现在预装系统的机器上大都集成安装了系统恢复功能,可以很方便的让我们在系统出现故障无法启动时,进行快速的系统恢复,同时系统恢复功能中还有文件备份,这样也保证了文件不丢失。惠普dv606b12TX,在开机时左下

声明:《使用SQLTuningAdvisorSTA 优化SQL语句 如何优化sql语句》为网友請叫我丶偏執狂分享!如侵犯到您的合法权益请联系我们删除