Re: Slow Inserts
- From: "Juan Cachito Reyes Pacheco" <jreyes@xxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 19 Mar 2004 15:16:37 -0400
Hi Mkline, if what you want is to ooooptimize the process I suggest you the
following
1. I think you should try to do one in one select
this is insert into xxx select from xxxx, you can use case to do some
changes.
2. You should use append hint insert /*+ append */ into, if possible
3. If possible do a
ALTER TABLE ADM.ANF_RATIOS_ME NOLOGGING;, and a full backup after the insert
4. if you can't use an append hint, you could try to commit every 1,000,000
records.
5. the indexes of your table, should be in other tablespaces in other
physical disk.
Here is the text from my paper, I hope this be useful.
0) Always if possible try to do all in one command
Insert into DESTINY select * from SOURCE, you can use CASE to modify data
in the same source select.
1) To execute several inserts, deletes or updates you can disable the
logging, this means you will have to do a full back up after that, because
it eliminates redo generation, needed for backups in archivelog mode.
You can do directly in the table
ALTER TABLE ADM.ANF_RATIOS_ME NOLOGGING;
2) …, remember indexes will save log information.
, this bypasses undo generation, your table will have to be commit, before
issuing this command and after issuing if you want to access it again.
This is completely safe.
3) Analyze the use of import or load utilities to load that table or data,
usually is the fastest.
4) When you have to insert (if not exists) and update if it exists you can
use MERGE command
5) If you are using loops, to insert data, use bulk collect.
6) If you are not using APPEND hint and if is possible in the logic of your
program, you can do frequently commits, to avoid undo size, growing too
much.
----- Original Message -----
From: <mkline1@xxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Friday, March 19, 2004 12:40 PM
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
> -----------------------------------------------------------------
>
----------------------------------------------------------------
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:
