Re: Oracle8i performance

On Sat, 18 May 2002 05:52:02 -0400, Rajendran Kumaraguru wrote:

> Hi,
>
> We had a problem while inserting the records into a global partition
> table. 1. We have pro*c program to insert the records to a global
> partition table let's say table1 it has about 120 million records per
> year (10 million per month).
>
> 2. While inserting the records the program will do the select from
> another table let's say table 2 ( size 10million records, global
> partition) and do the insert or update to table2 by using proper index.
>
> 3. Finally the program is inserting the records to another 2 local
> partition tables (let's say table 3,4).
>
> We used sql loader with 2 database triggers: Trigger one will fire only
> on table 2. Trigger two will fire to table 3 and 4.
>
> Currently we had the problem on this process, we are loading the data by
> every hour but it is taking more than one hour for just 22,000 records,
> we are not able to complete each hour process within the hour itself.
> This problem occurred only after we migrated to Oracle8i.
>
> Is there any way to solve this performance issue?
>
> Your help is very much appreciated.
>
>
> Thanks
> Guru

Well, however ridiculuos this may sound, I'd start from examining v$session_event and v$session_wait to find out what are you waiting for. When you find that out, you do what is necessary to tune the application. Another alternative is to turn on the event 10046 and send the tracefiles to Cary Milsap (http://www.hotsos.com) who will gladly analyze them for you for a small fee. Hotsos professionals are even available to tune the application for you. And no, I do not work for them. They have, probably, never heard of my name. Received on Sun May 19 2002 - 23:10:03 CDT