The Jonathan Lewis Pages

Who is sorting how much in Oracle 8.1

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