8i vs 9i Speed

[stefanhei]

ANSI-SQL should not affect speed.
Are the execution plans for those views the same for 8i and 9i?
Some init-parameters affecting memory usage are new in 9i (db_cache_size,pga_aggregate_target,...). Maths there can be tricky to match 8i settings (esp. with new hardware).

Stefan

 

[Creepers]

Thanks for everyone's responses. We are on 9.2.0.5. I would like to look at the explain plan, but again this one query takes 3+hrs in Dev, so I have not "bitten the bullet" yet. Probably should.

Thanks
Rich

 

[Creepers]

Our DBA just finished moving the test instance of Oracle from 8i to 9i. After doing so, I noticed that many of the views are running much slower. After doing some research, I learned that 9i supports ANSI join syntax. Does this impact the speed? If not, does anyone have any thoughts as to why the speed of views has decreased?

Thanks

ps
I also posted this same question in the Oracle Developer group.

 

[KenCunningham]

I take it that the test database in on equivalently specced hardware?

 

[LKBrwnDBA]

How about the statistics -- are all tables/indexes analyzed?


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Creepers]

No, it does not have the same hardware. A view (group of views) running in production takes 2 seconds (select *). The same view took 3 hours in development. This view has been in production for over a year and therefore in development for a year. Previous to the 9i upgrade, the same view in the development instance took 6 seconds (select *).

In short, the hardware is different and I expect to see approx. 66% decrease in speed in the development instance. But this does not account for the major slow down.

Thanks

 

[hoinz]

... from 8i to 9i
Which version of 9i? Current is 9.2.0.6 ( or is it 9.2.0.7?)
As far as I know there were performance problems with early versions of 9i.

 

[dbtoo2001]

Can you run the query in sqlplus? If so, set autotrace traceonly explain, that way you get the plan, but don't have to wait for the data.