Re: Detecting whether the RBO or CBO is being used

Alan wrote:

> Hi
> I'm investigating some performance problems within our system and am
> looking at the explain plans for some sql. On one box (v8.1.7) when I
> run the following:
>
> alter session set optimizer_mode = rule
> select blah from blah where blah
>
> I can see that plan and no costs are present.
>
> When I run it on our v8.0.5 box, I can see costs in the plan. Does
> this mean that the optimizer_mode is not set to rule for some reason?
>
> Thanks in advance
> Alan

You will not see a cost if you don't have statistics from running DBMS_STATS package or the older ANALYZE. But then ... if you don't run DBMS_STATS regularly you won't get good optimization.

To find the current optimizer mode:

SELECT *
FROM v$parameter
WHERE name = 'optimizer_mode';

Daniel Morgan Received on Mon Jun 17 2002 - 11:26:49 CDT