Re: Slow Inserts

From: Juan Cachito Reyes Pacheco <jreyes_at_dazasoftware.com>
Date: Fri, 19 Mar 2004 15:16:37 -0400
Message-ID: <009601c40de6$bbb1b000$2501a8c0@dazasoftware.com>

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.

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_at_comcast.net> To: <oracle-l_at_freelists.org> 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_at_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
> -----------------------------------------------------------------
>

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_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
-----------------------------------------------------------------

Received on Fri Mar 19 2004 - 13:17:10 CST