Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2004
    Posts
    306

    Unanswered: Performance SQL or Reorg?

    DB2 8.2 AIX 5.3

    Hey all,

    I'm experiencing allegedly sluggish performance on a lot of DB activitiy, so in order to try and get to the root of it I'm analysing a single task that is especially slow.

    This statement in particular appears very slow as do several others using the IHEAD table. This one has an average execution time of almost 200,000ms

    Code:
    SELECT * FROM "IHEAD" WHERE "CLIENT" = ? AND "WI_TYPE" IN ( ? , ? , ? , ? ) AND "I_STAT" = ? AND "RETRY_CNT" = ? AND ( "I_CD" = ? AND "I_CT" <= ? OR "I_CD" < ? )
    I also note that this table has a fair ammount of Page Reorgs and Overflow Access.

    Now I've run a reorg in a temporary tablespace using the tables primary index but that hasn't helped....so I was looking for some tips...also tips on which type of reorg to use in general would be ace!

    Cheers

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Please post the DDL including all indexes for the IHEAD table. What is the access plan for the query? What is the output of REORGCHK for the table?

    Andy

  3. #3
    Join Date
    Jul 2004
    Posts
    306
    DDL:

    Code:
     CREATE TABLE "SCH1   "."IHEAD"  (
    		  "CLIENT" CHAR(3) NOT NULL WITH DEFAULT '000' , 
    		  "I_ID" CHAR(12) NOT NULL WITH DEFAULT '000000000000' , 
    		  "I_TYPE" CHAR(1) NOT NULL WITH DEFAULT ' ' , 
    		  "I_CREATOR" VARCHAR(90) NOT NULL WITH DEFAULT ' ' , 
    		  "I_LANG" CHAR(1) NOT NULL WITH DEFAULT ' ' , 
    		  "I_TEXT" VARCHAR(120) NOT NULL WITH DEFAULT ' ' , 
    		  "I_RHTEXT" VARCHAR(40) NOT NULL WITH DEFAULT ' ' , 
    		  "I_STAT" VARCHAR(12) NOT NULL WITH DEFAULT ' ' , 
    		  "I_CD" VARCHAR(8) NOT NULL WITH DEFAULT '00000000' , 
    		  "I_CT" VARCHAR(6) NOT NULL WITH DEFAULT '000000' , 
    		  "I_AED" VARCHAR(8) NOT NULL WITH DEFAULT '00000000' , 
    		  "I_AAGENT" VARCHAR(12) NOT NULL WITH DEFAULT ' ' , 
    		  "I_AADDR" VARCHAR(28) NOT NULL WITH DEFAULT ' ' , 
    		  "I_CRUSER" VARCHAR(12) NOT NULL WITH DEFAULT ' ' , 
    		  "I_CRADDR" VARCHAR(28) NOT NULL WITH DEFAULT ' ' , 
    		  "I_CHCKWI" CHAR(12) NOT NULL WITH DEFAULT '000000000000' , 
    		  "I_CBFB" VARCHAR(30) NOT NULL WITH DEFAULT ' ' , 
    		  "I_RH_TASK" VARCHAR(14) NOT NULL WITH DEFAULT ' ' , 
    		  "I_PRIO" CHAR(1) NOT NULL WITH DEFAULT '0' , 
    		  "I_CONFIRM" CHAR(1) NOT NULL WITH DEFAULT ' ' , 
    		  "I_COMP_EV" CHAR(1) NOT NULL WITH DEFAULT ' ' , 
    		  "I_FORW_BY" VARCHAR(12) NOT NULL WITH DEFAULT ' ' , 
    		  "I_DISABLE" CHAR(1) NOT NULL WITH DEFAULT ' ' , 
    		  "I_REJECT" CHAR(1) NOT NULL WITH DEFAULT ' ' , 
    		  "TCLASS" VARCHAR(12) NOT NULL WITH DEFAULT ' ' , 
    		  "NOTE_EXIST" CHAR(1) NOT NULL WITH DEFAULT ' ' , 
    		  "NOTIFY" CHAR(1) NOT NULL WITH DEFAULT ' ' , 
    		  "RETRY_CNT" CHAR(2) NOT NULL WITH DEFAULT '00' , 
    		  "MAX_RETRY" CHAR(2) NOT NULL WITH DEFAULT '00' , 
    		  "RC_MODELED" CHAR(1) NOT NULL WITH DEFAULT ' ' , 
    		  "CB_DONE" CHAR(1) NOT NULL WITH DEFAULT ' ' , 
    		  "HANDLE" CHAR(12) NOT NULL WITH DEFAULT '000000000000' , 
    		  "SYNCCHAIN" CHAR(1) NOT NULL WITH DEFAULT ' ' , 
    		  "NO_DEADL" CHAR(1) NOT NULL WITH DEFAULT ' ' , 
    		  "I_DH_STAT" CHAR(4) NOT NULL WITH DEFAULT '0000' , 
    		  "I_RELEASE" VARCHAR(4) NOT NULL WITH DEFAULT ' ' , 
    		  "WLC_DISPL" CHAR(1) NOT NULL WITH DEFAULT ' ' , 
    		  "I_RESTLOG" CHAR(1) NOT NULL WITH DEFAULT ' ' , 
    		  "REMOTE_ID" CHAR(12) NOT NULL WITH DEFAULT '000000000000' , 
    		  "REMOTE_SYS" VARCHAR(10) NOT NULL WITH DEFAULT ' ' , 
    		  "STATUS_EVT" CHAR(1) NOT NULL WITH DEFAULT ' ' , 
    		  "TIMEOUT" INTEGER NOT NULL WITH DEFAULT 0 , 
    		  "WF_TYPE" CHAR(4) NOT NULL WITH DEFAULT '0000' , 
    		  "WLC_FLAGS" CHAR(4) FOR BIT DATA , 
    		  "XMLPROT" CHAR(2) NOT NULL WITH DEFAULT '00' , 
    		  "WAIT4CB" CHAR(1) NOT NULL WITH DEFAULT ' ' , 
    		  "TOP_I_ID" CHAR(12) NOT NULL WITH DEFAULT '000000000000' , 
    		  "DEF_GUID" VARCHAR(40) NOT NULL WITH DEFAULT ' ' , 
    		  "EXEC_TIME" INTEGER NOT NULL WITH DEFAULT 0 , 
    		  "NOTE_COUNT" INTEGER NOT NULL WITH DEFAULT 0 , 
    		  "PRS_PROFIL" CHAR(4) NOT NULL WITH DEFAULT '0000' , 
    		  "CREA_TMP" DECIMAL(21,7) NOT NULL WITH DEFAULT 0 , 
    		  "CHECK_STAT" CHAR(2) NOT NULL WITH DEFAULT '00' )   
    		 IN "DHS#BTABD" INDEX IN "DHS#BTABI" ; 
    -- DDL Statements for indexes on Table "SCH1   "."IHEAD"
    
    CREATE UNIQUE INDEX "SCH1   "."IHEAD~0" ON "SCH1   "."IHEAD" 
    		("CLIENT" ASC,
    		 "I_ID" ASC) ALLOW REVERSE SCANS;
    
    -- DDL Statements for indexes on Table "SCH1   "."IHEAD"
    
    CREATE INDEX "SCH1   "."IHEAD~A" ON "SCH1   "."IHEAD" 
    		("I_RH_TASK" ASC,
    		 "I_CD" ASC,
    		 "I_STAT" ASC) ALLOW REVERSE SCANS;
    
    -- DDL Statements for indexes on Table "SCH1   "."IHEAD"
    
    CREATE INDEX "SCH1   "."IHEAD~B" ON "SCH1   "."IHEAD" 
    		("I_CHCKWI" ASC,
    		 "I_TYPE" ASC,
    		 "I_STAT" ASC,
    		 "I_RH_TASK" ASC,
    		 "I_CD" ASC) ALLOW REVERSE SCANS;
    
    -- DDL Statements for indexes on Table "SCH1   "."IHEAD"
    
    CREATE INDEX "SCH1   "."IHEAD~C" ON "SCH1   "."IHEAD" 
    		("I_TYPE" ASC,
    		 "I_STAT" ASC,
    		 "I_RH_TASK" ASC) ALLOW REVERSE SCANS;
    
    -- DDL Statements for indexes on Table "SCH1   "."IHEAD"
    
    CREATE INDEX "SCH1   "."IHEAD~D" ON "SCH1   "."IHEAD" 
    		("TOP_I_ID" ASC) ALLOW REVERSE SCANS;
    
    
    -- DDL Statements for primary key on Table "SCH1   "."IHEAD"
    
    ALTER TABLE "SCH1   "."IHEAD" 
    	ADD CONSTRAINT "IHEAD~0" PRIMARY KEY
    		("CLIENT",
    		 "I_ID");
    Reorgchk indicates the table doesn't need a reorg but that some indexes are marked for F4
    Code:
    IHEAD~A         1e+06  2109     0     3    33   655  17992  37  75   4   0   0 *----
    IHEAD~B         1e+06 16608     0     4    46  1043  1e+06  74  84  29   0   0 *----
    IHEAD~C         1e+06  1928     0     3    24   632    118  37  74   6   0   0 *----

  4. #4
    Join Date
    Jul 2004
    Posts
    306
    Sorry.... the access plan:

    Code:
        Total Cost:   1.08989e+06
        Query Degree:  1
    
                         Rows
                        RETURN
                        (   1)
                         Cost
                          I/O
                          |
                        2951.95
                        NLJOIN
                        (   2)
                      1.08989e+06
                        612621
                  /--------+-------\
                4                  737.987
             TBSCAN                FETCH
             (   3)                (   6)
           0.00453035              272549
                0                  153155
               |                 /----+----\
                4         1.16209e+06    1.16209e+06
             SORT           IXSCAN     TABLE: SCH1
             (   4)         (   7)        IHEAD
           0.00339193        14808
                0           7827.21
               |              |
                4         1.16209e+06
             TBSCAN     INDEX: SCH1
             (   5)       IHEAD~0
           9.42327e-05
                0
               |
                4
        TABFNC: SYSIBM
             GENROW

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    We had a similiar situation a few months ago. We had these queries that took a very long time, but were using the appropriate indexes. The REORGCHK did not necessarily indicate that a reorg was necessary, but we went ahead and did it to the tables and the query went from taking about 24 hours to taking less than 1 hour. What I would suggest is that you do the following:

    1) reorg the table and using clustering (I would suggest the PK): REORG TABLE sch1.ihead index sch1.ihead~0 allow no access
    2) reorg the indexes (convert to type 2 if necessary): REORG INDEXES ALL FOR TABLE sch1.ihead allow no access CONVERT
    3) runstats ont the table: runstats on table sch1.ihead on all columns with distribution on all columns and detailed indexes all
    4) rebind all packages that use the table.

    Andy

  6. #6
    Join Date
    Jul 2004
    Posts
    306
    Quote Originally Posted by ARWinner
    We had a similiar situation a few months ago. We had these queries that took a very long time, but were using the appropriate indexes. The REORGCHK did not necessarily indicate that a reorg was necessary, but we went ahead and did it to the tables and the query went from taking about 24 hours to taking less than 1 hour. What I would suggest is that you do the following:

    1) reorg the table and using clustering (I would suggest the PK): REORG TABLE sch1.ihead index sch1.ihead~0 allow no access
    2) reorg the indexes (convert to type 2 if necessary): REORG INDEXES ALL FOR TABLE sch1.ihead allow no access CONVERT
    3) runstats ont the table: runstats on table sch1.ihead on all columns with distribution on all columns and detailed indexes all
    4) rebind all packages that use the table.

    Andy
    Cheers Andy.. I believe I've tried 1, 2 & 3 already (I just double checked the Type 2 thing with INSPECT CHECK command). I haven't done a rebind so I'll try that next....if that doesn't help I'll repeat steps 1,2 and 4 again in order to be sure...

    Actually woukd you say a db2rbind /all would be a good idea?

    THANKS!

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by meehange
    Cheers Andy.. I believe I've tried 1, 2 & 3 already (I just double checked the Type 2 thing with INSPECT CHECK command). I haven't done a rebind so I'll try that next....if that doesn't help I'll repeat steps 1,2 and 4 again in order to be sure...

    Actually woukd you say a db2rbind /all would be a good idea?

    THANKS!
    You should do the runstats after the reorgs (step 3). This has been recommended by IBM.

    Andy

  8. #8
    Join Date
    Jul 2004
    Posts
    306
    Quote Originally Posted by ARWinner
    You should do the runstats after the reorgs (step 3). This has been recommended by IBM.

    Andy
    Hehe oops yes, I meant 1,3 & 4 :P

  9. #9
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You should also reorg the indexes (without the CONVERT) -- step 2. I suggested doing the CONVERT just in case you had type-1 indexes. But even if you have type-2 indexes, you should do a reorg on them also.

    Andy

  10. #10
    Join Date
    Jul 2004
    Posts
    306
    OK so I've dug a little deeper and it seems that the code where this SQL is run has some hints that set the OPTLEVEL to 0 .... it doesn't look like I will be able to get the code altered, and I don't believe there is any way to override this, am I correct?

  11. #11
    Join Date
    Jan 2003
    Posts
    1,605
    mmehange, if optlevel is set in application I don't think it can be overwritten, but if not in app there is dft_queryopt db parameter.

    Get into on Unix/Linux:
    db2 get db cfg for <db_name> | grep DFT_QUERYOPT

    Get into on Windows:
    db2 get db cfg for <db_name> | find "DFT_QUERYOPT"

    You can update this value to desired value to for example 1.
    db2 update db cfg for <db_name> using dft_queryopt 1

    But don't forget that this info is default value for all queries on database.

    Hope this helps,
    Grofaty

  12. #12
    Join Date
    Jan 2003
    Posts
    1,605
    xxxxxxxxxxxxxxxxxxxxxxxxxx
    Last edited by grofaty; 07-19-07 at 04:52.

Posting Permissions

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