关于数据字典的查询效率优化 (40天)
公众号：杨建荣的学习笔记 · 作者：笔记40天 · 发布：2014-04-12 11:21:31 · 原文链接
最近因为测试需要对一些数据文件做压缩，腾出更多的空间来为其他环境做准备，压缩数据文件，
最开始采用了如下的sql
col name for a40
col resizecmd for a80
select a.file#,a.name,a.bytes/1024/1024 CurrentMB,
ceil(HWM * a.block_size)/1024/1024 ResizeTo,
(a.bytes – HWM * a.block_size)/1024/1024 ReleaseMB,
‘alter database datafile ”’||a.name||”’ resize ‘||
ceil(HWM * a.block_size/1024/1024) || ‘M;’ ResizeCMD
from v$datafile a,
(select file_id,max(block_id+blocks-1) HWM
from dba_extents
group by file_id) b
where a.file# = b.file_id(+)
and (a.bytes – HWM *block_size)>0
order by 5
基本思路还是根据高水位线来做判断数据文件中有多少空闲块。
但是这个sql运行的时候效率还是有些让人抓狂，在比较小的环境运行一次都需要5，6分钟，反复执行速度还是很慢（按理说速度应该会有提高)
决定自己优化一下，毕竟磨刀不误砍柴工嘛。
查看性能瓶颈，主要在dba_extents上，这是个数据字典视图，决定看看里面到底引用了那些基表，裁剪一下。
dba_extents包含两部分。内部做了union all,数据基本都是从下半部分中得到的。
(select f.file# file_id,max(e.block# +e.length -1) hwm
from sys.uet$ e, sys.file$ f
where
e.ts# = f.ts#
and e.file# = f.relfile#
group by f.file#
)
union all
(
select f.file# file_id, max(e.ktfbuebno+e.ktfbueblks -1) hwm
from sys.x$ktfbue e, sys.file$ f
where
e.ktfbuefno = f.relfile#
group by f.file#
)
把这部分语句替换一下原来的，就成了如下的形式
col name for a40
col resizecmd for a80
select a.file#,a.name,a.bytes/1024/1024 CurrentMB,
ceil(HWM * a.block_size)/1024/1024 ResizeTo,
(a.bytes – HWM * a.block_size)/1024/1024 ReleaseMB,
‘alter database datafile ”’||a.name||”’ resize ‘||
ceil(HWM * a.block_size/1024/1024) || ‘M;’ ResizeCMD
from v$datafile a,
(select file_id,max(hwm) hwm from
(select f.file# file_id,max(e.block# +e.length -1) hwm
from sys.uet$ e, sys.file$ f
where
e.ts# = f.ts#
and e.file# = f.relfile#
group by f.file#
union all
select f.file# file_id, max(e.ktfbuebno+e.ktfbueblks -1) hwm
from sys.x$ktfbue e, sys.file$ f
where
e.ktfbuefno = f.relfile#
group by f.file#
)
group by file_id) b
where a.file# = b.file_id(+)
and (a.bytes – b.HWM *block_size)>0
order by 5
速度确实有所提升，然后自己想看看v$datafile如果替换成基表后，速度是不是也能提升。
v$datafile根据需要，得到的sql如下
(select
fe.fenum file#,
fh.fhfsz*fe.febsz bytes,
fe.febsz block_size,
fn.fnnam name
from
x$kccfe fe,
x$kccfn fn,
x$kcvfh fh
where ((fe.fepax!=65535 and fe.fepax!=0 ) or ((fe.fepax=65535 or fe.fepax=0) and fn.fnfno=fe.fenum ))
and fn.fnfno=fh.hxfil
and fe.fedup!=0
and fn.fntyp=4
and fn.fnnam is not null
and bitand(fn.fnflg,4) != 4
order by fe.fenum)
但是自己在实际测试的时候发现，效率还是很差，不光没有提高时间还可能增加了。
最后整理，最终采用的sql如下。
select /*+rule */ a.file#,a.name,a.bytes/1024/1024 CurrentMB,
ceil(HWM * a.block_size)/1024/1024 ResizeTo,
(a.bytes – HWM * a.block_size)/1024/1024 ReleaseMB,
‘alter database datafile ”’||a.name||”’ resize ‘||
ceil(HWM * a.block_size/1024/1024) || ‘M;’ ResizeCMD
from v$datafile a,
(select file_id,max(hwm) hwm from
(select f.file# file_id,max(e.block# +e.length -1) hwm
from sys.uet$ e, sys.file$ f
where
e.ts# = f.ts#
and e.file# = f.relfile#
group by f.file#
union all
select f.file# file_id, max(e.ktfbuebno+e.ktfbueblks -1) hwm
from sys.x$ktfbue e, sys.file$ f
where
e.ktfbuefno = f.relfile#
group by f.file#
)
group by file_id) b
where a.file# = b.file_id(+)
and (a.bytes – b.HWM *block_size)>0
order by 5
测试花费的时间如下
在不同规模的库上，时间也有所不同，在800G容量的库中，大概花费在2分钟之内。
Elapsed: 00:00:03.27
Elapsed: 00:00:28.39
Elapsed: 00:00:58.73
Elapsed: 00:01:51.55
Elapsed: 00:01:11.70