Re: Q: Who is using temp tablespace?

The easiest way I know of determining who is using the temp is to find out who is doing the big sort. This user is most likely going to have a higher hit ratio. You can determine this by using the following command:

select sid,sql_text from v$session s,v$sqlarea a where sql_hash_value = hash_value and
sql_address = address and
sid in (select sid from v$sess_io where
1-(physical_reads/(block_gets+consistent_gets)) < .90 and physical_reads > 0 );

I have used this frequently to determine who is performing heavy sorts. This will also isolate the sql used in this sort.

This is not concrete proof that this user is chewing up temp, but it is a high likelihood that they are.

-Sheilah Scheurich
DBA
scheuric_at_sprynet.com Received on Wed May 21 1997 - 00:00:00 CDT