Kerry Osborne's Oracle Blog

There was a post on the oracle-l list the other day regarding disk space usage in the temp tablespace. Rich Jesse posted a neat little script for capturing large consumers of temp space along with the user and SQL statement responsible. I started playing around with the script and noticed that it wasn’t reporting the correct SQL Statement. Turns out there is a bug having to do with the definition of v$sort_usage. It’s apparently been around since 10gR1.

There is a listing on Metalink (sorry,… I mean My Oracle Support). I’ve pasted in the interesting bits here:

Bug 7210183: SQL_ID VALUE IS NOT SAME IN V$TEMPSEG_USAGE AND OTHER VIEWS.
~~~~~~~~~~~
The actual problem here looks to be in the definition of
GV$SORT_USAGE thus:

In RDBMS_MAIN_LINUX_080825:
GV$TEMPSEG_USAGE is a synonym resolving to GV_$SORT_USAGE.

GV_$SORT_USAGE is a VIEW which selects various columns from
GV$SORT_USAGE.

GV$SORT_USAGE has a definition like this:
select x$ktsso.inst_id, username, username, ktssoses, ktssosno,
prev_sql_addr, prev_hash_value, prev_sql_id, <<<<< NOTICE HERE ktssotsn, .... from x$ktsso, v$session where ktssoses = v$session.saddr and ktssosno = v$session.serial# So GV$SORT_USAGE SQL_ID etc.. are just taken from the sessions current PREV_* columns. This is not correct. The sort usage in a session could be tied to any of the open cursors for that session. The above definition just looks plain wrong. *** 09/09/08 05:45 pm *** *** 09/10/08 12:06 am *** (DEL: Impact/Symptom->WRONG RESULTS )
*** 09/10/08 12:06 am *** (ADD: Impact/Symptom->WRONG RESULTS )
*** 09/10/08 12:06 am ***
I am not sure that in all cases using V$SESSION.SQL_ADDRESS
etc.. would be correct.
eg: Consider that the session does something like the
following:
Open cursor 1
Parse , execute and fetch one row from a SQL that needs
temp space in part of the plan
Open cursor 2
select * from dual (no sorting)
At this point V$SESSION.SQL_ID would be for the
select from dual

I do not believe that we actually have the information
available to return the correct SQL_ID etc.. this view.

eg: a. Consider this simple test:
create global temporary table foo ( a number );
insert into foo values(10);
b. In a second session do:
select sql_id from v$sort_usage;
c. Now in the first session issue any old select:
select 99 from dual;
d. And check V$SORT_USAGE:
select sql_id from v$sort_usage;
e. Repeat c and d over using a different SQL each time
in c . eg: select 77 from dual A;

In this example V$SORT_USAGE will show us which session
has the temp space (SESSION_ADDR) but the SQL which
created the temp space is not even available as an open
cursor against that session (the insert created the temp
space).

It looks like this really needs a larger change – something
like capturing the SQL_ID etc.. at the time that the temp seg
gets created and then exposing that information through some
new X$ colums in x$ktsso ?

So I modified the script to report the current sql_id instead.
Here’s a quick replay of the investigation starting with the original query:

SYS@LAB1024> -- original query
SYS@LAB1024> l      
  1  SELECT sysdate "TIME_STAMP", vsu.username, vsu.sql_id, vsu.tablespace,
  2  vsu.usage_mb, vst.sql_text, vp.spid
  3          FROM
  4          (
  5                  SELECT username, sqladdr, sqlhash, sql_id, tablespace, session_addr,
  6  sum(blocks)*8192/1024/1024 "USAGE_MB"
  7                  FROM v$sort_usage
  8                  HAVING SUM(blocks)> 10000 -- 80MB
  9                  GROUP BY username, sqladdr, sqlhash, sql_id, tablespace, session_addr
 10          ) "VSU",
 11          v$sqltext vst,
 12          v$session vs,
 13          v$process vp
 14  WHERE vsu.sql_id = vst.sql_id
 15          AND vsu.sqladdr = vst.address
 16          AND vsu.sqlhash = vst.hash_value
 17          AND vsu.session_addr = vs.saddr
 18          AND vs.paddr = vp.addr
 19          AND vst.piece = 0;

no rows selected

SYS@LAB1024> -- note the filter to only get sorts using more than 10000 blocks of temp space
SYS@LAB1024> -- (that's why no records were returned)
SYS@LAB1024> -- go to another session and start a big sort, then check again

SYS@LAB1024> /

TIME_STAM USERNAME        SQL_ID        TABLESPACE        USAGE_MB SQL_TEXT                                                         SPID
--------- --------------- ------------- --------------- ---------- ---------------------------------------------------------------- ------------
19-JAN-10 SYS             9babjv8yq8ru3 TEMP                   145 BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;             31514

SYS@LAB1024> -- so now we have a big one
SYS@LAB1024> -- but obviously the get_lines procedure is not causing the big sort
SYS@LAB1024> -- hmmm, what's actually active right now?
SYS@LAB1024> @as

no rows selected

SYS@LAB1024> l
  1  select sid, substr(program,1,19) prog, address, hash_value, b.sql_id, sql_child_number child, plan_hash_value, executions execs,
  2  (elapsed_time/decode(nvl(executions,0),0,1,executions))/1000000 avg_etime,
  3  sql_text
  4  from v$session a, v$sqlarea b
  5  where status = 'ACTIVE'
  6  and username is not null
  7  and a.sql_id = b.sql_id
  8* and audsid != SYS_CONTEXT('userenv','sessionid')
SYS@LAB1024> del
SYS@LAB1024> /

 SID PROG       ADDRESS  HASH_VALUE SQL_ID         CHILD PLAN_HASH_VALUE      EXECS   AVG_ETIME SQL_TEXT
---- ---------- -------- ---------- ------------- ------ --------------- ---------- ----------- -----------------------------------------
 410 sqlplus@ho 74AAF3D0 1572343862 1mkjj1tfvh41q      0      1304592819          5       67.42 SELECT COL1,COL2 FROM KSO.SKEW ORDER BY C
 433 sqlplus@ho 74BB6C20 1310159811 bn8b1wp71fwy3      0       187045271          4         .01 select sid, substr(program,1,19) prog, ad

SYS@LAB1024> -- yep, SID 410 is running the query I started in the other session
SYS@LAB1024> -- let' check the prev_sql_id as indicated by the Metalink note
SYS@LAB1024>
SYS@LAB1024> select sid, sql_id, prev_sql_id from v$session where sid=410;

 SID SQL_ID        PREV_SQL_ID
---- ------------- -------------
 410 1mkjj1tfvh41q 9babjv8yq8ru3

SYS@LAB1024> -- aha, it is prev_sql_id that is exposed in v$sort_usage
SYS@LAB1024> -- just to prove it let's look at the fixed view definition
SYS@LAB1024> get fixed_view_def
  1  select * from v$fixed_view_definition
  2* where view_name like upper('&view_name')
SYS@LAB1024> /
Enter value for view_name: GV$SORT_USAGE

VIEW_NAME
------------------------------
VIEW_DEFINITION
-----------------------------------------------------------------------------------------------------------------------------------------------------------
GV$SORT_USAGE
select x$ktsso.inst_id, username, username, ktssoses, ktssosno, prev_sql_addr, prev_hash_value, prev_sql_id, 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, k
tssoblks, ktssorfno from x$ktsso, v$session where ktssoses = v$session.saddr and ktssosno = v$session.serial#

SYS@LAB1024> -- notice the prev_sql_id ...
SYS@LAB1024>
SYS@LAB1024> -- so now let's look at a fixed version (only modified slightly)
SYS@LAB1024> -- since we're already joining to v$session, we can just get the sql_id from there
SYS@LAB1024> -- I rearranged the columns a bit as well 
SYS@LAB1024> -- and joined to dba_tablespaces to allow for various block sizes
SYS@LAB1024> 
SYS@LAB1024> @temp_usage

no rows selected

SYS@LAB1024> l
  1  SELECT sysdate "TIME_STAMP", vsu.username, vs.sid, vp.spid, vs.sql_id, vst.sql_text, vsu.tablespace,
  2         sum_blocks*dt.block_size/1024/1024 usage_mb
  3     FROM
  4     (
  5             SELECT username, sqladdr, sqlhash, sql_id, tablespace, session_addr,
  6  -- sum(blocks)*8192/1024/1024 "USAGE_MB",
  7                  sum(blocks) sum_blocks
  8             FROM v$sort_usage
  9             HAVING SUM(blocks)> 1000
 10             GROUP BY username, sqladdr, sqlhash, sql_id, tablespace, session_addr
 11     ) "VSU",
 12     v$sqltext vst,
 13     v$session vs,
 14     v$process vp,
 15     dba_tablespaces dt
 16  WHERE vs.sql_id = vst.sql_id
 17  -- AND vsu.sqladdr = vst.address
 18  -- AND vsu.sqlhash = vst.hash_value
 19     AND vsu.session_addr = vs.saddr
 20     AND vs.paddr = vp.addr
 21     AND vst.piece = 0
 22     AND dt.tablespace_name = vsu.tablespace
 23* order by usage_mb

SYS@LAB1024> -- start the sort again, and recheck
SYS@LAB1024> /

TIME_STAM USERNAME         SID SPID         SQL_ID        SQL_TEXT                                           TABLESPACE        USAGE_MB
--------- --------------- ---- ------------ ------------- -------------------------------------------------- --------------- ----------
19-JAN-10 SYS              410 31514        1mkjj1tfvh41q SELECT COL1,COL2 FROM KSO.SKEW ORDER BY COL3, COL2 TEMP                   191

SYS@LAB1024> -- that looks better, that's the statement that is causing the big sort and temp usage

As pointed out in the Metalink note, it’s possible that the current sql_id may not have the correct value in some situations. One specific case that was mentioned was when a statement has completed and the session has issued another statement, but the temp space has not yet been cleaned up. It seems to work pretty well in most situations though.

One other note, it appears that this issue (bug) has not been addressed as of 11.2.0.1.