關於資料字典的查詢效率優化
最近因為測試需要對一些資料檔案做壓縮,騰出更多的空間來為其他環境做準備,壓縮資料檔案,
最開始採用瞭如下的sql
最開始採用瞭如下的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,資料基本都是從下半部分中得到的。
基本思路還是根據高水位線來做判斷資料檔案中有多少空閒塊。
但是這個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如果替換成基表後,速度是不是也能提升。
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如下。
測試花費的時間如下
在不同規模的庫上,時間也有所不同,在800G容量的庫中,大概花費在2分鐘之內。
但是自己在實際測試的時候發現,效率還是很差,不光沒有提高時間還可能增加了。
最後整理,最終採用的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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 關於資料字典的查詢效率最佳化
- 優化mysql資料字典表查詢優化MySql
- 關於dataguard需要查詢的資料字典
- 關於分頁查詢的優化思路優化
- 關於樹結構的查詢優化,及許可權樹的查詢優化優化
- 資料庫查詢優化資料庫優化
- MySQL 優化五(關聯查詢子查詢以及 in 的效率問題)(高階篇)MySql優化
- 查詢所有資料字典的SQLSQL
- 資料庫查詢優化:巢狀查詢資料庫優化巢狀
- 記一次關於Laravel model查詢返回大量資料的效能優化Laravel優化
- mysql update join優化update in查詢效率MySql優化
- 【資料庫】MySQL查詢優化資料庫MySql優化
- 資料庫查詢優化:使用explain分析sql語句執行效率資料庫優化AISQL
- 【資料庫】查詢優化之子連線優化資料庫優化
- mysql關聯查詢優化MySql優化
- Oracle:優化方法總結(關於連表查詢)Oracle優化
- 資料庫全表查詢之-分頁查詢優化資料庫優化
- Oracle 資料字典及註釋查詢Oracle
- 關於Oracle資料庫的時間查詢Oracle資料庫
- 優化SQL Server資料庫查詢方法優化SQLServer資料庫
- 資料庫的優化和效率資料庫優化
- 優化資料庫EXP的效率優化資料庫
- Mysql表關聯欄位未建索引導致查詢慢,優化後查詢效率顯著提升MySql索引優化
- (轉)Oracle常用資料字典查詢語句Oracle
- 關聯查詢子查詢效率簡單比照
- 關於資料庫查詢業務的幾點思考資料庫
- 關於MySQL 查詢表資料大小的總結MySql
- 關於資料庫批次查詢的一個想法資料庫
- 改進資料庫效能-SQL查詢優化資料庫SQL優化
- 【效能優化】Oracle 效能優化:降低列值聚簇因子 提高查詢效率優化Oracle
- 查詢優化優化
- 資料庫設計與查詢語句的優化資料庫優化
- SQL Server資料庫查詢優化的方法總結SQLServer資料庫優化
- Oracle查詢優化器的相關引數Oracle優化
- MySQL 的查詢優化MySql優化
- Python—Django:關於在Django框架中對資料庫的查詢函式,查詢集和關聯查詢PythonDjango框架資料庫函式
- 關於SQL Server資料查詢基本方法的總結SQLServer
- 關於下拉選單查詢資料庫的問題資料庫