Query Optimization (DB2 V6.1)

praveen_kj

Query Optimization (DB2 V6.1)
Hi All,

We are analysing our queries with the help of a tool (Visual Explain )

We are facing some problem.

In a cobol programme there were some queries .
The same queries when run with Visual Explain tool uses indexes.

When these Queries are run in programme (after bind and compile) takes a
lot of time which suggests that inspite of index scan table space
scanning is taking place.

Now to overcome this problem we added 'optimize for 1 row' in the all
the Queries in the programmes.After adding
'optimize for 1 row' all the queries started running fine except one.

Either we add or not 'optimize for 1 row' for all the Queries Visual
explain tool suggests indexes are utilised But when the same query runs
through programme (COBOL) it doesn't perform well.

We also observed one strange behaviour that when the Queries in the
programme are written without host variable
(i.e. if we put exact valuse in the programme) the queries run fine(i.e.
they utilises index.)

For One Query we also found out that if we add 'optimize for 1 row'
inspite of index scanning table space scanning starts taking place.

Can Somebody throw light on the above mentioned strange behavior of DB2
(V 6.1)


Regards
Praveen.



Richard Simpson

Re: Query Optimization (DB2 V6.1)
(in response to praveen_kj)
When adding a host variable stops an index being used that is often because
the host variable is longer than the column being compared to. This is a
problem even in v6.

There are a few other things that could be happening, but this is one thing
it woudl be worthwhile to check.

Regards

Richard Simpson
IBM Global Services Australia



praveen_kj <[login to unmask email]>@RYCI.COM> on 18/12/2001 16:42:01

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:
Subject: [DB2-L] Query Optimization (DB2 V6.1)



Hi All,

We are analysing our queries with the help of a tool (Visual Explain )

We are facing some problem.

In a cobol programme there were some queries .
The same queries when run with Visual Explain tool uses indexes.

When these Queries are run in programme (after bind and compile) takes a
lot of time which suggests that inspite of index scan table space
scanning is taking place.

Now to overcome this problem we added 'optimize for 1 row' in the all
the Queries in the programmes.After adding
'optimize for 1 row' all the queries started running fine except one.

Either we add or not 'optimize for 1 row' for all the Queries Visual
explain tool suggests indexes are utilised But when the same query runs
through programme (COBOL) it doesn't perform well.

We also observed one strange behaviour that when the Queries in the
programme are written without host variable
(i.e. if we put exact valuse in the programme) the queries run fine(i.e.
they utilises index.)

For One Query we also found out that if we add 'optimize for 1 row'
inspite of index scanning table space scanning starts taking place.

Can Somebody throw light on the above mentioned strange behavior of DB2
(V 6.1)


Regards
Praveen.








praveen_kj

Re: Query Optimization (DB2 V6.1)
(in response to Richard Simpson)
Hi Richard ,
In our case we have checked this thing.The host variable length is equal
to the size of the column.Then also we are facing the problem.

Regards
Praveen


-----Original Message-----
From: Richard Simpson [mailto:[login to unmask email]
Sent: Tuesday, December 18, 2001 11:21 AM
To: [login to unmask email]
Subject: Re: Query Optimization (DB2 V6.1)


When adding a host variable stops an index being used that is often
because
the host variable is longer than the column being compared to. This is a
problem even in v6.

There are a few other things that could be happening, but this is one
thing
it woudl be worthwhile to check.

Regards

Richard Simpson
IBM Global Services Australia



praveen_kj <[login to unmask email]>@RYCI.COM> on 18/12/2001 16:42:01

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:
Subject: [DB2-L] Query Optimization (DB2 V6.1)



Hi All,

We are analysing our queries with the help of a tool (Visual Explain )

We are facing some problem.

In a cobol programme there were some queries .
The same queries when run with Visual Explain tool uses indexes.

When these Queries are run in programme (after bind and compile) takes a
lot of time which suggests that inspite of index scan table space
scanning is taking place.

Now to overcome this problem we added 'optimize for 1 row' in the all
the Queries in the programmes.After adding
'optimize for 1 row' all the queries started running fine except one.

Either we add or not 'optimize for 1 row' for all the Queries Visual
explain tool suggests indexes are utilised But when the same query runs
through programme (COBOL) it doesn't perform well.

We also observed one strange behaviour that when the Queries in the
programme are written without host variable
(i.e. if we put exact valuse in the programme) the queries run fine(i.e.
they utilises index.)

For One Query we also found out that if we add 'optimize for 1 row'
inspite of index scanning table space scanning starts taking place.

Can Somebody throw light on the above mentioned strange behavior of DB2
(V 6.1)


Regards
Praveen.



the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list
can




the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list
can



Sanjeev (CTS) S

Re: Query Optimization (DB2 V6.1)
(in response to praveen_kj)
Praveen,

You using parameter markers or literals in Visual Explain ? Those parameter
markers, if replaced by host variables can give the correct idea to the
optimizer if plan is bound with REOPT(VARS). Just look if you have this
parameters specified in your bind options. Also see if you have coldist
populated for the value which is populated in the host variables. Optimizer
is doing right, we need to look into the possible combination of mistakes we
might be doing.

With Best Regards,
Sanjeev

> -----Original Message-----
> From: praveen_kj [SMTP:[login to unmask email]
> Sent: Tuesday, December 18, 2001 11:12 AM
> To: [login to unmask email]
> Subject: Query Optimization (DB2 V6.1)
>
> Hi All,
>
> We are analysing our queries with the help of a tool (Visual Explain )
>
> We are facing some problem.
>
> In a cobol programme there were some queries .
> The same queries when run with Visual Explain tool uses indexes.
>
> When these Queries are run in programme (after bind and compile) takes a
> lot of time which suggests that inspite of index scan table space
> scanning is taking place.
>
> Now to overcome this problem we added 'optimize for 1 row' in the all
> the Queries in the programmes.After adding
> 'optimize for 1 row' all the queries started running fine except one.
>
> Either we add or not 'optimize for 1 row' for all the Queries Visual
> explain tool suggests indexes are utilised But when the same query runs
> through programme (COBOL) it doesn't perform well.
>
> We also observed one strange behaviour that when the Queries in the
> programme are written without host variable
> (i.e. if we put exact valuse in the programme) the queries run fine(i.e.
> they utilises index.)
>
> For One Query we also found out that if we add 'optimize for 1 row'
> inspite of index scanning table space scanning starts taking place.
>
> Can Somebody throw light on the above mentioned strange behavior of DB2
> (V 6.1)
>
>
> Regards
> Praveen.
>
>
>
> http://www.ryci.com/db2-l. The owners of the list can be reached at
> [login to unmask email]

David Hayes

Re: Query Optimization (DB2 V6.1)
(in response to Sanjeev (CTS) S)
Praveen

Have you checked whether you are getting any RID pool failures? If the optimizer decides to use list prefetch at bind time but then is unable to acquire enough storage in the RID pool to satisfy the request at runtime, the effect on
performance could be significant as the access will convert to a tablespace scan. By specifying Optimize for 1 Row you will be discouraging the optimizer from selecting list prefetch, hence reducing this risk.

Alternatively, if using literals rather than host variables fixes the performance problem, it is likely to be connected to the distribution of values in your indexed columns. Try re-Explaining the queries using the options you have
outlined below (i.e. using the Optimize clause and using literals rather than host variables). The difference in access paths should then be clear.
Regards

Dave Hayes


praveen_kj wrote:

> Hi All,
>
> We are analysing our queries with the help of a tool (Visual Explain )
>
> We are facing some problem.
>
> In a cobol programme there were some queries .
> The same queries when run with Visual Explain tool uses indexes.
>
> When these Queries are run in programme (after bind and compile) takes a
> lot of time which suggests that inspite of index scan table space
> scanning is taking place.
>
> Now to overcome this problem we added 'optimize for 1 row' in the all
> the Queries in the programmes.After adding
> 'optimize for 1 row' all the queries started running fine except one.
>
> Either we add or not 'optimize for 1 row' for all the Queries Visual
> explain tool suggests indexes are utilised But when the same query runs
> through programme (COBOL) it doesn't perform well.
>
> We also observed one strange behaviour that when the Queries in the
> programme are written without host variable
> (i.e. if we put exact valuse in the programme) the queries run fine(i.e.
> they utilises index.)
>
> For One Query we also found out that if we add 'optimize for 1 row'
> inspite of index scanning table space scanning starts taking place.
>
> Can Somebody throw light on the above mentioned strange behavior of DB2
> (V 6.1)
>
> Regards
> Praveen.
>
> =======================



Harish Gangadharanunni

Re: Query Optimization (DB2 V6.1)
(in response to David Hayes)
Hi Praveen,

Do experiment with KEYCARD and FREQVAL options in RUNSTATS to see whether
the column value distributions improve your app's performance. Also, just
because the EXPLAIN looks fine and uses indexes does not mean that the
query should run just fine, and should not drive you to the conclusion that
a tablespace scan is happening. (I am not sure whether you arrived at this
conclusion using any run-time monitors or not.)

Since the OPTIMIZE FOR 1 ROW helped in your case, it could suggest that
something associated with list prefetch may have been eliminated, or an
internal DB2 data-sort may have been avoided.

OPTIMIZE FOR 1 ROW need not improve perfomance, especially if you use it to
bring several rows using the SQL into your application.

Could you post the SQL if the problem persists.

Thanks,

Harish.