RE: Slow Inserts

From: Riyaj Shamsudeen <rshamsud_at_jcpenney.com>
Date: Fri, 19 Mar 2004 12:27:13 -0600
Message-id: <001901c40ddf$cccf6af0$212f200a@rshamsudxp>

('binary' encoding is not supported, stored as-is)

This code is processing over 42 million rows in 7 hours. The procedure Load_Acct_subset_table is called for each row. I would do the following:

Convert this single row operation in to a set operation. I am not sure what this procedure (load_acct_subset) does, but this is better to rewrite so that some kind of join between these tables done and the data is directly loaded in to a table completely avoiding row level operation.
Use nologging feature for inserts or better yet CTAS with nologging
It is also cheaper to convert the insert statement outside the loop : insert /*+ append */ w/ table changed to nologging mode..

Of course, nologging mode *will* affect the recovery...

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
Usual disclaimers apply.. This opinion does not bind my employer etc..etc.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jared.Still_at_radisys.com
Sent: Friday, March 19, 2004 12:12 PM
To: oracle-l_at_freelists.org
Subject: Re: Slow Inserts

In addition to Mark's comments, I am wondering about a couple of things.

The first is almost trivial, but not quite.

The record v_rpt_acct_subset_record is based on the table t_rpt_acct_subset.
It would be better practice to base it on the cursor c_rpt_acct_subset. One less thing
to worry about should you change the cursor definition in the future.

The other concerns the SQL string. The code for the procedure Load_Acct_Subset_Table is
not visible, so it's kind of hard to comment on.

Try using bind variables with 'execute immediate .. using ... '

HTH Jared

mkline1_at_comcast.net
Sent by: oracle-l-bounce_at_freelists.org

03/19/2004 08:40 AM
Please respond to oracle-l

        
        To:        oracle-l_at_freelists.org 
        cc:         
        Subject:        Slow Inserts

Client has what appears to be a VERY simple insert program, but this is taking forever.

Are there any "basic" things that might help with speed?

This takes about 7 hours and does 6,000,000 rows per hours.

This is HP-UX and 8.1.7.3.4... Seems pretty fast, but they'd like to get it up a bit. It's been so long for me though being the Production DBA side most of the time. Arraysize? buffers?

This is about an 600-800gb database.

Declare

   v_insert_clause        varchar2(500);
   v_set_clause           varchar2(500);
   v_select_from_clause   varchar2(500);
   v_where_clause         varchar2(500);
   v_sql_string           varchar2(900);

Cursor c_rpt_acct_subset IS

Select *

     From T_RPT_ACCT_SUBSET
                Where node_lvl_nbr is not null;

v_rpt_acct_subset_record t_rpt_acct_subset%ROWTYPE;

Begin

Open c_rpt_acct_subset;

LOOP Fetch c_rpt_acct_subset INTO v_rpt_acct_subset_record;

EXIT WHEN c_rpt_acct_subset%NOTFOUND;

v_insert_clause := 'Insert Into T_RPT_ACCT_SUBSET_TEMP

                           (acct_subset_cd, acct_nbr)';

v_select_from_clause := ' Select
'''||v_rpt_acct_subset_record.acct_subset_cd||''', Acct

                               From V_LDGR_ACCT_HIER ';

v_where_clause := 'Where '||v_rpt_acct_subset_record.node_lvl_nbr|| ' = '''|| v_rpt_acct_subset_record.node_nm|| '''';

v_sql_string := v_insert_clause || v_select_from_clause || v_where_clause;

Load_Acct_Subset_Table(v_sql_string);

END LOOP; Close c_rpt_acct_subset;

Insert Into t_rpt_acct_subset_temp

                (acct_subset_cd, 
                 acct_nbr)
     Select acct_subset_cd,
                     node_nm
       From T_RPT_ACCT_SUBSET
                  Where  node_lvl_nbr is null;

END;
/

-- Michael Kline, Principle Consultant Business To Business Solutions 13308 Thornridge Ct Midlothian, VA 23112 804-744-1545 ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------

The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If the reader of this message is not the intended recipient, you are hereby notified that your access is unauthorized, and any review, dissemination, distribution or copying of this message including any attachments is strictly prohibited. If you are not the intended recipient, please contact the sender and delete the material from any computer. ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------

Received on Fri Mar 19 2004 - 12:24:48 CST