How can I find out how much time is spent by a query in memory sorts?  i can easily get i/o timings, but I have suspicion my query is spending all its time on sorts...
64 bit linux
11.2.0.2
Thanks for your time,

Josh C.

-- http://www.freelists.org/webpage/oracle-l

Would you turn on 10046 trace for the sql?
 
For example,
SORT ORDER BY (cr=3 pr=0 pw=0 time�5 us)
 
The 'time' is a total of 'SORT' operation and its sub-steps. Please remember to subtract 'time' of its sub-steps from it.
 
Lei
DBspeed   http://www.dbspeed.com/index.html

Josh,
it's not that comfortable, but you can try to do something like http://blog.tanelpoder.com/2008/10/31/advanced-oracle-troubl[..]

to get the call stack traces where your processes are spending most of the
time.
If you manage to map the c-function name to your 'in memory sorts' you can
estimate how much time you spend there.

hth
Martin

-- http://www.freelists.org/webpage/oracle-l

It's been a while since I looked at the sort traces, but I *think* event
10032 will give that information, albeit possibly tangentially.

-- http://www.freelists.org/webpage/oracle-l

Is there any reason you can't add /*+ GATHER_PLAN_STATISTICS */ to your query and then
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

You should be able to see the actual time spent on each step of the execution without digging through a trace file.

Thanks,
T. J.
 
The information contained in this message is privileged and confidential information intended only for the use of the individual or entity identified above. If the receiver of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, use or copying of this message is strictly prohibited. If you have received this message in error, please immediately notify the sender by replying to his/her e-mail address noted above and delete the original message, including any attachments. Thank you.

Hi,
And of course  there's real time SQL monitoring, which will give you a nice
graphical view of the plan and timings:

http://www.oracle.com/technetwork/database/focus-areas/manag[..]

Real time SQL monitoring requires the Diagnostic and Tuning pack licenses.

Kurt

--
The opinions expressed in this email are my own, and not my company's.

-- http://www.freelists.org/webpage/oracle-l