 Temp Datafiles Size [message #255203] |
Tue, 31 July 2007 00:43  |
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 #255231 is a reply to message #255203] |
Tue, 31 July 2007 01:53  |
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 |
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
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
