Re: Oracle v7.3.2 Benchmark Tests

Perhaps I should clairify my situation. We currently use the RULE base optimizer under Oracle v7.1.4, and intended on using the RULE based optimizer under v7.3.2.

However, performance under v7.3.2 was worse than v7.1.4 using the RULE based optimizer. This is strange since Oracle has made performance enhancements since v7.1.4. Our benchmark tests did not reflect any performance gains, so we tried the COST based optimizer. In doing so we analyzed every table associated with the benchmark tests using the ESTIMATE option, changed OPTIMIZER_MODE to CHOOSE in init.ora, shutdown, startup, and gave the tests another run. That didn't work so I started digging though the documentation, and then changed COMPATIBILITY and SORT_DIRECT_WRITES. Changing compatibility to 7.3.2 had no effect. Changing SORT_DIRECT_WRITES (which default to FALSE on v7.2, and AUTO on v7.3) caused a further decrease in performance using RULE.

It was assumed that v7.3.2 would perform better than v7.1.4 on the same database with the same initialization parameters. It did not, and I was wondering if there was somthing I missed.

We did not intend on switching to the COST based optimizer, however the results that I posted reflect that perhaps we should. I am looking for input on how to make RULE under v7.3.2 perform the same or better than v7.1.4.

Also, my post was informational showing how performance differs between optimizer modes for different types of SQL statments. For example, INSERTs and DELETEs take longer using COST; ORDER BY, GROUP BY, and HAVING take longer under RULE.

Darren Darnell, darren.darnell_at_edwardjones.com Database Systems Team, Edward Jones
201 Progress Parkway, Maryland Heights, MO 63043-3042 Phone: 314.515.1172 FAX: 314.515.2599 Received on Wed Nov 13 1996 - 00:00:00 CST