SQL Query Optimization Messages

You can tune the performance of SQL queries by using the SQL Query Optimization Messages to determine:
datacom151
You can tune the performance of SQL queries by using the SQL Query Optimization Messages to determine:
  • What processing steps the data access plan contains,
  • Why those processing steps were selected, and
  • The estimated and actual costs of those processing steps.
There are two main types of Optimization messages:
  • Bind Time (see Bind-time Messages):
    • Summary:
      Join order and method, sort required, total estimated cost.
    • Detail:
      Detail estimates for each join order/method combination.
  • Execution Time (see Execution-Time Messages):
    • Summary:
      Rows read from index and data, rows qualified, rows sorted, and so on, during the time the plan was open.
    • Detail:
      Same data as summary, but for each execution of a statement.
The following topics are discussed on this page:
Message Table (SYSADM.SYSMSG)
Optimization messages are written to table SYSADM.SYSMSG. They are deleted when the plan is rebound or deleted.
This table was created as:
CREATE TABLE SYSADM.SYSMSG (AUTHID CHAR(18) NOT NULL, PLANNAME CHAR(18) NOT NULL, STMTID INTEGER NOT NULL, SEQNBR SMALLINT NOT NULL, MSG CHAR(80) NOT NULL, PRIMARY KEY (AUTHID, PLANNAME, STMTID, SEQNBR)) IN SYSMSG_AREA;
Requesting Messages
You specify that SQL Query Optimization Messages be generated by either using the MSG= plan option (see
CA Datacom®/DB
SQL Preprocessors) or by using the COMM function of
CA Datacom®/DB
Utility (DBUTLTY) as follows.
For summary level:
COMM OPTION=ALTER,TRACE=TRACEMSG,JOBNAME=xxxxxxxx
For detail level, also turn on:
COMM OPTION=ALTER,TRACE=TRACEDETAIL,JOBNAME=xxxxxxxx
When triggered by
CA Datacom®/DB
Utility (DBUTLTY) traces, the messages are written both to SYSADM.SYSMSG and to the PXX. For more information on using the
CA Datacom®/DB
Utility (DBUTLTY), see the
CA Datacom®/DB
DBUTLTY Reference Guide
.
CA Dataquery
deletes the messages from the previous query when the next query is executed if the plan option to generate messages is not turned off. Therefore, to save the messages turn off the message option and run:
SELECT * FROM SYSADM.SYSMSG
You can optionally use the WHERE clause to read only rows for the desired plan. After the SELECT statement has been run, use the DQRY STORE command to store the result in a different table. Alternately, before preparing another query (and without signing off DQRY) use any other method, such as DDOL, to query the SYSADM.SYSMSG table.
Examples
MSG=SD
This is the simpilest example, just to show the basic format of the report.
Note that you might want to first skip to the summary, and then use the detail information that precedes it only if needed.
Before Adding IND_CD to SELECT and GROUP BY List
PLAN:SYSADM .SQL7185D80EE8A76A STM:000000002 DT: 2016-03-14 16.25.00 DECLARE C1 CURSOR FOR SELECT CUST_NO, SUM(ORD_AMT) FROM ACCTS WHERE IND_CD = 'A' GROUP BY CUST_NO ***** BIND MESSAGES FOR SUBSELECT 01 ***** ------------------------ KEY DEFINITIONS ------------------------- INDEX DEFINITIONS FOR: ACT/010 SYSADM.ACCTS KEYS= 2, IDXLVLS=1, LN= 31, ROWS/BLK= 95 ROWS= 3 KEY ACTOR ID= 4 FLG=D0 80 FLDS= 1 DXX= 300 BLKCHG= 204 ROWS=3 OFFSET= 0, LN= 5, DIR=ASC SENS=N CARD= 3 ORD_ID KEY ACSTI ID= 1 FLG=00 80 FLDS= 4 DXX= 300 BLKCHG= 204 ROWS=3 OFFSET= 26, LN= 1, DIR=ASC SENS=N CARD= 2 IND_CD OFFSET= 27, LN= 4, DIR=ASC SENS=N CARD= 3 CUST_NO OFFSET= 26, LN= 1, DIR=ASC SENS=N CARD= 3 IND_CD OFFSET= 27, LN= 4, DIR=ASC SENS=N CARD= 3 CUST_NO
PLAN:SYSADM .SQL74162B33E8816A STM:000000002 DT: 2016-03-16 17.21.21 DECLARE C1 CURSOR FOR SELECT IND_CD, CUST_NO, NAME, STATE, OPEN_$, YTD_SALES FROM CUST ***** BIND MESSAGES FOR SUBSELECT 01 ***** ------------------------ KEY DEFINITIONS ------------------------- INDEX DEFINITIONS FOR: CUS/010 SYSADM.CUST KEYS= 4, IDXLVLS=1, LN= 153, ROWS/BLK= 24 ROWS= 114 KEY CUSID ID= 1 FLG=D0 00 FLDS= 4 DXX= 300 BLKCHG= 204 ROWS=114 OFFSET= 0, LN= 1, DIR=ASC SENS=N CARD= 2 IND_CD OFFSET= 1, LN= 4, DIR=ASC SENS=N CARD= 114 CUST_NO OFFSET= 0, LN= 1, DIR=ASC SENS=N CARD= 114 IND_CD OFFSET= 1, LN= 4, DIR=ASC SENS=N CARD= 114 CUST_NO KEY CRED ID= 3 FLG=40 00 FLDS= 1 DXX= 300 BLKCHG= 204 ROWS=114 OFFSET= 121, LN= 1, DIR=ASC SENS=N CARD= 1 CRED_IND KEY IDNO ID= 13 FLG=40 00 FLDS= 2 DXX= 300 BLKCHG= 204 ROWS=114 OFFSET= 0, LN= 1, DIR=ASC SENS=N CARD= 2 IND_CD OFFSET= 1, LN= 4, DIR=ASC SENS=N CARD= 114 CUST_NO KEY NAME ID= 2 FLG=00 00 FLDS= 1 DXX= 300 BLKCHG= 204 ROWS=114 OFFSET= 5, LN= 30, DIR=ASC SENS=N CARD= 114 NAME ----------------------- RESTRICTION COSTS ------------------------ TBL CUS DBID 010 RESTRICTION COSTS: KEY CUSID 1ST 0 FLDS SELECTIVITY= 1.000000000 0 LOW-ORDER FLDS SELECTIVITY= 1.000000000 DATA SELECTIVITY= 1.000000000 INDEX 1 DATA 23 SORT 0 ROWS 114 KEY CRED 1ST 0 FLDS SELECTIVITY= 1.000000000 0 LOW-ORDER FLDS SELECTIVITY= 1.000000000 DATA SELECTIVITY= 1.000000000 INDEX 1 DATA 23 SORT 0 ROWS 114 KEY IDNO 1ST 0 FLDS SELECTIVITY= 1.000000000 0 LOW-ORDER FLDS SELECTIVITY= 1.000000000 DATA SELECTIVITY= 1.000000000 INDEX 1 DATA 23 SORT 0 ROWS 114 KEY NAME 1ST 0 FLDS SELECTIVITY= 1.000000000 0 LOW-ORDER FLDS SELECTIVITY= 1.000000000 DATA SELECTIVITY= 1.000000000 INDEX 1 DATA 23 SORT 0 ROWS 114 *** KEY CUSID HAS LOWEST ESTIMATED COST OF 24 ========================= S U M M A R Y ========================== READ TBL SYSADM.CUST USING KEY CUSID ***** FULL TABLE SCAN REQUIRED FOR <SYSADM.CUST > ***** ============= E N D OF B I N D M E S S A G E S ============= ACTIVITY FOR QUERY SYSADM . SQL74162B33E8816A .2 SETS INDEX DATA QUAL ACCEPTED I/O CBS OPT REASONS TABLE <SYSADM.CUST > USING KEY <CUSID> 00001 000000114 000000114 000000114 000000114 0000004 < P P Y >
After Adding IND_CD to SELECT and GROUP BY List
SELECT STATE, AVG(OPEN_$), AVG(YTD_SALES) FROM CUST GROUP BY STATE ***** BIND MESSAGES FOR SUBSELECT 01 ***** ========================= S U M M A R Y ========================== READ TBL SYSADM.CUST USING KEY CUSID ESTIMATED GROUP SORT COST= 6 ***** FULL TABLE SCAN REQUIRED FOR <SYSADM.CUST > ***** GROUP BY SRT REASONS= CBS ORDER ============= E N D OF B I N D M E S S A G E S ============= ========================= S U M M A R Y ========================== READ TBL SYSADM.CUST USING KEY CUSID ESTIMATED GROUP SORT COST= 6 ***** FULL TABLE SCAN REQUIRED FOR <SYSADM.CUST > ***** GROUP BY SRT REASONS= CBS ORDER ============= E N D OF B I N D M E S S A G E S ============= ACTIVITY FOR QUERY SYSADM . SQL74162B33E8816A .4 SETS INDEX DATA QUAL ACCEPTED I/O CBS OPT REASONS TABLE <SYSADM.CUST > USING KEY <CUSID> 00001 000000114 000000114 000000114 000000114 0000000 < P P Y > SORT COMPLETED for GROUP BY TEXP: ROWS EST=114 IN=114, OUT=37
========================= S U M M A R Y ========================== READ TBL SYSADM.CUST USING KEY CUSID ESTIMATED GROUP SORT COST= 6 ***** FULL TABLE SCAN REQUIRED FOR <SYSADM.CUST > ***** GROUP BY SRT REASONS= CBS ORDER ============= E N D OF B I N D M E S S A G E S ============= ACTIVITY FOR QUERY SYSADM . SQL74162B33E8816A .4 SETS INDEX DATA QUAL ACCEPTED I/O CBS OPT REASONS TABLE <SYSADM.CUST > USING KEY <CUSID> 00001 000000114 000000114 000000114 000000114 0000000 < P P Y > SORT COMPLETED for GROUP BY TEXP: ROWS EST=114 IN=114, OUT=37