Home » RDBMS Server » Performance Tuning » truncate table (Linux 2.6.18-92.1.13.el5)
truncate table [message #539309] Fri, 13 January 2012 04:17 Go to next message
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.
Re: truncate table [message #539311 is a reply to message #539309] Fri, 13 January 2012 04:21 Go to previous messageGo to next message
Michel Cadot
Messages: 67946
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Activate the trace on the session that executes the procedure.

Regards
Michel
Re: truncate table [message #539315 is a reply to message #539309] Fri, 13 January 2012 04:25 Go to previous messageGo to next message
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.
Re: truncate table [message #539317 is a reply to message #539315] Fri, 13 January 2012 04:45 Go to previous messageGo to next message
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'
Re: truncate table [message #539318 is a reply to message #539317] Fri, 13 January 2012 04:50 Go to previous message
Michel Cadot
Messages: 67946
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, a SQL trace.

Regards
Michel
Previous Topic: Query running more time
Next Topic: Oracle10g Standard Edition on Linux machine
Goto Forum:
  


Current Time: Sun Sep 19 00:08:45 CDT 2021