RE: unable to extend temp
Date: Wed, 31 Jul 2019 18:35:46 -0400
Message-ID: <034a01d547f0$4c046fe0$e40d4fa0$_at_rsiz.com>
Not exactly aware of what causes it (and a client has an active SR open which became low priority because, drumroll):
IF it is the same problem you can add the amount of temp you need as a second temp in the group and then drop the first one.
My working theory is that there is a disconnect between releasing temp objects in the database and freeing them in the temp area. So from the database viewpoint and actual temp being actively used you have plenty, but there is nothing to allocate left. Lack of access to the host in a DBaaS environment prevented me from verifying my working theory.
Let us know how you make out, especially if my suggested workaround is effective. If you have access to the host disks, take a look at the place temp lives from the OS, too.
The problem has resurrected itself twice, both times preceded by temp operations becoming a factor of 100x slower than usual (which I *suspect* is hunting for places to squeeze out another extent.)
Since there is so little operational overhead to add temp/drop old temp, that is now a rule out exercise when unable to extend is seen. Same environment.
Good luck,
mwf
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jack van Zanen
Sent: Tuesday, July 30, 2019 6:57 PM
Hi All
Oracle 12.2.0.1 RAC exadata OEL
In our test PDB we had an issue yesterday where we were getting s lot of unable to extent in tablespace temp messages.
Fair enough so I checked the sort_usage but this came back with hardly any temp being used at the time.
I have searched metalink and found a bug that was fixed in in version 11 or so..
Anyone aware of anything that could cause this
at the time we had parameter temp_undo_enabled set to true (testing this across all our test environments)
so I also checked
select * from gv$tempundostat
which also did not return a usage consistent with running out of temp.
The bug I found led me to a couple of sql statements which do not mean a whole lot to me
select inst_id, tablespace_name, round((total_blocks*8192)/(1024*1024*1024),2) "Space(GB)"
from gv$sort_segment
select sum(bytes)/1024/1024/1024, owner from gv$temp_extent_map group by owner;
select inst_id, blocks_cached, blocks_used, extents_cached, extents_used from GV$TEMP_EXTENT_POOL;
what are they supposed to represent as the only information the document provided was that it could assist with troubleshooting
Jack van Zanen
<https://docs.google.com/uc?id=0BwovDucFT1fXaEREVHNWRWZyNjg&export=download>
To: oracle-l_at_freelists.org
Subject: unable to extend temp
SELECT b.TABLESPACE
, b.segfile#
, b.segblk#
, ROUND ( ( ( b.blocks * p.VALUE ) / 1024 / 1024 ), 2 ) size_mb
, a.inst_ID
, a.SID
, a.serial#
, a.username
, a.osuser
, a.program
, a.status
FROM gv$session a
, gv$sort_usage b
, gv$process c
, gv$parameter p
WHERE p.NAME = 'db_block_size'
AND a.saddr = b.session_addr
AND a.paddr = c.addr
-- AND b.TABLESPACE='TEMP2'
ORDER BY a.inst_ID , b.TABLESPACE
, b.segfile#
, b.segblk#
, b.blocks;
where tablespace_name='TEMP'
order by 1;
This e-mail and any attachments may contain confidential material for the sole use of the intended recipient. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of this e-mail or any attachment is prohibited. If you have received this e-mail in error, please contact the sender and delete all copies.
Thank you for your cooperation
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Aug 01 2019 - 00:35:46 CEST