FullTime Tool

• Report both Oracle session wait time and CPU time details •

Download v3h Now Download v3e Now

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 Linux OS tool perf.

Note: If the current FullTime tool version does not work, use the older version. Sometimes a new Linux release forces a newer FullTime release, which means the most recent FullTime tool may NOT work with your Linux.

This tool is the result of a collaborative effort between Craig and Frits Hoogland (frits.hoogland@gmail.com).

To receive updates about the FullTime Tool changes, sign-up for the Tools list.

Search Craig's blog for relevant posts at the link above.

An actual example of real output is shown below.

$ 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...

$ 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

Related Videos