RE: Slow Inserts

From: Powell, Mark D <mark.powell_at_EDS.COM>
Date: Fri, 19 Mar 2004 12:33:50 -0500
Message-ID: <564DE4477544D411AD2C00508BDF0B6A1CE0AA60@USAHM018.amer.corp.eds.com>

I may have misread this, but I believe you could replace the use of dynamic SQL with an insert select built using bind variables. As posted the code does not contain any error checking or commits though you omitted the routine, Load_Acct_Subset_Table. If no error handling or commits are done in the insert routine then you can replace this entire pl/sql routine with a single insert select.

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

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of mkline1_at_comcast.net Sent: Friday, March 19, 2004 11:40 AM
To: oracle-l_at_freelists.org
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 - 11:30:30 CST