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

From: Stefan Koehler <contact_at_soocs.de>
Date: Mon, 10 Nov 2014 11:57:24 +0100 (CET)
Message-ID: <154924426.4384.1415617044647.open-xchange_at_app04.ox.hosteurope.de>

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: _at_OracleSK

> Karth Panchan <keyantech_at_gmail.com> 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--
> http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Nov 10 2014 - 11:57:24 CET