| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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
 |
 |