| |
 |
|
Oracle Tips by Burleson |
Output from the
sortusage.sql query
The output will display how many users are currently using space in
each temporary tablespace along with the current used extent and
free extent numbers. The last two columns will display the largest
number of total extents ever used for all sorts and the size of the
single largest sort, in extents, since the database has been up.
Such knowledge can help the DBA plan the size of the temporary
tablespace(s). Many DBAs will size very large temporary tablespaces
in anticipation of heavy sort activity only to find that such sorts
do not occur. If there is not heavy temporary tablespace usage, it
may be possible to resize the temporary tablespace datafiles and
reclaim space on the server.
If the sortusage.sql query shows that users are currently using
space in a temporary tablespace, the DBA should dig deeper to see
exactly what they are doing. The sortdet.sql query can provide this
exact detail:
* sortdet.sql
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
select
sql_text,
sid,
c.username,
machine,
tablespace,
extents,
blocks
from
sys.v_$sort_usage a,
sys.v_$sqlarea b,
sys.v_$session c
where
a.sqladdr = b.address and
a.sqlhash = b.hash_value and
a.session_addr = c.saddr
order by
sid;
The output from the above query might look like the following:
SQL_TEXT SID USERNAME MACHINE TABLESPACE EXTENTS BLOCKS
-------------------------------------------------------------------
SELECT * FROM 10 ERADMIN ROBS TEMP 10 80
Using this query, the SQL call, current session information, and
details on how much temporary space the SQL call is using can be
obtained. Red flags should begin to run up the flagpole if
continuous large disk sorts are occurring on the system.
The above book excerpt is from:
Oracle
Tuning Power Scripts
With 100+ High Performance
SQL Scripts
Oracle In-Focus Series
ISBN
0-9744486-7-2
Mike Ault, Donald K.
Burleson. Harry Conway
http://www.rampant-books.com/book_2005_1_power_tuning.htm |
