RE: Temporary tablespace usage
Date: Thu, 27 Mar 2014 06:13:34 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901DE61C0_at_exmbx05.thus.corp>
Apologies if this has been said already - I haven't followed the thread.
If you're after SQL that's used a lot of temp then query v$sql_workarea -- which includes sql_id and max_tempseg (or something similar) columns.
Temporary LOBs
Global Temporary tables - including from "with subqueries"
Hash, Sort and bitmap merges could all use temp.
There are also various bugs, though, with temp space not being released - one recent one involves repeated use a statement with "with" subqueries inside a pl/sql block (e.g. in a loop) not releasing temp until the session ends.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of kellyn.potvin_at_ymail.com [kellyn.potvin_at_ymail.com] Sent: 27 March 2014 02:20
To: kellyn.potvin_at_ymail.com; oratune_at_yahoo.com; mcunningham_at_thedoctors.com; peter.schauss_at_ngc.com; oracle-l Subject: Re: Temporary tablespace usage
Apologies, html may have removed some formatting
That's
SELECT * FROM TABLE (DBMS_XPLAN. DISPLAY_AWR (SQLID HERE));
Kellyn Pot'Vin
Sent from Yahoo Mail on Android<https://overview.mail.yahoo.com/mobile/?.src=Android>
From: kellyn.potvin_at_ymail.com <kellyn.potvin_at_ymail.com>; To: oratune_at_yahoo.com <oratune_at_yahoo.com>; mcunningham_at_thedoctors.com <mcunningham_at_thedoctors.com>; peter.schauss_at_ngc.com <peter.schauss_at_ngc.com>; oracle-l <oracle-l_at_freelists.org>; Subject: Re: Temporary tablespace usage
Sent: Thu, Mar 27, 2014 2:11:55 AM
Do you know how to capture the sql_id for the statement in question?
With this information, run a sql_id specific awr report from the command line. It should tell you the amount of temp estimated, the plan with totals for each step. This will tell you what is eating up temp, but please consider correcting the issue before just upping temp...:)
From SQL Plus:
SET LINESIZE 240
SET PAGESIZE 500
SELECT * FROM TABLE (DBMS_XPLAN. DISPLAY_AWR ( ));
Let me know if you need more,
Kellyn Pot'Vin
_at_DBAKevlar
Sent from Yahoo Mail on Android<https://overview.mail.yahoo.com/mobile/?.src=Android>
From: David Fitzjarrell <oratune_at_yahoo.com>; To: mcunningham_at_thedoctors.com <mcunningham_at_thedoctors.com>; peter.schauss_at_ngc.com <peter.schauss_at_ngc.com>; oracle-l <oracle-l_at_freelists.org>; Subject: Re: Temporary tablespace usage
Sent: Wed, Mar 26, 2014 6:42:59 PM
Sort segments aren't the only segments in TEMP as hash operations can also use TEMP (you'll see HASH segments when they do). The V$SQL_PLAN view can report ho much temp space was consumed for a given sql_id but the TEMP_SPACE column may not always be populated. In one database I manage I find 57 rows where TEMP_SPACE is not null, out of just over 57,500 rows in the view. I can't figure out WHEN Oracle decides to populate that column but occasionally I find values. You might be able to see, in a limited scope, how much TEMP space a query is using and, through AWR and/or ASH reports see how oftren that query runs. It may help you decide just how large to make your TEMP tablespace. I would think, though, that in a fairly active database 32G is rather small for a TEMP tablespace.
David Fitzjarrell
Primary author, "Oracle Exadata Survival Guide"
On Wednesday, March 26, 2014 11:08 AM, "Cunningham, Mike" <mcunningham_at_thedoctors.com> 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[X]707.226.0221 - desk
707.337.0184[X]707.337.0184 - cell
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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
--
http://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.
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Mar 27 2014 - 07:13:34 CET