Access Path Anomaly.

Gopal Tripathi · New User Joined: 16 Sep 2009 Posts: 15 Location: Hyd,Ind

Hi,

Am trying to execute a dynamic query, in development the query is getting timed out while in uat its getting executed successfully.

The only difference that i can see in development is below-

TNAME
------+-------
DSNWFQB(10)

and

ACCESSTYPE
------+----
R

Inspite of

TNAME
------+-------
tablename

and

ACCESSTYPE
------+----
I

in UAT.

Also, the indexes being picked up are different in these two environment.
Is there any way I can sync the access paths.
Any help is appreciated.

Thanks,
Gopal.

Dsingh29 · Active User Joined: 16 Dec 2008 Posts: 132 Location: IBM

I dont think access paths can be made that way as you want to, as the environments are totally different. Therefore, access paths are meant to be different.
Moreover, the other reason is UAT env. may be getting more resources then the development env. (which is true in most shops). Try to optimise query, I would suggest.

sushanth bobby · Posted: Wed Jun 22, 2011 4:40 pm

Gopal,

Its doing an Tablespace scan in development and in UAT its using an index.

Thanks,
Sushanth

Dsingh29 · Active User Joined: 16 Dec 2008 Posts: 132 Location: IBM

R - Table space scan. This is the worst type of access.

Gopal Tripathi · New User Joined: 16 Sep 2009 Posts: 15 Location: Hyd,Ind

Hi,

I agree as R for Tablespace scan and I for Indexed access.Can you please elaborate per se
"optimising the query".

Thanks,
Gopal.

sushanth bobby · Posted: Wed Jun 22, 2011 5:35 pm

Gopal,

In the UAT plan table check the ACCESSNAME and findout if that index is available in DEVL.

GuyC · Posted: Wed Jun 22, 2011 8:43 pm

(Table space scan is not always the worst accesspath)

Tablescans are likely picked when runstats aren't run and db2 thinks the table is really small.

don.leahy · Posted: Wed Jun 22, 2011 9:37 pm

Gopal Tripathi · New User Joined: 16 Sep 2009 Posts: 15 Location: Hyd,Ind

Hi,

I am unable to post the query as such, but the only thing that I see is the DEV and UAT both are set up as similar(All objects created similarly).

The stats are updated, I see no reason for optimiser to pick different access path, as the query is getting time out in one and running successfully in another.

Thanks,
Gopal.

sushanth bobby · Posted: Thu Jun 23, 2011 6:31 pm

GT,

How many rows are there in UAT and DEV ?
What is the SQLCODE you are getting, Post the error message ?

Without query and error message, this post wont go anywhere ?

Thanks,
Sushanth

Gopal Tripathi · New User Joined: 16 Sep 2009 Posts: 15 Location: Hyd,Ind

In addition to it,

NOTE:
The subqueries are indicated by a row in the PLAN_TABLE having TNAME="DSNWFQB(nn)", where 'nn' is the query block number associated with the subquery, and TABLETYPE='S'. In PLAN_TABLE, the PARENT_PLANNO column corresponds to the plan number in the parent query block where the correlated subquery is invoked for correlated subqueries. For non-correlated subqueries it corresponds to the plan number in the parent query block that represents the work file for the subquery.

As per the explain outout in development-

TNAME
------+-------
DSNWFQB(10)

With reference to above explanation and corresponding to the query block no 10 the respective table(access name) have two different indexes A and B in both the environments. In UAT the index A being picked up in UAT and index B being picked up in DEV.

Thanks,
Gopal.

GuyC · Posted: Thu Jun 23, 2011 8:23 pm

If all objects are the same & the stats are similar, you're done.
There isn't much else that can influence the optimizer, except a few zparms.

Stats are more then just #rows : filterfactors, clusterratios, nlevels,...
With OSC you can generate SQL statements that would copy all relevant statistics.
if you copy the statistics from UAT and apply them in DEV and then you still get a different accesspath, only then we have to go look further.

gylbharat · Posted: Fri Jun 24, 2011 11:00 am

The value of RID Pool in ZPARMS also is dependent -

"The RID Pool was an enhancement added with DB2 V2.2, and is used whenever the Optimizer determines that List Prefetch (LP) and/or Multiple Index Access (MIAP) will be used as the most efficient Access Path for you SQL request. The size of the RID Pool is specified in the ZPARM, and it can expand dynamically up to its maximum amount. The number in the ZPARM specifies the maximum number of blocks for the pool, and each block is 16K. Let's look at how we determine how much space may be necessary to use MIAP or LP. a RID (Row ID) is 4 bytes. If DB2 must sort 1 million RIDs, this is 4 Megabytes - times two, because you need twice the amount of space to be able to perform the sort. This is now 8 Megabytes. One user may not use more than 50% of the RID Pool, so the actual space necessary is 16 Megabytes. There are several causes for LP or MIAP to fail dynamically, and the failure can be an expensive process since DB2 must then restart the access using a different access path (usually a tablespace scan). "

GuyC · Posted: Fri Jun 24, 2011 1:50 pm