Oracle-L: RE: slow insert

From: Basavaraja, Ravindra <Ravindra.Basavaraja_at_T-Mobile.com>
Date: Mon, 24 Feb 2003 21:33:51 -0800
Message-ID: <F001.00557F23.20030224213351@fatcity.com>

Thanks for the inputs.

Here are some important results.

1)The insert into Original table with 43million records takes about 10minutes and the explain plan as attached in the text file "original table"
insert into original table.......
select ....... from a,b
where a.col1=b.col1 --->index columns
and a.col2=x -->non index
and b.col2=x -->non index

Note:Original table is having a composite primary key of 3 columns

2)I created a temp table similiar to the original table as create table temp1 as select * from originaltable where 1=2; this didn't create any index on the table and the insert got executed in 4 seconds. I am attaching the explain plan in the attachment "new table"

3)I created a composite primary key of the 3 cols on this new table and the insert took 13 seconds. The execution can be found in the same file "new table" at the end.

pls advice

thanks

-----Original Message-----

Sent: Monday, February 24, 2003 7:04 PM
To: Multiple recipients of list ORACLE-L

Ignoring bulk insert, and assuming you are performing a single insert statement have you looked at the following (as already suggested):

What, and how many, indexes exist on the destination table. Each index requires updating as records are inserted. If there are indexes not required then remove them. Alternatively you might even consider disabling the indexes before inserting the data then rebuilding them... Although not likely if you are performing a single insert with no other work.
Is col2 (in your example query) indexed in either table? What proportion of the table meets this selection criteria? IE: If col2 was gender and your data was evenly spread then selecting male would return ~50% of rows. If col2 was age and your data was evenly spread between 1 and 100 then selecting 20 would return ~1% of rows. If either of the selection returns a relatively low number of rows then look at indexing these columns.
Have you looked at an explain plan for this statement? If not, start sql*plus, type "set autotrace on", then execute the query. This will show if indexes are being used, etc. We may be able to help further if we know this information and know about your data.
Have you tried running the select statement on it's own, or inserting into a table which contains no indexes (and maybe has nologging set). This may give an idea of whether the time is consumed retrieving the data or inserting it into the destination. This will give you a good idea of where to focus your tuning efforts.
Have you tried a parallel hint? This may help depending on your physical configuration.

Regards,

Mark.

                                                                                                                             
                    "Basavaraja, Ravindra"                                                                                   
                    <Ravindra.Basavaraja_at_T-M       To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>       
                    obile.com>                     cc:                                                                       
                    Sent by:                       Subject:     RE: slow insert                                              
                    root_at_fatcity.com                                                                                         
                                                                                                                             
                                                                                                                             
                    25/02/2003 13:14                                                                                         
                    Please respond to                                                                                        
                    ORACLE-L

I tried this way and infact bulk collect took about 1 minutes more than my current case?

Any thoughts....

thanks

-----Original Message-----

Sent: Saturday, February 22, 2003 7:54 AM To: Multiple recipients of list ORACLE-L

You can read more abt it in PL/SQL User's Guide and Reference - Chapter 4 - Collections and Records !

anyway, below is the simple code to copy data from emp table. note that, after u fetch from the cursor, u have to check for the notfound condition at
the end. say, the emp table has 15 rows and u r fetching 2 rows at a time. during the 1st 7 fetches u will be ok. but during the 8th fetch, the fetch will bring only 1 row and the condition "notfound" will be true. thus u will
never process the last set of rows. hence, check for the condition after u have inserted/updated/deleted the rows.

finally, try playing with the limit clause in the fetch and see what number is best for u. considering my hardware etc, i got better performance with 5000 fetches at a time.

also, till 8.1.7. u could not have any stmts in the FORALL loop. only 1, i.e. insert/update/delete. but with 9i u can have a pl/sql block & any dynamic stmts too. moreover, with 9i u can also trap for individual exception whereas with 8.1.7. if there was an error, entire bulk process would rollback.

hope this helps & let me know if u need any more help.

code follows declare cursor c_emp is select empno, ename, hiredate from emp ;

type empno_arr_type is table of number(4); v_empno_arr empno_arr_type;

type name_arr_type is table of varchar2(10); v_ename_arr name_arr_type;

type date_arr_type is table of date;
v_hiredate_arr date_arr_type;

  v_arr_idx           binary_integer := 0;
  v_arr_cnt           binary_integer := 0;

begin
  open c_emp;
  loop
    fetch c_emp bulk collect

     into v_empno_arr, v_ename_arr, v_hiredate_arr
     limit 2;

DO NOT CHECK for notfound here

v_arr_cnt := v_empno_arr.count();
dbms_output.put_line(v_arr_cnt);

forall v_arr_idx in 1 .. v_arr_cnt
insert into emp_copy(empno, ename, hiredate)

values(v_empno_arr(v_arr_idx),v_ename_arr(v_arr_idx),v_hiredate_arr(v_arr_id

x));

commit;

note : the condition has to be checked here ONLY exit when c_emp%notfound;

end loop;
close c_emp;

end;
/

-----Original Message-----

Ravindra
Sent: Friday, February 21, 2003 7:09 PM
To: Multiple recipients of list ORACLE-L

Yes I am usng 8.1.7 EE.How do I use bulk inserts..Any docs or links on metalink plss?

-----Original Message-----

Sent: Friday, February 21, 2003 3:34 PM
To: Multiple recipients of list ORACLE-L

if u r having oracle 8.1.7 EE or higher, try using bulk inserts ! that surely will speed up !

and of course, u surely might have thought of APPEND hint & dropping/disabling indexes etc.

-----Original Message-----

Ravindra
Sent: Friday, February 21, 2003 3:08 PM
To: Multiple recipients of list ORACLE-L

hi,

I have an insert statement that will insert about 400000 records into a table having 43million records.The values for the insert statement are from a select statement that has a join.This query take
about 5-10minutes.What are the ways in
which we can speed up this process.the statement looks like

insert into .......
select ....... from a,b
where a.col1=b.col1 --->index columns
and a.col2=x -->non index
and b.col2=x -->non index

Thanks
--

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: <netmadcap_at_netzero.com
INET: netmadcap_at_netzero.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Basavaraja, Ravindra
INET: Ravindra.Basavaraja_at_T-Mobile.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: <netmadcap_at_netzero.com
INET: netmadcap_at_netzero.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
   Privileged/Confidential information may be contained in this message.
          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 deliver this message to anyone.
In such case, you should destroy this message and kindly notify the sender
           by reply e-mail or by telephone on (61 3) 9612-6999.
   Please advise immediately if you or your employer does not consent to
                Internet e-mail for messages of this kind.
        Opinions, conclusions and other information in this message
              that do not relate to the official business of
                         Transurban City Link Ltd
         shall be understood as neither given nor endorsed by it.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Mark Richard
INET: mrichard_at_transurban.com.au

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Basavaraja, Ravindra
INET: Ravindra.Basavaraja_at_T-Mobile.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

text/plain attachment: original_table.txt

text/plain attachment: new_table.txt

Received on Mon Feb 24 2003 - 23:33:51 CST