Index puzzler

Steve Grimes

Index puzzler
Hello,

I rebuilt a 25 million row, 108 partition table in production over the
weekend, and now it's tablescanning for (at least) one major query.

I use VE against this and two other systems (test & training) (which have
nearly identical data and went through a nearly identical process in the
last 30 days.) They use the NPI designed for this query. Production does
not.

Statistics are current for all three systems.
I just did a reorg, shrlevel change with full stats on the NPI I want it
to use, in prod. No change, still table scanning.
I compared Cluster ratios, first and full key cardinality, leaf pages,
levels, stats times, unique rules -- it's (about) the same for all three
systems.

Here's the query:

SELECT *
FROM DBAPROD.FIT_TRAN_SOURCE TRSO
WHERE TRSO.FISCAL_YEAR = 2006
AND TRSO.ACCT_DEPT = '003021'
AND TRSO.ACCT_FUND = ' '
AND TRSO.ACCT_LEDGER = '1'
AND TRSO.ACCT_CLASS = '2'
AND TRSO.TRAN_DESC LIKE 'PRI%';

Here's the index I think it should use (and does use in Test and
Training):

create index DBAPROD.FIXTRSOC on DBAPROD.FIT_TRAN_SOURCE
(FISCAL_YEAR asc, ACCT_DEPT asc, ACCT_FUND asc, ACCT_LEDGER asc,
ACCT_CLASS asc, TRAN_DESC asc)
using stogroup SMSGROUP
priqty 500400
secqty 50400
pctfree 10
freepage 10
COPY NO
bufferpool BP5;
RESULT OF SQL STATEMENT:
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION
create SUCCESSFUL

Any ideas what I may be missing or where I should look next?

TIA!

Stg

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

[login to unmask email]

Re: Index puzzler
(in response to Steve Grimes)
Did you do a rebind after the runstats?

Bud Greenman
Applications Programming Manager



Steve Grimes
<[login to unmask email]
TL.EDU> To
Sent by: DB2 Data [login to unmask email]
Base Discussion cc
List
<[login to unmask email] Subject
ORG> [DB2-L] Index puzzler


01/02/2007 03:45
PM


Please respond to
DB2 Database
Discussion list
at IDUG
<[login to unmask email]
2-L.ORG>







Hello,

I rebuilt a 25 million row, 108 partition table in production over the
weekend, and now it's tablescanning for (at least) one major query.

I use VE against this and two other systems (test & training) (which have
nearly identical data and went through a nearly identical process in the
last 30 days.) They use the NPI designed for this query. Production does
not.

Statistics are current for all three systems.
I just did a reorg, shrlevel change with full stats on the NPI I want it to
use, in prod. No change, still table scanning.
I compared Cluster ratios, first and full key cardinality, leaf pages,
levels, stats times, unique rules -- it's (about) the same for all three
systems.

Here's the query:

SELECT *
FROM DBAPROD.FIT_TRAN_SOURCE TRSO
WHERE TRSO.FISCAL_YEAR = 2006
AND TRSO.ACCT_DEPT = '003021'
AND TRSO.ACCT_FUND = ' '
AND TRSO.ACCT_LEDGER = '1'
AND TRSO.ACCT_CLASS = '2'
AND TRSO.TRAN_DESC LIKE 'PRI%';

Here's the index I think it should use (and does use in Test and Training):


create index DBAPROD.FIXTRSOC on DBAPROD.FIT_TRAN_SOURCE
(FISCAL_YEAR asc, ACCT_DEPT asc, ACCT_FUND asc, ACCT_LEDGER asc,
ACCT_CLASS asc, TRAN_DESC asc)
using stogroup SMSGROUP
priqty 500400
secqty 50400
pctfree 10
freepage 10
COPY NO
bufferpool BP5;
RESULT OF SQL STATEMENT:
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION
create SUCCESSFUL

Any ideas what I may be missing or where I should look next?

TIA!

Stg
---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Victor Bond

Re: Index puzzler
(in response to BudGreenman@ONGOV.NET)
Steve,

What do you mean by "about the same"? What is different? Also, is your
test and production environment at the same release and the same
maintenance level?

________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Steve Grimes
Sent: Tuesday, January 02, 2007 12:36 PM
To: [login to unmask email]
Subject: [DB2-L] Index puzzler



Hello,

I rebuilt a 25 million row, 108 partition table in production over the
weekend, and now it's tablescanning for (at least) one major query.

I use VE against this and two other systems (test & training) (which
have nearly identical data and went through a nearly identical process
in the last 30 days.) They use the NPI designed for this query.
Production does not.

Statistics are current for all three systems.
I just did a reorg, shrlevel change with full stats on the NPI I want it
to use, in prod. No change, still table scanning.
I compared Cluster ratios, first and full key cardinality, leaf pages,
levels, stats times, unique rules -- it's (about) the same for all three
systems.

Here's the query:

SELECT *
FROM DBAPROD.FIT_TRAN_SOURCE TRSO
WHERE TRSO.FISCAL_YEAR = 2006
AND TRSO.ACCT_DEPT = '003021'
AND TRSO.ACCT_FUND = ' '
AND TRSO.ACCT_LEDGER = '1'
AND TRSO.ACCT_CLASS = '2'
AND TRSO.TRAN_DESC LIKE 'PRI%';

Here's the index I think it should use (and does use in Test and
Training):

create index DBAPROD.FIXTRSOC on DBAPROD.FIT_TRAN_SOURCE
(FISCAL_YEAR asc, ACCT_DEPT asc, ACCT_FUND asc, ACCT_LEDGER asc,
ACCT_CLASS asc, TRAN_DESC asc)
using stogroup SMSGROUP
priqty 500400
secqty 50400
pctfree 10
freepage 10
COPY NO
bufferpool BP5;
RESULT OF SQL STATEMENT:
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION
create SUCCESSFUL

Any ideas what I may be missing or where I should look next?

TIA!

Stg
------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Patrick Bossman

Re: Index puzzler
(in response to Dee Reins)
Hello,
Does the query qualify for limited partition scan based on the predicates?

Check the SYSTABSTATS for the qualified partition(s). Often R-scan is
chosen when the statistics for the qualified partition indicate the
partition is empty.

Regards,
Pat

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Dee Reins

Re: Index puzzler
(in response to Victor Bond)
Update test with production stats, rebind, then see if test starts
scanning. This will give you a clue where to start if it is a
statistics opportunity.



If this is not perspicuous please let me know.
Dee Reins
360 905-7343 Business
360 905-7212 Fax
360 608-5262 Cell

_____

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Steve Grimes
Sent: Tuesday, January 02, 2007 12:36 PM
To: [login to unmask email]
Subject: [DB2-L] Index puzzler




Hello,

I rebuilt a 25 million row, 108 partition table in production over the
weekend, and now it's tablescanning for (at least) one major query.

I use VE against this and two other systems (test & training) (which
have nearly identical data and went through a nearly identical process
in the last 30 days.) They use the NPI designed for this query.
Production does not.

Statistics are current for all three systems.
I just did a reorg, shrlevel change with full stats on the NPI I want it
to use, in prod. No change, still table scanning.
I compared Cluster ratios, first and full key cardinality, leaf pages,
levels, stats times, unique rules -- it's (about) the same for all three
systems.

Here's the query:

SELECT *
FROM DBAPROD.FIT_TRAN_SOURCE TRSO
WHERE TRSO.FISCAL_YEAR = 2006
AND TRSO.ACCT_DEPT = '003021'
AND TRSO.ACCT_FUND = ' '
AND TRSO.ACCT_LEDGER = '1'
AND TRSO.ACCT_CLASS = '2'
AND TRSO.TRAN_DESC LIKE 'PRI%';

Here's the index I think it should use (and does use in Test and
Training):

create index DBAPROD.FIXTRSOC on DBAPROD.FIT_TRAN_SOURCE
(FISCAL_YEAR asc, ACCT_DEPT asc, ACCT_FUND asc, ACCT_LEDGER asc,
ACCT_CLASS asc, TRAN_DESC asc)
using stogroup SMSGROUP
priqty 500400
secqty 50400
pctfree 10
freepage 10
COPY NO
bufferpool BP5;
RESULT OF SQL STATEMENT:
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION
create SUCCESSFUL

Any ideas what I may be missing or where I should look next?

TIA!

Stg
------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Steve Grimes

Re: Index puzzler
(in response to Patrick Bossman)
This was it!

There were no stats on partitions 46-108. I'm not sure why. (The
RUNSTATS through DSNUTILS ended with RC 0.) I'm adding the stats
through batch utility runstats now, and verifying as I go. Hopefully
we'll be in good shape soon.

Thank you Thank you Thank you!

Stg





Patrick Bossman <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
01/02/2007 03:34 PM
Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To
[login to unmask email]
cc

Subject
Re: [DB2-L] Index puzzler






Hello,
Does the query qualify for limited partition scan based on the predicates?

Check the SYSTABSTATS for the qualified partition(s). Often R-scan is
chosen when the statistics for the qualified partition indicate the
partition is empty.

Regards,
Pat

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Larry Kintisch

Re: Index puzzler
(in response to Steve Grimes)
Hi Steve,
  You didn't tell us how many rows you would expect to qualify with those values.
 
  I have a few gueses here.  If you have a 200 byte row, it might "cost" 125 seconds in sequential read eleapsed time.  If your predicates returned more than 12,500 rows it might take that long to do synchronous reads.  You may be near the cusp of going one way or the other.
 
  Perhaps the parallelism bind option was set for production, not for test. Are you using Visual Explain with actual predicate values as you've shown or with place-holders? And are you binding with host variables?  Pat Bossman would know better how the optimizer will respond to h-v's with various statistics available.  Optimizer estimates are just that, and you may be choosing real values that work with the test system stats but just miss with the production stats.
 
  How big is the row: number of columns, number of bytes? If not much more than your index columns, maybe add to the  index all of the table columns [a "fully fat" index]. Or if you don't need the SELECT *, select only the columns you need and add those [plus the predicate columns] to the index ["semi-fat"]. With that done, index-only access will give you blazingly fast access.
 
  You haven't mentioned either a sort requirement nor whether you need all "N" rows that qualify [or just the first screen's worth].  Each will affect the choice of access path.
  Hope this helps.
  Larry Kintisch  ABLE Information Services  845-353-0885     www.DBIndexDesign.com


-----Original Message-----
From: Steve Grimes
Sent: Jan 2, 2007 3:36 PM
To: [login to unmask email]
Subject: [DB2-L] Index puzzler


Hello,

I rebuilt a 25 million row, 108 partition table in production over the weekend, and now it's tablescanning for (at least) one major query.  

I use VE against this and two other systems (test & training)  (which have nearly identical data and went through a nearly identical process in the last 30 days.)  They use the NPI designed for this query.  Production does not.

Statistics are current for all three systems.
I just did a reorg, shrlevel change with full stats on the NPI I want it to use, in prod.  No change, still table scanning.
I compared Cluster ratios, first and full key cardinality, leaf pages, levels, stats times, unique rules -- it's (about) the same for all three systems.

Here's the query:

SELECT  *
FROM    DBAPROD.FIT_TRAN_SOURCE TRSO
WHERE   TRSO.FISCAL_YEAR = 2006
    AND TRSO.ACCT_DEPT   = '003021'
    AND TRSO.ACCT_FUND   = ' '
    AND TRSO.ACCT_LEDGER = '1'
    AND TRSO.ACCT_CLASS  = '2'
    AND TRSO.TRAN_DESC LIKE 'PRI%';  

Here's the index I think it should use (and does use in Test and Training):

    create index DBAPROD.FIXTRSOC on DBAPROD.FIT_TRAN_SOURCE
    (FISCAL_YEAR asc, ACCT_DEPT asc, ACCT_FUND asc,  ACCT_LEDGER asc,
    ACCT_CLASS asc, TRAN_DESC asc)
    using stogroup SMSGROUP
      priqty  500400
      secqty   50400
      pctfree     10
      freepage    10
    COPY NO
    bufferpool BP5;
  RESULT OF SQL STATEMENT:
  DSNT400I SQLCODE = 000,  SUCCESSFUL EXECUTION
 create    SUCCESSFUL

Any ideas what I may be missing or where I should look next?

TIA!

Stg --------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm --------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm