Home » RDBMS Server » Performance Tuning » Poor Insert Performance after table truncation was interupted
icon4.gif  Poor Insert Performance after table truncation was interupted [message #284681] Fri, 30 November 2007 09:33 Go to next message
williamhwh
Messages: 1
Registered: November 2007
Location: Kuala Lumpur
Junior Member
We have a OLAP database and loading activities is performed by operator. Unfortunately, she mistakenly truncate the wrong table but halfway, she cancel the job. Now, the monthly loading activity has degrades/hang until 1 row(or none) per second.

It is a 17GB table and one of the partition is holding up 14GB (64MB extents). All our tablespace is using ASSM. The problem is we do not know what causes the slow insert.

1) Could it be fragmentation? How can check for tablespace fragmentation?

2) I have searched some bugs from Metalink but the bugs seems doesn't apply to my case where I did not get any ORA-600 error.

Bug 3279497 - Interupted (Ctrl-C) TRUNCATE can corrupt ASSM table
Bug 3610777 - Excessive CPU from INDEX insert into ASSM index segment

3) Is there any possible buffer chain latches issue? How can I verify it?


Any suggestion/advice will be helpful. Thanks a lot!



William Ha
Re: Poor Insert Performance after table truncation was interupted [message #284682 is a reply to message #284681] Fri, 30 November 2007 09:35 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Try some/all of the suggestion in the URL above.
Re: Poor Insert Performance after table truncation was interupted [message #284711 is a reply to message #284681] Fri, 30 November 2007 11:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1) No, it is not possible to get fragmentation on insert and fragmentation is meaningless in ASSM

2) Bugs are dependent on version, post it with 4 decimals

3) How to Identify Performance Problem and Bottleneck

Regards
Michel


[Updated on: Sat, 01 December 2007 13:43]

Report message to a moderator

Re: Poor Insert Performance after table truncation was interupted [message #284841 is a reply to message #284681] Sat, 01 December 2007 13:30 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Run and post TKPROF + event 10046.

Previous Topic: Removing Duplicates Query
Next Topic: Question about joins
Goto Forum:
  


Current Time: Sat Dec 10 05:02:11 CST 2016

Total time taken to generate the page: 0.13639 seconds