Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Tuning SQL query using apptune performance data

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
adarshkhanna

New User


Joined: 05 Oct 2012
Posts: 3
Location: India

PostPosted: Tue Oct 09, 2012 6:47 pm    Post subject: Tuning SQL query using apptune performance data
Reply with quote

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
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Oct 09, 2012 7:34 pm    Post subject:
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Oct 09, 2012 10:07 pm    Post subject:
Reply with quote

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
View user's profile Send private message
adarshkhanna

New User


Joined: 05 Oct 2012
Posts: 3
Location: India

PostPosted: Wed Oct 10, 2012 11:32 am    Post subject: Reply to: Tuning SQL query using apptune performance data
Reply with 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.

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Oct 10, 2012 11:41 am    Post subject:
Reply with quote

I would first go about optimizing the EXPLAIN
Back to top
View user's profile Send private message
adarshkhanna

New User


Joined: 05 Oct 2012
Posts: 3
Location: India

PostPosted: Wed Oct 10, 2012 12:16 pm    Post subject:
Reply with quote

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
View user's profile Send private message
razesh84

New User


Joined: 05 Apr 2010
Posts: 41
Location: Kolkata,India

PostPosted: Wed Oct 10, 2012 12:38 pm    Post subject:
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Oct 10, 2012 1:03 pm    Post subject:
Reply with quote

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 :
http://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
View user's profile Send private message
razesh84

New User


Joined: 05 Apr 2010
Posts: 41
Location: Kolkata,India

PostPosted: Wed Oct 10, 2012 1:52 pm    Post subject:
Reply with quote

thanks for the link guyc icon_biggrin.gif
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Oct 10, 2012 8:30 pm    Post subject:
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Is the 'prompt' data for the DSLIST p... Willy Jensen TSO/ISPF 2 Tue Dec 06, 2016 4:38 am
This topic is locked: you cannot edit posts or make replies. How to move a long alphanumeric data ... lind sh COBOL Programming 8 Mon Dec 05, 2016 7:51 pm
No new posts PL/I code tuning/Performance improvement Virendra Shambharkar PL/I & Assembler 4 Mon Dec 05, 2016 11:57 am
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts ODPP(Optim Data privacy Provider) Iss... Rama kishore IBM Tools 1 Mon Nov 07, 2016 5:46 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us