Home » RDBMS Server » Performance Tuning » Temp Datafiles Size
icon14.gif  Temp Datafiles Size [message #255203] Tue, 31 July 2007 00:43 Go to next message
mian imran
Messages: 29
Registered: July 2004
Junior Member
Assalam O Alikum

How can i find the query which is increasing my temp file size. i have given different temp to different schema users. Size of some tmep files is increasing with the passage of time due to heavy sorting and calculation. i want to know which one is actually creating problem so i can make that query efficient.
thanks
Re: Temp Datafiles Size [message #255220 is a reply to message #255203] Tue, 31 July 2007 01:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Try v$sort_usage.

Regards
Michel
Re: Temp Datafiles Size [message #255231 is a reply to message #255203] Tue, 31 July 2007 01:53 Go to previous messageGo to next message
mian imran
Messages: 29
Registered: July 2004
Junior Member
Dear
v$sort_usage is empty but v$sort_segment gives me the information about the user, blocks and extents. i need the actual queries which is costing me temp size. please if you can help me in this finding , i will be thankful to you dear.
Re: Temp Datafiles Size [message #255323 is a reply to message #255231] Tue, 31 July 2007 07:16 Go to previous messageGo to next message
cbruhn2
Messages: 41
Registered: January 2007
Member
Some time ago I did this
It seems that in Oracle 9i vers 2 temporary and sort segments is equivalent.

When you want to see something about temporary table sizes at runtime, you should look into v$sort_usage and v$sort_segment. I haven't been able to find a clear cut way to see how large at temporary table is at runtime, but you could do something like the following sql. At least it will show you how much space a specifik user is actually using in the temporary tablespaces, and with what sql_text.
When looking at temp segment please notice they are not freed in v$temp_extent_map and v$temp_space_header.
The columns for owner, bytes used etc doesn't even seem to be freed by smon when the instance is restarted or ....??

Tested on
Linux ites 2.4.18-14 #1 Wed Sep 4 13:35:50 EDT 2002 i686 i686 i386 GNU/Linux
SQL*Plus: Release 9.2.0.1.0 - Production on Fri Jan 17 11:48:54 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
 
TABLESPACE_NAME FILE_ID BYTES_USED BLOCKS_USED BYTES_FREE BLOCKS_FREE	RELATIVE_FNO
TEMP		  1	   149946368  18304	       0       	0	1 

/* shows you what sql_statements 
  and which user is doing something on the temporary segments. */
select  a.sql_text,
        b.username,
        b.tablespace,
        b.extents,
        b.blocks from v$sqlarea a, v$sort_usage b
where a.address = b.sqladdr ;
SQL_TEXT
--------------------------------------------------------------------------------
USERNAME                       TABLESPACE                         EXTENTS
------------------------------ ------------------------------- ----------
    BLOCKS
----------
insert into bbb values ('anders')
CRB                            TEMP                                     1
       128

In this example bbb is a global temporary table.

Hope this is of any help
Carl Bruhn
Re: Temp Datafiles Size [message #256149 is a reply to message #255323] Fri, 03 August 2007 03:17 Go to previous messageGo to next message
mian imran
Messages: 29
Registered: July 2004
Junior Member
hi
This is giving nothing now. any other solution.
Re: Temp Datafiles Size [message #256164 is a reply to message #256149] Fri, 03 August 2007 04:23 Go to previous message
martijn
Messages: 286
Registered: December 2006
Location: Netherlands
Senior Member
Then there is probably no sorting going on at the moment of the query, I guess.
Previous Topic: query tuning
Next Topic: Problem when installing statspack
Goto Forum:
  


Current Time: Thu Dec 05 13:44:43 CST 2024