cancel
Showing results for 
Search instead for 
Did you mean: 

PSAPTEMP Used Daily Growth - XI System

Former Member
0 Kudos

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/872,832,00051
5/768,995,07248
5/654,336,51238
5/539,149,56827
5/424,988,67217
5/311,931,6488
5/29,745,4086
5/15,886,9764
4/308,956,9286
4/298,113,152

5

4/286,899,7124
4/276,014,9764

Thanks

View Entire Topic
stefan_koehler
Active Contributor
0 Kudos

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
0 Kudos

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
Active Contributor
0 Kudos

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