But we are already using bulk insert as mentioned in original post: " but we are simulating by storing 1M records in 2nd table and using bulk collect/forall selecting 1000 rows at a time and insert into 1st table" -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Juan Carlos Reyes Pacheco Sent: Tuesday, July 27, 2004 5:07 PM To: oracle-l@xxxxxxxxxxxxx Subject: RE: Insert Performance question BULK INSERT example =20 DECLARE =20 tCOC_CODCLI DBMS_SQL.NUMBER_TABLE; tCOC_CTACORR DBMS_SQL.NUMBER_TABLE; tCUOTAS DBMS_SQL.NUMBER_TABLE; tCOC_COMISION DBMS_SQL.NUMBER_TABLE; tCOC_COMISION_EXITO DBMS_SQL.NUMBER_TABLE; =20 BEGIN =20 SELECT CUF_CODCLI, CUF_CTACORR, NVL(( CUF_DBCUO - CUF_CRCUO ),0 ) CUOTAS BULK COLLECT INTO tCOC_CODCLI, tCOC_CTACORR, tCUOTAS FROM CUENTASF WHERE NOT NVL(( CUF_DBCUO - CUF_CRCUO ),0 )=3D0; =20 FOR i IN 1..tCOC_CTACORR.count LOOP =20 tCOC_COMISION_EXITO(i) :=3D ROUND( nPeso * nParComisionExito, 2 ); END LOOP; =20 FORALL I IN 1..tCOC_CTACORR.count INSERT INTO FON.COMCLI_RW ( COC_CODCLI, COC_CTACORR, COC_FECHA, COC_COMISION, COC_DSC, COC_COMISION_EXITO ) VALUES ( tCOC_CODCLI(i), tCOC_CTACORR(i), dFecha, tCOC_COMISION(i), cDs, tCOC_COMISION_EXITO(i) ) ; Juan Carlos Reyes Pacheco OCP -------Original Message------- =20 From: oracle-l@xxxxxxxxxxxxx Date: 07/27/04 17:02:47 To: oracle-l@xxxxxxxxxxxxx Subject: RE: Insert Performance question =20 Juan, =20 0) already increased 1) nope we are using sql laoder to insert into 2nd table 3) we can't do this since other users might be reading using indexes in real application mode =20 Thanks --Harvinder =20 =20 -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Juan Carlos Reyes Pacheco Sent: Tuesday, July 27, 2004 3:50 PM To: oracle-l@xxxxxxxxxxxxx Subject: Re: Insert Performance question =20 Hi, Harvinder some ideas 0) incrase your table size to amount required. 1) could you use sql loader instad of insert? 2) if not try append and set to nologging mode and do a full backup after. 3) disable all: index, contrints (specially foreing constrains to this table), triggers. and enable after, you can enable constrains using novalidate if you know it s ok. and reindex =3D20 Juan Carlos Reyes Pacheco OCP -------Original Message------- =3D20 From: oracle-l@xxxxxxxxxxxxx Date: 07/27/04 15:45:43 To: oracle-l@xxxxxxxxxxxxx Subject: Insert Performance question =3D20 Hi, =3D20 We are having insert performance problem and need some suggestions on tuning.=3D3D20 Overview of the scenario ------------------------- In our application, one of the main components is insertion of about 2 million records into big usage table by selecting 1000 rows in a batch from 2nd table. In real scenario second table will be truncated after every 1000 records but we are simulating by storing 1M records in 2nd table and using bulk collect/forall selecting 1000 rows at a time and insert into 1st table. Also we are doing the same test on SQL Server. With 2 sessions inserting 1M each by selecting from same table and inserting into 1st table inserts_rows_per_Second on SQL Server is 25000 whereas on Oracle is only 10000. Tab1 is having about 80M rows, 1 primary key on 2 columns, 1 unique key on varbinary column, 2 1 column indexes. I checked with trace 10046, iostat, v$views and following are the waitstats: Oracle is spending most of the time reading from the index tablespace(where all the 4 indexes are stored) and corresponding wait event is "db file sequential read" 2nd time consuming wait event is "log file sync" And then "read by other session" etc =3D20 Configuration -------------- Oracle 10g on Red hat linux 9. 2 14 disk 500G each raid 0 array and 1 34G scsi hard drive Boot,swap,OS on 34 G drive Index tablespace,system,redo logs on 1 raid array Tab1,tab2, rollback on 2nd raid array Tab2 is IOT. Tablespace1 which stored data for tab1 and Index tablespace both are having 5 10G files each with uniform extent size of 16M. Database block size is 8k. RAM on system is 2G and SGA size is 1.2G, data_buffer_cache having 1G Log buffer size is 4M, 3 Log files each having 2G size. Using AUTO UNDO and tabelspace size is 10G Database is in NOARCHIVING mode. 2 cpu's and cpu usage is about 30-55% for both sessions.=3D3D20 =3D20 What can be the possible options that we can try to speed up the insert performance? =3D20 Thanks --Harvinder =3D3D20 =3D20 =3D20 =3D20 ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- =20 ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------