Re: Personal Oracle 8i and cost based query optimization

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Tue, 5 Nov 2002 23:38:54 +1000
Message-ID: <OMOx9.69813$g9.198444@newsfeeds.bigpond.com>

"GW" <gwandsh_at_KILLpacifierSPAM.com> wrote in message news:usek4ph5viu2ba_at_corp.supernews.com...
> Trying to evaluate TOAD against personal Oracle 8i. Instructions say TOAD
> will perform an Explain Plan with associated Costs if cost-based
> optimization is enabled. Explain plan is working fine, but no costs are
> displayed.
>
> Anybody have a quick way to enable Cost-based optimization in Personal
> Oracle 8i. I have only set up a small sample DB install, but so far it
> eludes me...

Hi Gary,

OPTIMIZER_MODE parameter, at database level

alter session set OPTIMIZER_MODE = all_rows (or first_rows), at session level

ALL_ROWS or FIRST_ROWS hint, at statement level

Note the default optimizer_mode setting of CHOOSE will also use the CBO *if* at least one referenced table has statistics. As *all* tables really should have been analyzed/dbms_stats(ed) and hence have statistics when using the CBO, CHOOSE should be a hunky dory option. It will use the all_rows CBO option.

Cheers

Richard Received on Tue Nov 05 2002 - 07:38:54 CST