Below is the "Explain SQL" from an extremely SLOW stored proc.
I see:
SQL4010 Table scan access for table 1
How do I identify "Table 1"?
I also wonder about lines ... #55 and #93
How might I deduce why it 619 seconds and 19435 seconds?
1 - 5722SS1 V5R3M0 040528 Print SQL information Program CSIDEV/DVLCHRT 09/30/08 12:13:00 Page 1
2 - Object name...............CSIDEV/
DVLCHRT
3 - Object type...............*PGM
4 - CRTSQLCI
5 - OBJ(CSIDEV/DVLCHRT)
6 - SRCFILE(QTEMP/QSQLSRC)
7 - SRCMBR(DVLCHRT)
8 - COMMIT(*NONE)
9 - OPTION(*SQL *PERIOD *NOCNULRQD)
10 - TGTRLS(V5R3M0)
11 - ALWCPYDTA(*OPTIMIZE)
12 - CLOSQLCSR(*ENDACTGRP)
13 - RDB(*LOCAL)
14 - DATFMT(*ISO)
15 - TIMFMT(*ISO)
16 - DFTRDBCOL(*NONE)
17 - DYNDFTCOL(*NO)
18 - SQLPKG(CSIDEV/DVLCHRT)
19 - ALWBLK(*ALLREAD)
20 - DLYPRP(*YES)
21 - DYNUSRPRF(*USER)
22 - SRTSEQ(*HEX)
23 - LANGID(ENU)
24 - RDBCNNMTH(*DUW)
25 - TEXT('SQL PROCEDURE DVLCHR ')
26 - STATEMENT TEXT CCSID(37)
27 - SQLPATH("QSYS" "QSYS2" "CISFGV")
28 - DECRESULT(31 31 0)
29 - DECLARE C1 CURSOR FOR SELECT DISTINCT LOC AS COLNAME FROM SESSION . ITEMS WHERE
30 - LOC > '' ORDER BY COLNAME
31 - SQL4021 Access plan last saved on 09/30/08 at 11:46:45.
32 - SQL4020 Estimated query run time is 0 seconds.
33 - SQL4002 Reusable ODP sort used.
34 - SQL4010 Table scan access for table 1.
35 - SQL4029 Hashing algorithm used to process the grouping.
36 - DECLARE C2 CURSOR FOR SELECT DISTINCT MTHYR AS ROWNAME , YYYY , MM FROM SESSION
37 - . ITEMS ORDER BY YYYY , MM
38 - SQL4021 Access plan last saved on 09/30/08 at 11:46:45.
39 - SQL4020 Estimated query run time is 0 seconds.
40 - SQL4002 Reusable ODP sort used.
41 - SQL4010 Table scan access for table 1.
42 - DECLARE C3 CURSOR FOR SELECT LOC AS COLNAME , MTHYR AS ROWNAME , DIVAMT AS VALU
43 - FROM SESSION . ITEMS
44 - SQL4021 Access plan last saved on 09/30/08 at 11:46:45.
45 - SQL4020 Estimated query run time is 0 seconds.
46 - SQL4010 Table scan access for table 1.
47 - DECLARE C4 CURSOR FOR SELECT SUM ( CSIDEV . DVL . EXTENDED_TOTAL_ALLOC ) AS
48 - MTHAMT , CSIDEV . DVL . MONNAME || ' ' || CAST ( CSIDEV . DVL . YYYY AS VARCHAR
49 - ( 4 ) ) AS MTHYR , CSIDEV . DVL . YYYY , CSIDEV . DVL . MM FROM CSIDEV . DVL
50 - GROUP BY CSIDEV . DVL . FISCALYYYY , CSIDEV . DVL . MONNAME || ' ' || CAST (
51 - CSIDEV . DVL . YYYY AS VARCHAR ( 4 ) ) , CSIDEV . DVL . YYYY , CSIDEV . DVL .
52 - MM HAVING ( CSIDEV . DVL . FISCALYYYY = : H : H ) ORDER BY CSIDEV . DVL . YYYY ,
53 - CSIDEV . DVL . MM
54 - SQL4021 Access plan last saved on 09/30/08 at 11:46:46.
55 - SQL4020 Estimated query run time is 619 seconds.
56 - SQL4017 Host variables implemented as reusable ODP.
57 - SQL4002 Reusable ODP sort used.
58 - SQL402A Hashing algorithm used to process join.
59 - SQL402C Temporary table created for hash join results.
60 - SQL402B Table 4XXXXXX used in hash join step 1.
61 - SQL402B Table 1XXXXXX used in hash join step 1.
62 - SQL402B Table 3XXXXXX used in hash join step 2.
63 - SQL402B Table 2XXXXXX used in hash join step 3.
64 - DECLARE GLOBAL TEMPORARY TABLE ITEMS ( LOC VARCHAR ( 4 ) , DIVAMT INTEGER ,
65 - MTHYR VARCHAR ( 50 ) , YYYY INTEGER , MM INTEGER ) WITH REPLACE NOT LOGGED
66 - SELECT FISCALYYYY INTO : H : H FROM CSIDEV . YMD WHERE DAYDATE = CURRENT DATE
67 - SQL4021 Access plan last saved on 09/29/08 at 15:35:36.
68 - SQL4020 Estimated query run time is 0 seconds.
69 - SQL4008 Index YMD_INDX used for table 1.
70 - SQL4011 Index scan-key row positioning used on table 1.
71 - SQL4006 All indexes considered for table 1.
72 - INSERT INTO SESSION . ITEMS ( LOC , DIVAMT , MTHYR , YYYY , MM ) SELECT
73 - DISTINCT '' , 0 , CSIDEV . YMD . MONNAME || ' ' || CAST ( YYYY AS VARCHAR ( 4 )
74 - ) AS MTHYR , CSIDEV . YMD . YYYY , CSIDEV . YMD . MM FROM CSIDEV . YMD WHERE
75 - FISCALYYYY = : H : H
76 - SQL4021 Access plan last saved on 09/30/08 at 11:45:39.
77 - SQL4020 Estimated query run time is 1 seconds.
78 - SQL4010 Table scan access for table 1.
79 - SQL4021 Access plan last saved on 09/29/08 at 15:35:36.
80 - SQL4020 Estimated query run time is 0 seconds.
81 - SQL4010 Table scan access for table 1.
82 - INSERT INTO SESSION . ITEMS ( LOC , DIVAMT , MTHYR , YYYY , MM ) SELECT CSIDEV
83 - . DVL . LOC , SUM ( CSIDEV . DVL . EXTENDED_TOTAL_ALLOC ) AS DIVAMT , CSIDEV .
84 - DVL . MONNAME || ' ' || CAST ( CSIDEV . DVL . YYYY AS VARCHAR ( 4 ) ) AS MTHYR ,
85 - CSIDEV . DVL . YYYY , CSIDEV . DVL . MM FROM CSIDEV . DVL GROUP BY CSIDEV .
86 - DVL . LOC , CSIDEV . DVL . FISCALYYYY , CSIDEV . DVL . MONNAME || ' ' || CAST (
87 - CSIDEV . DVL . YYYY AS VARCHAR ( 4 ) ) , YYYY , MM HAVING ( CSIDEV . DVL .
88 - FISCALYYYY = : H : H )
89 - SQL4021 Access plan last saved on 09/30/08 at 11:46:45.
90 - SQL4020 Estimated query run time is 1 seconds.
91 - SQL4010 Table scan access for table 1.
92 - SQL4021 Access plan last saved on 09/30/08 at 11:45:40.
93 - SQL4020 Estimated query run time is 19435 seconds.
94 - SQL4017 Host variables implemented as reusable ODP.
95 - SQL4001 Temporary result created.
96 - SQL402A Hashing algorithm used to process join.
97 - SQL402C Temporary table created for hash join results.
98 - SQL402B Table 4XXXXXX used in hash join step 1.
99 - SQL402B Table 1XXXXXX used in hash join step 1.
100 - SQL402B Table 3XXXXXX used in hash join step 2.
101 - SQL402B Table 2XXXXXX used in hash join step 3.
102 - OPEN C1
103 - OPEN C2
104 - OPEN C3
105 - OPEN C4
106 - * * * * * E N D O F L I S T I N G * * * * *
Start Free Trial
