/*
過去，我幾乎沒有使用過 Oracle Related Hints 去改善 sql 的 performance，
一直到我們家熊熊說，在他開發的專案裡，測試效果可以從十幾分鐘降到不到一分鐘...
*/
/* 試試 Set Autotrace On 是否可使用 */
SQL> set autotrace on
/* 假使出現以下錯誤訊息
1.改以 dba 身份登入
2.執行 plustrce.sql
3.grant PLUSTRACE role
*/
SP2-0618: 找不到階段作業 ID. 請檢查是否啟用 PLUSTRACE 角色
SP2-0611: 啟動 STATISTICS 報表時發生錯誤
SQL> connect / as sysdba
已連線.
SQL> @C:\oracle\product\10.2.0\db_1\sqlplus\admin\plustrce.sql
SQL> disconnect
已切斷與 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options 的連線
SQL> grant plustrace to donkey;
順利授權.
SQL> connect donkey/donkey
已連線.
SQL> set autotrace on
/* 建立測試 table 與資料 */
create table countings
(
col1 integer not null,
col2 integer not null,
col3 varchar2(32)
);
insert into countings
select x,y,to_char(x) || ' x ' || to_char(y) || ' = ' || to_char(x*y)
from
(select level x from dual connect by level <= 1000) t,
(select level y from dual connect by level <= 1000) t1;
alter table countings add constraint countings_pk primary key (col1,col2);
/* 使用 traceonly 不列出 query 資料 */
SQL> set autotrace traceonly
/* 加 Related Hints 告訴 Oracle 不要使用 index */
SQL> select /*+ full(countings) */ *
2 from countings
3 where col1 = 555;
已選取 1000 個資料列.
執行計畫
----------------------------------------------------------
Plan hash value: 2354059736
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 44000 | 991 (5)| 00:00:12 |
|* 1 | TABLE ACCESS FULL | COUNTINGS | 1000 | 44000 | 991 (5)| 00:00:12 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("COL1"=555)
Note
-----
- dynamic sampling used for this statement
統計值
----------------------------------------------------------
7 recursive calls
0 db block gets
4489 consistent gets
0 physical reads
0 redo size
36769 bytes sent via SQL*Net to client
1111 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000 rows processed
/* 加 Related Hints 告訴 Oracle 使用 index */
SQL> select /*+ index(countings) */ *
2 from countings
3 where col1 = 555;
已選取 1000 個資料列.
執行計畫
----------------------------------------------------------
Plan hash value: 3805481017
--------------------------------------------------------------------------------------------
| Id |Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 |SELECT STATEMENT | | 1000 | 44000 | 10 (0)| 00:00:01 |
| 1 |TABLE ACCESS BY INDEX ROWID |COUNTINGS | 1000 | 44000 | 10 (0)| 00:00:01 |
|* 2|INDEX RANGE SCAN |COUNTINGS_PK | 1000 | | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COL1"=555)
Note
-----
- dynamic sampling used for this statement
統計值
----------------------------------------------------------
7 recursive calls
0 db block gets
221 consistent gets
0 physical reads
0 redo size
40712 bytes sent via SQL*Net to client
1111 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000 rows processed
