--查询数据库实例基本运行情况的
SQL
dbaccess sysmaster
select
dbinfo('UTC_TO_DATETIME',sh_boottime)
start_time,
current year to second -
dbinfo('UTC_TO_DATETIME',sh_boottime) run_time,
sh_maxchunks as maxchunks,
sh_maxdbspaces maxdbspaces,
sh_maxuserthreads maxuserthreads,
sh_maxtrans maxtrans,
sh_maxlocks locks,
sh_nlrus buff_lrus,
sh_longtx longtxs,
dbinfo('UTC_TO_DATETIME',sh_pfclrtime)
onstat_z_running_time
from sysmaster:sysshmvals;
--查询数据库实例概要信息的
SQL
dbaccess sysmaster
select name, value
from sysmaster:sysprofile;
--查询 Session
的连接情况的 SQL
dbaccess sysmaster
SELECT s.sid, s.username, s.hostname,
q.odb_dbname database,
dbinfo('UTC_TO_DATETIME',s.connected)
conection_time,
dbinfo('UTC_TO_DATETIME',t.last_run_time)
last_run_time,
current-dbinfo('UTC_TO_DATETIME',t.last_run_time)
idle_time
FROM syssessions s, systcblst t, sysrstcb r,
sysopendb q
WHERE t.tid = r.tid AND s.sid = r.sid AND s.sid =
q.odb_sessionid
ORDER BY 7 DESC;
--查询 Session
等待事件的 SQL
dbaccess sysmaster
select sid,pid, username, hostname
is_wlatch, -- blocked waiting on a latch
is_wlock, -- blocked waiting on a locked record
or table
is_wbuff, -- blocked waiting on a buffer
is_wckpt, -- blocked waiting on a
checkpoint
is_incrit -- session is in a critical section of
transaction
from syssessions order by username;
--查询 Informix
正在执行的 SQL 语句的 SQL
dbaccess sysmaster
select
username,sqx_sessionid,
sqx_sqlstatement
from sysmaster:syssqexplain,
sysmaster:sysscblst
where sqx_sessionid = sid
--and sqx_sqlstatement like '%tabname%';
--查询数据库当前运行最慢
SQL 语句的 SQL
dbaccess sysmaster
select first 25 sqx_estcost,
sqx_estrows,
sqx_sqlstatement
from sysmaster:syssqexplain
where 1=1
order by sqx_estcost desc;
--打开 SQLTRACE 跟踪
SQL
echo 'execute function task ("set sql tracing
on",100000, "1k", "low","portalcms1");' | dbaccess sysadmin
--关闭 SQLTRACE
功能
echo ' execute function sysadmin:task("SET SQL
TRACING OFF"); ' | dbaccess sysadmin
--顺序扫描的
SQL
select distinct sql_statement
from sysmaster:Syssqltrace t
inner join sysmaster:syssqltrace_iter i
on t.sql_id = i.sql_id
where i.sql_itr_info='Seq Scan';
--查询速度慢 SQL
可以通过不同的指标进行排名
echo "select first 20 * from sysmaster:syssqltrace order by
sql_totaltime"| dbaccess demodb
--监控表使用锁的情况的
SQL
dbaccess sysmaster
select dbsname databanse, tabname,
sum(pf_rqlock) as locks,sum(pf_wtlock) as
lockwaits,
sum(pf_deadlk) as deadlocks
from sysactptnhdr,systabnames
where systabnames.partnum =
sysactptnhdr.partnum
--and pf_wtlock >=0 and pf_rqlock >=0
group by dbsname,tabname
order by lockwaits desc;
--监控锁等待情况的
SQL
dbaccess sysmaster
select dbsname databanse, tabname,
sum(pf_rqlock) as locks,sum(pf_wtlock) as
lockwaits,
sum(pf_deadlk) as deadlocks
from sysactptnhdr,systabnames
where systabnames.partnum =
sysactptnhdr.partnum
--and pf_wtlock >=0 and pf_rqlock >=0
group by dbsname,tabname
order by lockwaits desc;
-- 监控 DBSpace
空间使用情况的 SQL dbaccess
sysmaster
SELECT A.dbsnum as No, trim(B.name) as
name,
CASE WHEN
(bitval(B.flags,'0x10')>0 AND bitval(B.flags,'0x2')>0)
THEN
'MirroredBlobspace' WHEN
bitval(B.flags,'0x10')>0 THEN
'Blobspace' WHEN bitval(B.flags,'0x2000')>0 AND
bitval(B.flags,'0x8000')>0 THEN
'TempSbspace' WHEN bitval(B.flags,'0x2000')>0 THEN
'TempDbspace' WHEN (bitval(B.flags,'0x8000')>0 AND
bitval(B.flags,'0x2')>0) THEN
'MirroredSbspace' WHEN
bitval(B.flags,'0x8000')>0 THEN
'SmartBlobspace' WHEN
bitval(B.flags,'0x2')>0 THEN 'MirroredDbspace' ELSE 'Dbspace' END as
dbstype, CASE WHEN
bitval(B.flags,'0x4')>0 THEN
'Disabled'
WHEN bitand(B.flags,3584)>0 THEN 'Recovering' ELSE 'Operational' END as dbsstatus,
format_units(sum(chksize),max(A.pagesize)) as
DBS_SIZE ,
format_units(sum(decode(mdsize,-1,nfree,udfree)),max(A.pagesize)) as free_size,
TRUNC(100-sum(decode(mdsize,-1,nfree,udfree))*100/sum(chksize),2)||'%'
as used, TRUNC(MAX(A.pagesize/1024)) as pgsize,
MAX(B.nchunks) as nchunks
FROM syschktab A, sysdbstab
B WHERE A.dbsnum =
B.dbsnum GROUP BY A.dbsnum,name, 3,
4 ORDER BY A.dbsnum;
--监控 Chunk I/O
情况的 SQL
dbaccess sysmaster
select d.name dbspace, fname[1,125]
chunk_name,
reads read_count,
writes write_count,
reads+writes total_count,
pagesread,
pageswritten,
pagesread+pageswritten total_pg
from sysmaster:syschkio c, sysmaster:syschunks k,
sysmaster:sysdbspaces d
where d.dbsnum = k.dbsnum
and k.chknum =
c.chunknum --# c.chknum
order by 8 desc;
--监控临时表空间使用情况况的
SQL
dbaccess sysmaster
select trim(n.dbsname) tab_type,
trim(n.owner) users,trim(n.tabname)
tab_name,
dbinfo('UTC_TO_DATETIME',i.ti_created)
index_createtime,
trim(dbinfo('DBSPACE', i.ti_partnum))
dbspace,
format_units(i.ti_nptotal,i.ti_pagesize)
total_size,i.ti_nrows
FROM sysmaster:systabnames n,
sysmaster:systabinfo i
WHERE (sysmaster:bitval(i.ti_flags, 32) = 1
OR sysmaster:bitval(i.ti_flags, 64) = 1
OR sysmaster:bitval(i.ti_flags, 128) = 1)
AND i.ti_partnum = n.partnum
order by 1,3;
--查询表使用空间情况的
SQL
dbaccess sysmaster
--A 含分片
select st.dbsname databasename,st.tabname,sd.name
dbs_name,
ti_nextns extents,
sin.ti_nrows,sin.ti_pagesize, sin.ti_rowsize,
sin.ti_nptotal nptotal,
format_units(sin.ti_nptotal,sd.pagesize) total_size,
sin.ti_npused npused,
format_units(sin.ti_npused,sd.pagesize) used_size,
sin.ti_nextsiz nextsize
from sysmaster:systabnames st,
sysmaster:sysdbspaces sd,
sysmaster:systabinfo sin,demodb:systables
dt
where sd.dbsnum = trunc(st.partnum/1048576)
and dt.tabid>99 and
dt.tabname=st.tabname
and st.partnum=sin.ti_partnum
and st.dbsname='demodb'
--and sd.name= ’ demodbs ’
order by 10 desc;
--B 总和
select st.dbsname databasename,st.tabname,
sum(ti_nextns) extents,
sum(sin.ti_nrows) nrows,max(sin.ti_pagesize)
pagesize, sum(sin.ti_nptotal) nptotal,
format_units(sum(sin.ti_nptotal),max(sd.pagesize))
total_size,
sum(sin.ti_npused) npused,
format_units(sum(sin.ti_npused),max(sd.pagesize)) used_size
from sysmaster:systabnames st,
sysmaster:sysdbspaces sd,
sysmaster:systabinfo sin,demodb:systables
dt
where sd.dbsnum = trunc(st.partnum/1048576) and
dt.tabid>99
and dt.tabname=st.tabname and
st.partnum=sin.ti_partnum and st.dbsname='demodb'
group by 1,2
order by 8 desc;
--查询表 I/O 情况的
SQL
dbaccess sysmaster
SELECT p.tabname, sum(sin.ti_nrows) nrows,
format_units(sum(sin.ti_nptotal),max(sd.pagesize))
total_size,
format_units(sum(sin.ti_npused),max(sd.pagesize))
used_size,
sum(seqscans) as seqscans , sum( pagreads) diskreads,
sum(bufreads) bufreads, sum( bufwrites)
bufwrites,
sum( pagwrites) diskwrites,sum( pagreads)+ sum(
pagwrites) disk_rsws ,
trunc(decode(sum(bufreads),0,0,
(100-((sum(pagreads)*100)/sum(bufreads+pagreads)))),2) rbufhits
,
trunc(decode(sum(bufwrites),0,0,
(100-((sum(pagwrites)*100)/sum(bufwrites+pagwrites)))),2)
wbufhits
from demodb:systables s , sysmaster:sysptprof p
,
sysmaster:systabinfo sin, sysmaster:sysdbspaces sd,sysmaster:systabnames st
where s.tabid>99
and s.tabname = p.tabname and
p.dbsname=st.dbsname
and sd.dbsnum = trunc(st.partnum/1048576)
and p.partnum=st.partnum and
s.tabname=st.tabname
and st.partnum=sin.ti_partnum and st.dbsname='demodb'
group by 1 order by 10
desc;
--查询索引创建时间的
SQL
dbaccess sysmaster
select
i.owner,st.dbsname,t.tabname,i.idxname,
dbinfo('UTC_TO_DATETIME',ti.ti_created)
index_createtime
from demodb:systables t, demodb:sysindexes i
,
sysmaster:systabinfo ti,sysmaster:systabnames
st
where t.tabid=i.tabid
and t.tabid>99
and st.partnum = ti.ti_partnum
and i.idxname = st.tabname
-- and t.tabid=102
-- and t.tabname='tabname'
--and
dbinfo('UTC_TO_DATETIME',ti.ti_created)>='2010-11-03
08:00:00'
and st.dbsname='demodb'
order by t.tabname;
--查询索引空间使用情况的
SQL dbaccess sysmaster
--A 含分片
select st.dbsname
databasename,dt.tabname,di.idxname,sd.name dbs_name,
di.levels,sin.ti_nextns
extents, sin.ti_nptotal nptotal,
format_units(sin.ti_nptotal,sd.pagesize) total_size,
sin.ti_npused npused,
format_units(sin.ti_npused,sd.pagesize) used_size
from sysmaster:systabnames st,
sysmaster:sysdbspaces sd,sysmaster:systabinfo sin,
demodb:sysindexes di,demodb:systables dt
where sd.dbsnum = trunc(st.partnum/1048576)
and dt.tabid>99 and di.idxname =
st.tabname
and dt.tabid=di.tabid and
st.partnum=sin.ti_partnum
and st.dbsname='demodb' order
by 2,1,3;
--B 总和
select st.dbsname
databasename,dt.tabname,di.idxname ,
max(di.levels) levels,max(sin.ti_nextns)
extents, sum(sin.ti_nptotal) nptotal,
format_units(sum(sin.ti_nptotal),
max(sd.pagesize)) total_size,
sum(sin.ti_npused) npused,
format_units(sum(sin.ti_npused),
max(sd.pagesize)) used_size
from sysmaster:systabnames st,
sysmaster:sysdbspaces sd,sysmaster:systabinfo sin,
demodb:sysindexes di,demodb:systables dt
where sd.dbsnum = trunc(st.partnum/1048576)
and dt.tabid>99 and di.idxname =
st.tabname
and dt.tabid=di.tabid and
st.partnum=sin.ti_partnum
and st.dbsname='demodb'
group by 1,2,3 order by 8 desc;
--查询索引 I/O 情况的
SQL
dbaccess sysmaster
select
st.dbsname
databasename,dt.tabname,di.idxname,sd.name dbs_name,
di.levels,sin.ti_nextns
extents, sin.ti_nptotal nptotal,
format_units(sin.ti_nptotal,sd.pagesize) total_size,
sin.ti_npused npused,
format_units(sin.ti_npused,sd.pagesize) used_size,
pagreads diskreads,
bufreads bufreads, bufwrites bufwrites,
pagwrites diskwrites,pagreads
+ pagwrites disk_rsws
from sysmaster:systabnames st,
sysmaster:sysdbspaces sd,sysmaster:systabinfo sin,
demodb:sysindexes di,demodb:systables
dt,sysmaster:sysptprof p
where sd.dbsnum = trunc(st.partnum/1048576)
and dt.tabid>99
and di.idxname = st.tabname
and dt.tabid=di.tabid
and st.partnum=sin.ti_partnum
and st.dbsname='demodb' and p.partnum=st.partnum
order by 2,1,3;