1.每次读取的块数
show parameter db_file_multiblock_read_count
2.找出耗费时间较多的语句
SELECT ADDRESS,
substr(SQL_TEXT,1,20) Text,
buffer_gets,
executions,
buffer_gets/executions AVG
FROM v$sqlarea
WHERE executions>0
AND buffer_gets > 100000
ORDER BY 5
3.hints
对对象进行分析后:
analyze table a compute statistics;
analyze table b compute statistics;
analyze table c compute statistics;
analyze index inx_col12A compute statistics;
select A.col4
from C , A , B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=8 Bytes=336)
1 0 HASH JOIN (Cost=5 Card=8 Bytes=336)
2 1 MERGE JOIN (CARTESIAN) (Cost=3 Card=8 Bytes=64)
3 2 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=2 Bytes=8)
4 2 SORT (JOIN) (Cost=2 Card=4 Bytes=16)
5 4 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=4 Bytes=16)
6 1 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=30 Bytes=1020)
select /*+ ORDERED */ A.col4
from C , A , B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=9 Bytes=378)
1 0 HASH JOIN (Cost=5 Card=9 Bytes=378)
2 1 HASH JOIN (Cost=3 Card=30 Bytes=1140)
3 2 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=4 Bytes=16)
4 2 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=30 Bytes=1020)
5 1 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=2 Bytes=8)