SQL Tuning Advisor使用实例

在Oracle 10g之前，想要优化一个sql语句是比较麻烦，但是在Oracle 10g这个版本推出的SQL Tuning Advisor这个工具，能大大减少sql

在Oracle 10g之前，想要优化一个sql语句是比较麻烦，但是在Oracle 10g这个版本推出的SQL Tuning Advisor这个工具，能大大减少sql调优的工作量，不过要想使用SQL Tuning Advisor，一定要保证你的优化器是CBO模式。

1.首先需要创建一个用于调优的用户bamboo,并授予advisor给创建的用户

SQL> create user bamboo identified by bamboo;
User created.
SQL> grant connect,resource to bamboo;
Grant succeeded.
SQL> grant advisor to bamboo;
Grant succeeded.

2.创建用户做测试的2张表,大表里面插入500万条数据，小表里面插入10万条数据，其创建方法如下
SQL> create table bigtable (id number(10),name varchar2(100));
Table created.

SQL> begin
2 for i in 1..5000000 loop
3 insert into bigtable values(i,'test'||i);
4 end 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..100000 loop
3 insert into smalltable values(i,'test'||i);
4 end loop;
5 end;
6 /

PL/SQL procedure successfully completed.

SQL> commti;

3.然后对bigtable和smalltable做一个等连接查询，然后跟踪其执行计划
SQL> select a.id,a.name,b.id,b.name from bigtable a,smalltable b where a.id=b.id and a.id=40000;

ID NAME ID NAME
---------- ---------------------------------------- ---------- ----------------------------------------
40000 test40000 40000 test40000

Execution Plan
----------------------------------------------------------
Plan hash value: 1703851322

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 839 | 106K| 3656 (5)| 00:00:44 |
|* 1 | HASH JOIN | | 839 | 106K| 3656 (5)| 00:00:44 |
|* 2 | TABLE ACCESS FULL| SMALLTABLE | 5 | 325 | 71 (3)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| BIGTABLE | 173 | 11245 | 3584 (5)| 00:00:44 |
---------------------------------------------------------------------------------

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

Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
16151 consistent gets
11469 physical reads
0 redo size
588 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
熟悉执行计划的就可以看出，这个sql执行是很慢的，，2个表都做的是全表扫描，并且其物理读是11469，按照优化的经验，给2个表的id创建索引，减少查询时候的物理读,下面我们就看看通过优化器，oracle能我们什么样的建议呢？