Re: Performance Problems with Ora 7.33

Uwe Schneider wrote in message <36419371.ED5B3F7E_at_xlink.net>...

>after finally finished upgrading from Oracle 7.23 to 7.3.3 we
>are experiencing dramatical performance decays with queries, esp. with
>complex joins which used to run flawlessly.

In my experience you can "blame" the CBO for that. With fairly large tables, we had to resort to use estimate stats as oppose to compute stats. As the tables grow larger, we had to decrease the percentage of data used for the estimation (currently at 10%).

We experienced exactly the same problem as you're having. One month the month-end jobs will run without any problems, and the next month they decrease by more than a 1000% in performance. The reason was that the statistics were not accurate enough for the CBO to make the right decision ito the execution plan to use. One month it may use a hash join, the next month it may decide to use a nested loop join instead.

Very frustrating. The only solution was to determine the best execution plan and then hardcode the hints into the month-end jobs.

Having said that, sometimes we ourselves were to blame - like dropping indexes for loading data quicker, re-running the re-create of the indexes, but then failing to notice that one of the indexes fail because of lack of space due to the growth of the table. :-)

To get a hand on performance problems, use EXPLAIN PLAN and dig around in the v$session_event, v$session_wait and v$sesstat tables for clues.

regards,
Billy Received on Thu Nov 05 1998 - 06:21:58 CST