Oracleパフォーマンス調査-2

メモ

set autotrace on explain
select sysdate from dual

set autotrace traceonly on explain
select sysdate from dual

set timing on
set autotrace on
set linesize 300

結果

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17294 | 2854K| 1072 (1)|
|* 1 | TABLE ACCESS FULL| TBL_OCD_REPAIRLOG | 17294 | 2854K| 1072 (1)|
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 1 - filter("TBL_AAA_LOG"."AAA_CD1" IS NOT NULL AND
 "TBL_AAA_LOG"."DELETE_FLAG"=0)

Note
-----
 - 'PLAN_TABLE' is old version


統計
----------------------------------------------------------
 5 recursive calls
 0 db block gets
 6136 consistent gets
 4752 physical reads
 0 redo size
 2141690 bytes sent via SQL*Net to client
 15870 bytes received via SQL*Net from client
 1407 SQL*Net roundtrips to/from client
 0 sorts (memory)
 0 sorts (disk)
 21089 rows processed

性能評価時にバッファをクリアする

alter system flush buffer_cache ;
alter system flush shared_pool ;

set autotraceで下記のエラーが出る場合にはユーザーにplustraceロールを割り当てる