|
View previous topic :: View next topic
|
| Author |
Message |
sandip_mainframe
Warnings : 2
New User
Joined: 20 Sep 2006
Posts: 63
Location: pune
|
|
|
|
Hi All,
We have one daily running job and in this job one program is getting called in this program below query is taking huge
time to execute, around 95-96% time it's wasting in I/O calls. Please suggest how I can optimize this query.
Please find required details here.
| Code: |
EXEC SQL
SELECT IFNULL(SUM(X_AMT),0)
INTO :WS-IS-CURNT
FROM XYZ_ABC
WHERE XX_NUM = :XYZ-ABC.XX-NUM (Table host variables)
AND YY_NUM = :XYZ-ABC.YY-NUM (Table host variables)
AND X_YEAR = :WS-YEAR-CUR (Working-storage variables)
AND Y_MONTH = :WS-MONTH-CUR (Working-storage variables)
GROUP BY YY_NUM, XX_NUM,
X_YEAR, Y_MONTH
END-EXEC.
|
INDEX Columns :
| Code: |
YY_NUM
XX_NUM
X_YEAR
Y_MONTH
P_CD |
Tables Details :
| Code: |
XX_NUM CHAR 12
YY_NUM CHAR 12
X_YEAR DECIMAL 02
Y_MONTH DECIMAL 02
P_CD CHAR 02 |
X_AMT DECIMAL 11
Recommendations From APPTUNE :
| Code: |
THIS SELECT STATEMENT WAS EXECUTED 1605K TIMES DURING THE REPORTED
INTERVAL.
THE STATEMENT'S AVERAGE IN-SQL ELAPSED TIME WAS 00:00.00486 AND THE
AVERAGE IN-SQL CPU TIME PER STATEMENT WAS 00:00.00011.
THE NUMBER OF GETPAGES REQUIRED FOR THIS STATEMENT AVERAGES 3.42,
WHICH IS EXCELLENT (LESS THAN 10 ). 0.00 PAGES, OR 0.0 %, ARE
RETRIEVED FROM THE BUFFER POOL WITHOUT INCURRING THE COST OF AN I/O.
THIS BUFFER POOL HIT RATIO IS POOR (LESS THAN OR EQUAL TO 50 %).
THIS STATEMENT SPENDS 97.9 % OF ITS TIME IN I/O, WHICH IS THE SINGLE
LARGEST COMPONENT OF ITS ELAPSED TIME.
APPTUNE LOGS :
CALL SECT STMT +--- SQL --+ +- TOTAL IN-SQL TIME -+
TYPE NO. NO. CALLS ERRS ELAPSED CPU GETPAGES
-------- ----- ------ ------ ----- ----------- ----------- --------
SELECT 30 15505 1502K 0 2:17:03.163 02:26.77652 5243681
AVERAGES: 00:00.00548 00:00.00010 3
|
EXPLIAN for this query is as below
| Code: |
STMTNO COST*RATE SQL-STATEMENT
15505 6.096961 SELECT IFNULL ( SUM ( X_AMT ) , 0 )
|
Details of Query are as below -
| Code: |
STATEMENT TYPE.... STATIC DISTRIBUTED TYPE.... N/P
CALL TYPE......... SELECT REQUESTING LOCATION. N/P
EVENT TOTAL SQL CALL
----------------------------------- ------------ ------------
SQL CALLS..........................: 1501547
IN-SQL ELAPSED.....................: 2:17:03.163 00:00.00548
IN-SQL CPU.........................: 02:26.77652 00:00.00010
IN-SQL ZIIP........................: 00:00.00000 00:00.00000
GETPAGE COUNT......................: 5243681 3
AVERAGE PE
EVENT TOTAL SQL CAL
IN-SQL CPU TIME...................: 02:26.77652 00:00.00010
SYNC I/O TIME.....................: 1:42:41.312 00:00.00410
ASYNC I/O TIME....................: 31:47.87067 00:00.00127
LOCK TIME.........................: 00:00.17485 00:00.00000
OTHER WAIT TIME...................: 00:00.00000 00:00.00000
UNCATEGORIZED.....................: 00:07.02914 00:00.00000
--------------------------------------------------- HIGH/LOW
HIGH
CPU.............................................: 00:00.00094
ELAPSED TIME....................................: 00:00.70539
GET PAGES..........................................: 8
SYNCHRONOUS READ I/OS..............................: 6
AVERAGE
BUFFER STATISTICS TOTAL PER CALL
------------------------------------------- ------- --------
SQL CALLS.................................: 1501547
GETPAGE REQUEST...........................: 5243681 3
GETPAGE FAILURE.........................: 0 0
PREFETCH REQUEST..........................: 651374 0
SEQUENTIAL..............................: 0 0
LIST....................................: 0 0
DYNAMIC.................................: 651374 0
PFETCH PAGES READ.........................: 14885K 10
BPOOL UPDATES.............................: 0 0
TOTAL TOTAL
TYPE OF WAIT NUMBER WAIT TIME
---------------------------------------------------- -------- ----------
I/O WAITS
SYNC I/O.........................................: 2127219 1:42:41.31
ASYNC READ I/O...................................: 542510 31:47.8706
ASYNC WRITE I/O..................................: 0 N/
LOG WRITE I/O....................................: 0 N/
COMMIT PHASE 1 WRITE I/O WAIT....................: 0 N/
ARCHIVE LOG......................................: 0 N/
TAPE ARCHIVE READ................................: 0 N/ |
|
|
| Back to top |
|
 |
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
the statement was executed about 1.5 millions times
the I/O does not depend on the query per se, but from the <data/row> access pattern and <data/row> distribution
if the data accessed is <uniformly> spread across the whole database/table
no query/buffering optimization will get rid of the I/O,
unless You make the buffer pool capable of holding ...
number of rows returned for a select times 1500000,
but then You will face problems somewhere else |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
if x_amt is not updated often, a possible solution could be to add x_amt in the index as an include column.
You didn't mention the clusterratio of the index and whether you are allowed to manipulate the order of the input-records/variables. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
How many rows are in the table? How many of these rows must be "touched" by the process?
Sometimes unloading data and working from a flat file performs far better than a query run over and over or some "monster query". Yours does not appear to be a "monster" by itself, but the number of executions would surely drive up the cost.
It may help someone help you if you explain the entire process briefly and what this particular query does for the process. Possibly there are alternatives . . .
Edit: Didn't see the post from Guy . . .  |
|
| Back to top |
|
|
|
|
