Home » RDBMS Server » Performance Tuning » large sort
large sort [message #115923] Fri, 15 April 2005 05:31 Go to next message
_simma_dba
Messages: 34
Registered: November 2003
Member
We have temporary tablespace that gorws very large from time to time. I know there is problem with sorting but I cant find query that couse problem. My question is: how can I find query that use temp tablespace so i can tune it?

thx in advance s.
Re: large sort [message #115929 is a reply to message #115923] Fri, 15 April 2005 05:53 Go to previous messageGo to next message
Frank Naude
Messages: 4566
Registered: April 1998
Senior Member
Try something like this:

SQL> col sid format 999 heading "Session ID"
SQL> col username format a10 heading "User Name"
SQL> col tablespace format a10 heading "Tablespace"
SQL> col mb format 999,999,990 heading "Used Space (Meg)"
SQL> col pct format 990.00 heading "% of Avail TS Space"
SQL> col sql_text heading "SQL"
SQL>
SQL> select  s.sid,
  2          s.username,
  3          u.tablespace,
  4          u.blocks/128 mb,
  5          a.sql_text
  6  from    v$sort_usage    u,
  7          v$session       s,
  8          v$sqlarea       a
  9  where   s.saddr = u.session_addr
 10  and     a.address (+) = s.sql_address
 11  and     a.hash_value (+) = s.sql_hash_value
 12  group by s.sid, s.username, u.tablespace, u.blocks/128, a.sql_text
 13  /

Session ID User Name  Tablespace Used Space (Meg)
---------- ---------- ---------- ----------------
SQL
--------------------------------------------------------------------------------
       146 SCOTT      TEMP                      4
select * from bigemp order by ename


Best regards.

Frank
Re: large sort [message #115941 is a reply to message #115929] Fri, 15 April 2005 07:00 Go to previous messageGo to next message
_simma_dba
Messages: 34
Registered: November 2003
Member
Thx for very handy script. Razz It's very good for using in real-time monitoring system. But I need to find query that alredy has been executed.
Re: large sort [message #116262 is a reply to message #115923] Mon, 18 April 2005 23:06 Go to previous messageGo to next message
chunyuh
Messages: 13
Registered: April 2005
Junior Member

You can try statspack with a high collection level. statspack can tell you which sqls are resouce hog and also tell you these sql's execution plan (sprepsql.sql). By checking their exectuion plan ,you can tell which ones have lots of sorts.

Chunyu Hu
http://mtsmart.kmip.net
Re: large sort [message #116281 is a reply to message #116262] Tue, 19 April 2005 03:21 Go to previous message
_simma_dba
Messages: 34
Registered: November 2003
Member
Thx Chunyu, Idea I just forgot about STATSPACK!!
Previous Topic: URGENT:-Tuning a query
Next Topic: lock_sga parameter cause out of memory error
Goto Forum:
  


Current Time: Sun Jan 17 12:50:56 CST 2021