关于数据字典的查询效率优化

最近因为测试需要对一些数据文件做压缩，腾出更多的空间来为其他环境做准备，压缩数据文件，
最开始采用了如下的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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23718752/viewspace-1137336/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23718752/viewspace-1137336/