為了提高日常維護IDS的效率，特提供以下腳本供大家參考。檢查各個dbspaces的空間使用狀況：
select name dbspace,sum(chksize) allocated,sum(nfree) free,
round(((sum(chksize)-sum(nfree))/sum(chksize))*100)||"%" pcused
form sysdbspaces d,syschunks c
where d.dbsnum=c.dbsnum group by name order by 4 desc
剩余空間肯定是越大越好了
顯示各個dbspaces的I/O狀況：
select d.name,fname path_name,sum(pagesread) diskreads,sum(pageswritten) diskwrites
from syschkio c,syschunks k,sysdbspaces d
where d.dbsnum=k.dbsnum and k.chknum=c.chunknum
group by 1,2 order by 3 desc
根據(jù)各個dbspaces的I/O來調整表的布局，使整個系統(tǒng)I/O均衡
檢查哪個表具有最多的磁盤I/0：
select dbsname, tabname, (isreads + pagreads) diskreads, (iswrites + pagwrites) diskwrites
from sysptprof
order by 3 desc,4 desc
同上。
檢查表的extent的分布狀況：
select t.tabname, count(*) num_ext
from sysextents e, npmdb:systables t
where e.tabname=t.tabname
and dbsname = "npmdb"
and t.tabname not like "sys%"
group by 1
having count(*) > 1
order by 2 desc
表的extent建議不超過30個，如果太大，就需要重建表修改extent size的大小從而修改extent的數(shù)量
檢查表中索引的層數(shù)（越少越好）：
select idxname, levels from sysindexes order by 2 desc
檢查命中率不高的索引(nrows和unique越接近越好）：
select tabname, idxname, nrows, nunique
from systables t, sysindexes I
where t.tabid =i.tabid and t.tabid > 99
and nrows > 0 and nunique > 0
當索引的效率不高的時候，需要根據(jù)實際情況修改
看數(shù)據(jù)庫里面那些表的碎片比較多（碎片小比較好）
select dbsname , tabname ,count(*), sum(size)
from sysextents
group by 1,2
order by 3 desc;
表和索引的讀寫情況，(考查那個數(shù)據(jù)庫實體讀寫比較多)
select dbsname, tabname, (isreads + pagreads) diskreads, (iswrites + pagwrites)
diskwrites
from sysptprof
order by 3 desc, 4 desc
那些表的鎖競爭比較厲害(越小越好)
select a.tabname,nrows,lockwts,deadlks
from sysmaster:sysptprof a,systables b
where a.tabname=b.tabname and lockwts>0
and a.dbsname = 庫名
and b.tabid >= 100
order by tabname;
表的順序掃描數(shù)（OLTP系統(tǒng)的話，大表的順序掃描數(shù)越小越好）
select a.tabname,nrows,seqscans
from sysmaster:sysptprof a,systables b
where a.tabname=b.tabname and seqscans>0
and a.dbsname = '庫名'
and b.tabid>=100
order by tabname;
select name dbspace,sum(chksize) allocated,sum(nfree) free,
round(((sum(chksize)-sum(nfree))/sum(chksize))*100)||"%" pcused
form sysdbspaces d,syschunks c
where d.dbsnum=c.dbsnum group by name order by 4 desc
剩余空間肯定是越大越好了
顯示各個dbspaces的I/O狀況：
select d.name,fname path_name,sum(pagesread) diskreads,sum(pageswritten) diskwrites
from syschkio c,syschunks k,sysdbspaces d
where d.dbsnum=k.dbsnum and k.chknum=c.chunknum
group by 1,2 order by 3 desc
根據(jù)各個dbspaces的I/O來調整表的布局，使整個系統(tǒng)I/O均衡
檢查哪個表具有最多的磁盤I/0：
select dbsname, tabname, (isreads + pagreads) diskreads, (iswrites + pagwrites) diskwrites
from sysptprof
order by 3 desc,4 desc
同上。
檢查表的extent的分布狀況：
select t.tabname, count(*) num_ext
from sysextents e, npmdb:systables t
where e.tabname=t.tabname
and dbsname = "npmdb"
and t.tabname not like "sys%"
group by 1
having count(*) > 1
order by 2 desc
表的extent建議不超過30個，如果太大，就需要重建表修改extent size的大小從而修改extent的數(shù)量
檢查表中索引的層數(shù)（越少越好）：
select idxname, levels from sysindexes order by 2 desc
檢查命中率不高的索引(nrows和unique越接近越好）：
select tabname, idxname, nrows, nunique
from systables t, sysindexes I
where t.tabid =i.tabid and t.tabid > 99
and nrows > 0 and nunique > 0
當索引的效率不高的時候，需要根據(jù)實際情況修改
看數(shù)據(jù)庫里面那些表的碎片比較多（碎片小比較好）
select dbsname , tabname ,count(*), sum(size)
from sysextents
group by 1,2
order by 3 desc;
表和索引的讀寫情況，(考查那個數(shù)據(jù)庫實體讀寫比較多)
select dbsname, tabname, (isreads + pagreads) diskreads, (iswrites + pagwrites)
diskwrites
from sysptprof
order by 3 desc, 4 desc
那些表的鎖競爭比較厲害(越小越好)
select a.tabname,nrows,lockwts,deadlks
from sysmaster:sysptprof a,systables b
where a.tabname=b.tabname and lockwts>0
and a.dbsname = 庫名
and b.tabid >= 100
order by tabname;
表的順序掃描數(shù)（OLTP系統(tǒng)的話，大表的順序掃描數(shù)越小越好）
select a.tabname,nrows,seqscans
from sysmaster:sysptprof a,systables b
where a.tabname=b.tabname and seqscans>0
and a.dbsname = '庫名'
and b.tabid>=100
order by tabname;