Re: unable to extend temp

From: Powell, Mark <mark.powell2_at_dxc.com>
Date: Wed, 31 Jul 2019 18:22:39 +0000
Message-ID: <SN6PR01MB4335DAFFFDFE0F5BF20BBA5ECEDF0@SN6PR01MB4335.prod.exchangelabs.com>

Bad query plans can result in the exhaustion of the TEMP tablespace. As soon as the query consuming excessive TEMP fails the temporary space held by the failing statement will become available for reuse. There should be an associated message in the alert log though it may not provide enough information to identify the source of the problem. If that is true you can set trace on for the error code.

Mark Powell
Database Administration
(313) 592-5148

---

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Jack van Zanen <jack_at_vanzanen.com> Sent: Tuesday, July 30, 2019 6:56:51 PM
To: oracle-l_at_freelists.org <oracle-l_at_freelists.org> Subject: unable to extend temp

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

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;

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
 where tablespace_name='TEMP'
 order by 1;

 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]

---

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

DXC Technology Company - Headquarters: 1775 Tysons Boulevard, Tysons, Virginia 22102, USA. DXC Technology Company -- This message is transmitted to you by or on behalf of DXC Technology Company or one of its affiliates. It is intended exclusively for the addressee. The substance of this message, along with any attachments, may contain proprietary, confidential or privileged information or information that is otherwise legally exempt from disclosure. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient of this message, you are not authorized to read, print, retain, copy or disseminate any part of this message. If you have received this message in error, please destroy and delete all copies and notify the sender by return e-mail. Regardless of content, this e-mail shall not operate to bind DXC Technology Company or any of its affiliates to any order or other contract unless pursuant to explicit written agreement or government initiative expressly permitting the use of e-mail for such purpose. --.

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jul 31 2019 - 20:22:39 CEST