Let's begin by taking a look at all of the time model statistics available to us. SQL> r 1 select distinct stat_name from v$sess_time_model 2* order by stat_name STAT_NAME -------------------------------------------------- DB CPU DB time Java execution elapsed time PL/SQL compilation elapsed time PL/SQL execution elapsed time background cpu time background elapsed time connection management call elapsed time failed parse (out of shared memory) elapsed time failed parse elapsed time hard parse (bind mismatch) elapsed time hard parse (sharing criteria) elapsed time hard parse elapsed time inbound PL/SQL rpc elapsed time parse time elapsed sequence load elapsed time sql execute elapsed time Dumping the V$SYS_TIME_MODE view to determine where the processes are spending their time. Most of the time is being spent executing SQL, which is most often the case. SQL> r 1 select * from v$sys_time_model 2* order by value desc STAT_ID STAT_NAME VALUE ---------- ------------------------------------------------ ---------------- 3649082374 DB time 45,873,764,893 2821698184 sql execute elapsed time 18,437,149,318 2748282437 DB CPU 18,155,804,510 4157170894 background elapsed time 4,676,150,049 2451517896 background cpu time 2,169,766,558 2643905994 PL/SQL execution elapsed time 1,821,772,751 1431595225 parse time elapsed 307,899,383 372226525 hard parse elapsed time 227,899,612 1311180441 PL/SQL compilation elapsed time 36,625,139 751169994 Java execution elapsed time 20,411,128 1990024365 connection management call elapsed time 9,945,853 3138706091 hard parse (sharing criteria) elapsed time 5,363,984 268357648 hard parse (bind mismatch) elapsed time 4,175,897 4127043053 sequence load elapsed time 491,769 1824284809 failed parse elapsed time 308,942 4125607023 failed parse (out of shared memory) elapsed time 0 290749718 inbound PL/SQL rpc elapsed time 0 I'm joining V$SESS_TIME_MODEL to V$SESSION to see what user processes are consuming the most amount of time in the database. V$SESSION is providing the account they are using in the database (DBUSER - column name change using column format command), the account they are logged into on the calling system (OSUSER), the program they are running (PROGRAM), the calling MACHINE and TERMINAL NAME. SQL> r 1 select a.sid, b.stat_name, b.value, a.username, a.osuser, a.program, a.machine, a.terminal 2 from v$session a, v$sess_time_model b 3 where a.sid = b.sid and 4 osuser 'oracle' 5 and stat_name = 'DB time' 6* order by b.value SID STAT_NAME VALUE DBUSER OSUSER PROGRAM MACHINE TERMINAL ---- ---------- ---------- ---- --- -------- ------- ------- 229 DB time 112535 EVUSA appdev java.exe OZZIE OZZIE 224 DB time 23023473 EVUSA appdev java.exe OZZIE OSBORN 255 DB time 35752413 SYSTEM cfoot sqlplusw CFOOTE CFOOTE 170 DB time 104309841 EVUSA appdev java.exe OZZIE OZZIE Let's see exactly where session 170 is spending the bulk of their time. It looks like most of the time is being spent executing SQL. Since I'm already tasked with the responsibility of reviewing the SQL for this application, that is where I'll start. SQL> r 1 select a.sid, b.stat_name, b.value, a.username, a.osuser, a.program, a.machine, a.terminal 2 from v$session a, v$sess_time_model b 3 where a.sid = b.sid and 4 a.sid=170 5* order by b.value desc SID STAT_NAME VALUE ---- ------------------------------------------------ ---------------- 170 DB time 104,309,841 170 sql execute elapsed time 49,773,662 170 DB CPU 39,817,961 170 PL/SQL execution elapsed time 8,748,373 170 parse time elapsed 2,491,402 170 hard parse elapsed time 2,204,755 170 PL/SQL compilation elapsed time 1,253,593 170 connection management call elapsed time 10,389 170 hard parse (sharing criteria) elapsed time 6,563 170 sequence load elapsed time 3,143 170 background elapsed time 0 170 failed parse elapsed time 0 170 hard parse (bind mismatch) elapsed time 0 170 Java execution elapsed time 0 170 inbound PL/SQL rpc elapsed time 0 170 failed parse (out of shared memory) elapsed time 0 170 background cpu time 0