RE: Question about Append hint in Insert
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 18 Jun 2004 15:06:09 -0400
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
=3D20
----------------------------------------------------------------
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:
