常用的informxi sql(转载)

oncheck -pe   检查dbspaces extents 的情况 //oncheck -pe dbspaces_name |grep "onltranrechis"
oncheck -pT   database:tablname

1.检查锁竞争情况：
unload to lock.unl
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.tabname= "onltranrec"
and b.tabid >99
order by tabname;

2.检查表占用空间的大小 (4K/IBM,2K/HP)
unload to dbspacessuage.unl
select tabname,sum(pe_size)
from sysmaster:systabnames,sysmaster:sysptnext
where partnum=pe_partnum
and tabname='onltranrec'
group by tabname

3.检查索引使用情况：（剔除部分使用率比较低的索引）
unload to indexusage.unl
select dbsname, tabname, (isreads + pagreads) diskreads,
(iswrites + pagwrites) diskwrites
from sysmaster:sysptprof
where dbsname ="   "
and tabname matches "onltranrecidx*"
order by 3 desc, 4 desc

4.检查索引命中率不高的（需要做update statistics）
unload to idx_catch.unl
select tabname, idxname, nrows, nunique
from systables t, sysindexes i
where t.tabid =i.tabid and t.tabid > 99
and t.tabname="onltranrec"
and nrows > 0 and nunique > 0

5.检查索引B+ Tree 层次 （需要修改索引的fillfactor）
unload to btreelevel.unl
select a.idxname, a.levels ,b.tabname
from sysindexes a,systables b
where a.tabid=b.tabid
and tabname="onltranrec"
order by 2 desc

6.检查extent 分布
unload to extentdistribution.unl
select t.tabname,t.tabid, count(*) num_ext
from sysmaster:sysextents e, systables t
where e.tabname=t.tabname
and dbsname = 'xxxxx'
and t.tabid>99
group by 1,2
having count(*) > 1
order by 3,2 desc