Re: OPTIMIZER-Problem

ralf_at_hydrogen.emi.de (Ralf Korell) wrote:
>Hi, DB-Gurus !
>OPTIMIZER Problem :
>
>Caused by some performance-problems, I had to tune some
>SQL-statements in several program-units (all written in Pro*C).
>Tuning by hand was very hard and sometimes the desired effect was
>not guaranteed, so we decided to force the optimizer mode to 'COST'.
>This resulted into the first problem, because the init.ora parameter
>OPTIMIZER_MODE won't run with 'COST' (ORA Server Concepts ,
>13-29), but it runs with 'FIRST_ROWS'.

  It does not work yet. Keep using RULE. Try sqltools' Explain.SQL. It   will help you to tune applications. SQLTOOLS_at_DELPHI.COM.

>Q 1a : is this the same ?
>

>SELECT TO_CHAR(DAT_ZEIT_VON,'DD.MM., HH24:MI'),AUFTRAG.TYP, >
>the plan is exactly the same when I change the order of columns in
>the WHERE-clause in any way (I've permuted all !).

   Order of columns in the WHERE doesn't care! I saw two cases where    changing the order of columns did chances - in 6 years working with    Oracle! Table in the from clause is really very important when you    are using RULE optimizer. Oracle seems to be making some LALR parsing    that usualy build lists from right to left. I think that because of    this Oracle selects the last table to be the outer loop.

>In optimizer-mode RULE I got a different execution plan :
>

[deleted lines]

>
>Q 1c : How can I change the statement to force the RDBMS to use
> the first execution plan whithout using the COST-based
> optimizer ?

   try some hints ( /*+ hint... */ ) Received on Sat Aug 12 1995 - 00:00:00 CEST