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
bibi92Asked:
Who is Participating?
 
Mark GeerlingsConnect With a Mentor Database AdministratorCommented:
For database upgrades invovling purchased applications, you should work with the application's support staff.  I think Peoplesoft is now owned by Oracle, correct?  If that is true, you should be working with Oracle Support on this upgrade, first to make sure that the application supports this database version, then for any tips, suggestions and requirements that the vendor knows about.

(If I'm wrong on who owns Peoplesoft now, you should at least be working with the owner of the Peoplesoft product.)
0
 
Richard OlutolaConsultantCommented:
Have you refreshed statistics?


R.
0
 
bibi92Author Commented:
Yes, we have rebuild and refresh statictics.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
mhenry20IT Operations DirectorCommented:
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.
0
 
slightwv (䄆 Netminder) Commented:
How did you 'upgrade'?
0
 
Mark GeerlingsDatabase AdministratorCommented:
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?
0
 
Mark GeerlingsDatabase AdministratorCommented:
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?
0
 
bibi92Author Commented:
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
0
 
bibi92Author Commented:
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
0
 
Mark GeerlingsDatabase AdministratorCommented:
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?
0
 
bibi92Author Commented:
I have renamed the table before doing this but I have to understand how explain this problem?
why?

Thanks
bibi
0
 
Mark GeerlingsDatabase AdministratorCommented:
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.
0
 
bibi92Author Commented:
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
0
 
bibi92Author Commented:
1:optimizer_mode=ALL_ROWS
2:both in the init.ora and spfile
3:spfile
4:UNDO_TBS
6:No
7:No
Thanks
bibi
0
 
Devinder Singh VirdiLead Oracle DBA TeamCommented:
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.
0
 
bibi92Author Commented:
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
0
 
simonpaul64Commented:
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.
0
 
bibi92Author Commented:
Thanks bibi
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.