Topic
No replies
SystemAdmin
SystemAdmin
228 Posts
ACCEPTED ANSWER

Pinned topic IDS 11.50.TC3: unusable SQL query plan

‏2008-12-10T12:48:58Z |
See the following excerpt from onstat -g his:


Statement # 34461:     @ 13901818   Database:        0x500002 Statement text: SELECT F.TXT[1,10] reason, SUM ( W.WNUM ) numwaits, ( SUM ( W.WCUMTIME )/1000000 ) 
"wait time (s)", SUM ( W.WCUMTIME )/ ( SUM ( W.WNUM ) *1000 ) 
"avg. wait (ms)" FROM SYSMASTER:SYSSCBLST S, SYSMASTER:SYSRSTCB R, SYSMASTER:SYSTWAITS W, SYSMASTER:FLAGS_TEXT F WHERE S.SID = R.SID AND R.TID = W.TID AND W.WREASON = F.FLAGS AND F.TABNAME = 
'systwaits' AND F.TXT  
'condition' AND W.WCUMTIME > 0 and W.WNUM  0 GROUP BY 1 ORDER BY 3 DESC   SELECT using tables [ sysscblst sysrstcb systwaits flags_text ]   Iterator/Explain ================ ID   Left  Right   Est Cost   Est Rows   Num Rows    Partnum Type 6      0      0          4         16         21    1048832 Index Scan 7      0      0          9         30        126         43 Seq Scan 5      6      7         22         24        108          0 Hash Join 8      0      0          9        100         20         26 Seq Scan 4      5      8         53         25         63          0 Hash Join 9      0      0          1        100          1         40 Index Scan 3      4      9         93         25         63          0 Nested Join 2      3      0          1          1         10          0 Group 1      2      0          1          1         10          0 Sort   Statement information: Sess_id  User_id  Stmt Type        Finish Time    Run Time   TX Stamp   PDQ 174      1        SELECT           13:07:32       9251125412.6703 c9ca2e1    0   Statement Statistics: Page       Buffer     Read       Buffer     Page       Buffer     Write Read       Read       % Cache    IDX Read   Write      Write      % Cache 0          51         100.00     0          0          0          0.00   Lock       Lock       LK Wait    Log        Num        Disk       Memory Requests   Waits      Time (S)   Space      Sorts      Sorts      Sorts 45         0          0.0000     0.000 B    2          0          2   Total      Total      Avg        Max        Avg        I/O Wait   Avg Rows Executions Time (S)   Time (S)   Time (S)   IO Wait    Time (S)   Per Sec 1          9251125412.6802 9251125412.6802 9251125412.6703 0.000000   0.000000   0.0000   Estimated  Estimated  Actual     SQL        ISAM       Isolation  SQL Cost       Rows       Rows       Error      Error      Level      Memory 93         1          10         0          0          CR         48944


In the section Iterator/Explain it tells one partnum that is valid (1048832) the others aren't (23, 26, 40) => no chance to find the table that is meant here. Further it says "Index Scan" but does not tell which index is used. In this particular case the table flags_text has only one index. In real life this is not always the case and then you can't tell if the right index is used or not. In this form the SQL trace is almost useless for SQL query tuning. Besides there are also pointlessly high time values.

Regards
Christian