Link to home
Start Free TrialLog in
Avatar of columbus131
columbus131

asked on

performance issue after upgrade 9.2.0.6 to 10.2.0.4

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?
ASKER CERTIFIED SOLUTION
Avatar of Chakravarthi Ayyala
Chakravarthi Ayyala
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
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.
Another possibility ismaintanance work on the network, midlle tier and so on.
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.