Home   |   Register   |   Rules   |   Calendar   |   Get Daily   |     |  
 


If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > Performance SQL or Reorg?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 07-10-07, 03:27
meehange meehange is offline
Registered User
  
Join Date: Jul 2004
Posts: 256
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
Reply With Quote
  #2 (permalink)  
Old 07-10-07, 08:16
ARWinner ARWinner is offline
Registered User
  
Join Date: Jan 2003
Posts: 3,575
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
Reply With Quote
  #3 (permalink)  
Old 07-10-07, 21:07
meehange meehange is offline
Registered User
  
Join Date: Jul 2004
Posts: 256
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 *----
Reply With Quote
  #4 (permalink)  
Old 07-10-07, 21:12
meehange meehange is offline
Registered User
  
Join Date: Jul 2004
Posts: 256
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
Reply With Quote
  #5 (permalink)  
Old 07-11-07, 08:30
ARWinner ARWinner is offline
Registered User
  
Join Date: Jan 2003
Posts: 3,575
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
Reply With Quote
  #6 (permalink)  
Old 07-11-07, 22:09
meehange meehange is offline
Registered User
  
Join Date: Jul 2004
Posts: 256
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!
Reply With Quote
  #7 (permalink)  
Old 07-12-07, 08:12
ARWinner ARWinner is offline
Registered User
  
Join Date: Jan 2003
Posts: 3,575
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
Reply With Quote
  #8 (permalink)  
Old 07-12-07, 20:36
meehange meehange is offline
Registered User
  
Join Date: Jul 2004
Posts: 256
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
Reply With Quote
  #9 (permalink)  
Old 07-13-07, 08:04
ARWinner ARWinner is offline
Registered User
  
Join Date: Jan 2003
Posts: 3,575
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
Reply With Quote
  #10 (permalink)  
Old 07-18-07, 23:45
meehange meehange is offline
Registered User
  
Join Date: Jul 2004
Posts: 256
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?
Reply With Quote
  #11 (permalink)  
Old 07-19-07, 03:30
grofaty grofaty is offline
Registered User
  
Join Date: Jan 2003
Posts: 1,570
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
Reply With Quote
  #12 (permalink)  
Old 07-19-07, 03:35
grofaty grofaty is offline
Registered User
  
Join Date: Jan 2003
Posts: 1,570
xxxxxxxxxxxxxxxxxxxxxxxxxx
Last edited by grofaty; 07-19-07 at 03:52.
Reply With Quote
Reply

« Previous Thread | Next Thread »
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


 
 
 
Home  |   Register  |   Get New  |   FAQ's  |   Sitemap

Powered by vBulletin
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.