Home » RDBMS Server » Performance Tuning » 9i to 10g
9i to 10g [message #214975] Thu, 18 January 2007 16:08 Go to next message
jfrano
Messages: 11
Registered: January 2007
Location: New York
Junior Member
Anyone aware of MAJOR performance issues that might occur when upgrading from 9i to 10g?

We have a Data Warehouse, which had been running fine under 9i. Since the upgrade, performance has been disastrous.

Any thoughts, patches, fixes, etc available.

Thanks,

[Updated on: Thu, 18 January 2007 16:09]

Report message to a moderator

Re: 9i to 10g [message #214977 is a reply to message #214975] Thu, 18 January 2007 16:10 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> performance has been disastrous
How exactly?
You may want to isolate the issue and proceed further.
10g is very different in many ways. We cannot just wildly address everything Smile.
Gather statspacks/trace on isolated sql/issues and deal with it.
Regards~
Re: 9i to 10g [message #215207 is a reply to message #214977] Fri, 19 January 2007 13:14 Go to previous messageGo to next message
jfrano
Messages: 11
Registered: January 2007
Location: New York
Junior Member
below is a small sampling.
The command would list all the partions in a table and the record count. with 9i(sqlplus) it would take 5 minutes tops, with 10g(sqlplus) it took 2 hours to return yesterday.

select dtp.partition_name as "PARTITION_NAME", dtp.num_rows AS "NUM_ROWS", to_char(dtp.last_analyzed, 'MM-DD-YYYY HH:MM:SS') as "LAST_ANALYZED_DATE " from all_tab_partitions dtp where dtp.table_name = 'D1_PROC_EVENT_FACT' order by dtp.partition_position

Thanks,
Re: 9i to 10g [message #216249 is a reply to message #214975] Thu, 25 January 2007 14:13 Go to previous messageGo to next message
Sanjay Bajracharya
Messages: 279
Registered: October 2001
Location: Florida
Senior Member
The optimizer changes A LOT from 8i to 9i.

From 9i to 10g there is not much change in the optimizer. FYI, the RULE BASED optimizer is gone in 10g.

But this is just one of the topics ...

Did you run queries against table data in 9 and 10 to see the affect .. not just the dictionary.

Since this is a Warehouse, did you see your bulk load time (for the same data) also increase dramatically ??
--
Sanjay
Re: 9i to 10g [message #218994 is a reply to message #214975] Mon, 12 February 2007 07:27 Go to previous message
cbruhn2
Messages: 41
Registered: January 2007
Member
I don't agree with Sanya that the optimizer hasn't changede from 9i->10g. But of course how much is much. Smile

And then you have an example on data dictionary tables. Please notice that Oracle recommends having statistics/CBO on internal tables as well on 10g.
There is a job that should do the trick in 10g running automatically every weekend.
Have this been implemented when upgrading?

best regards.
Carl Bruhn.
Previous Topic: how to tune query
Next Topic: ORA-12520: TNS:listener could not find available handler for requested type of server
Goto Forum:
  


Current Time: Sun Dec 11 06:01:46 CST 2016

Total time taken to generate the page: 0.08864 seconds