Re: Can ORA-01013 error cause by Temp TS contention?

  • From: Stefan Koehler <contact@xxxxxxxx>
  • To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>, keyantech@xxxxxxxxx
  • Date: Mon, 10 Nov 2014 11:57:24 +0100 (CET)

Hi Karth,
i am not quite sure what your DBAs specify with "TS enq contention in TEMP 
table space", but i assume that they are talking about the TS/SS enqueue
(and DFS lock handle).

This is a pretty common issue with your temporary tablespace as it is shared 
between your 6 RAC nodes. Your temporary tablespace is split into extents
and each instance caches a part of these extents in its SGA. You can crosscheck 
this with view gv$temp_extent_pool. The whole extent caching procedure
is based on soft reservation (extent caching and uncaching leads to SS Enqueue 
and DFS lock handle).

Unfortunately you have not provided the amount of temporary tablespace files, 
but there is the general guideline for temporary tablespaces in RAC:

- Create the same amount of temporary data files in every temp tablespace as 
the amount of RAC nodes (in your case there should be 6 temporary data
files for your temp tablespace)
- Create a new user and a separate temp tablespace with application affinity to 
a single node, if only your application uses that amount of temporary
tablespace

Riyaj Shamsudeen has also written some blog post about this some time ago: 
http://orainternals.wordpress.com/2012/02/13/temporary-tablespaces-in-rac/

Best Regards
Stefan Koehler

Oracle performance consultant and researcher
Homepage: http://www.soocs.de
Twitter: @OracleSK

> Karth Panchan <keyantech@xxxxxxxxx> hat am 10. November 2014 um 05:26 
> geschrieben:
>
> All
>
> We are running Production Oracle 11.2.0.2 with 6 node RAC.
>
> Our OLTP and Reports sharing same schema with single Temp table space.
>
> Our OLTP App servers(.net) getting 40 occurrence of ORA-01013 error per hour. 
> Application have time out set for 30secs with Oracle connections.
>
> Some occurrence generate Trace file with entry in alert.log, but some NO 
> entry in alert.log.
>
> From trace file took sql and ran in production it came out instantly. SQL 
> wasn't cancelled by any end user or by time out due to no response from
> Oracle.
>
> Talking to Production DBA they inform me this error is caused by TS enq 
> contention in TEMP table space caused by Reports running with large volume
> of data.
>
> My question was, then why my SQL plus comes with quick response all the time. 
> I was informed my session may be ran when there was no contention.
>
> I am not convinced because my session was ran when our App Servers receiving 
> error and reports sessions were running.
>
> Anyone agree with this TEMP TS contention cause ORA-01013 (agree I may be 
> missing something here) or any other scenarios?
>
> Appreciate your input.
>
> Thanks
> Karth--
> //www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l


Other related posts: