Re: Performance issue in Oracle8i

"Rajendran Kumaraguru" <rajendran.k_at_pacific.net.sg> wrote in message news:<ac5517$hq3$1_at_newton3.pacific.net.sg>...
> 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).
>

I am not aware of globally or locally partitioned tables. Could you be referring to global and local indexes?

A good starting point would be to look in v$sqlarea and identify the problem SQL, and then find out it's access path. Depending on the outcome there are a number of things you can do. If you wish you can post your results to seek more help.

> 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 one hour the total (ProC+sqlldr) or sqlldr alone?

> Is there any way to solve this performance issue?
>
> Your help is very much appreciated.
>
>
> Thanks
> Guru
>
>
> --
> ----------------------------------------------------------------------------
> IMPORTANT NOTICE: This message and any attachment may contain privileged
> and/or confidential information. If you are not the addressee indicated in
> this message (or responsible for delivery of the message to such person),
> you may not copy or disseminate this message and any attachment to anyone.
> In such case, you should destroy this message and any attachments, and
> kindly notify us by reply email. We do not assure the security of
> information electronically transmitted, and your communication with us or
> request for communication through such means shall signify your acceptance
> of such risks. Conclusions and other information in this message that do
> not relate to the official business of Pacific Internet Limited shall be
> understood as neither given nor endorsed by it.
> ----------------------------------------------------------------------------
Received on Thu May 23 2002 - 20:59:08 CEST