SQL优化

最新推荐文章于 2018-11-24 17:59:31 发布

Phoenix_99

最新推荐文章于 2018-11-24 17:59:31 发布

阅读量394

点赞数

分类专栏： oracle调优文章标签： sql table access join buffer merge

本文链接：https://blog.csdn.net/Phoenix_99/article/details/5934379

版权

oracle调优专栏收录该内容

16 篇文章 0 订阅

订阅专栏

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)