Re: Parsing in cost-based mode on Oracle 7.3

On Wed, 18 Feb 1998 21:44:47 +0100, "Michel Tilman" <mctilman_at_cargo.be> wrote:

>We recently switched to cost-based optimizer. While on the average the
>resulting parsed and cached queries give good access time, the actual
>parsing process takes a lot of time in several cases, e.g. nearly 3 min
>parsing (first time) vs 4 sec actual execution (involving say 7 tables). As
>we have a rather dynamic query interface, whether a query (plan) will remain
>cached is not always very predictable. Can someone give me some insight in
>what goes on in the actual analysis phase (query parsing) to understand what
>is going on? Or am I missing something? As far I know our statistics are
>updated weekly, and the population grows rather linearly. We use parameter
>binding. We have several hundred tables, the largest being about 100.000
>record (just a few, the others are much smaller).

Do you know how to use the sql_trace and timed_statistics sesion parameters and run the resulting trace file through tkprof? If so, could you post the results? Could be helpful in figuring out why the parse phase is taking so long. Also might demonstrate whether the CBO is picking the best plan [it rarely does without a little help]. Please include the explain option in tkprof.

The other thing you could do is just give the query your own hints...it might cut down on the amount of analysis and make your parses faster.

/cpk Received on Thu Feb 19 1998 - 00:00:00 CST