infodb% onstat -- usage: onstat [ -abcdfghklmpstuxzBCDFRX ] [ -i ] [ -r [] ] [ -o [] ] [ ] -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 (default: 5) -o Put shared memory into specified file (default: onstat.out) 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 Dump the stack of a specified thread glo Print MT global information mem [|] Print pool statistics. seg Print memory segment statistics rbm Print block map for resident segment nbm Print block map for non-resident segments afr Print allocated pool fragments ffr Print free pool fragments ufr 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 [ | 0] Print partition profiles tpf [ | 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 [] Print net shared memory status nsc [] Print net shared memory status nsd Print net shared memory data sts Print max and current stack sizes dic Print dictionary cache information opn [] Print open tables qst Print queue statistics wst Print thread wait statistics rwm print Read/Write Mutex lists ses [] Print session information sql [] Print SQL information stq [] 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 | [] ] [[,...]] Display environment variable settings. dmp Dump bytes of shared memory starting at src Search memory for , where ==(memory&) dll Print dynamic library statistics ssc [pool|all] Prints ssc pool summary, or statement cache summary and entries, including key only entries (all) stm [] Prints all prepared statements approximate memory usage in a session
六、显示会话连接信息，找出造成长事务的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......;
