> Tools and Products >

  Fulltime.sh - Report both Wait and CPU time detatils (3e, 1-Nov-2013)



 

Product Code: FULLTIME-SH

Description
 
Search OraPub ("fulltime") for associated conference presentations and blog postings.

Fulltime.sh - Report both Oracle session Wait time and CPU time details

Oracle provides session wait time details, like db file sequential reads. But there is nothing like this for CPU time, just DB CPU or background cpu time. That's it! So when asked, "What is Oracle doing with all that CPU?" we do not have the details and must infer based on a number of other pieces of information.

This tool collects, for a selected Oracle process, both wait time and CPU time. Then it merges them together into a single report. The wait time is collected from v$session_event and the CPU is collected using the OS tool perf.

This tool is the result of a collaborate effort of Craig Shallahamer (craig@orapub.com) and Frits Hoogland (frits.hoogland@gmail.com).

Use at your own risk, not ours!

Fulltime.sh - Report both Oracle Wait and CPU time details from OraPub on Vimeo.



Here is an example output.

$ fulltime.sh 124545 15 1
PID: 12545  SID: 168  SERIAL: 9  USERNAME: SYSTEM  at 18-Oct-2013 06:40:13
CURRENT SQL: SELECT COUNT(*) FROM DBA_OBJECTS WHERE 1=0
total time: 14.468 secs,  CPU: 13.239 secs (91.51%),  wait: 1.229 secs (8.49%)
                                                                   Time
Time Component                                                     secs       %
------------------------------------------------------------ ---------- -------
cpu : [?] sum of funcs consuming less than 2% of CPU time        10.938   75.60
cpu : [.] __intel_new_memset                                      1.252    8.66
wait: cursor: pin S                                               1.229    8.49
cpu : [.] opiexe                                                  0.436    3.01
cpu : [.] audsel                                                  0.367    2.53
cpu : [.] kxsxsi                                                  0.270    1.87
Samples remaining: 0
Gathering next 15 second sample...

		


And another example:


$ fulltime.sh 32873 45
PID: 32873  SID: 9  SERIAL: 13  USERNAME: OE2  at 18-Oct-2013 12:13:49
CURRENT SQL: SELECT COUNT(*) FROM ( SELECT SUM(OBJECT_ID) FROM ORDERS UNION SELECT
total time: 44.438 secs,  CPU: 41.611 secs (93.64%),  wait: 2.827 secs (6.36%)
                                                                   Time
Time Component                                                     secs       %
------------------------------------------------------------ ---------- -------
cpu : [.] kcbgtcr                                                29.714   66.87
cpu : [.] kdstf000010100001km                                     3.716    8.36
cpu : [.] lnxsum                                                  3.541    7.97
cpu : [?] sum of funcs consuming less than 2% of CPU time         2.393    5.38
cpu : [.] kaf4reasrp0km                                           2.180    4.91
wait: PL/SQL lock timer                                           2.100    4.73
wait: latch: cache buffers chains                                 0.727    1.64

		




keywords: eco, eco 2013, bgoug, doag, Understanding Oracle CPU Consumption

Share your knowledge of this product with other customers... Be the first to write a review

Browse for more products in the same category as this item:

Tools and Products


view my cart my account my support contact us about us        

Built with Volusion                                To receive OraPub activity updates »