在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
--------------------
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.