JL Computer Consultancy

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.