Results 1 to 7 of 7
  1. #1
    Join Date
    May 2006
    Posts
    82

    Unanswered: Query takes 67% of the CPU

    Hi All,

    I have a complaint from DBA saying that one the queries takes 67% of the CPU. Query has left outer join. I copied this query from one of the existing packages to do the same function. This query is used in online program.

    DBA has sent me the following information which i could not decode.

    SQL_CALL STMTÆ SECTÆ SQL TIMEPCT CPUPCT INDB2_TIME INDB2_CPU
    -------- ----- ----- ---------- ------- ------- ------------ ------------
    SELECT 01451 00002 5476504 22.09% 39.64% 00:00.000232 00:00.000090
    OPEN 01390 00001 12810 63.09% 31.13% 00:00.283133 00:00.030186
    SELECT 01490 00004 5431293 6.23% 11.79% 00:00.000066 00:00.000027
    SELECT 01473 00003 5476504 2.76% 10.13% 00:00.000029 00:00.000023
    FETCH 01550 00001 5431746 1.22% 5.24% 00:00.000013 00:00.000012
    SELECT 01519 00005 107072 4.58% 1.92% 00:00.002461 00:00.000223
    CLOSE 01564 00001 12811 .00% .00% 00:00.000009 00:00.000008

    I dont event know what those columns say. The query is pasted below. query uses the synonyms instead of the qulaified tables.

    DECLARE ERID_CURSOR CURSOR FOR
    SELECT DISTINCT B.INV_ID
    FROM ORG_STRUKTUR_S A
    LEFT OUTER JOIN IP_IP_REL_S B
    ON SUBSTR(A.IP_ID,1,10) = B.INV_ID_2
    WHERE B.IR_KD = 'PRIMAER'
    AND (CURRENT TIMESTAMP <= B.TIL_TS OR B.TIL_TS IS NULL)
    AND A.OEST_NV = 'RAADGIV'
    AND A.OEST_REL_TP IN ('DIREKTE','SAMLAGT','STAB')
    AND A.IP_ID_2 = :USER_ID;

    DBA says its at the OPEN statement where the query is actually executed. EXPLAIN says it is going for Index scan with MATCHCOLS = 3 for first table and MATCHCOLS = 1 for the second table. COST_CATEGORY is B and i guess its because of RANGE predicates in the WHERE clause and Host variable. (Reason for COST CATEGORY is Host variable).

    Do anyone of you has any idea about the data DBA has sent..?. Is that from any of the monitor tools..?. Does COST_TABLE, STRUCTURE_TABLE, PREDICATE_TABLE has something to do with EXPLAIN in DB2 V8 for Z/os..?.

    Sorry for lots of questions. I still have lots of questions. Performance tuning is always been a difficult job for me. Please help me with this issue.

    Here is the explain results:
    PLANNO METHOD TNAME ACCESSTYPE MATCHCOLS INDEXONLY
    ------ ------ ------------------ ---------- --------- ---------
    1 0 ORG_STRUKTUR_S N 3 Y
    2 1 IP_IP_REL_S I 1 N
    3 3 0 N
    Vinay,

  2. #2
    Join Date
    May 2006
    Posts
    82

    Data in the right format and no of rows in table

    Code:
    sql_call  Stmtæ  Sectæ  Sql        Timepct Cpupct  Indb2_time   Indb2_cpu   
    --------  -----  -----  ---------- ------- ------- ------------ ------------
    Select    01451  00002     5476504  22.09%  39.64% 00:00.000232 00:00.000090
    Open      01390  00001       12810  63.09%  31.13% 00:00.283133 00:00.030186
    Select    01490  00004     5431293   6.23%  11.79% 00:00.000066 00:00.000027
    Select    01473  00003     5476504   2.76%  10.13% 00:00.000029 00:00.000023
    Fetch     01550  00001     5431746   1.22%   5.24% 00:00.000013 00:00.000012
    Select    01519  00005      107072   4.58%   1.92% 00:00.002461 00:00.000223
    Close     01564  00001       12811    .00%    .00% 00:00.000009 00:00.000008
    
    
    
     Planno  Method  Tname                  Tabno  Accesstype  Matchcols  Indexonl
     ------  ------  --------------------  ------  ----------  ---------  --------
          1       0  Org_struktur_s             1  N                   3  Y       
          2       1  Ip_ip_rel_s                2  I                   1  N       
          3       3                             0                      0  N
    The no of rows in the tables are:

    IP_IP_REL_S: 18450790 (Last stats gathered on Nov 4th 2007)
    ORG_STRUKTUR_S: 68652(Last stats on DEC 19 2007)
    Vinay,

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by vini_srcna
    Code:
    OPEN      01390  00001       12810  63.09%  31.13% 00:00.283133 00:00.030186
    FETCH     01550  00001     5431746   1.22%   5.24% 00:00.000013 00:00.000012
    Apparently, the "open cursor" takes 63% of the program execution.
    Not exceptional, I would say: it just means that the "real work" is done during "open cursor" and not during the subsequent fetches (since they only take 1% of the execution time).
    This is always the case when "materialization" takes place: the full result set has to be generated (at "open" time) and stored by DB2 before the first line can be returned (by the first FETCH) because sorting is needed for a certain reason.
    Quote Originally Posted by vini_srcna
    Code:
    DECLARE ERID_CURSOR CURSOR FOR                               
     SELECT DISTINCT B.INV_ID                                    
     FROM   ORG_STRUKTUR_S A                                     
     LEFT   OUTER JOIN IP_IP_REL_S B                             
     ON     SUBSTR(A.IP_ID,1,10) = B.INV_ID_2                    
     WHERE  B.IR_KD = 'PRIMAER'                                  
     AND    (CURRENT TIMESTAMP <= B.TIL_TS OR B.TIL_TS IS NULL)  
     AND    A.OEST_NV     = 'RAADGIV'                            
     AND    A.OEST_REL_TP IN ('DIREKTE','SAMLAGT','STAB')        
     AND    A.IP_ID_2 = :USER_ID
    The "DISTINCT" requires a sort, since that's the most efficient way to remove duplicates.
    Remove the DISTINCT and the percentages will be totally different.

    Certainly if your FETCH iteration does not continue until SQLCODE=100, materialization is suboptimal. But if you retrieve all rows from the result set, materialization might even be preferred, since it will e.g. release locks earlier (i.e., after the OPEN) than without materialization.

    By removing the DISTINCT, the third line (METHOD=3) of explain output will disappear.

    B.t.w., the JOIN condition (using SUBSTR) is potentially suboptimal ("stage-2" predicate); are you sure you need to substring to the first 10 positions? If the positions from 11 on are guaranteed to be blanks, you should better write "A.IP_ID = B.INV_ID_2".

    Quote Originally Posted by vini_srcna
    Code:
    PLANNO  METHOD  TNAME               ACCESSTYPE  MATCHCOLS  INDEXONLY
    ------  ------  ------------------  ----------  ---------  ---------
         1       0  ORG_STRUKTUR_S      N                   3  Y        
         2       1  IP_IP_REL_S         I                   1  N        
         3       3                                          0  N
    The join is a nested one, i.e., the indexed access to table B is repeated for every matching row of table A. This need not be suboptimal, though; certainly not if the conditions on A (like A.IP_ID_2 = :USER_ID) are well filtering.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    May 2006
    Posts
    82

    I can remove SUBSTR

    Hi Peter,

    Thanks for your help.

    I can remove SUBSTR. All are spaces after 11 position however we have an in house tool which doesn't allow if there is mismatch in the columns or host variables. (It doenst allow to promote the code if there is mismatch is size for the columns however we can force it to do). In this case
    A.IP_ID is CHAR(20) and B.INV_ID_2 is CHAR(10).

    I can remove DISTINCT and put some programming logic for this.

    How do I materialize the query..?. Is that a MQT Concept..?

    The table on the right side contains 18.5 Million rows.

    Dont you think changing the tables order to the other side in the join will do better. (leftone to right and right one to left). So that ORG Table will go for Nested loop join.

    The filtering factor of the predicate A.IP_ID_2 = :USER_ID is very less.

    This query is from Online program. This issue arised during the peak business hours. There were 68,000 transations with 25 million SQL CALLS in 2 hours and 45 minutes. So executing this query 68,000 times in approx 3 hours will be COSTLY. The right table IP_IP has HUGE data.
    Vinay,

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by vini_srcna
    How do I materialize the query..?
    You don't: DB2 does.
    From the moment you see a "METHOD=3" line in the explain output, DB2 is materializing. (It's also materializing when you see a "Y" in one of the "SORT*" columns.)

    Quote Originally Posted by vini_srcna
    Dont you think changing the tables order to the other side in the join will do better.
    Again, that's up to DB2 which table is chosen as left (= outer) table.
    Quote Originally Posted by vini_srcna
    The right table IP_IP has HUGE data.
    That's OK with the chosen access path (indexed access); its size is not too relevant. That explains why it is not taken as outer table in the nested loop join.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by vini_srcna
    I can remove SUBSTR. All are spaces after 11 position however we have an in house tool which doesn't allow if there is mismatch in the columns or host variables.
    If this is DB2 for z/OS v8 or higher, you will see a substantial benefit from removing the SUBSTR; with DB2 v7 for z/OS, comparing two unequal length columns ("non-matching datatypes") was equally costy as using the SUBSTR.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  7. #7
    Join Date
    May 2006
    Posts
    82

    Thanks

    Thanks a lot peter.

    Wish you Happy new year to all..!!
    Vinay,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •