> 
> Ari,
> 
> I enjoyed your session this morning on "A Bag of Tricks and Tips for
> DBAs and Developer."
> 
> As discussed during the question and answer period,  I would like to be
> able to identify the SQL statement (and Oracle user executing the
> statement) responsible for a disk sort when a disk sort is in progress.
> I know there must be a way to extract this information from Oracle's
> data dictionary.  When temporary objects appear in the dba_segments
> view, they are owned by SYS and have a naming convention of
> ., which appears to be 
. data.  I've
> been unable to trace these temporary objects back to a user or SQL
> statement.  Can you tell me what is the proper SQL statement to query
> the database for this information?
> 
> The database in question is version 7.1.6.
> 
> Thanks,
> Bill Reidy
> wreidy@csc.com
> 
Bill,

Thanks for coming to the speech and being so patient with my slow reply. I
was out of the country and just recently got back. Anyway, I do remember your
question and went to investigate, as I thought that I remembered the answer.
From what I concluded, I could not link the user with the sort, as Oracle
assigns SYS as the owner (like you mentioned). What I have been doing, however,
is seeing which users are using which rollback segments. I will give you the
script for you to try. It only shows transactions that take more than a few
seconds to complete, which is the case when the TEMP segments are used. It is
this way that I can relate the user to the TEMP segment (if there is only one
such rollback segment being used in the database). Otherwise I can only limit
it to those users using rollback segments at the moment.

Try the following SQL to see which users are using rollback segments:

set pagesize 24
select
to_char(rownum+3) ||') ' || rpad(r.name,17) || rpad(to_char(p.pid),11) 
      || rpad(p.spid,11)
      || rpad(nvl(p.username,'NO TRANSACTION'),17)
      || rpad(p.terminal,8)
FROM
v$lock l, v$process p, v$rollname r, v$session s
WHERE l.sid = s.sid (+)
and p.addr = s.paddr
and trunc(l.id1(+)/65536)=r.usn
and l.type(+) = 'TX'
and l.lmode(+) = 6
UNION
select '2) ROLLBACK SEGMENT'||' '||  'ORACLE PID' ||' '|| 'SYSTEM PID' ||' '|| 'TRANSACTION     ' ||' '|| 'TERMINAL'
from dual
UNION
select '1) ' from dual
UNION
select '3) ----------------' ||' '|| '----------' ||' '|| '----------' ||' '|| '----------------' ||' '|| '--------'
from dual
ORDER BY 1
/

Best of luck, and I hope that this helps you,

Back to Ari Kaplan's Home Page "> Back to Ari Kaplan's Home Page