| 9i to 10g [message #214975] |
Thu, 18 January 2007 16:08  |
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 #215207 is a reply to message #214977] |
Fri, 19 January 2007 13:14  |
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 |
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 |
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. 
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
