Re: Temporary tablespace usage
- From: John Hurley <hurleyjohnb@xxxxxxxxx>
- To: "mcunningham@xxxxxxxxxxxxxx" <mcunningham@xxxxxxxxxxxxxx>
- Date: Wed, 26 Mar 2014 13:23:10 -0400
Definitely look at SQL first 32 gb temp often from missing join
Sent from my iPhone
> On Mar 26, 2014, at 1:07 PM, "Cunningham, Mike" <mcunningham@xxxxxxxxxxxxxx>
> wrote:
>
> Hi Peter, yes, the TOTAL is showing you how large the TEMP tablespace is
> right now. However, it is not necessarily the size since the last database
> restart, but it is the total size of all files that make up the TEMP
> tablespace. Based on what I see I would guess you have 1 file in the TEMP
> space and it is at its max size (32GB for a datafile with 8K block size). If
> you have autoextend turned on then there was a statement - or statements -
> that pushed it all the way to 32GB.
>
> I don't know of any way to calculate how much TEMP would be required to
> complete the failed transaction. You could try and add a datafile of 1GB (if
> autoextend is on) and see how much it grows to complete the transaction. In
> my experience you would want to look at the query first and make sure it is
> performing optimally. I've seen queries that were fine before and then, with
> bad stats and query plan, they used an excess of TEMP space.
>
> Michael Cunningham
> Senior Database Administrator
> The Doctors' Company
> 707.226.0221 - desk
> 707.337.0184 - cell
>
>
>
>
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
> Behalf Of Schauss, Peter (ESS)
> Sent: Wednesday, March 26, 2014 9:42 AM
> To: oracle-l
> Subject: Temporary tablespace usage
>
> This is Oracle 11.2.0.3 running on 32 bit Windows. We have had an "ORA-1652
> - Unable to extend temp segment ..." and I am trying to get an idea of how
> much larger the temp tablespace needs to be based on previous use.
>
> When I run this query:
>
> select total_blocks*8192/1000000000 Total,
> free_blocks*8192/1000000000 Free,
> current_users,
> max_sort_size*8192/1000000000 Max
> from v$sort_segment
> where tablespace_name='TEMP';
>
> I get:
>
> TOTAL FREE CURRENT_USERS MAX
> 35.06962432 35.060187136 9 0.273932288
>
> Does this mean that the high water mark in TEMP since the last database
> restart is 35 gb and the maximum use by any session is .27 gb?
>
> Is there anything other than the sort segment which uses space in TEMP?
>
> Thanks,
> Peter Schauss
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
>
> Confidentiality Notice: This message and any attachments hereto may contain
> confidential and privileged communications or information and/or attorney
> client communications or work-product protected by law. The information
> contained herein is transmitted for the sole use of the intended
> recipient(s). If you are not the intended recipient or designated agent of
> the recipient of such information, you are hereby notified that any use,
> dissemination, copying or retention of this e-mail or the information
> contained herein is strictly prohibited and may subject you to penalties
> under federal and/or state law. If you received this e-mail in error, please
> notify the sender immediately and permanently delete this e-mail.
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
