You can use various onstat utility commands to determine how many threads are active and the shared-memory resources that those threads use.
You can use the onstat -g mgm option to monitor how MGM coordinates memory use and scan threads. The onstat utility reads shared-memory structures and provides statistics that are accurate at the instant that the command executes.
Figure 65 shows sample output.
The onstat -g mgm output displays a unit of memory called a quantum. The memory quantum represents a unit of memory, as follows:
memory quantum = DS_TOTAL_MEMORY / DS_MAX_QUERIES
The following calculation shows the memory quantum for the values that Figure 65 displays:
memory quantum = 4000 kilobytes / 5
= 800 kilobytes
The scan thread quantum is always equal to 1.
Memory Grant Manager (MGM)
--------------------------
MAX_PDQPRIORITY: 100
DS_MAX_QUERIES: 5
DS_MAX_SCANS: 10
DS_TOTAL_MEMORY: 4000 KB
Queries: Active Ready Maximum
3 0 5
Memory: Total Free Quantum
(KB) 4000 3872 800
Scans: Total Free Quantum
10 8 1
Load Control: (Memory) (Scans) (Priority) (Max Queries) (Reinit)
Gate 1 Gate 2 Gate 3 Gate 4 Gate 5
(Queue Length) 0 0 0 0 0
Active Queries:
---------------
Session Query Priority Thread Memory Scans Gate
7 a3d0c0 1 a8adcc 0/0 1/1 -
7 a56eb0 1 ae6800 0/0 1/1 -
9 a751d4 0 96b1b8 16/16 0/0 -
Ready Queries: None
Free Resource Average # Minimum #
-------------- --------------- ---------
Memory 489.2 +- 28.7 400
Scans 8.5 +- 0.5 8
Queries Average # Maximum # Total #
-------------- --------------- --------- -------
Active 1.7 +- 0.7 3 23
Ready 0.0 +- 0.0 0 0
Resource/Lock Cycle Prevention count: 0The first portion of the output shows the values of the PDQ configuration parameters.
The second portion of the output describes MGM internal control information. It includes four groups of information.
The first group is Queries. The group contains this information:
| Column | Description |
|---|---|
| Active | Number of PDQ queries that are currently executing |
| Ready | Number of user queries ready to run but whose execution the database server deferred for load-control reasons |
| Maximum | Maximum number of queries that the database server permits to be active. Reflects current value of the DS_MAX_QUERIES configuration parameter |
The next group is Memory. The group contains this information:
The next group is Scans. The group contains this information:
| Column | Description |
|---|---|
| Total | The total number of scan threads as specified by the DS_MAX_SCANS configuration parameter |
| Free | Number of scan threads currently available for decision-support queries |
| Quantum | The number of scan threads in a scan-thread quantum |
The last group in this portion of the output describes MGM Load Control. The group contains this information:
| Column | Description |
|---|---|
| Memory | Number of queries that are waiting for memory |
| Scans | Number of queries that are waiting for scans |
| Priority | Number of queries that are waiting for queries with higher PDQ priority to run |
| Max Queries | Number of queries that are waiting for a query slot |
| Reinit | Number of queries that are waiting for running queries to complete after an onmode -M or -Q command |
The next portion of the output, Active Queries, describes the MGM active and ready queues. This portion of the output shows the number of queries waiting at each gate.
| Column | Description |
|---|---|
| Session | The session ID for the session that initiated the query |
| Query | Address of the internal control block associated with the query |
| Priority | PDQ priority assigned to the query |
| Thread | Thread that registered the query with MGM |
| Memory | Memory currently granted to the query or memory reserved for the query (Unit is MGM pages, which is 8 kilobytes.) |
| Scans | Number of scan threads currently used by the query or number of scan threads allocated to the query |
| Gate | Gate number at which query is waiting |
The next portion of the output, Free Resource, provides statistics for MGM free resources. The numbers in this portion and in the final portion reflect statistics since system initialization or the last onmode -Q, -M, or -S command. This portion of the output contains the following information:
| Column | Description |
|---|---|
| Average | Average amount of memory and number of scans |
| Minimum | Minimum available memory and number of scans |
The last portion of the output, Queries, provides statistics concerning MGM queries:
| Column | Description |
|---|---|
| Average | Average active and ready queue length |
| Maximum | Maximum active and ready queue length |
| Total | Total active and ready queue length |
To obtain information on all of the threads that are running for a decision-support query, use the onstat -u and onstat -g ath options.
The onstat -u option lists all the threads for a session. If a session is running a decision-support query, the output lists the primary thread and any additional threads. For example, session 10 in Figure 66 has a total of five threads running.
Userthreads address flags sessid user tty wait tout locks nreads nwrites 80eb8c ---P--D 0 informix - 0 0 0 33 19 80ef18 ---P--F 0 informix - 0 0 0 0 0 80f2a4 ---P--B 3 informix - 0 0 0 0 0 80f630 ---P--D 0 informix - 0 0 0 0 0 80fd48 ---P--- 45 chrisw ttyp3 0 0 1 573 237 810460 ------- 10 chrisw ttyp2 0 0 1 1 0 810b78 ---PR-- 42 chrisw ttyp3 0 0 1 595 243 810f04 Y------ 10 chrisw ttyp2 beacf8 0 1 1 0 811290 ---P--- 47 chrisw ttyp3 0 0 2 585 235 81161c ---PR-- 46 chrisw ttyp3 0 0 1 571 239 8119a8 Y------ 10 chrisw ttyp2 a8a944 0 1 1 0 81244c ---P--- 43 chrisw ttyp3 0 0 2 588 230 8127d8 ----R-- 10 chrisw ttyp2 0 0 1 1 0 812b64 ---P--- 10 chrisw ttyp2 0 0 1 20 0 812ef0 ---PR-- 44 chrisw ttyp3 0 0 1 587 227 15 active, 20 total, 17 maximum concurrent
The onstat -g ath output also lists these threads and includes a name column that indicates the role of the thread. Threads that a primary decision-support thread started have a name that indicates their role in the decision-support query. For example, Figure 67 lists four scan threads, started by a primary thread (sqlexec).
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
Use the onstat -g ses option to monitor the resources allocated for, and used by, a session that is running a decision-support query. The onstat -g ses option displays the following information:
For example, in Figure 68, session number 49 is running five threads for a decision-support query.
session #RSAM total used id user tty pid hostname threads memory memory 57 informix - 0 - 0 8192 5908 56 user_3 ttyp3 2318 host_10 1 65536 62404 55 user_3 ttyp3 2316 host_10 1 65536 62416 54 user_3 ttyp3 2320 host_10 1 65536 62416 53 user_3 ttyp3 2317 host_10 1 65536 62416 52 user_3 ttyp3 2319 host_10 1 65536 62416 51 user_3 ttyp3 2321 host_10 1 65536 62416 49 user_1 ttyp2 2308 host_10 5 188416 178936 2 informix - 0 - 0 8192 6780 1 informix - 0 - 0 8192 4796