Home » RDBMS Server » Performance Tuning » Performance Issue while truncating table partition. (3 Merged) (Oracle 11g , Linux)
Performance Issue while truncating table partition. (3 Merged) [message #561638] Tue, 24 July 2012 08:53 Go to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 22506
Registered: January 2009
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.
Re: Performance Issue while truncating table partition. [message #564089 is a reply to message #561641] Tue, 21 August 2012 06:30 Go to previous message
Kevin Meade
Messages: 1926
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
Previous Topic: dbms_xplan.display_cursor
Next Topic: Slow query with full table scan
Goto Forum:
  


Current Time: Sat Jul 26 00:08:08 CDT 2014

Total time taken to generate the page: 0.20710 seconds