Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Slow Inserts

Re: Slow Inserts

From: <Jared.Still_at_radisys.com>
Date: Fri, 19 Mar 2004 10:12:18 -0800
Message-ID: <OF19567A65.A2F71AC4-ON88256E5C.0063BEBD-88256E5C.0063EA25@radisys.com>


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


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:08:53 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US