SQL Tuning Advisor使用实例

SQL Tuning Advisor使用实例

在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能我们什么样的建议呢？