OraFAQ Forum: Performance Tuning » Performance Issue while truncating table partition. (3 Merged)

Home » RDBMS Server » Performance Tuning » Performance Issue while truncating table partition. (3 Merged) (Oracle 11g , Linux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Performance Issue while truncating table partition. (3 Merged) [message #561638]

Tue, 24 July 2012 08:53

palanisvr
Messages: 21
Registered: March 2007

Junior Member

HI Gurus,

I have two partitions on table named Partition A and Partition B .

I am inserting the data into the partitioned table and truncating Partition B at the same time.

Thus ,the truncating is taking very long time . Please advise if we can do any thing to optimise the Truncate performace.

Scenario :

Session 1 :

Insert into partitioned_table ()

Session 2 :

Alter table partitioned_table truncate partition PARTITION B

Thanks in advance.

[EDITED by LF: removed superfluous empty lines]

[Updated on: Tue, 24 July 2012 09:14] by Moderator

Report message to a moderator

Re: Performance Issue while truncating table partition. [message #561641 is a reply to message #561638]

Tue, 24 July 2012 09:00

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

>Please advise if we can do any thing to optimise the Truncate performace.
issue COMMIT after the INSERT might improve the situation.

Report message to a moderator

Re: Performance Issue while truncating table partition. [message #564089 is a reply to message #561641]

Tue, 21 August 2012 06:30

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

I would guess this is a locking issue. The insert locks the table against DDL operation and I believe TRUNCATE is classified as DDL because it does an implicit commit; thus you cannot truncate until the insert is commited.

Kevin

Report message to a moderator

Previous Topic:	dbms_xplan.display_cursor
Next Topic:	Slow query with full table scan

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 21 16:00:57 CST 2024