[Oracle]ASMディレクトリ毎の使用量/使用率/ファイル数を確認するSQL
ASMディレクトリ毎の使用サイズ、使用率、およびファイル数を確認するSQLです。
asmcmd lsdgコマンドでディスクグループの使用量を確認することができますが、もうちょっと踏み込んで調査したい、どのDBで何のファイルが容量を食っているのか調べたいといったときに使えます。
ASMディレクトリ毎の使用サイズ/使用率/ファイル数を確認するSQL
SQL:
※ASMインスタンスに接続して実行します。
set linesize window
set pagesize 100
col file_type format a20
col path format a100
col "USED%" for 990.9
define dirlv=2
select
file_type,
count(*) files,
round(sum(space)/1024/1024) mbytes,
round(sum(space)/1024/1024/dgmsize*100, 1) "USED%",
path
from
( select
concat('+'||gname, replace(sys_connect_by_path(case when level < &dirlv then '/'||aname else '' end, '|'), '|', null)) path,
file_type,
space,
gname,
dgmsize
from
( select b.name gname, a.name aname,
a.reference_index rindex, a.parent_index pindex,
c.type file_type, c.space,
a.system_created, a.alias_directory,
b.total_mb dgmsize
from v$asm_alias a, v$asm_diskgroup b, v$asm_file c
where a.group_number = b.group_number
and a.group_number = c.group_number(+)
and a.file_number = c.file_number(+)
and a.file_incarnation = c.incarnation(+)
)
where
connect_by_isleaf = 1 and alias_directory = 'N' -- 経路ディレクトリと空ディレクトリを省く
and not (system_created = 'N' and alias_directory = 'N') -- エイリアスファイルを省く
start with (mod(pindex, power(2, 24))) = 0
connect by prior rindex = pindex
)
group by file_type, path, gname, dgmsize
order by gname, mbytes desc
;
実行例:
ASMディレクトリに含まれるファイル数、合計サイズ、および使用率がファイルタイプ毎に表示されます。
下記例ではディレクトリ2階層目で集計しています。この値は置換変数(ここではdirlvとして定義)の数値で調整できます。ちなみにこの値を大きくすることでASMのファイル一覧の取得SQLとしても使用することができます。
使用率はそれぞれのディスクグループ最大サイズに対するパーセンテージです。
old 9: concat('+'||gname, replace(sys_connect_by_path(case when level < &dirlv then '/'||aname else '' end, '|'), '|', null)) path,
new 9: concat('+'||gname, replace(sys_connect_by_path(case when level < 2 then '/'||aname else '' end, '|'), '|', null)) path,
FILE_TYPE FILES MBYTES USED% PATH
-------------------- ---------- ---------- ------ ----------------------------------------------------------------------------------------------------
ONLINELOG 4 816 8.0 +DATA/HANPEN
DATAFILE 3 696 6.8 +DATA/HANPEN
TEMPFILE 2 608 5.9 +DATA/HANPEN
OCRFILE 1 160 1.6 +DATA/rac-x
OCRBACKUP 16 64 0.6 +DATA/rac-x
CONTROLFILE 1 32 0.3 +DATA/HANPEN
ASMPARAMETERFILE 1 4 0.0 +DATA/rac-x
PARAMETERFILE 1 4 0.0 +DATA/HANPEN
PASSWORD 2 0 0.0 +DATA/ASM
PASSWORD 1 0 0.0 +DATA/HANPEN
PASSWORD 1 0 0.0 +DATA/SPARKING
DATAFILE 16 14724 28.8 +FLASH/HANPEN
TEMPFILE 2 5028 9.8 +FLASH/HANPEN
ARCHIVELOG 16 2868 5.6 +FLASH/HANPEN
AUTOBACKUP 17 340 0.7 +FLASH/HANPEN
AUTOBACKUP 2 40 0.1 +FLASH/SPARKING
ARCHIVELOG 1 16 0.0 +FLASH/SPARKING
17 rows selected.
SQL>
クエリの説明
ファイル名の情報をもつV$ASM_ALIAS、サイズ情報を持つV$ASM_FILES、ディスクグループ情報をもつV$ASM_SISKGROUP、これらのビューから作ったファイル一覧をディレクトリ毎に集計しています。
V$ASM_ALIASが持つASMファイル名はdba_data_filesのようにファイルのフルパス情報がそのまま格納されているわけではなく、ファイル名やディレクトリ名が木構造データとして格納されています。そのため階層問い合わせ(start with、connect by句) を使用してフルパスに組み立てています。
- 木の根(rootディレクトリ)を指定するstart with句
start with (mod(pindex, power(2, 24))) = 0
- レコード間の親子関係を紐づけるconnect by句 (prior接頭辞は 親のレコードを表す 親rindex ＝ 子pindex が一致するレコードを検索するという意)
connect by prior rindex = pindex
V$ASM_ALIASは自身のID：reference_indexと自身の親となるファイル(ディレクトリ)のID：parent_indexを持っており、これがディレクトリ階層の親子関係をひもづけるキーになるのですが、このIDは上位8bitがディスクグループ番号、下位24bitがファイルIDの計32bitのIDとなっており、トップディレクトリ(このビューにはディスクグループが含まれていないのでディスクグループ直下のディレクトリ）が持つ親ID＝ディスクグループのIDは下位24bitのファイルIDが0となるため、(mod(pindex, power(2, 24))) = 0 で判定でき、そこから問い合わせをスタートして末端の枝葉まで再帰的にたどることができます。
ディレクトリ毎に集計するため、階層問い合わせのlevel疑似列を使用し任意のディレクトリ階層でパスの連結を打ち切っています。
concat('+'||gname, replace(sys_connect_by_path(case when level < &dirlv then '/'||aname else '' end, '|'), '|', null)) path,
階層問い合わせで木の根からたどった経路を連結するsys_connect_patch 関数では、第2引数に連結時の区切り文字を指定する必要があります。パスを打ち切る際に不都合だったのですが、このパラメータは省略やnullを指定することができないため、いったん区切り文字として|を指定しreplace関数で後から除外しています。区切り文字は「パスに含まれている文字を使用できない」という制約があるためASMファイルで使用できない文字なら何でもよいかと思います。
エイリアスファイルを混ぜるとファイルサイズが実ファイルとダブルカウントされてしまうため、エイリアスファイルは検索対象から除外しています。
not (system_created = 'N' and alias_directory = 'N') -- エイリアスファイルを省く
エイリアスファイルは必ずsystem_created = ‘N’となるため上記判定で除外していますが、エイリアスファイルはファイル名で判定することができるようなので下記のフィルターでも同じ結果を取得できます。
regexp_like(aname, '.+\.[[:digit:]]+\.[[:digit:]]+$')
エイリアスOracle ASMファイル名はドット区切りの数値ペアで終わっていないため、完全修飾ファイル名や数値ファイル名とは区別されます。USERS.259.685366091形式など、ドット区切りの数値ペアで終わるエイリアスを作成しようとすると、エラーが発生します。
Oracle Automatic Storage Management管理者ガイド19c より
ファイルサイズ情報を持つV$ASM_FILEにはbytesとspaceの2つのカラムがあります。bytesは実際のファイル使用量、spaceは ファイルに割り当てられたASMのAUサイズ(DBのブロックサイズみたいなもの)という違いです。またspaceはディスグループのミラー化が考慮されたサイズになります。(1GBの表領域の場合、bytesの場合はそのまま1GB、spaceはミラー度合に応じてNORMALの場合は2GB、HIGHの場合は3GBになります。)。ASMの領域管理という点ではspaceを使用するのが適切かと思いこちらを使用しています。PASSWORDファイルは0になってしまうようのですが(理由は知りません)、微々たるサイズなので考慮してません。
参考：再帰WITH句での書き換え
Oracleの階層問い合わせは再帰WITH句で書き換えることができます。せっかくなので書き換えてみました。下記クエリで同じ結果を取得できます。と思ったのですが残念なことに下記のクエリは使用できませんでした。というのもASMはただのインスタンスであるため、WITH区で内部的に使用される一時表が作成できないためです。ということで参考までに。v$asm各表を db側にコピーすればもちろん使用できます。テーブルコピー手順はこちら
SQL:
再帰WITHは真ん中のハイライト部分のみです。他２つはインラインビューを書き換えた非再帰WITHです。
set linesize window
set pagesize 100
col file_type format a20
col path format a100
col "USED%" for 990.9
define dirlv=2
with
origin as
( select b.name gname, a.name aname,
a.reference_index rindex, a.parent_index pindex,
a.system_created, a.alias_directory,
c.type file_type, c.space, b.total_mb dgmsize
from v$asm_alias a, v$asm_diskgroup b, v$asm_file c
where a.group_number = b.group_number
and a.group_number = c.group_number(+)
and a.file_number = c.file_number(+)
and a.file_incarnation = c.incarnation(+)
),
saiki(gname, aname, rindex, pindex, system_created, alias_directory, file_type, space, dgmsize, lv, path) as
( select
gname, aname, rindex, pindex, system_created, alias_directory,
file_type, space, dgmsize, 1, concat('+'||gname, '/'||to_char(aname))
from origin
where (mod(pindex, power(2, 24))) = 0
union all
select b.gname, b.aname, b.rindex, b.pindex, b.system_created, b.alias_directory,
b.file_type, b.space, b.dgmsize, a.lv+1, a.path || '/' || to_char(b.aname)
from saiki a, origin b
where a.rindex = b.pindex
),
dasoku as (
select
file_type, space, gname, dgmsize,
substr(path,1, replace(instr(path, '/', 1, &dirlv)-1, '-1', length(path))) path
from saiki c
where not exists (select 1 from saiki d where c.rindex = d.pindex) -- 途中経路ディレクトリを省く(子レコードがないレコードに絞っている)
and alias_directory = 'N' -- (空)ディレクトリは省く
and not ( system_created = 'N' and alias_directory = 'N') -- エイリアスを省く
)
select
file_type,
count(*) files,
round(sum(space)/1024/1024) mbytes,
round(sum(space)/1024/1024/dgmsize*100, 1) "USED%",
path
from dasoku
group by file_type, path, gname, dgmsize
order by gname, mbytes desc
;
おまけ①エイリアス一覧の出力
エイリアスファイルの一覧を取得するＳＱＬです。
SQL:
set linesize window
set pagesize 100
col file_type format a20
col alias_path format a50
col path format a60
select
file_type,
max(alias_path) alias_path,
max(path) path
from
( select
case
when regexp_count(aname, '.+\.[[:digit:]]+\.[[:digit:]]+$', 1, 'i') > 0
then null
else concat('+'||gname, sys_connect_by_path(aname, '/'))
end alias_path,
case
when regexp_count(aname, '.+\.[[:digit:]]+\.[[:digit:]]+$', 1, 'i') > 0
then concat('+'||gname, sys_connect_by_path(aname, '/'))
else null
end path,
file_type, group_number, file_number, incarnation
from
( select b.name gname, a.name aname,
a.reference_index rindex, a.parent_index pindex,
a.system_created, a.alias_directory,
c.type file_type, a.group_number,
a.file_number, c.incarnation
from v$asm_alias a, v$asm_diskgroup b, v$asm_file c
where a.group_number = b.group_number
and a.group_number = c.group_number(+)
and a.file_number = c.file_number(+)
and a.file_incarnation = c.incarnation(+)
) d
where exists ( select 1 from v$asm_alias
where file_number = d.file_number
and group_number = d.group_number
and incarnation = d.incarnation
and system_created = 'N'
and alias_directory = 'N'
)
start with (mod(pindex, power(2, 24))) = 0
connect by prior rindex = pindex
)
group by group_number, file_number, incarnation, file_type
order by file_type, alias_path
;
実行例:
FILE_TYPE ALIAS_PATH PATH
-------------------- -------------------------------------------------- ------------------------------------------------------------
ASMPARAMETERFILE +DATA/asmspfile.ora +DATA/rac-x/ASMPARAMETERFILE/REGISTRY.253.1041586293
DATAFILE +FLASH/HANPEN/DATAFILE/test/testtbs3.dbf +FLASH/HANPEN/DATAFILE/TESTTBS3.350.1094495411
DATAFILE +FLASH/HANPEN/DATAFILE/test/testtbs4.dbf +FLASH/HANPEN/DATAFILE/TESTTBS4.384.1094495417
DATAFILE +FLASH/HANPEN/DATAFILE/test/testtbs5.dbf +FLASH/HANPEN/DATAFILE/TESTTBS5.445.1094495511
DATAFILE +FLASH/HANPEN/DATAFILE/test/testtbs6.dbf +FLASH/HANPEN/DATAFILE/TESTTBS6.416.1094495525
DATAFILE +FLASH/HANPEN/DATAFILE/testtbs.dbf +FLASH/HANPEN/DATAFILE/TESTTBS.300.1094492329
PASSWORD +DATA/orapwasm +DATA/ASM/PASSWORD/pwdasm.256.1041585795
PASSWORD +DATA/orapwasm_backup +DATA/ASM/PASSWORD/pwdasm.257.1041585859
8 rows selected.
SQL>
おまけ②空ディレクトリ一覧の出力
空ディレクトリの一覧を取得するＳＱＬです。(ASMディレクトリの使用率を確認するSQLで検索から省いているやつ)。 システムで生成されたASMディレクトリは空になると自動で削除されるのでここで出力される空ディレクトリは手動で作ったものという事になります。
SQL:
set linesize window
set pagesize 100
col path format a100
select
concat('+'||gname, sys_connect_by_path(aname, '/')) path
from
( select b.name gname, a.name aname,
a.reference_index rindex, a.parent_index pindex,
a.system_created, a.alias_directory
from v$asm_alias a, v$asm_diskgroup b, v$asm_file c
where a.group_number = b.group_number
and a.group_number = c.group_number(+)
and a.file_number = c.file_number(+)
and a.file_incarnation = c.incarnation(+)
)
where connect_by_isleaf=1 and alias_directory = 'Y'
start with (mod(pindex, power(2, 24))) = 0
connect by prior rindex = pindex
order by path
;
実行例:
PATH
----------------------------------------------------------------------------------------------------
+DATA/HANPEN/TEMPFILE/tes
SQL>
参考サイト
以下のサイトで勉強させていただきました。
ASMファイルパス取得
http://www.dba-oracle.com/t_asm_files_display_script.htm
階層問合せ
https://blogs.oracle.com/otnjp/post/oracle-sql-image-006
再帰WITH
https://blogs.oracle.com/otnjp/post/oracle-sql-image-007
1件の返信
[…] こちらの記事の検証で実施したときのメモ。クエリ確認用のため、見かけ上のコピー。(X$表やら付随するオブジェクトなどは考慮していない) […]