RE: Slow Inserts
- From: "Mercadante, Thomas F" <thomas.mercadante@xxxxxxxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 19 Mar 2004 13:31:35 -0500
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@xxxxxxxxxxx [mailto:Jared.Still@xxxxxxxxxxx]
Sent: Friday, March 19, 2004 1:12 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxx
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
03/19/2004 08:40 AM
Please respond to oracle-l
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
