Solved

Upgrade from 9.2.0.8 to 11gR2

Posted on 2011-02-15
18
1,853 Views
Last Modified: 2012-05-11
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
Comment
Question by:bibi92
18 Comments
 
LVL 16

Expert Comment

by:rolutola
ID: 34900067
Have you refreshed statistics?


R.
0
 

Author Comment

by:bibi92
ID: 34900090
Yes, we have rebuild and refresh statictics.
0
 
LVL 4

Expert Comment

by:mhenry20
ID: 34900123
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34900152
How did you 'upgrade'?
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 34900172
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
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 34900193
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
 

Author Comment

by:bibi92
ID: 34900220
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
 
LVL 34

Accepted Solution

by:
Mark Geerlings earned 500 total points
ID: 34901221
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
 

Author Comment

by:bibi92
ID: 34901303
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 34901362
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
 

Author Comment

by:bibi92
ID: 34901474
I have renamed the table before doing this but I have to understand how explain this problem?
why?

Thanks
bibi
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 34901572
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
 

Author Comment

by:bibi92
ID: 34901647
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
 

Author Comment

by:bibi92
ID: 34901798
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
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 34901916
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
 

Author Comment

by:bibi92
ID: 34902000
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
 
LVL 5

Expert Comment

by:simonpaul64
ID: 34904844
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
 

Author Closing Comment

by:bibi92
ID: 34905248
Thanks bibi
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now