Re: unable to extend temp
Date: Mon, 5 Aug 2019 14:20:13 +1000
Message-ID: <CAFeFPA8uNJBxEiH8cyrzNQqtZF7+o2N5Oj4+8kG_AmU_YgTLhQ_at_mail.gmail.com>
Hi
I worked with support on this and apparently we are hitting " Bug 27999073 - Query from more than one instance fails with ORA-1652 (Doc ID 27999073.8) "
Supposedly fixed in July 2019 patch or available as OneOf fix.
Jack van Zanen
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
On Thu, Aug 1, 2019 at 8:36 AM Mark W. Farnham <mwf_at_rsiz.com> wrote:
> 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
> *To:* 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
>
>
>
>
> -------------------------
> 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-lReceived on Mon Aug 05 2019 - 06:20:13 CEST