--查询数据库实例基本运行情况的 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;