informix linux 速度,Informix常用性能分析SQL

--查询数据库实例基本运行情况的

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;