Optimiser

[bgill]

I have a large database. The optimiser is currently set to 'RULE'.
Should I be using the cost based optimiser?
I would be grateful if anyone could explain a bit more about the optimiser or point me to some relevant links or articles on this subject.

Thanks in advance.

[Orca777]

Hello;
If you switch the Optimizer to CHOOSE then never, never collect statistics for every Table.
You will wonder but the CBO in some Cases decreases the perfomance.
Go table by table and most by tables you like to use features which needs CBO;
The CBO is not bad but you have to think a lot how you create the statitistics, when to create histograms, when to compute when to estimate, how often per week/month ...

Orca

[samdba]

Hi Orca777,

That's interesting. How to decide which tables should be analyzed and which should be not.

I presently have RULE defined in init.ora but still analyzes all my schema tables and then use CBO hints in queries.

Is this OK?

Thanks,

[bgill]

Hi Sam
What happens when you analyse tables?
Can you give me some example of how to use a CBO hint in a query.

[Orca777]

Hello samdba;

i think your way is the best!
So the (professional) SQL-Developer can decide with a Hint when to use CBO.
The only thing is that you define an analyze-Subsystem which anlyze the objects for you in a CPU-NonWaisting and exact way.

Orca