|
View previous topic :: View next topic
|
| Author |
Message |
adarshkhanna
New User
Joined: 05 Oct 2012
Posts: 3
Location: India
|
|
|
|
Hi,
I have a simple SELECT query
SELECT col1,col2
from tableA
where col3 = :host-var
An index is defined on col3
Below is the performance data from apptune.
Since number of times the query is executed is huge - what could be performance optimization remedies? Multi-row Fetch?
| Code: |
AVERAGE PER
EVENT TOTAL SQL CALL
----------------------------------- ------------ ------------
SQL CALLS..........................: 44652
IN-SQL ELAPSED.....................: 01:28.44532 00:00.00198
IN-SQL CPU.........................: 00:05.24446 00:00.00012
IN-SQL ZIIP........................: 00:00.00000 00:00.00000
GETPAGE COUNT......................: 97953 2
AVERAGE PER
EVENT TOTAL SQL CALL
IN-SQL CPU TIME...................: 00:05.24446 00:00.00012
SYNC I/O TIME.....................: 01:11.86427 00:00.00161
ASYNC I/O TIME....................: 00:05.18697 00:00.00012
LOCK TIME.........................: 00:01.80784 00:00.00004
OTHER WAIT TIME...................: 00:03.29062 00:00.00007
UNCATEGORIZED.....................: 00:01.05116 00:00.00002
# OF SELECTS.......................................: 44652
# OF INSERTS.......................................: 0
# OF UPDATES.......................................: 0
# OF DELETES.......................................: 0
TOTAL
TYPE OF WAIT NUMBER
---------------------------------------------------- --------
I/O WAITS
SYNC I/O.........................................: 24732
ASYNC READ I/O...................................: 1311
ASYNC WRITE I/O..................................: 0
LOG WRITE I/O....................................: 0
COMMIT PHASE 1 WRITE I/O WAIT....................: 0
ARCHIVE LOG......................................: 0
TAPE ARCHIVE READ................................: 0
LOCKS AND LATCHES
LOCK/LATCH WAIT..................................: 99
DRAIN LOCK WAIT..................................: 0
RELEASE CLAIM LOCK...............................: 0
PAGE LATCH.......................................: 0
CPU PARALLELISM
REQUEST SUMMARY NUMBER
---------------------------------------------------- --------
REQUEST FOR LOCK...................................: 23492
REQUEST FOR UNLOCK.................................: 106
LOCK QUERIES.......................................: 0
REQUEST FOR CHANGE.................................: 0
OTHER IRLM REQUESTS................................: 0
CLAIM REQUESTS.....................................: 35618
CLAIM FAILURES,....................................: 0
DRAIN REQUESTS.....................................: 0 |
|
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello and welcome to the forum,
How often is the query about to be executed the same as the one previously executed?
When a "thing" takes more resources than you want, try to find a way to do this "thing" less.
On an average query how many rows are processed?
Trying multi-row fetch should reduce the number of "trips" to/from db2. Suggest you run a few experiments and compare the results. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
where col3 = :host-var
does the EXPLAIN for this SQL use the Col3-INDEX?
I would first go about optimizing the EXPLAIN
after determining what these
| Code: |
SYNC I/O TIME.....................: 01:11.86427 00:00.00161
ASYNC I/O TIME....................: 00:05.18697 00:00.00012 |
mean.
how does SYNC and ASYNC I/O time relate to what is being performed by DB2 for this SQL.
is this dynamic or static SQL?
do you have an ORDER BY statement?
are there any other WHERE clauses?
Your base table - number of rows
- last reorg'd
- RUNSTATS before the BIND (if not dynamic SQL)
Avg RESULTS set size (Rows?)
do Col1 or Col2 return any large objects? (LOB's,CLOB's,etc...) |
|
| Back to top |
|
|
adarshkhanna
New User
Joined: 05 Oct 2012
Posts: 3
Location: India
|
|
|
|
How often is the query about to be executed the same as the one previously executed? - always different - the value of predicate is always different.
On an average query how many rows are processed? around 2M
is this dynamic or static SQL? It is a Static SQL
do you have an ORDER BY statement? No
are there any other WHERE clauses? No
Your base table
number of rows - 64M
last reorg'd - 20 days back
RUNSTATS before the BIND (if not dynamic SQL) - done 20 days back
Avg RESULTS set size (Rows?) - 2M
do Col1 or Col2 return any large objects? (LOB's,CLOB's,etc...) - No
There are no primary keys defined on the table
The index is unique with a single column used in where clause
Index is clustered and cluster ratio is 100%
Looking at all this - I can only think of Multi-row fetch option and to sort the input file in order of col3 - so when select is executed it is in sequential order.
Any additional ideas and how to reduce Sync I/O? |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| I would first go about optimizing the EXPLAIN |
|
| Back to top |
|
|
adarshkhanna
New User
Joined: 05 Oct 2012
Posts: 3
Location: India
|
|
|
|
Below is the explain result
| Code: |
COST*RATE QB PL MIX QTYPE METH ACC MTCH IX TBNAME IXNAME NU J O G CU J O G LCK
4.261257 1 1 0 SELECT 0 I 1 N TABLEA COL3 N N N N N N N N IS
|
From the result - Prefetch is not used |
|
| Back to top |
|
|
razesh84
New User
Joined: 05 Apr 2010
Posts: 41
Location: Kolkata,India
|
|
|
|
| Quote: |
The index is unique with a single column used in where clause
|
and
| Quote: |
| How often is the query about to be executed the same as the one previously executed? - always different - the value of predicate is always different |
if both are true, then what is the use of a cursor?
and also explain result does not catch dynamic prefetch.you need to look in some db2 tools for dynamic prefetch. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
If you are doing the select in ascending order of col3 (=input is in the correct order) and if the index on col3 is 100% Clustered (check runstats date), it is a matter of triggering dynamic prefetch :
catterallconsulting.blogspot.be/2009/04/db2-for-zos-prefetch-part-1-basics.html
| Quote: |
| DB2 tracks pages as they are accessed in the execution of a database-accessing program (the classic example is a singleton SELECT in a do-loop - at bind time DB2 doesn't know that the statement will be executed repeatedly, and that pages in the target table or index might be accessed in a sequential fashion). When the second page in the target table or index is accessed, DB2 checks to determine whether or not it's within half of the prefetch quantity (i.e., 16 pages, if the prefetch quantity is 32 pages) forward of the first page (or backward, if we're talking about the backward index scan capability introduced with DB2 for z/OS Version 8). If it is, that second page is noted as being sequential, access-wise, relative to the first. When the third page is accessed, DB2 checks to see that it's within half a prefetch quantity forward (or backward) of the second page. If it is, the third pages is noted as being sequential with respect to the second page. When 5 out of the last 8 pages accessed are sequential in this sense, DB2 turns on prefetch. It turns prefetch off if the number of sequential pages drops below 5 of the last 8. |
Another possibility is that the bufferpool is too small, or vpseqt is too low |
|
| Back to top |
|
|
razesh84
New User
Joined: 05 Apr 2010
Posts: 41
Location: Kolkata,India
|
|
|
|
thanks for the link guyc  |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
If the number of times the query is executed is "huge" and
| Quote: |
| On an average query how many rows are processed? around 2M |
each query will process 2M rows, i expect there should be a performance issue?
What is going on that requires so many queries that hit so many rows?
What is the current response time at the terminal and what would be considered acceptable? |
|
| Back to top |
|
|
|
|
