Link to home
Start Free TrialLog in
Avatar of bibi92
bibi92Flag for France

asked on

Upgrade from 9.2.0.8 to 11gR2

Hello,

Following an upgrade from 9.2.0.8 to 11gR2, an query execution takes more time.
Example, a select count(*) from TABLE which contains 2000000rows takes 20s on 9.2.0.8 and
32minutes on 11GR2. I can't do an export from the tables.
I have done a dbverify on all datafiles, all is ok.
Which occurs this problem?

Thanks

bibi
Avatar of Rich Olu
Rich Olu
Flag of United Kingdom of Great Britain and Northern Ireland image

Have you refreshed statistics?


R.
Avatar of bibi92

ASKER

Yes, we have rebuild and refresh statictics.
You may also find that you were using rule based optimization in Oracle 9i and now you are using Choice or Cost.  You should check your optimizer_mode database parameter in v$parameter to see if it changed.
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

How did you 'upgrade'?
Please tell us:
1. Do you have the "optimizer_mode" value set correctly in your init*.ora file or spfile?
2. Do you have a "compatibility" value set in your init*.ora file or spfile?
3. Which of those files do you use, an init*.ora file or an spfile?
4. Do you have an UNDO tablespace now and system-managed undo, or do you still have rollback segments?
5. Do you have a custom application (that was developed for your company) or do you have a purchased application?  If purchased, which one is it?
Also:
6. Did you change any of your init*.ora or spfile settings for Oracle11, especially those that affect the memory usage?
7.  Is the Oracle11 database on the same server as the Oracle9 database was, or do you have a new server?
Avatar of bibi92

ASKER

Hello,

The application is peoplesoft.
I try rename the table and create it and transfer the datas from the Oracle db 9.2.0.8 with dblink and the select count(*) from this new table takes 20s.
How it's possible, I don't understand where is the problem.

Thanks

bibi
ASKER CERTIFIED SOLUTION
Avatar of Mark Geerlings
Mark Geerlings
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bibi92

ASKER

Thanks, but I try rename the table and create it and transfer the datas from the Oracle db 9.2.0.8 with dblink and the select count(*) from this new table takes 20s. I works
How it's possible, I don't understand where is the problem.
Regards
bibi
So, you copied the data manually for this one table and now you get the same fast query results in the Oracle11 database as you did in the Oracle9 database?  That means there is no problem now, correct?
Avatar of bibi92

ASKER

I have renamed the table before doing this but I have to understand how explain this problem?
why?

Thanks
bibi
What exactly is the problem?  (That is not clear to me.)  Is it just the fact that after the upgrade, this table was slow, but you manually copied the data, and now it is fast?

You only answered one of the 7 questions I asked you earlier, and you didn't indicate why you are apparently not working with the application vendor on this problem.
Avatar of bibi92

ASKER

Hello, I try to analyze the table for repair chained and I see in the trace :
when DBWR writes a "dirty bufferRunning Count Dump DIRTY
Running cnt[0]: 2, Runnable cnt[0]: 0
Avatar of bibi92

ASKER

1:optimizer_mode=ALL_ROWS
2:both in the init.ora and spfile
3:spfile
4:UNDO_TBS
6:No
7:No
Thanks
bibi
Couple of things to review:
for Performance.
Did you increase the SGA? How much for 9i and 11i.
What about tablespaces block and extent size setting.
What about the servers configuration.
What parameter changed during migrations.

For analyze,
Please provide the command you are using, and complete log file.
Avatar of bibi92

ASKER

I copied the data manually for this one table and now I get the same fast query results in the Oracle11 database as you did in the Oracle9 database.
So I try to understand why.

Thanks

bibi
I would also want to look at an explain plan for a query in both environments, to see what effect (if any) the settings such as optimizer have made.

Oracle does own PeopleSoft and there are fairly widely reported issues when moving from Oracle 9.x to 11.x. If you call support they should have a few things to try.
Avatar of bibi92

ASKER

Thanks bibi