> 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.
> Bill Reidy
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
to_char(rownum+3) ||') ' || rpad(r.name,17) || rpad(to_char(p.pid),11)
|| rpad(nvl(p.username,'NO TRANSACTION'),17)
v$lock l, v$process p, v$rollname r, v$session s
WHERE l.sid = s.sid (+)
and p.addr = s.paddr
and l.type(+) = 'TX'
and l.lmode(+) = 6
select '2) ROLLBACK SEGMENT'||' '|| 'ORACLE PID' ||' '|| 'SYSTEM PID' ||' '|| 'TRANSACTION ' ||' '|| 'TERMINAL'
select '1) ' from dual
select '3) ----------------' ||' '|| '----------' ||' '|| '----------' ||' '|| '----------------' ||' '|| '--------'
ORDER BY 1
Best of luck, and I hope that this helps you,
Back to Ari Kaplan's Home Page