we used Oracle db 8.1.6 in a web app. Now, our client wants to run that app on an 8.0.5 db. So I created exactly the same schema for the 8.0.5 version (same data types, same indexes, same storage params, ...). Surprisingly, performance is not nearly the same. The main query took less than a second to execute in the 8.1.6 database and takes over two and a half minute to execute in the 8.0.5 db.
Offcourse my question is: what's causing this difference in performance? Are there specific problems in 8.0.5 regarding performance or should I change some of the storage parameters or ... ? Any information would be greatly appreciated.
With that big a difference, I'd start looking at the query plans. Assuming the tables have been analyzed and your running similar parameters (optimizer_mode, db_block_buffers, shared_pool_size, etc) then the differences in the optimizer wouldn't be that much of a difference, IMHO.