ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

[janardan_ad]

Hi Erman,

We are encountering a strange issue, we have performed recently cross platform migration from On Prem Unix platform to Linux platform, post that we are observing an error with custom concurrent program where its failing due to temp error.

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

The same program in the previously done iteration completing within seconds but in the 2nd iteration instance its running for 20 min and then failing, as of now the temp size is around 150 GB, still the concurrent request is filing with same temp error , log file is attached.

[janardan_ad]

log_file_of_concurrent_request.txt

[ErmanArslansOracleBlog]

Check the relevant custom query which is used by that custom concurrent request.
According to your inputs, It seems the execution plan is changed for that query.
Analyze it with your developers and tune the query so that it will you less temporary segments.

[janardan_ad]

A08527_CR16623574_1121_102813.txt


Explain plan is attached, one of the query with bind variables causing the issue, since its PLSQL block we couldn't run tuning analyzer and plan hash value for this is 0.

Any inputs to tune this statement which has bind variables and tuning analyzer not giving any suggestions.

[ErmanArslansOracleBlog]

The problematic query seems to be with localizations.. (CLE)
I already told you that you need to concentrate on reducing temp usage. Sorting and complex joins for instance.. These triggers temp usage, and you should tune your query to minimize them..  You may decrease to inputs to those things by reducing the operations that provides the inputs.. For instance if you get lots of data using FTS and then sort them, you will use lots of temporary tablespace.
So, tuning to query is the way, but you have to have 2 perspectives, 1) trying to reduce the sorting operations and unnecessary complex joins, 2) reduce the data to be sorted or joined..
Please have a look with your developer(s)

Note that, if it is a standard query and if it is related with the localization, check oracle support for performance patches of the product CLE.

[janardan_ad]

Thanks Erman, actually we exported plan from an instance where its working fine and imported into the problematic instance post which the program is completing as expected.

Generally, is this recommended method to enhance performance or what's the criteria of steps to be performed as part of troubleshooting performance issue.
Generally, I follow below criteria.

- Gather stats
- Tuning SQL query
- Tuning analyzer recommendations (Like index creation etc)
- And final resort of exporting and importing the plan.

Could you confirm me if this holds to be an effective approach in addressing performance issues.

[ErmanArslansOracleBlog]

Good move, but in these migrations you should first test your workload in the new platform.. You may use Oracle Testing Suite, Oracle RAT and Database Replay for these things.

Other than that, your criteria seems ok, but you should do these things before taking things to PROD..

[janardan_ad]

Thanks for your feedback Erman, as customers prefer to migrate the workloads asap to Cloud platforms considering costing factor Real time testing will have less scope,

It was nice interacting with you!!