Hi,

We have a query (pretty complex) that we have investigating at
different
optimisation Levels and I am Little surprised at the results, and it
raises
some questions:
Notes.
1.Package cache flushed before each running.
2.The majority of the elapsed time - 99% is spent preparing
statement - verified with db2batch

Level Elapsed time(ms)
0 125
1 141
2 328
3 7,250
5 49,828
7 17,938
9 7,328

Q1. If Level5 has heuristics to avoid spending too much time
optimising
what is a very short running query, why does it insist on
continuing
to Prepare for some 50 seconds on a query that can run in 100ms ?

Q2. Can this very long prepare be controlled/influenced?

We will move to using 2 to avoid this, but it does seem very strange?

Paul.

Using embedded static SQL eliminates all "prepare time" except the one-off
bind. Using dynamic SQL - which includes all forms of CLI, ODBC, ADO, etc,
means you have to choose the trade-off.

DB2 performs query caching that can assist with repetitive prepares;
needless to say flushing the package cache is the way to eliminate any such
benefit.

The 50 seconds prepare seems excessive to me, unless your query really is
ludicrously complicated (and even then I find 50 seconds hard to believe).
See if you can determine where the time is going (CPU, disk, swapping): you
may just need to tweak some database parameters.

There is nothing strange in prepare's swamping the total execution time. It
is a direct consquence of the various database APIs you can use, and your
choice of what may well be the wrong one for your problem.

BTW, you can tweak the optimiztion level dynamically (SET CURRENT QUERY
OPTIMIZATION statement).

"Paul Reddin" <paul@abacus.co.uk> wrote in message
news:1fd2a603.0310090627.71f50a6a@posting.google.c om...
> Hi,
>
> We have a query (pretty complex) that we have investigating at
> different
> optimisation Levels and I am Little surprised at the results, and it
> raises
> some questions:
> Notes.
> 1.Package cache flushed before each running.
> 2.The majority of the elapsed time - 99% is spent preparing
> statement - verified with db2batch
>
> Level Elapsed time(ms)
> 0 125
> 1 141
> 2 328
> 3 7,250
> 5 49,828
> 7 17,938
> 9 7,328
>
> Q1. If Level5 has heuristics to avoid spending too much time
> optimising
> what is a very short running query, why does it insist on
> continuing
> to Prepare for some 50 seconds on a query that can run in 100ms ?
>
> Q2. Can this very long prepare be controlled/influenced?
>
> We will move to using 2 to avoid this, but it does seem very strange?
>
> Paul.

Mark,

I hear what you are saying, but ...

My bottom line here is that Optimiser appears to have the
ability to disappear off for 50 seconds (with 100% CPU) without
anyway of overiding that behaviour?

- i.e I would expect the heuristics to kick in and just get
on with running the query after some reasonable or
controllable point?

PS. I don't want to shoot the messenger here. :-)

Paul.

Documentation has always stated that levels 7 and 9 (can) consider
more possibilities than 5. In particular, level 9 is unlimted. So
the fact that level 5 takes much longer than 9 - which should be
considering more possibilities rather than fewer - seems all wrong,
surely?


Jeremy Rickard

If your query is very very complex (after application of RI, triggers, etc),
then it is possible that you may have massive prepare times at higher
optimization levels (and a slow CPU).

You should definitely review the section on optimization levels in the DB2
Administration Guide. It is basically pointless to use an optimization level
that is too high for your problem (which, in your case appears to include
mandatory, forced run-time re-preparation). Static SQL is still the best way
to go for high performance.

"Paul Reddin" <paul@abacus.co.uk> wrote in message
news:1fd2a603.0310140751.11c3628b@posting.google.c om...
> Mark,
>
> I hear what you are saying, but ...
>
> My bottom line here is that Optimiser appears to have the
> ability to disappear off for 50 seconds (with 100% CPU) without
> anyway of overiding that behaviour?
>
> - i.e I would expect the heuristics to kick in and just get
> on with running the query after some reasonable or
> controllable point?
>
> PS. I don't want to shoot the messenger here. :-)
>
> Paul.

>
> You should definitely review the section on optimization levels in the DB2
> Administration Guide. It is basically pointless to use an optimization level
> that is too high for your problem (which, in your case appears to include
> mandatory, forced run-time re-preparation). Static SQL is still the best way
> to go for high performance.
>

whenever we have to shoot at a moving target, we need to take a
careful aim each time before pulling the trigger. Taking an aim just
once would result in misses every time after the first shot. It is a
good rule of thumb to use static whenever posiible. Yet, like every
other rule of thumb I know about, this one has got a lot of
exceptions, usually relevant to data skew

"Mark Yudkin" <myudkinATcompuserveDOTcom@nospam.org> wrote in message news:<bmiscu$dk0$1@ngspool-d02.news.aol.com>...
> If your query is very very complex (after application of RI, triggers, etc),
> then it is possible that you may have massive prepare times at higher
> optimization levels (and a slow CPU).

But has anyone a plausible explanation as to why levels 7 and 9 are
taking so much longer than 5? If not, I would argue this is something
Paul should be taking up with IBM support as a possible bug.


Jeremy Rickard

I've seen queries take tens of minutes or even hours to compile.
Legitimately. Join enumeration - the work involved to find the best join
order - is an NP-complete problem, and the higher optlevels use an algorithm
that grows exponentially with the number of tables involved in the join.
Other properties of the various objects in the query can add to this too.
(Lower optlevels use a polynomial-time algorithm).

Why Paul would see a peak at opt 5, I wouldn't know without looking in
detail, but I can dream up a bunch of possible explanations, so honestly
this doesn't surprise me.

Optimization level is the Big Knob that you use to control this. The
DB2_REDUCED_OPTIMIZATION registry variable may also come in handy (see
online v8 doc). With both of these you face a tuning tradeoff - more
optimization means possibly a better plan; less optimization means you may
miss the best plan.

Hope this helps,

Paul Reddin wrote:

> Hi,
>
> We have a query (pretty complex) that we have investigating at
> different
> optimisation Levels and I am Little surprised at the results, and it
> raises
> some questions:
> Notes.
> 1.Package cache flushed before each running.
> 2.The majority of the elapsed time - 99% is spent preparing
> statement - verified with db2batch
>
> Level Elapsed time(ms)
> 0 125
> 1 141
> 2 328
> 3 7,250
> 5 49,828
> 7 17,938
> 9 7,328
>
> Q1. If Level5 has heuristics to avoid spending too much time
> optimising
> what is a very short running query, why does it insist on
> continuing
> to Prepare for some 50 seconds on a query that can run in 100ms ?
>
> Q2. Can this very long prepare be controlled/influenced?
>
> We will move to using 2 to avoid this, but it does seem very strange?
>
> Paul.

Where did you do these testings? On the testing machine?
Can you post your hardware resources - # of processors, CPU speed,
physical memory size, # of disks your table is stored ..?
The number of table records? If more than one table refered in the sql
query, how many?
How large is the result size?
Is it a EE or EEE db? intral_partition? inter_partition?

Regards,
FRX

Thanks again All,

But my basic problem is that the Optimiser at Level 5
is able to take 50 secs over a Prepare without me having
control to stop/override this - other than changing the
optimisation Level, which we may not want to do
for many other reasons e.g Mixed Workload etc.

PS. Our CPUs are very Fast!

What I would really like is a governor/timeout on the optimiser
Prepares to ensure I can limit the maxmimum time it takes
doing the prepare.

- Optimisation Level is a crude/inflexible/unquantifiable way to have to do this.

Paul.