一、登陆数据库服务器，切换到informix用户
infodb% su - informix
Password:
Sun Microsystems Inc. SunOS 5.8 Generic Patch February 2004
You have new mail.
二、查看一下onstat工具的用法
onstat 应用程序的功能是读取共享内存的结构，提供执行该命令那一瞬间的统计数据。
在onstat 显示输出的过程中，共享内存的内容可能会发生变化。执行该操作不封锁共享内存，因此不会影响操作运行。
它是informix提供的一种交互式的系统监控工具。
infodb% onstat --
usage: onstat [ -abcdfghklmpstuxzBCDFRX ] [ -i ] [ -r [<seconds>] ]
[ -o [<outfile>] ] [ <infile> ]
-a Print all info
-b Print buffers
-c Print configuration file
-d [update]
Print spaces and chunks
update - Ask server to update BLOB chunk statistics
-f Print dataskip status
-g MT subcommand (see subcommands below)
-i Interactive mode
-h Print buffer hash chain info
-j Print interactive status of the active onpload process
-k Print locks
-l Print logging
-m Print message log
-p Print profile
-s Print latches
-t Print TBLspaces
-u Print user threads
-x Print transactions
-z Zero profile counts
-B Print all buffers
-C Print btree cleaner requests
-D Print spaces and detailed chunk stats
-F Print page flushers
-G Print global transaction ids
-P Print partition buffer summary
-R Print LRU queues
-T Print tablespace information
-X Print entire list of sharers and waiters for buffers
-r Repeat options every <seconds> seconds (default: 5)
-o Put shared memory into specified file (default: onstat.out)
<infile>
Read shared memory information from specified dump file
MT COMMANDS:
all Print all MT information
ath Print all threads
wai Print waiting threads
act Print active threads
rea Print ready threads
sle Print all sleeping threads
spi Print spin locks with long spins
sch Print VP scheduler statistics
lmx Print all locked mutexes
lsc Print Light Scan information
wmx Print all mutexes with waiters
con Print conditions with waiters
stk <tid>
Dump the stack of a specified thread
glo Print MT global information
mem [<pool name>|<session id>]
Print pool statistics.
seg Print memory segment statistics
rbm Print block map for resident segment
nbm Print block map for non-resident segments
afr <pool name|session id>
Print allocated pool fragments
ffr <pool name|session id>
Print free pool fragments
ufr <pool name|session id>
Print pool usage breakdown
iov Print disk IO statistics by vp
iof Print disk IO statistics by chunk/file
iog Print AIO global information
iob Print big buffer usage by IO VP class
ppf [<partition number> | 0]
Print partition profiles
tpf [<tid> | 0]
Print thread profiles
ntu Print net user thread profile information
ntt Print net user thread access times
ntm Print net message information
ntd Print net dispatch information
nss [<session id>]
Print net shared memory status
nsc [<client id>]
Print net shared memory status
nsd Print net shared memory data
sts Print max and current stack sizes
dic Print dictionary cache information
opn [<tid>]
Print open tables
qst Print queue statistics
wst Print thread wait statistics
rwm print Read/Write Mutex lists
ses [<session id>]
Print session information
sql [<session id>]
Print SQL information
stq [<session id>]
Print stream queue information
smb Print smart-large-object usage
dri Print data replication information
pos Print /INFORMIXDIR/etc/.infos.DBSERVERNAME file
mgm Print Memory Grant Manager information
lap Print light append information
ddr Print DDR log post processing information
env [ all | [<session-id>] ] [<variable-name>[,<variable-name>...]]
Display environment variable settings.
dmp <address> <length>
Dump <length> bytes of shared memory starting at <address>
src <pattern> <mask>
Search memory for <pattern>, where <pattern>==(memory&<mask>)
dll Print dynamic library statistics
ssc [pool|all]
Prints ssc pool summary, or statement cache summary and
entries, including key only entries (all)
stm [<sesson id>]
Prints all prepared statements approximate memory usage in a session
主要选项说明如下：
-a 打印所有信息
-b 显示正在使用的缓冲区
-B 显示所有的缓冲区（不仅是正在使用的）
-c 显示ONCONFIG 文件内容，数据库首先检测你是否定义了环境变量
ONCONFIG，若有则显示 $INFORMIX/etc/$ONCONFIG 文件的内容，若未定义，则显示$INFORMIX/etc/onconfig文件的内容，此文件包含了许多系统需要的定义和设置。
-C 显示B+树清除（B+ tree cleaner）情况
-d 显示每个数据空间（dbspace）的块（chunk）信息
-D 显示每个数据空间头50个块的页（page）读写的情况
-F 显示每一种类型的写回磁盘（flush pages to disk）的统计数
-h 显示哈西链（hash chain）缓冲区信息
-i 进入onstat的交互方式，可直接敲入onstat命令，使用CTRL-d 退出交互方式
-k 显示活动锁（active lock）的信息
-l 显示物理日志和逻辑日志的信息
-m 显示20个系统最近用到的系统信息日志
-o file 将当前共享内存段内容写入指定文件，缺省为 onstat.out
-p 显示各项统计数
-r seconds 按指定秒数定期执行本次onstat命令，缺省为5秒，可用CTRL-c或DEL 键中止
-R 显示 LRU队列，FLRU队列，MLRU队列的详细信息
-t 显示活动（active）的表空间（tblspace）信息
-u 显示用户情况
-x 显示事务（transaction）信息
-X 显示正在共享（sharing）和等待（waiting）缓冲区的线索（thread）的精确信息
-z 将所有的统计计数置0
三、查看数据库状态
正常情况下是
infodb% onstat -
IBM Informix Dynamic Server Version 9.40.FC7 -- On-Line -- Up 35 days 16:51:16 -- 3920896 Kbytes
长事务情况下是
infodb% onstat -
IBM Informix Dynamic Server Version 9.40.FC7 -- On-Line (LONGTX) -- Up 35 days 16:41:40 -- 3920896 Kbytes
Blocked:LONGTX
四、显示事务（transaction）信息
其中flag字段中第三个标志位为R说明事务在rollback，说明这个事务是长事务
infodb% onstat -x
IBM Informix Dynamic Server Version 9.40.FC7 -- On-Line (LONGTX) -- Up 35 days 16:41:56 -- 3920896 Kbytes
Blocked:LONGTX
Transactions
address flags userthread locks beginlg curlog logposit isol retrys coord
1cf0a6748 A-R-- 1cd55c618 642073 119403 119405 0x1aa91e4 DIRTY 0
1cf0a69b8 A---- 1d44f7ad0 1 0 0 0x0 COMMIT 0
278 active, 512 total, 447 maximum concurrent
五、通过长事务的userthread值找出session id
infodb% onstat -u |grep 1cd55c618
1cd55c618 --RPX-- 1880841 informix - 0 0 642073 256446 323049
六、显示会话连接信息，找出造成长事务的SQL语句，并优化
infodb% onstat -g ses 1880841
IBM Informix Dynamic Server Version 9.40.FC7 -- On-Line (LONGTX) -- Up 35 days 16:42:40 -- 3920896 Kbytes
Blocked:LONGTX
session #RSAM total used dynamic
id user tty pid hostname threads memory memory explain
1880841 informix - 14283 infodb 1 417792 409528 off
tid name rstcb flags curstk status
1990929 sqlexec 1cd55c618 --RPX-- 14095 sleeping(Forever)
Memory pools count 1
name class addr totalsize freesize #allocfrag #freefrag
1880841 V 1cadeb040 417792 8264 592 14
name free used name free used
overhead 0 3248 mtmisc 0 80
scb 0 144 opentable 0 38792
filetable 0 5776 ru 0 600
log 0 2184 temprec 0 10104
keys 0 824 ralloc 0 278856
gentcb 0 1592 ostcb 0 2872
sort 0 136 sqscb 0 33384
sql 0 72 rdahead 0 608
hashfiletab 0 552 osenv 0 3240
buft_buffer 0 5312 sqtcb 0 6696
fragman 0 14144 shmblklist 0 152
udr 0 160
sqscb info
scb sqscb optofc pdqpriority sqlstats optcompind directives
1cdf2d028 1caf31028 0 0 0 0 1
Sess SQL Current Iso Lock SQL ISAM F.E.
Id Stmt type Database Lvl Mode ERR ERR Vers Explain
1880841 INSERT datadb DR Not Wait 0 0 9.03 Off
Stored procedure stack :
context proc-counter opcode name
------------------------------------------------------------------
0x00000001cbbfde60 0x1cacfcde8+0x0010 SQL datadb:datatodb
0x00000001cbbfde60 0x1cacfcde8+0x0010 SQL datadb:datatodb
Current SQL statement in procedure datadb:datatodb
proc-counter 0x1cacfcde8 opcode SQL
insert into userdb:alarm_tbl(column_1,column_2......column_n)
select column_1,column_2....column_n
from table_b as b, table_a as a, outer(table_c as c)
where a.column1=b.column and a.column_1=c.column_1......;
Last parsed SQL statement :
execute procedure datatodb()
User-created Temp tables :
partnum tabname rowsize
14000c5 c_list 38
14000c3 aa_list 34
infodb% su - informix
Password:
Sun Microsystems Inc. SunOS 5.8 Generic Patch February 2004
You have new mail.
二、查看一下onstat工具的用法
onstat 应用程序的功能是读取共享内存的结构，提供执行该命令那一瞬间的统计数据。
在onstat 显示输出的过程中，共享内存的内容可能会发生变化。执行该操作不封锁共享内存，因此不会影响操作运行。
它是informix提供的一种交互式的系统监控工具。
infodb% onstat --
usage: onstat [ -abcdfghklmpstuxzBCDFRX ] [ -i ] [ -r [<seconds>] ]
[ -o [<outfile>] ] [ <infile> ]
-a Print all info
-b Print buffers
-c Print configuration file
-d [update]
Print spaces and chunks
update - Ask server to update BLOB chunk statistics
-f Print dataskip status
-g MT subcommand (see subcommands below)
-i Interactive mode
-h Print buffer hash chain info
-j Print interactive status of the active onpload process
-k Print locks
-l Print logging
-m Print message log
-p Print profile
-s Print latches
-t Print TBLspaces
-u Print user threads
-x Print transactions
-z Zero profile counts
-B Print all buffers
-C Print btree cleaner requests
-D Print spaces and detailed chunk stats
-F Print page flushers
-G Print global transaction ids
-P Print partition buffer summary
-R Print LRU queues
-T Print tablespace information
-X Print entire list of sharers and waiters for buffers
-r Repeat options every <seconds> seconds (default: 5)
-o Put shared memory into specified file (default: onstat.out)
<infile>
Read shared memory information from specified dump file
MT COMMANDS:
all Print all MT information
ath Print all threads
wai Print waiting threads
act Print active threads
rea Print ready threads
sle Print all sleeping threads
spi Print spin locks with long spins
sch Print VP scheduler statistics
lmx Print all locked mutexes
lsc Print Light Scan information
wmx Print all mutexes with waiters
con Print conditions with waiters
stk <tid>
Dump the stack of a specified thread
glo Print MT global information
mem [<pool name>|<session id>]
Print pool statistics.
seg Print memory segment statistics
rbm Print block map for resident segment
nbm Print block map for non-resident segments
afr <pool name|session id>
Print allocated pool fragments
ffr <pool name|session id>
Print free pool fragments
ufr <pool name|session id>
Print pool usage breakdown
iov Print disk IO statistics by vp
iof Print disk IO statistics by chunk/file
iog Print AIO global information
iob Print big buffer usage by IO VP class
ppf [<partition number> | 0]
Print partition profiles
tpf [<tid> | 0]
Print thread profiles
ntu Print net user thread profile information
ntt Print net user thread access times
ntm Print net message information
ntd Print net dispatch information
nss [<session id>]
Print net shared memory status
nsc [<client id>]
Print net shared memory status
nsd Print net shared memory data
sts Print max and current stack sizes
dic Print dictionary cache information
opn [<tid>]
Print open tables
qst Print queue statistics
wst Print thread wait statistics
rwm print Read/Write Mutex lists
ses [<session id>]
Print session information
sql [<session id>]
Print SQL information
stq [<session id>]
Print stream queue information
smb Print smart-large-object usage
dri Print data replication information
pos Print /INFORMIXDIR/etc/.infos.DBSERVERNAME file
mgm Print Memory Grant Manager information
lap Print light append information
ddr Print DDR log post processing information
env [ all | [<session-id>] ] [<variable-name>[,<variable-name>...]]
Display environment variable settings.
dmp <address> <length>
Dump <length> bytes of shared memory starting at <address>
src <pattern> <mask>
Search memory for <pattern>, where <pattern>==(memory&<mask>)
dll Print dynamic library statistics
ssc [pool|all]
Prints ssc pool summary, or statement cache summary and
entries, including key only entries (all)
stm [<sesson id>]
Prints all prepared statements approximate memory usage in a session
主要选项说明如下：
-a 打印所有信息
-b 显示正在使用的缓冲区
-B 显示所有的缓冲区（不仅是正在使用的）
-c 显示ONCONFIG 文件内容，数据库首先检测你是否定义了环境变量
ONCONFIG，若有则显示 $INFORMIX/etc/$ONCONFIG 文件的内容，若未定义，则显示$INFORMIX/etc/onconfig文件的内容，此文件包含了许多系统需要的定义和设置。
-C 显示B+树清除（B+ tree cleaner）情况
-d 显示每个数据空间（dbspace）的块（chunk）信息
-D 显示每个数据空间头50个块的页（page）读写的情况
-F 显示每一种类型的写回磁盘（flush pages to disk）的统计数
-h 显示哈西链（hash chain）缓冲区信息
-i 进入onstat的交互方式，可直接敲入onstat命令，使用CTRL-d 退出交互方式
-k 显示活动锁（active lock）的信息
-l 显示物理日志和逻辑日志的信息
-m 显示20个系统最近用到的系统信息日志
-o file 将当前共享内存段内容写入指定文件，缺省为 onstat.out
-p 显示各项统计数
-r seconds 按指定秒数定期执行本次onstat命令，缺省为5秒，可用CTRL-c或DEL 键中止
-R 显示 LRU队列，FLRU队列，MLRU队列的详细信息
-t 显示活动（active）的表空间（tblspace）信息
-u 显示用户情况
-x 显示事务（transaction）信息
-X 显示正在共享（sharing）和等待（waiting）缓冲区的线索（thread）的精确信息
-z 将所有的统计计数置0
三、查看数据库状态
正常情况下是
infodb% onstat -
IBM Informix Dynamic Server Version 9.40.FC7 -- On-Line -- Up 35 days 16:51:16 -- 3920896 Kbytes
长事务情况下是
infodb% onstat -
IBM Informix Dynamic Server Version 9.40.FC7 -- On-Line (LONGTX) -- Up 35 days 16:41:40 -- 3920896 Kbytes
Blocked:LONGTX
四、显示事务（transaction）信息
其中flag字段中第三个标志位为R说明事务在rollback，说明这个事务是长事务
infodb% onstat -x
IBM Informix Dynamic Server Version 9.40.FC7 -- On-Line (LONGTX) -- Up 35 days 16:41:56 -- 3920896 Kbytes
Blocked:LONGTX
Transactions
address flags userthread locks beginlg curlog logposit isol retrys coord
1cf0a6748 A-R-- 1cd55c618 642073 119403 119405 0x1aa91e4 DIRTY 0
1cf0a69b8 A---- 1d44f7ad0 1 0 0 0x0 COMMIT 0
278 active, 512 total, 447 maximum concurrent
五、通过长事务的userthread值找出session id
infodb% onstat -u |grep 1cd55c618
1cd55c618 --RPX-- 1880841 informix - 0 0 642073 256446 323049
六、显示会话连接信息，找出造成长事务的SQL语句，并优化
infodb% onstat -g ses 1880841
IBM Informix Dynamic Server Version 9.40.FC7 -- On-Line (LONGTX) -- Up 35 days 16:42:40 -- 3920896 Kbytes
Blocked:LONGTX
session #RSAM total used dynamic
id user tty pid hostname threads memory memory explain
1880841 informix - 14283 infodb 1 417792 409528 off
tid name rstcb flags curstk status
1990929 sqlexec 1cd55c618 --RPX-- 14095 sleeping(Forever)
Memory pools count 1
name class addr totalsize freesize #allocfrag #freefrag
1880841 V 1cadeb040 417792 8264 592 14
name free used name free used
overhead 0 3248 mtmisc 0 80
scb 0 144 opentable 0 38792
filetable 0 5776 ru 0 600
log 0 2184 temprec 0 10104
keys 0 824 ralloc 0 278856
gentcb 0 1592 ostcb 0 2872
sort 0 136 sqscb 0 33384
sql 0 72 rdahead 0 608
hashfiletab 0 552 osenv 0 3240
buft_buffer 0 5312 sqtcb 0 6696
fragman 0 14144 shmblklist 0 152
udr 0 160
sqscb info
scb sqscb optofc pdqpriority sqlstats optcompind directives
1cdf2d028 1caf31028 0 0 0 0 1
Sess SQL Current Iso Lock SQL ISAM F.E.
Id Stmt type Database Lvl Mode ERR ERR Vers Explain
1880841 INSERT datadb DR Not Wait 0 0 9.03 Off
Stored procedure stack :
context proc-counter opcode name
------------------------------------------------------------------
0x00000001cbbfde60 0x1cacfcde8+0x0010 SQL datadb:datatodb
0x00000001cbbfde60 0x1cacfcde8+0x0010 SQL datadb:datatodb
Current SQL statement in procedure datadb:datatodb
proc-counter 0x1cacfcde8 opcode SQL
insert into userdb:alarm_tbl(column_1,column_2......column_n)
select column_1,column_2....column_n
from table_b as b, table_a as a, outer(table_c as c)
where a.column1=b.column and a.column_1=c.column_1......;
Last parsed SQL statement :
execute procedure datatodb()
User-created Temp tables :
partnum tabname rowsize
14000c5 c_list 38
14000c3 aa_list 34