Oracle9i(9.2) is slower than Oracle 8i(8.1.7)

[julian]

Originally posted by jmodic
That one is a news to me. Tim, can you provide any reference that would confirm this?

I've searched the documentation and various Oracle related sites but could not find anything that would support this claim. On the contrary, I've found many warnings against gathering statistics for the data dictionary, valid still for 9i. And there were also some *very logical* explanations as to why SYS objects still shouldn't be analyzed. BTW, DBMS_STATS.GATHER_DATABASE_STATS procedure indeed has a parameter GATHER_SYS, however it default's to FALSE! I would imagine they would rurn it to TRUE if it realy was "recommended to gather stats for the SYS schema also".

It is a bit unclear to me if I should analyze SYS and SYSTEM, so I do not. But here is some info from Metalink:

"With the desupport of the RBO pending in 10i there will only be a CBO so the dictionary will need to be analyzed. The dictionary can now be analyzed without any problems. Note that whilst DBMS_UTILITY.ANALYZE_SCHEMA can be used against the SYS schema Oracle does not perform any regression tests with these tables analyzed and so it is possible that deadlocks or performance problems may be encountered. Also note that a significant number of large databases run extremely succesfully with an analyzed dictionary. "

[skdas]

Analyzed the SYS, SYSTEM and the schema which contains the stored procedures and data. There is no improvement.
Only auto PGA I have to set then try, running same stored procedures

[supower]

Check important parameter setting is same in 9i as 8i

optimizer_index_caching
optimizer_index_cost_adj
db_file_multiblock_read_count
sort_area_size
etc...

[skdas]

The paremeters you specified is same in Oracle 8i and 9i.