RE: Temporary tablespace usage
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 27 Mar 2014 06:13:34 +0000
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
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of kellyn.potvin@xxxxxxxxx [kellyn.potvin@xxxxxxxxx]
Sent: 27 March 2014 02:20
To: kellyn.potvin@xxxxxxxxx; oratune@xxxxxxxxx; mcunningham@xxxxxxxxxxxxxx;
peter.schauss@xxxxxxx; 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@xxxxxxxxx <kellyn.potvin@xxxxxxxxx>;
To: oratune@xxxxxxxxx <oratune@xxxxxxxxx>; mcunningham@xxxxxxxxxxxxxx
<mcunningham@xxxxxxxxxxxxxx>; peter.schauss@xxxxxxx <peter.schauss@xxxxxxx>;
oracle-l <oracle-l@xxxxxxxxxxxxx>;
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
@DBAKevlar
Sent from Yahoo Mail on
Android<https://overview.mail.yahoo.com/mobile/?.src=Android>
________________________________
From: David Fitzjarrell <oratune@xxxxxxxxx>;
To: mcunningham@xxxxxxxxxxxxxx <mcunningham@xxxxxxxxxxxxxx>;
peter.schauss@xxxxxxx <peter.schauss@xxxxxxx>; oracle-l
<oracle-l@xxxxxxxxxxxxx>;
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@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[X]707.226.0221 - desk
707.337.0184[X]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
Other related posts:
