RE: Question about Append hint in Insert
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 18 Jun 2004 19:01:22 -0400
No Dave, youre right, as I mentioned, index changes will always log.
Only option there is to mark unusable, load table, then rebuild w/ =
nologging.
Jonathan, you're right of course, I saw APPEND and immediately assumeed =
NOLOGGING,
but that's not the case. At least, the original poster didn't mention
NOLOGGING.
Harvinder,
Allow me to summarize:
For optimal insert performance
1.) alter table ... nologging
2.) alter index ... unusable for each index on the table
3.) alter session set skip_unusable_indexes=3Dtrue
4.) INSERT /*+ APPEND */ from one session and commit
5.) rebuild all indexes w/ nologging
That will generate minimal redo and undo on the table inserts as well as =
the=20
index rebuilds. Note however, the recovery implications if you utilize=20
nologging. You'll need a backup of the tablespace your table is in. =
Since=20
there was no data written to the redo log (except extent invalidation =
records)
there's no way to recover that data in the event of a media failure.
Hope I didn't overlook or omit anything that time....
-Mark
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx on behalf of David Kurtz
Sent: Fri 6/18/2004 6:03 PM
To: oracle-l@xxxxxxxxxxxxx
Cc:=09
Subject: RE: Question about Append hint in Insert
But if the table has an index, and even if you use the NOLOGGING hint =
(and
create the table and index NOLOGGING too)
it appears that you still generate redo when maintaining the index.
The only way I have found to avoid redo on the index is to populate the
table and then build the index NOLOGGING.
(am I missing anything?)
Attached is a simple test script and my output on 9.2.0.5.
_________________________
David Kurtz
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Jonathan Lewis
Sent: 18 June 2004 22:33
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Question about Append hint in Insert
Mark,
APPEND stops table UNDO, not table REDO.
That point often gets hidden because the hint
is used so often in conjunction with nologging tables.
Otherwise your comments are correct - the benefit
applies only to the table segment, and you get the
exclusive table lock.
There is some potential for benefit on the indexes
because the index entries are per-sorted before
inserting, and there is an optimization that reduces
undo and redo if multiple rows goes into a single
leaf block.
There is some downside on the table because the
HWM is bumped for the append, so you could
leave a trail of empty space behind you as you
do lots of appends - but at 1,000 rows per insert,
the percentage waste of space is likely to be small.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated May 1st
----- Original Message -----
From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Friday, June 18, 2004 8:06 PM
Subject: RE: Question about Append hint in Insert
Harvinder,
1.) Should be no real disadvantage, other than an exclusive lock on the
table. More on that later.
2.) Hint will only apply to table. Any index updates that result from
table load will always log. If you want to, you could alter index ...
unusable, then do the load, then alter index ... rebuild nologging;
3.) The problem here is that an INSERT /*+ APPEND */ takes an
*exclusive* lock on the table. This prevents any other DML from running
on the table till that transaction commits. The second process will
wait on the exclusive mode TM enqueue that the first process is holding.
Hope that helps,
-Mark
Mark J. Bobak
Oracle DBA
ProQuest Company
Ann Arbor, MI
"Post Hoc Ergo Propter Hoc"
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Harvinder Singh
Sent: Friday, June 18, 2004 2:43 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Question about Append hint in Insert
Hi,
We are testing insert performance in one of out tables and we are
selecting from 1 table 1000 rows at a time and inserting in 2nd table.
So we were getting tps of about 17000 for 1 million records. Then I
added append hint to insert and tps went up to 23000. Now if I a try to
insert from 2 clients to increase scalibility both insert at tps of
11000. so I have 2 questions:
1) What is the disadvantage of adding append hint during insert (we
never delete or update this table in production)
2) Will the hint applicable to all the indexes or only table
3) Why with 2 clients using append result in less tps than normal 2
client inserts?
Thanks
--Harvinder
=3D3D20
----------------------------------------------------------------
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
-----------------------------------------------------------------
-- Binary/unsupported file stripped by Ecartis --
-- Type: application/octet-stream
-- File: nologdemo.LST
-- Attached file included as plaintext by Ecartis --
-- File: nologdemo.sql
set echo on head on message on feedback on trimspool on timi off pause =
off autotrace off
column name format a20
drop table dmk;
spool nologdemo
clear screen=20
select * from v$version;
create table dmk nologging
as select *
from dba_objects
where 1=3D2
;
truncate table dmk;
ttitle before-noindex
select s.name, s.value sysstat, m.value mystat
from v$sysstat s, v$mystat m
where s.name =3D 'redo size'
and s.statistic# =3D m.statistic#
/
insert /*+ APPEND NOLOGGING */ into dmk
select *=20
from dba_objects
where rownum <=3D 1000;
commit
/
ttitle after-noindex
select s.name, s.value sysstat, m.value mystat
from v$sysstat s, v$mystat m
where s.name =3D 'redo size'
and s.statistic# =3D m.statistic#
/
create index dmk
on dmk(owner, objecT_name)
nologging
;
ttitle before+index
select s.name, s.value sysstat, m.value mystat
from v$sysstat s, v$mystat m
where s.name =3D 'redo size'
and s.statistic# =3D m.statistic#
/
insert /*+ APPEND NOLOGGING */ into dmk
select *=20
from dba_objects
where rownum <=3D 1000;
commit
/
ttitle after+index
select s.name, s.value sysstat, m.value mystat
from v$sysstat s, v$mystat m
where s.name =3D 'redo size'
and s.statistic# =3D m.statistic#
/
spool off
drop table dmk;
----------------------------------------------------------------
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:
