Use the following tools to monitor DSS queries:
For examples, see Interpreting SET EXPLAIN Output.
Monitor the shared memory and thread resources that the database server has granted for queries. For information about how the database server allocates resources to queries, see Appendix B. How SQL Queries Are Executed.
To monitor the resource use of currently executing queries, use ISA or the isatool utility commands. The information displayed helps you understand how user applications are using the database server and lets you identify sessions and queries that use more than their share of resources.
For information about tuning the configuration parameters that affect queries, see the following sections:
The following table lists command-line utilities for specific resource-monitoring purposes. Run the command-line utilities from the connection coserver, which is the coserver where the query originated.
Use this information to get a snapshot of current query status and resource use. Use the session ID to identify or request query information in other onstat command output.
For an output sample and more information, refer to Monitoring All Concurrent Queries.
For more information, refer to Monitoring Current Session Activity and Resource Use.
For more information, refer to Monitoring Query Segments and SQL Operators.
SET EXPLAIN output also displays query-plan information, as discussed in Using Command-Line Utilities to Monitor Query Execution.
For more information, refer to Monitoring SQL Operator Statistics.
For more information, refer to Monitoring Query Plans.
For more information, refer to Monitoring User Threads and Transactions.
Use ISA or onstat -g rgm to display information about waiting and executing queries.
Resource Grant Manager (RGM)
============================
DS_ADM_POLICY: FAIR
DS_MAX_QUERIES: 32
MAX_PDQPRIORITY: 100
DS_TOTAL_MEMORY: 4096 KB
DS_TOTAL_TMPSPACE: 1600 KB
Coserver Capabilities
-----------------------------------------------------------
Number cosvrs Active/Enabled
Total cosrvs 2 2
Compute Capable cosvrs 2 2
TempData Capable cosvrs 2 2
-----------------------------------------------------------
DS Total Memory Across Coservers: 8192 KB
Queries: Waiting Active
0 2
Memory: Total Free
(KB) 8192 7680
RGM Wait Queue: (len = 0)
---------------
No waiting queries.
RGM Active Queue: (len = 1)
-----------------
Lvl Session Plan PdqPrio Memory (KB) #Cosvrs Local Cosvr Tmp Limit Tmp Quota (Kb)
50 1.14 3 75.00-75.00 6144 2 NO LIMIT -
50 1.20 6 5.00-5.00 512 2 1.00 % 16 Figure 13 shows the output of onstat -g rgm. The first output section displays the following information.
The second output section displays the following coserver capability information.
The third output section displays the following query information.
The fourth output section displays memory use.
The fifth and sixth output sections list the queries in the Wait and Active queues. The following information appears for each query in the Active queue.
The global session ID has the following format:
coserver_number.local-id
If SET ENVIRONMENT IMPLICIT_PDQ is in effect for the session that initiated the query, memory is estimated by the optimizer. For information, see Enabling Optimizer-Determined Memory Allocation
If temporary space limits are not enabled for this session, NO LIMIT appears even if DS_TOTAL_TMPSPACE is set in the configuration file. For information about enabling temporary space limits for query overflow, see Limiting Temporary Space for Query-Operator Overflow
For queries in the Wait queue, the following information appears instead of memory-grant and temporary-space information.
As queries execute, you can use ISA and onstat command options to monitor their current activity. The following sections describe the onstat command options and provide output samples where appropriate.
Use the xctl onstat -g xmp option to display information about the query segments and SQL operators that are currently executing on a coserver.
Figure 14 shows the XMP Query Segments section and the XMP Query Operators section of onstat -g xmp output on both coservers.
XMP Query Segments segid width numbr qryid sessid flags seqno 0 1 1 16 1.36 0x11a 1 3 2 1 16 1.36 0x118 3 XMP Query Operators opaddr qry segid branch brtid opname phase rows in1 in2 0xb3147f8 16 3 0-0 133 xchg create 0 0xb314940 0x0 0xb314940 16 3 0-0 133 group create 0 0xb3171f8 0x0 0xb3171f8 16 3 0-0 133 hjoin overflow 2305 0xb317310 0xb317538 0xb317310 16 3 0-0 133 xchg done 1489 0x0 0x0 0xb317538 16 3 0-0 133 xchg done 4973 0x0 0x0
Figure 14 shows the first section of onstat -g xmp, which contains Query Segments information.
Use this value for the onstat -g xqp qryid and onstat -g xqs qryid commands.
The second section of the display shows information about the SQL operators that the query uses. For information about interpreting query operator information, see Interpreting SQL Operator Runtime Data in the Query Plan and Interpreting SET EXPLAIN Output.
This information helps to associate in1 and in2 values with other operators
Operators are constructed into tree structures, and in1 and in2 represent linkage operators. A 0x0 value represents no child.
Use the xctlonstat -g xqp qryid option to display summary information about a specific query plan while the query is executing. A plan can be displayed only from the connection coserver, which is the coserver where the sqlexec thread is running.
The database server uses SQL operators and exchanges to divide a query plan into segments and construct a tree of operators. The onstat -g xqp command displays this operator tree in the order of execution. The order of the operators in the output of the onstat -g xqp command is the same as in the output for the SQL statement SET EXPLAIN ON. For information about SQL operators, refer to Interpreting SQL Operator Runtime Data in the Query Plan. For information about the output of SET EXPLAIN ON, refer to Interpreting SET EXPLAIN Output.
To display the query plan summary for query plan 17, enter following command:
onstat -g xqp 17
Figure 15 shows the output for the preceding command.
XMP Query Plan oper segid brid width misc info ----------------------------------------- scan 4 0 2 dist scan 5 0 2 orders hjoin 3 0 2 group 3 0 2 group 2 0 2 sort 1 0 2
This onstat -g xqp display shows the following query-plan information.
For more information on the types of SQL operators, refer to Interpreting SQL Operator Runtime Data in the Query Plan.
Multiple instances can exist for each branch because the SQL operator can execute in parallel on multiple fragments of a table.
You can use the onstat -g xqs qryid option to display query statistics for a specific query plan. A plan is available for display only on the connection coserver, which is the coserver on which the sqlexec thread is running.
A query plan is made up of SQL operators and exchanges. The onstat -g xqs command displays this operator tree in the order of execution. The following information appears for each SQL operator instance:
The onstat -g xqs command displays statistics for all currently active query plans. The statistics reported by onstat -g xqs are updated periodically as the query runs, and might not reflect the current state of the query. Some information, such as overflow information, might not be useful until the query is complete. The final statistics for the query appear in the sqexplain.out file if you run SET EXPLAIN ON before you run the query.
The following sample command displays SQL operator statistics for query plan ID 17:
onstat -g xqs 17
Figure 16 shows the output for the preceding command.
XMP Query Statistics
Cosvr_ID: 1
Plan_ID: 17
type segid brid information
---- ----- ---- -----------
scan 4 0 inst cosvr time rows_prod rows_scan
---- ----- ---- --------- ---------
0 1 1 1481 1481
1 2 1 1519 1519
--------------------------------------
2 3000 3000
scan 5 0 inst cosvr time rows_prod rows_scan
---- ----- ---- --------- ---------
0 1 0 6423 6423
1 2 0 3577 3577
--------------------------------------
2 10000 10000
hjoin 3 0 inst cosvr time rows_prod rows_bld rows_probe mem ovfl tmp
---- ----- ---- --------- -------- ---------- --- ---- ---
0 1 12 4941 1489 4973 552 808 214
1 2 12 4993 1511 5027 568 832 222
------------------------------------------------------------------------
2 9934 3000 10000 (384)
group 3 0 inst cosvr time rows_prod rows_cons mem ovfl tmp
---- ----- ---- --------- --------- --- ---- ---
0 1 12 112 4941 48 0 0
1 2 12 112 4993 48 0 0
-------------------------------------------------------------
2 224 9934 (256)
group 2 0 inst cosvr time rows_prod rows_cons mem ovfl tmp
---- ----- ---- --------- --------- --- ---- ---
0 1 0 63 126 32 0 0
1 2 0 49 98 16 0 0
-------------------------------------------------------------
2 112 224 (256)
sort 1 0 inst cosvr time rows
---- ----- ---- ----
0 1 0 62
1 2 0 50
----------------------The onstat -g xqs output in Figure 16 shows the following SQL operator statistics.
For information about these SQL operator-specific statistics, refer to Interpreting SQL Operator Runtime Data in the Query Plan.
The order of the operators in the output of the onstat -g xqs command is the same as in the output of SET EXPLAIN and onstat -g xmp.
Each decision-support query has a primary thread on the connection coserver. This primary thread can start additional threads to perform tasks for the query, such as scans and sorts.
For information about all the threads that are running for a decision-support query, use the xctl onstat -u and xctl onstat -g ath options. The xctl onstat -u option lists primary thread and any additional threads that are executing queries on participating coservers. The output of the onstat -u command on a specific coserver lists only the threads on that specific coserver.
The thread information shows what threads each session is running, how busy each thread is, and how many locks each thread holds.
For example, session 10 in Figure 17 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
P in the fourth flags column indicates the primary thread for a session. The code in the first flags column indicates why a thread is waiting. The possible flag codes are as follows.
The onstat -g ath option display also lists the primary thread and secondary threads on the connection coserver and participating coservers. In addition, it includes a name column that describes the activity of the thread, as Figure 18 shows.
Threads: tid tcb rstcb prty status vp-class name 5 40067294 0 4 sleeping(secs: 0) 1cpu xmf_svc 12 407c85e8 0 2 running 5aio aio vp 38 40a0deec 4006bdd0 2 sleeping(secs: 49) 1cpu btclean 40 409e5c84 4006c540 4 sleeping(secs: 1) 1cpu onmode_mon 128129 40b97788 4006df48 2 sleeping(secs: 1) 1cpu sqlexec_1.3567 128130 4138187c 4006f598 2 sleeping(Forever) 1cpu x_exec_1.3567 128134 40b01d18 4006d420 2 ready 1cpu x_hjoin_0 128136 408cd1a0 4006fd08 2 sleeping(Forever) 1cpu x_scan_0 128137 408cd3cc 4006f950 2 running 1cpu x_scan_4 128138 904510d4 4006e6b8 2 sleeping(Forever) 1cpu x_scan_8
Figure 18 output lists the following items:
The session ID (1.3567) is part of the name of the sqlexec thread.
The x_exec thread initiates secondary threads on the participating coserver.