RE: Slow Inserts
- From: Riyaj Shamsudeen <rshamsud@xxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 19 Mar 2004 12:27:13 -0600
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:
1. 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.
2. Use nologging feature for inserts or better yet CTAS with
nologging
3. 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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
Jared.Still@xxxxxxxxxxx
Sent: Friday, March 19, 2004 12: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
-----------------------------------------------------------------
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.
Other related posts:
