Re: Report running is way slow on a new database

mlei_at_txi.com (LittleDBA) wrote in message news:<b6ba5bb.0211091159.5f9798e0_at_posting.google.com>...
> Most recently i moved our production database (8.1.6.1.0) to a newly
> purchased sun solaris box (sun fire 280) and upgrade the database to
> 8.1.7.2.0.
>
> Developer reported that a report is running very slow. It is through a
> cobol application called CYBORG. From what he said the old box and new
> box setting and configuration is the same.
>
> i ran statspack and turn on sql trace and found out even both
> optimizers pick up the same path and utilize indexes. However the new
> database trace file is huge (500MB).
>
> Any help is very much appreciated.
>
> Regards,
> LittleDBA

LittleDBA, We were formerly on 8.1.6 and currently have 8.1.7.x on both AIX and Solaris and did not experience any upgrade related performance problems so you might want to start by

1 - Verifying that the report did not always run this slow, or that the run in question isn't the monthly close-out run and it always runs 5X longer than the daily version. You would be surprised how many times someone has brought up a performance issue right after an upgrade that turned out to have to running as it always ran. Though that does not mean it might not actually need improvement.

2 - looking closely at the trace output to make sure the plan being shown matches the statistics reported; it is possible for the plan not to match under certain conditions.

3 - Then look closely at the new disk layout and compare it to the layout of the old system. You are looking for things like was the old system OS level stripped and the new disk is unstripped, or the new disk consists on one RAID-5 stripe while the old system was unstripped. You went from cached disk to uncached disk etc...

4 - I take it that you did re-generate the statistics in the new system rather than use the imported statistics from the old system. This brings up the settings for init.ora parameters/Sort/Rollback. Numerous setting have an effect on the optimizer. You might want to compare setting for differences and make sure they are not related.

5 - Can the program be reran to verify the performance problem was not due to a conflict with another task: lockwaited, latch contention etc ...?

That is all that comes to mind except have you verified the performance problem is in the DB rather than in the application program(s) itself?

HTH -- Mark D Powell -- Received on Sat Nov 09 2002 - 21:10:44 CST