From oracle-l-bounce@freelists.org Fri Mar 19 12:29:00 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i2JIT0Q21272 for ; Fri, 19 Mar 2004 12:29:00 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i2JISxo21263 for ; Fri, 19 Mar 2004 12:28:59 -0600 Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B3CD3390CA0; Fri, 19 Mar 2004 13:27:00 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 19 Mar 2004 13:25:55 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from GAVHSEN0A1AD.HSEN (exchsen0a1ra.dfa.state.ny.us [198.22.236.246]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id CFB7E390B96 for ; Fri, 19 Mar 2004 13:25:12 -0500 (EST) Received: from unknown(172.16.96.46) by GAVHSEN0A1AD.HSEN via csmap id 8dfb1314_79d3_11d8_9bef_0002b3c8ffd5_23623; Fri, 19 Mar 2004 13:30:53 -0500 (EST) Received: from snysdolmail1.labor.state.ny.us ([172.16.96.117]) by exchsen0a1ra.dfa.state.ny.us with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2657.72) id HGNY86DD; Fri, 19 Mar 2004 13:31:36 -0500 Received: by snysdolmail1.labor.state.ny.us with Internet Mail Service (5.5.2650.21) id ; Fri, 19 Mar 2004 13:31:34 -0500 Message-ID: X-Sybari-Trust: 9984274c 61ed6794 eec771c9 0000013d From: "Mercadante, Thomas F" To: "'oracle-l@freelists.org'" Subject: RE: Slow Inserts Date: Fri, 19 Mar 2004 13:31:35 -0500 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2650.21) Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C40DE0.67AF65C0" X-archive-position: 1283 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: thomas.mercadante@labor.state.ny.us Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l ------_=_NextPart_001_01C40DE0.67AF65C0 Content-Type: text/plain Mike, First of all, I don't think 6 million rows an hour is bad. But I think that the way the thing is written slows the process down. The way I look at it, your are performing 6 million individual insert statements. rather than one insert into...select from statement. Your way is much slower than the other way. I would try and find a way to combine all of the inerts/selects you are doing into one statement. I tried, but can't make sense out of what the query is doing. This is what it looks like it's doing to me. Note that this is a big assumption on my part as I don't know what the Load_Acct_Subset_Table procedure is doing. Ther query below also seems to be mssing any type of 'where' clause for the V_LDGR_ACCT_HIER VLGR view. Insert Into T_RPT_ACCT_SUBSET_TEMP (acct_subset_cd, acct_nbr) Select TRPT.acct_subset_cd, VLGR.Acct From V_LDGR_ACCT_HIER VLGR, T_RPT_ACCT_SUBSET TRPT Where TRPT.node_lvl_nbr = TRPT.node_nm and TRPT.node_lvl_nbr is null; Good Luck! Tom Mercadante Oracle Certified Professional -----Original Message----- From: Jared.Still@radisys.com [mailto:Jared.Still@radisys.com] Sent: Friday, March 19, 2004 1:12 PM To: oracle-l@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@comcast.net Sent by: oracle-l-bounce@freelists.org 03/19/2004 08:40 AM Please respond to oracle-l To: oracle-l@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@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 ----------------------------------------------------------------- ------_=_NextPart_001_01C40DE0.67AF65C0 Content-Type: text/html
Mike,
 
First of all, I don't think 6 million rows an hour is bad.  But I think that the way the thing is written slows the process down. 
The way I look at it, your are performing 6 million individual insert statements. rather than one insert into...select from statement.  Your way is much slower than the other way.
 
I would try and find a way to combine all of the inerts/selects you are doing into one statement.  I tried, but can't make sense out of what the query is doing.  This is what it looks like it's doing to me.  Note that this is a big assumption on my part as I don't know what the Load_Acct_Subset_Table procedure is doing.  Ther query below also seems to be mssing any type of 'where' clause for the V_LDGR_ACCT_HIER VLGR view.
 
 
Insert Into T_RPT_ACCT_SUBSET_TEMP (acct_subset_cd, acct_nbr)
 Select TRPT.acct_subset_cd, VLGR.Acct
        From V_LDGR_ACCT_HIER VLGR, T_RPT_ACCT_SUBSET TRPT
  Where TRPT.node_lvl_nbr = TRPT
.node_nm
  and   TRPT.node_lvl_nbr is null;
 
Good Luck!

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
From: Jared.Still@radisys.com [mailto:Jared.Still@radisys.com]
Sent: Friday, March 19, 2004 1:12 PM
To: oracle-l@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@comcast.net
Sent by: oracle-l-bounce@freelists.org

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

       
        To:        oracle-l@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@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
-----------------------------------------------------------------


------_=_NextPart_001_01C40DE0.67AF65C0-- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@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 -----------------------------------------------------------------