JL Computer Consultancy

Who is sorting how much in Oracle 8.1

November 2000


One question that still appears fairly frequently on the comp.database.oracle.server news group is 'Who is using the TEMP segment right now'. This question can (nearly) be answered by a dynamic performance view called v$sort_usage introduced in Oracle 8. However, if you use this view, you will notice that only one user ever appears to be doing any sorting - and their user id always matches the userid that you are using to query the view !

If you examine the view definition, you will discover why this is the case - the view below is from Oracle 8.1.6, and includes the column SEGTYPE that does not exist in Oracle 8.1.5:

        Name                            Type
        ------------------------------- -----------
        USER                            VARCHAR2(30)
        SESSION_ADDR                    RAW(4)
        SESSION_NUM                     NUMBER
        SQLADDR                         RAW(4)
        SQLHASH                         NUMBER
        TABLESPACE                      VARCHAR2(31)
        CONTENTS                        VARCHAR2(9)
        SEGTYPE                         VARCHAR2(9)
        SEGFILE#                        NUMBER
        SEGBLK#                         NUMBER
        EXTENTS                         NUMBER
        BLOCKS                          NUMBER
        SEGRFNO#                        NUMBER

USER is not a very good name for a column! How often you do 'select user from dual' to find out the identity of the user running a particular process ?

The problem can easily be circumvented. All we need to do is to log on as SYS and create a modified version of the v$sort_usage view that substitutes a legal column name for the problem one. For example, the following is adequate::

rem
rem     Script:        fix_sort_usage.sql
rem     Author:        Jonathan Lewis
rem     Dated:         June 2000
rem
rem     Purpose:       Work around a bug in v$sort_usage
rem
rem     Note:
rem     This version is commented to run on 8.1.5
rem     It adds the session id (SID) that is missing from v$sort_usage.
rem
 
 
create or replace view v$_sort_usage_2 (
        username, sid, session_addr, session_num, sqladdr, sqlhash, 
        tablespace, contents, /* segtype, */ segfile#, segblk#, 
        extents, blocks, segrfno#
)
as
select
        username,
        sid,
        ktssoses,
        ktssosno,
        prev_sql_addr,
        prev_hash_value,
        ktssotsn,
        decode(ktssocnt,
               0, 'PERMANENT',
               1, 'TEMPORARY'
        ),
/*
        decode(ktssosegt,
               1, 'SORT',
               2, 'HASH',
               3, 'DATA',
               4, 'INDEX',
               5, 'LOB_DATA',
               6, 'LOB_INDEX' ,
                  'UNDEFINED'
        ),
*/
        ktssofno,
        ktssobno,
        ktssoexts,
        ktssoblks,
        ktssorfno 
from 
        x$ktsso,
        v$session 
where
        ktssoses = v$session.saddr 
and     ktssosno = v$session.serial#    
and     x$ktsso.inst_id = sys_context('userenv','instance')
;
 
rem
rem     Could make this v$sort_usage to hide the erroneous view completely
rem
 
create or replace view v$sort_usage_2 as 
select * from V$_sort_usage_2;
 
grant select on v$sort_usage_2 to public;
 
create public synonym v$sort_usage_2 for v$sort_usage_2;
 

Note that there is a section of code commented out that is relevant only to Oracle 8.1.6 - not only can you see who is using space in the temporary segment, you can also see what they are using it for. A particular threat to watch out for is users that get too keen on using Temporary LOBS (a feature new to 8.1.6), or are pushing large volumes of data into 'proper' temporary tables (a feature of 8.1)


Sample Output

USERNAME                  SID SESSION_ SESSION_NUM SQLADDR     SQLHASH
------------------ ---------- -------- ----------- -------- ----------
TABLESPACE          CONTENTS   SEGFILE#    SEGBLK#    EXTENTS     BLOCKS   SEGRFNO#
------------------ --------- ---------- ---------- ---------- ---------- ----------
JPL1                        8 05AA2D30         163 061360C4 3375885015
TEMP               TEMPORARY         33       7201          1       1024          1

If you want to pursue users who are using a lot of temporary space, the SQLHASH corresponds to the HASH_VALUE column of the v$sql view, so you can easily find out the text that actually causes this space to be used. Unless, of course, it is being used during a 'create index' or 'create table' statement.

Back to Main Index of Topics