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能我們什麼樣的建議呢？

1 2 3 下一頁