Rule or Cost based

[martinm]

Hi all, not really an Oracle expert by any means, but just started a contract where I'm the best they've got....

Using Oracle 8.0.5 (9i on it's way)

I have some fairly simple queries that are taking waaay too long to execute. I have found that (by looking at the explain plan) the optimiser was doing very odd things. I found the /* + RULE */ in my new 'Oracle SQL Tuning pocket reference' and added it. Magical!!


So, should the whole installation be changed? If so, how, or have I just found some rare cases where rule based is best?

Cheers.

 

[ddiamond]

Martinm,

I also am using Oracle version 8.0.5.0., and I often find that /*+ rule */ makes queries run much faster even when my statistics are all up todate (in one case a query that took 8 hours to run, took 30 seconds when I added /*+ rule */). The rule based optimizer always favors index joins, whereas the cost based one often prefers hash-joins or merge-joins. I don't know anything about version 9.1, though. I can only hope that version 9.1 will not require as many optimizer hints as 8.0.5.0.

- Dan

 

[Beilstwh]

What is the server defined as? Do the following


show parameters optimizer_mode





Bill
Oracle DBA/Developer
New York State, USA

 

[ddiamond]

Bill,

optimizer_mode = 'CHOOSE'

- Dan

 

[martinm]

Well got it sorted. Optimizer_mode = choose.

I had analyed some tables but not all, so the opt. was using cost based, but without full stats. Have set to opt._mode = Rule and all is happy again!!

Will do full stats as time allows.

Thanks.