Monitor threads with onstat -g ath output

Use the onstat -g ath command to view a list of all threads. Unlike the onstat –u command, this list includes internal daemon threads that do not have a database server task-control block.

The onstat -g ath command display does not include the session ID (because not all threads belong to sessions).

The status field contains information on the status of thread, such as running, cond wait, IO Idle, IO Idle, sleeping secs:number_of_seconds, or sleeping forever. The following output example identifies many threads as sleeping forever. To improve performance, you can remove or reduce the number of threads that are identified as sleeping forever.

Figure 1: onstat -g ath output

Threads:
tid  tcb           rstcb        prty  status              vp-class   name
2    10bbf36a8      0            2    sleeping forever       3lio    lio vp 0
3    10bc12218      0            2    sleeping forever       4pio    pio vp 0
4    10bc31218      0            2    sleeping forever       5aio    aio vp 0
5    10bc50218      0            2    sleeping forever       6msc    msc vp 0
6    10bc7f218      0            2    sleeping forever       7aio    aio vp 1
7    10bc9e540      10b231028    4    sleeping secs: 1       1cpu    main_loop()
8    10bc12548      0            2    running                1cpu    tlitcppoll
9    10bc317f0      0            3    sleeping forever       1cpu    tlitcplst
10   10bc50438      10b231780    2    sleeping forever       1cpu    flush_sub(0)
11   10bc7f740      0            2    sleeping forever       8aio    aio vp 2
12   10bc7fa00      0            2    sleeping forever       9aio    aio vp 3
13   10bd56218      0            2    sleeping forever      10aio    aio vp 4
14   10bd75218      0            2    sleeping forever      11aio    aio vp 5
15   10bd94548      10b231ed8    3    sleeping forever       1cpu    aslogflush
16   10bc7fd00      10b232630    1    sleeping secs: 26       1cpu    btscanner 0
32   10c738ad8      10b233c38    4    sleeping secs: 1        1cpu    onmode_mon
50   10c0db710      10b232d88    2    cond wait  netnorm      1cpu    sqlexec

Threads that a primary decision-support thread started have a name that indicates their role in the decision-support query. The following figure shows four scan threads that belong to a decision-support thread.

Figure 2: onstat -g ath output showing scan threads belonging to a decision-support thread

Threads:
tid  tcb            rstcb      prty  status                vp-class   name
11   994060         0           4     sleeping(Forever)       1cpu    kaio
12   994394         80f2a4      2     sleeping(secs: 51)      1cpu    btclean
26   99b11c         80f630      4     ready                   1cpu    onmode_mon
32   a9a294         812b64      2     ready                   1cpu    sqlexec
113  b72a7c         810b78      2     ready                   1cpu    sqlexec
114  b86c8c         81244c      2     cond wait(netnorm)      1cpu    sqlexec
115  b98a7c         812ef0      2     cond wait(netnorm)      1cpu    sqlexec
116  bb4a24         80fd48      2     cond wait(netnorm)      1cpu    sqlexec
117  bc6a24         81161c      2     cond wait(netnorm)      1cpu    sqlexec
118  bd8a24         811290      2     ready                   1cpu    sqlexec
119  beae88         810f04      2     cond wait(await_MC1)    1cpu    scan_1.0
120  a8ab48         8127d8      2     ready                   1cpu    scan_2.0
121  a96850         810460      2     ready                   1cpu    scan_2.1
122  ab6f30         8119a8      2     running                 1cpu    scan_2.2