Home | Previous Page | Next Page   Monitoring Database Server Performance > Monitoring Sessions and Queries >

Monitoring Current Session Activity and Resource Use

For information about using monitoring information to tune sessions, see Managing Resource Use for Specific Sessions.

Use ISA or the various onstat options to determine how queries and transactions are executing in your database server. ISA and onstat information provides only a snapshot of the current state of the system. However, you can execute any onstat command with the -r option to repeat the command at specified intervals. The following sections illustrate the most commonly used onstat options with examples.

For complete information about onstat options, see the IBM Informix: Extended Parallel Server Administrator's Reference.

onstat Option
Description
xctl onstat -g ses
Lists summaries of all sessions.
xctl onstat -g ses session id
Lists session information by session ID.

If you omit session id, this option displays one-line summaries of all active sessions.

xctl onstat -g sql session id
Lists SQL information by session.

If you omit session id, this option displays summaries of all sessions.

xctl onstat -g stk thid
Lists stack information by thread.

The thid variable is the value in the tid field that appears in the output of the onstat -g ses commands.

The following sections illustrates onstat -g ses options with examples.

Table 6 shows the output of the onstat -g ses command on the connection coserver to display a list of sessions on all coservers.

Table 6. onstat -g ses Output
local
sessid
sessid user tty pid #RSAM
hostname
total
threads
used
memory
memory Start Time Accum Time
1.10 64 informix - 436 - 1 65536 29208 - -
1.63 63 informix - 0 - 0 16384 8408 - -
1.60 62 gxypl - 8619 - 3 139264 111320 12:23:01 -
1.61 61 informix - 0 - 0 16384 8408 - -
1.60 60 gxypl pts/2 8619 mesmero 1 98304 75520 - -
1.56 59 informix - 8618 - 1 81920 28920 - -
1.54 57 superv - 8587 - 2 147456 95960 - -
1.56 56 informix pts/1 8618 mesmero 1 73728 30704 12:20:04 -
1.54 54 superv pts/4 8587 mesmero 1 98304 75192 11:58:00 -
1.10 10 informix CEREBUS 436 cerebus 1 73728 30896 11:10:09 -
1.8 8 informix - 0 - 0 16384 12368 - -
1.7 7 informix - 0 - 0 16384 8408 - -
1.5 5 informix - 0 - 0 16384 12368 - -
1.4 4 informix - 0 - 0 16384 8408 - -
1.3 3 informix - 0 - 0 16384 8408 - -
1.2 2 informix - - 1 24576 16864 - -

The onstat -g ses option output displays the following information:

Use the onstat -g ses option to obtain this information for only the coserver on which you issue the onstat command.

For example, Table 6 shows that session 1.60 has started four threads.

To display detailed information about a session, run the xctl onstat -g ses session id option. Figure 12 shows the onstat -g ses session id output.

Figure 12. onstat -g ses id Output
        local                                  #RSAM    total      used           
sessid  sessid user     tty     pid   hostname threads  memory   memory 
1.24     24    hannam   pts/4   28531 veritas. 1        118784   89280 
                                                     Start     Accum
                                                     Time      Time
                                                     08:30:00  -    

tid      tcb      rstcb    flags    curstk   status                name
86       af17230  a67c798  Y--P---  1560     cond wait(netnorm)    sqlexec_1.24

Memory pools    count 1
name         class addr          totalsize  freesize   #allocfrag #freefrag 
24           V     b04e018         118784     29504      311        16        

name      free    used            name       free    used                
overhead   0      128             scb        0        96                  
opentable  0     3600             filetable  0       632                 
log        0     4216             temprec    0      1608                
blob       0       48             keys       0       256                 
ralloc     0    26800             gentcb     0      9728                
ostcb      0     2560             sqscb      0     13632               
rdahead    0      112             hashfiletab 0     280                 
osenv      0     1640             sqtcb       0     560                 
fragman    0      864                      

|--Session ID--|  SQL           Current     Iso  Lock   SQL  ISAM   F.E.
 Global  Local  Stmt type      Database     Lvl  Mode   ERR  ERR   Vers
1.24      24    SELECT         sales         CR Not Wait  0    0    8.50
          Submit     Admit     Current        
          Time       Time      Role
          08:32:00   -        -
PDQ priority : 50
Scheduling level : 50
Client time zone : OFF

Current statement name : slctcur

Current SQL statement :
  select   dist.lastname, dist.state, count(*) from   dist, orders 
    where dist.id = orders.dist and orders.amt > 50 group by 1,2
    order by 1,2,3

The xctl onstat -g ses session id option displays the threads and memory allocated for and used by a session as shown in Figure 12.

Column Name
Column Description
Session ID and local session ID
The database server uses global session numbers in the following format:

connection_coserver.local-id

The connection_coserver is the number of the coserver where the user is logged in for this session. The local-sessid is the session ID on the connection coserver.

User
The login name of the connected user
Tty
The terminal ID
Pid
The UNIX process ID

If the process ID is a negative number, the process is a client process running an external application. For example, user sessions that run ad hoc queries from applications on PCs will have negative PIDs.

Hostname
The name of the database server to which the session is attached
# RSAM threads
The number of threads executing transactions or queries for this session
Total memory
The total amount of memory allocation for this session
Used memory
The amount of allocated memory that the session is using
Start time
The time when the user started the client session
Accum time
The total RGM usage of all queries in the session. This is the sum of all of the completed queries in the session, not including the current query. To determine the time of the current query, use onstat -g sql sessid.

In addition, the onstat -g sql session-id command displays the type of SQL statement, database name, isolation level, and lock mode.

Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]