Home » SQL & PL/SQL » SQL & PL/SQL » Append Hint in oracle
Append Hint in oracle [message #661436] Fri, 17 March 2017 10:38 Go to next message
amit.sonar
Messages: 98
Registered: December 2009
Location: Mumbai
Member
Hi All,

what would be the impact if I use PARALLEL & APPEND hint together in INSERT OR MERGE statement?

Also what would be the impact if I use APPEND hint in insert statement and same insert or merge statement executed in parallel in multiple session? Also the impact of APPEND hint use in different dml statement like MERGE OR INSERT which will be executed in parallel in different sessions?

Thanks
Amit

[Updated on: Fri, 17 March 2017 10:44]

Report message to a moderator

Re: Append Hint in oracle [message #661437 is a reply to message #661436] Fri, 17 March 2017 11:08 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Are you incapable or just unwilling to run some actual DML yourself to measure the results yourself?
Re: Append Hint in oracle [message #661438 is a reply to message #661436] Fri, 17 March 2017 11:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

He'd be the impact resulting of using such hints, parallelism, statements...

Re: Append Hint in oracle [message #661442 is a reply to message #661436] Fri, 17 March 2017 12:38 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
APPEND requires a table lock. Do you really want that?
Re: Append Hint in oracle [message #661454 is a reply to message #661437] Fri, 17 March 2017 14:59 Go to previous messageGo to next message
amit.sonar
Messages: 98
Registered: December 2009
Location: Mumbai
Member
Well Not exactly. I do use both the hints. As per my understanding APPEND can cause high water mark in the tables since it uses DIRECT PATH to write the data and it writes the the at the end of a table which creates sparse blocks in a table. I want know more consequences of APPEND hint and if run it parallaly.

Thanks.

[Updated on: Fri, 17 March 2017 15:00]

Report message to a moderator

Re: Append Hint in oracle [message #661455 is a reply to message #661442] Fri, 17 March 2017 15:01 Go to previous messageGo to next message
amit.sonar
Messages: 98
Registered: December 2009
Location: Mumbai
Member
APPEND requires a table lock Could you please explain it?

Thanks
Re: Append Hint in oracle [message #661456 is a reply to message #661454] Fri, 17 March 2017 15:12 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
amit.sonar wrote on Fri, 17 March 2017 12:59
Well Not exactly. I do use both the hints. As per my understanding APPEND can cause high water mark in the tables since it uses DIRECT PATH to write the data and it writes the the at the end of a table which creates sparse blocks in a table. I want know more consequences of APPEND hint and if run it parallaly.

Thanks.
post EXPLAIN PLAN for each of the alternatives
Re: Append Hint in oracle [message #661460 is a reply to message #661455] Sat, 18 March 2017 03:44 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
amit.sonar wrote on Fri, 17 March 2017 20:01
APPEND requires a table lock Could you please explain it?

Thanks
Amit, if a statement requires a table lock then
A) the statement cannot run if any other session has a row lock already
B) when the statement is running and has not committed, no other session can acquire a row lock
This is very basic, you know. There is a chapter in the Concepts Guide titled Data Concurrency And Consistency that you may want to read.
Re: Append Hint in oracle [message #661480 is a reply to message #661442] Mon, 20 March 2017 07:31 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
John Watson wrote on Fri, 17 March 2017 13:38
APPEND requires a table lock. Do you really want that?
John are you sure? All the append hint does is tells oracle to start inserting after the high water mark so it doesn't have to search for available extents. All indexes are maintained during the inserts and only the new rows would be locked until the commit. It is very possible that I am wrong, can you provide a link to the information?
Re: Append Hint in oracle [message #661481 is a reply to message #661480] Mon, 20 March 2017 07:45 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
You can't even query direct path loaded objects until you commit. Same for parallel DML.

It will throw

ORA-12838: cannot read/modify an object after modifying it in parallel
Re: Append Hint in oracle [message #661485 is a reply to message #661481] Mon, 20 March 2017 09:44 Go to previous message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Just to clarify - in same session.

SY.
Previous Topic: ORA-14028
Next Topic: need help in setting constraints for the column based on subquery (2 threads merged by bb)
Goto Forum:
  


Current Time: Thu Mar 28 17:27:54 CDT 2024