Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

performance issue after upgrade 9.2.0.6 to 10.2.0.4

Posted on 2011-02-10
4
Medium Priority
?
410 Views
Last Modified: 2012-05-11
There was NO CHANGE in performance on TEST, good or bad, until...

ATP ran for 1hr and 7min, Saturday night at 6:07PM then collided with the 7:07PM reload and crashed until I reset it this morning.

Up to that time the RUP7 and Oracle ATP
patches seem to have no effect on the
reload timing.  The only thing that produced any change (for the better) was AMM configuration.

The net effect is that, when DEV is down, TEST is still running slower (and there are no increases in products, warehouses or orders) than prior to the UG.

Meanwhile, PROD has grow to 30min 30sec on average, with better performance on Sunday.

One more observation I have to offer, the WORSE time recorded on PROD was at 6:07PM on Saturday (Hummmmm, the same time TEST had it's worse time) with a time of 42 minutes to complete the reload.

Is there some network activity that is causing the data transfer between DB_Server and App_Server to degrade at certain times?

Are their any other DB tuning parameters or kernel parameters that can be considered for 11gR2?
0
Comment
Question by:columbus131
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 8

Accepted Solution

by:
Chakravarthi Ayyala earned 2000 total points
ID: 34868406
Did you try setting optimizer_features_enable to the version before upgrade as an interim fix?
Also, what are your optimizer_mode init.ora parameter settings?
ALL_ROWS or FIRST_ROWS ?
If it is ALL_ROWS, please try FIRST_ROWS and see.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 34869823
What about the statistics?
Is it fresh?

Also 6:00 PM is out of business time. Check if DBMS_SCHEDULER has scheduled maintanance jobs at that time.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 34869830
Another possibility ismaintanance work on the network, midlle tier and so on.
0
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 34933500
hi

1.you should check all FLASHBACK related parameters since they add overhead with extra logging for flashback options.

2. remember that since 10g the optimizer will always use cost based optimization, this means that is you lack statistics then the optimizer will sample (see parameter optimizer_dynamic_sampling)
by default the value for dynamic sampling is 2 and that could by to low (like if you need histograms etc..)

so update all database and system statistics.

3. the optimizer uses timed statistics (in oracle 9i it didnt by default).

4. use the sql advisories  and ASH to review what couses the poor performance.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

609 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