在Oracle10g之前，优化SQL是个比较费力的技术活，不停的分析执行计划，加hint，分析统计信息等等。在Oracle10g中推出了自己的SQL优化辅助工具: SQL优化器(SQL Tuning Advisor ：STA)，它是新的DBMS_SQLTUNE包。一定要保证你的优化器是CBO模式。
1.执行DBMS_SQLTUNE包进行sql优化需要有advisor的权限
SQL> create user shall identified by shall;
SQL> grant connect,resource to shall;
SQL> grant advisor to shall;
----实验
2.创建做测试表2张，大表500万条数据，小表10万条数据，如下：
SQL> conn shall/shall
SQL> create table bigtable (id number(10),name varchar2(100));
begin
for i in 1..5000000 loop
insert into bigtable values(i,'test'||i);
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
SQL> create table smalltable (id number(10),name varchar2(100));
begin
for i in 1..100000 loop
insert into smalltable values(i,'test'||i);
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
3.然后对bigtable和smalltable做一个等连接查询，然后跟踪其执行计划
SQL> set autotrace trace
SQL> select a.id,a.name,b.id,b.name from bigtable a,smalltable b where a.id=b.id and a.id=40000;
Execution Plan
----------------------------------------------------------
Plan hash value: 1703851322
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 721 | 93730 | 4488 (1)| 00:00:54
|* 1 | HASH JOIN | | 721 | 93730 | 4488 (1)| 00:00:54
|* 2 | TABLE ACCESS FULL| SMALLTABLE | 4 | 260 | 103 (1)| 00:00:02
|* 3 | TABLE ACCESS FULL| BIGTABLE | 177 | 11505 | 4385 (1)| 00:00:53
--------------------------------------------------------------------------------
Predicate Information (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 (level=2)
Statistics
----------------------------------------------------------
30 recursive calls
2 db block gets
31462 consistent gets
8282 physical reads
1096960 redo size
739 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
熟悉执行计划的就可以看出，这个sql执行是很慢的，2个表都做的是全表扫描，并且其物理读是8282，按照优化的经验，给2个表的id创建索引，减少查询时候的物理读,下面我们就看看通过优化器，oracle能我们什么样的建议呢？
第一步：创建优化任务
4.通过DBMS_SQLTUNE包的CREATE_TUNING_TASK来创建一个优化任务。
5.通过DBMS_SQLTUNE.EXECUTE_TUNING_TASK来执行前面创建好的优化任务，生成调优建议。
SQL> exec DBMS_SQLTUNE.execute_tuning_task('tuning_sql_test');
PL/SQL procedure successfully completed
第三步：检查优化任务的状态
6.通过user_advisor_tasks或者dba_advisor_tasks来查看调优任务执行的状况
SQL> select task_name,advisor_name,status from user_advisor_tasks;
TASK_NAME ADVISOR_NAME STATUS
----------------------- -------------------------- ---------------------------------
tuning_sql_test SQL Tuning Advisor COMPLETED
----如果status是EXECUTING，则表示任务正在执行，如果为COMPLETED，则任务已经执行完毕。
第四步：查看优化结果
7.通过调用dbms_sqltune.report_tuning_task查询调优的结果，不过在查询结果之前，得设置sqlplus的环境，如果不设置，则查询的结果出不来。
set long 999999
set LONGCHUNKSIZE 999999
set serveroutput on size 999999
set linesize 200
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 : SHALL
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 04/23/2016 02:32:18
Completed at : 04/23/2016 02:32:47
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
-------------------------------------------------------------------------------
Schema Name: SHALL
SQL ID : 7arau1k5a3mv1
SQL Text : select a.id,a.name,b.id,b.name from bigtable a,smalltable b
where a.id=b.id and a.id=40000
-------------------------------------------------------------------------------
FINDINGS SECTION (3 findings)
-------------------------------------------------------------------------------
1- Statistics Finding
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
-----------------------------------------------------------------------
Table "SHALL"."SMALLTABLE" was not analyzed.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'SHALL', tabname =>
'SMALLTABLE', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');
Rationale
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
-----------------------------------------------------------------------
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.
2- Statistics Finding
---------------------
Table "SHALL"."BIGTABLE" was not analyzed.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
-----------------------------------------------------------------------
execute dbms_stats.gather_table_stats(ownname => 'SHALL', tabname =>
'BIGTABLE', 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.
3- Index Finding (see explain plans section below)
--------------------------------------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
-----------------------------------------------------------------------
The execution plan of this statement can be improved by creating one or more
indices.
Recommendation (estimated benefit: 99.88%)
------------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index SHALL.IDX$$_00200001 on SHALL.BIGTABLE("ID","NAME");
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
-----------------------------------------------------------------------
create index SHALL.IDX$$_00200002 on SHALL.SMALLTABLE("ID","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.
-------------------------------------------------------------------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
-----------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 1703851322
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 721 | 93730 | 4488 (1)| 00:00:54 |
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
---------------------------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN | | 721 | 93730 | 4488 (1)| 00:00:54 |
|* 2 | TABLE ACCESS FULL| SMALLTABLE | 4 | 260 | 103 (1)| 00:00:02 |
|* 3 | TABLE ACCESS FULL| BIGTABLE | 177 | 11505 | 4385 (1)| 00:00:53 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."ID"="B"."ID")
2 - filter("B"."ID"=40000)
3 - filter("A"."ID"=40000)
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
---------------------------------------------------------------------------------------------------------------------------------
2- Using New Indices
--------------------
Plan hash value: 1578481415
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 130 | 5 (0)| 00:00:01 |
| 1 | MERGE JOIN CARTESIAN| | 1 | 130 | 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX$$_00200001 | 1 | 65 | 3 (0)| 00:00:01 |
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
-----------------------------------------------------------------------
| 3 | BUFFER SORT | | 1 | 65 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX$$_00200002 | 1 | 65 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."ID"=40000)
4 - access("B"."ID"=40000)
-------------------------------------------------------------------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
---------------------------------------------------------------------------------------------------------------------------------
从上面的结果可以看到oracle的调优顾问给我们3条建议：
（1）SHALL.SMALLTABLE表没有做分析，需要做一下表结构的分析，并且给出一个分析的建议，如下所示：
execute dbms_stats.gather_table_stats(ownname => 'SHALL', tabname => 'SMALLTABLE', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');
（2）SHALL.BIGTABLE表没有做分析，需要做一下表结构的分析，并且给出一个分析的建议，如下所示：
execute dbms_stats.gather_table_stats(ownname => 'SHALL', tabname => 'BIGTABLE', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');
（3）oracle建议我们在表SHALL.SMALLTABLE，SHALL.BIGTABLE的id列创建一个bitree索引，给的建议如下：
create index SHALL.IDX$$_00200002 on SHALL.SMALLTABLE("ID","NAME");
create index SHALL.IDX$$_00200001 on SHALL.BIGTABLE("ID","NAME");
当然创建索引的名字可以改成别的名字。
create index SHALL.IDX_SMALLTABLE_ID on SHALL.SMALLTABLE("ID","NAME");
create index SHALL.IDX_BIGTABLE_ID on SHALL.BIGTABLE("ID","NAME");
最后，报告还给出了原有的查询计划，以及采用优化建议以后的查询计划的对比。可以看出COST值大大下降。
通过以上查看oracle的调优顾问给的建议，基本和我们在前面给出的调优方案是一致，因此当我们给一个大的SQL做优化的时候，可以先使用oracle调优顾问，得到一些调优方案，然后根据实际情况做一些调整就可以。
五、删除优化任务
8.通过调用dbms_sqltuen.drop_tuning_task可以删除已经存在的优化任务
SQL> select task_name,advisor_name,status from user_advisor_tasks;
TASK_NAME ADVISOR_NAME STATUS
------------------------------ ------------------------------ -----------
tuning_sql_test SQL Tuning Advisor COMPLETED
SQL>exec dbms_sqltune.drop_tuning_task('tuning_sql_test');
PL/SQL procedure successfully completed.
--------------------------------------reference http://blog.sina.com.cn/s/blog_4bde435b01017urb.html
http://www.linuxidc.com/Linux/2013-05/85156.htm
sql调优脚本
使用到的视图：
DBA_ADVISOR_LOG
DBA_ADVISOR_TASKS
DBA_ADVISOR_FINDINGS
DBA_ADVISOR_RECOMMENDATIONS
DBA_ADVISOR_RATIONALE
DBA_SQLTUNE_STATISTICS
DBA_SQLTUNE_BINDS
DBA_SQLTUNE_PLANS
脚本：sta.sql 内容如下：
SET ECHO OFF TERMOUT ON FEEDBACK OFF VERIFY OFF
SET SCAN ON
SET LONG 1000000 LINESIZE 180
COL recs FORMAT a135
VARIABLE tuning_task VARCHAR2(30)
DECLARE
l_sql_id v$session.prev_sql_id%TYPE;
BEGIN
SELECT prev_sql_id INTO l_sql_id
FROM v$session
WHERE audsid = userenv('SESSIONID');
:tuning_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id);
dbms_sqltune.execute_tuning_task(:tuning_task);
END;
/
SELECT dbms_sqltune.report_tuning_task(:tuning_task) as recs
FROM dual;
SET VERIFY ON FEEDBACK ON
调用调优脚本
sql>@sta.sql
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30130773/viewspace-2120907/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/30130773/viewspace-2120907/
扫一扫
1579
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
