Bart,
another good thought, but we are talking about tables that have upwards of 10,000,000 rows and the index will return 1 to 10 rows. I can not think of any reason why the optimizer would want to scan that much data to return 1 to 10 rows.
PL
> To: "LazyDBA Discussion" <[Email address protected]
> From: "Coffman Bart \(STL\) " <db2udbdba-ezmlmshield-x43072454.[Email address protected]
> Date: Tue, 23 May 2006 10:56:19 -0500
> Subject: RE: RE: Index usage
>
> Quick question...Did you say you had performance problems? If your
> tables are small enough, a table scan is faster than using indexes.
>
> One more point. Fixpack 8 was replaced by 8a due to a security issue.
> Just fyi.
> http://www-306.ibm.com/software/data/db2/udb/support/downloadv8_aix5.htm
> l
>
> J. Bart Coffman
> Application DBA
> Express Scripts, Inc.
> 314.702-7982 (w)
> 314.265-8883 (c)
> [Email address protected]
>
>
>
> -----Original Message-----
> From: Coffman Bart (STL)
> [mailto:db2udbdba-ezmlmshield-x72431697.[Email address protected]
> Sent: Tuesday, May 23, 2006 10:34 AM
> To: LazyDBA Discussion
> Subject: RE: RE: Index usage
>
> Good points. Is there anything in your app scripts that messes with any
> optimization parms?
>
> If I didn't get anything different when doing steps below, I would open
> a ticket with IBM. Some setting seems to be amiss, or else there is
> corruption...
>
> J. Bart Coffman
> Application DBA
> Express Scripts, Inc.
> 314.702-7982 (w)
> 314.265-8883 (c)
> [Email address protected]
>
>
>
> -----Original Message-----
> From: DANIEL CALLAHAN [mailto:db2udbdba-ezmlmshield-
> Sent: Tuesday, May 23, 2006 10:30 AM
> To: LazyDBA Discussion
> Subject: RE: RE: Index usage
>
>
>
>
>
> Maybe even better drop the indices. run db2advis , create the new
> indices
> exactly like what db2advis says, runstats and rebind.
>
> Is it possible this application is resetting the optimization level
> before
> executing ?
>
>
>
>
>
>
>
> "Coffman Bart
>
> \(STL\) "
>
> <db2udbdba-ezmlms
> To
> a.com>
> cc
>
>
> 05/23/2006 11:20
> Subject
> AM RE: RE: Index usage
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> If db2advis is calling out indexes that already exist, doesn't this mean
> that he cannot see them? Anyone confirm this? But then again your prod
> is acting the same way. Were all the db's created from a restore of one
> another?
>
> Seems like maybe you need to drop and re-create indexes???
>
> J. Bart Coffman
> Application DBA
> Express Scripts, Inc.
> 314.702-7982 (w)
> 314.265-8883 (c)
> [Email address protected]
>
>
>
> -----Original Message-----
> Sent: Tuesday, May 23, 2006 9:59 AM
> To: LazyDBA Discussion
> Subject: Fwd: RE: Index usage
>
> Bart,
>
> all excelelnt suggestions, unfortunately all tried already. Funny,
> db2advis gives me indexes that exist already and then when I apply an
> index or two that does not, it still does nothing to change the access
> path, even though I create the index and issue a runstats on table and
> indexes all. Rebind is the olnly thing I have not tried, good catch.
> Join column is the first column in the indthey are all type 2 indexes...
>
> PL
>
> To: "LazyDBA Discussion" <[Email address protected]
> address protected]
> > Date: Tue, 23 May 2006 09:51:20 -0500
> > Subject: RE: Index usage
> >
> > Be sure you have done the runstats as mentioned below, and do a rebind
> > on all affected packages. Assume your indexes are type-2? Do you
> have
> > primary key indexes in place? Are the join columns in the leading
> > position in any of the indexes?
> >
> > Also, try running some of the sql through db2advis tool to see what it
> > says. Maybe you will see something there.
> >
> > J. Bart Coffman
> > Application DBA
> > Express Scripts, Inc.
> > 314.702-7982 (w)
> > 314.265-8883 (c)
> > [Email address protected]
> >
> >
> >
> > -----Original Message-----
> > From: DANIEL CALLAHAN [mailto:db2udbdba-ezmlmshield- Sent: Tuesday,
> May
> > 23, 2006 9:42 AM
> > To: LazyDBA Discussion
> > Subject: Re: Index usage
> >
> >
> >
> >
> >
> > Can we assume you have done runstats?
> > and maybe set the optimization to at least 3
> > and make the table volatile, if the activity is high?
> >
> >
> >
> >
> >
> >
> >
> > "paul"
> >
> > <db2udbdba-ezmlms
> >
> > a.com> <[Email address protected]
> >
> >
> > cc
> > 05/23/2006 10:31
> >
> > AM
> > Subject
> > Index usage
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > Hello all,
> >
> > Have a rather strange occerence here that is perplexing me and am in
> > need
> > of a little guidance. Running AIX 5.3 UDB "DB2 v8.1.1.80",
> "s041221",
> > "U800400", and FixPak "8". I have many occurrences of specifying a
> join
> > on
> > two tables on an index, but the optimizer is determined to do a table
> > scan.
> > I would like to avoid optimizer hints as they could bite me in the
> > preverbial rear down the road. I did find where I had the DFT_DEGREE
> > set
> > to one, shich of course is no paralellism at all. Are there any other
> > DB
> > parameters that you are aware of that I should be looking at before
> > going
> > after fix packs?
> >
> > TIA
> >
> > PL
> >
> >
> > ---------------------------------------------------------------------
> > PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
> > website: http://www.LazyDBA.com
> > To unsubscribe: http://www.lazydba.com/unsubscribe.html
> >
> >
> >
> >
> >
> > ---------------------------------------------------------------------
> > PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
> > website: http://www.LazyDBA.com
> > To unsubscribe: http://www.lazydba.com/unsubscribe.html
> >
>
>
> ******* Confidentiality Notice *******
> This email, its electronic document attachments, and the contents of its website linkages may contain confidential health information. This information is intended solely for use by the individual or entity to whom it is addressed. If you have received this information in error, please notify the sender immediately and arrange for the prompt destruction of the material and any accompanying attachments.
>
>
>
>
>
> ---------------------------------------------------------------------
> PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
> website: http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
DB2 & UDB email list listserv db2-l LazyDBA home page