PSAPTEMP Used Daily Growth - XI System

[Former Member]

We are currently experiencing a used space growth issue with PSAPTEMP tablespace. We had this issue about 2 months ago and it reached 100% used and it was only resolved by bouncing the DB.

I opened a SAP message and it was determined

"As per the attached document the most objects are lob segments

please refer to note 500340. "

I have revewied all the SAP notes that seem to be relavent to this but can't find an actual resolution to the issue.

When I check V$SORT_USAGE is see 20+ LOB_DATA or LOB_INDEX TEMPORARY entries that reside in PSAPTEMP.

When I check V$TEMPORYAR_LOBS is see entries indicating CACHE_LOBS.

I am not sure how to proceed further with determining what is the root cause and a resolution.

Date	Used (kb)	Used %
5/8	72,832,000	51
5/7	68,995,072	48
5/6	54,336,512	38
5/5	39,149,568	27
5/4	24,988,672	17
5/3	11,931,648	8
5/2	9,745,408	6
5/1	5,886,976	4
4/30	8,956,928	6
4/29	8,113,152	5
4/28	6,899,712	4
4/27	6,014,976	4

Thanks

[stefan_koehler · Accepted solution]

Hi Brian,

unfortunately you have not provided your exact Oracle version, but there is a known and unfixed bug in view GV$SORT_USAGE.

You can work around this issue by using this query starting with Oracle version 11.2.0.2 or higher (depends on new column in x$ktsso) :

SQL> select k.inst_id "INST_ID", ktssoses "SADDR", sid "SID", ktssosno "SERIAL#", username "USERNAME", osuser "OSUSER",
       ktssosqlid "SQL_ID", ktssotsn "TABLESPACE", decode(ktssocnt, 0, 'PERMANENT', 1, 'TEMPORARY') "CONTENTS",
       decode(ktssosegt, 1, 'SORT', 2, 'HASH', 3, 'DATA', 4, 'INDEX', 5, 'LOB_DATA', 6, 'LOB_INDEX' , 'UNDEFINED') "SEGTYPE",
       ktssofno "SEGFILE#", ktssobno "SEGBLK#", ktssoexts "EXTENTS", ktssoblks "BLOCKS", round(ktssoblks*p.value/1024/1024, 2) "SIZE_MB",
       ktssorfno "SEGRFNO#"
from x$ktsso k, v$session s, v$parameter p
where ktssoses = s.saddr and ktssosno = s.serial#  and p.name = 'db_block_size'
order by sid;

You can find out the SQL_ID (and corresponding text afterwards) with this query and maybe find the responsible code (by using Wily Introscope for example).

By the way ... temp segments (in temporary tablespace) are not de-allocated until shutdown ("SMON: disabling tx recovery") ... so it can be possible that it grow to its max possible size.

Regards

Stefan

[Former Member · Accepted solution]

Stefan,

We are currently running 11.2.0.2.

I am having an issue with the above SQL but I pulled the following from GV$SORT_USAGE. Let me know if this is any help.

[stefan_koehler · Accepted solution]

Hi Brian,

i am pretty sure, that the corresponding SQL_IDs in GV$SORT_USAGE (for example 8htgxubybas6p) are not the causer. This is a known issue with the view.

Please get the corresponding SQL text from V$SQLTEXT (like that):

SQL> select SQL_TEXT from V$SQLTEXT 
where SQL_ID = '8htgxubybas6p' order by SQL_ID, PIECE;

Use the other previous provided SQL (with x$ktsso), if you see no valid reason (by the SQL text) for allocating temporary LOB segments and rerun it with the new SQL_ID.

After you have located the right critical SQLs, you need to do further analysis with Wily Introscope (or search on SMP). We can not assist you with such analysis as we don't have access and so on.

Regards

Stefan

[Former Member]

Hi,

Can you please check this note 659946 : Temp Tablespaces

PSAPTEMP should be at least twice as large as the largest

index.