OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » truncate table (Linux 2.6.18-92.1.13.el5)

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

truncate table [message #539309]

Fri, 13 January 2012 04:17

dba_7722
Messages: 197
Registered: August 2010
Location: Delhi

Senior Member

Hello Expert,

We have a procedure, which do truncate to some of the tables. Most of the time it finished in short of spam of time. But from last few days, it is taking much longer time.

Shall you please hint me, from where should i start the investigation.

Thanks.

Report message to a moderator

Re: truncate table [message #539311 is a reply to message #539309]

Fri, 13 January 2012 04:21

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Activate the trace on the session that executes the procedure.

Regards
Michel

Report message to a moderator

Re: truncate table [message #539315 is a reply to message #539309]

Fri, 13 January 2012 04:25

jrnayak
Messages: 35
Registered: November 2011
Location: London

Member

the table that takes a long time has tons of dirty blocks in the cache.
when you truncate - do segment level checkpoints first - and then truncate. You are waiting for
the IO to complete.

Report message to a moderator

Re: truncate table [message #539317 is a reply to message #539315]

Fri, 13 January 2012 04:45

dba_7722
Messages: 197
Registered: August 2010
Location: Delhi

Senior Member

Thanks Experts.

I will check this time with segment level checkpoints and update forum also with result.

Michel, are you asking for below trace..??

alter session set events '10046 trace name context forever, level 12'

Report message to a moderator

Re: truncate table [message #539318 is a reply to message #539317]

Fri, 13 January 2012 04:50

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Yes, a SQL trace.

Regards
Michel

Report message to a moderator

Previous Topic:	Query running more time
Next Topic:	Oracle10g Standard Edition on Linux machine

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Feb 21 03:56:42 CST 2025