Home » RDBMS Server » Performance Tuning » to check for temporary space usage (10.2.0.4)  () 1 Vote
to check for temporary space usage [message #429598] Wed, 04 November 2009 13:04 Go to next message
ankush_chawla
Messages: 136
Registered: November 2006
Senior Member
hi

How can we monitor the temporary space usage by the current session.

Regards
Re: to check for temporary space usage [message #429600 is a reply to message #429598] Wed, 04 November 2009 13:09 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
This script reports temporary tablespace usage. (This script was created for Oracle9i Database.)

With this script you can monitor the actual space used in a temporary tablespace and see the HWM of the temporary tablespace. The script is designed to run when there is only one temporary tablespace in the database.

select sum( u.blocks * blk.block_size)/1024/1024 "Mb. in sort segments"
, (hwm.max * blk.block_size)/1024/1024 "Mb. High Water Mark"
from v$sort_usage u, (select block_size
from dba_tablespaces
where contents = 'TEMPORARY') blk
, (select segblk#+blocks max
from v$sort_usage
where segblk# = (select max(segblk#) from v$sort_usage) ) hwm
group by hwm.max * blk.block_size/1024/1024;


[Updated on: Wed, 04 November 2009 13:09]

Report message to a moderator

Re: to check for temporary space usage [message #429647 is a reply to message #429600] Thu, 05 November 2009 00:10 Go to previous messageGo to next message
ankush_chawla
Messages: 136
Registered: November 2006
Senior Member
I m executing the rebuild operation .
After that when i execute this command i dont find any result.
Size of index is 500 Mb
Does it mean rebuild doesnot used any temporary tablespace
Morever we even checked with temp_space_header . However we still find that after rebuild the value doesnot increases.
Kindly explain.
Re: to check for temporary space usage [message #429652 is a reply to message #429647] Thu, 05 November 2009 00:44 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
During the rebuild Oracle uses the space that will be the new index as temporary space.
It mostly does not need temporary tablespace.

Regards
Michel
Re: to check for temporary space usage [message #429657 is a reply to message #429652] Thu, 05 November 2009 01:18 Go to previous messageGo to next message
ankush_chawla
Messages: 136
Registered: November 2006
Senior Member
So approx how much temporary space a index takes while rebuilding and in which tablespace.?
Re: to check for temporary space usage [message #429665 is a reply to message #429657] Thu, 05 November 2009 02:00 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Approximatively:
- 0 in temporary space
- same size than the current index in target tablespace

Regards
Michel
Re: to check for temporary space usage [message #429683 is a reply to message #429665] Thu, 05 November 2009 03:54 Go to previous messageGo to next message
ankush_chawla
Messages: 136
Registered: November 2006
Senior Member
Thanks a lot for your replies and help
But i still have 2 more concerns.

- I wanted to confirm while rebuilding It creates the temp segment in the same tablespace where the index is created and drops the previous index as soon as new index is created.

- But i m confused when i m rebuilding an large index more than 5 B it gives error : Cannot extend temp segment. Then now how it moves to temporary tablespace.

Re: to check for temporary space usage [message #429701 is a reply to message #429683] Thu, 05 November 2009 05:33 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Confirmed
2/ If you read the name of the temp segment tablespace used you will see it is the target tablespace. As I said, Oracle uses temp space (segment) in this one not the temporary tablespace (don't mix up, temp tablespace and temp segment).

Regards
Michel

[Edit: typos]

[Updated on: Thu, 05 November 2009 08:51]

Report message to a moderator

Re: to check for temporary space usage [message #429722 is a reply to message #429598] Thu, 05 November 2009 07:20 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
You may find this useful

http://www.orafaq.com/wiki/Oracle_database_FAQ#Who_is_using_which_UNDO_or_TEMP_segment.3F

Bye Alessandro
Re: to check for temporary space usage [message #430178 is a reply to message #429683] Mon, 09 November 2009 06:35 Go to previous message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
see the below
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:374218170986

Sriram
Previous Topic: Oracle 10.2.0.4
Next Topic: User statistics
Goto Forum:
  


Current Time: Thu Sep 29 14:15:32 CDT 2016

Total time taken to generate the page: 0.32564 seconds