When a query coverts a column using TO_CLOB, a TEMPORARY LOB SEGMENT is created in the temporary tablespace.  If the result set is sorted, then this segment can become large and if the query is called concurrently by multiple sessions, it can be the cause of a lot of contention for the temp file headers, eg buffer busy waits.  The TEMPORARY LOB SEGMENT blocks are buffered and the DBWRs write them to the tempfiles (rather than the shadow process writing directly).

One way to reduce the performance impact is to avoid the sort by creating an index with the columns in the order by clause.  Alternatively, you might be able to sort the result set before using the TO_CLOB function, which allows efficient pipelining of the data.

If using UFS, then check the tempfiles are not sparse.

Consider using more tempfiles.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s