• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1874
  • Last Modified:

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
0
bibi92
Asked:
bibi92
1 Solution
 
Richard OlutolaConsultantCommented:
Have you refreshed statistics?


R.
0
 
bibi92Author Commented:
Yes, we have rebuild and refresh statictics.
0
 
mhenry20Commented:
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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
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
 
Mark GeerlingsDatabase 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
 
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 VirdiCommented:
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now