A Simple Example Using COMPUTE SUM OF in SQL*Plus

I needed some SQL to show the time spent on idle events in an Oracle 11.2 database with a grand total at the end. I wrote this as shown below. The SQL*Plus syntax at the start is taken from the Oracle documentation but I wanted to record it so I would have my own worked example for future use: 

SQL> column dummy noprint;

SQL> compute sum of seconds_waited on dummy;

SQL> break on dummy;

SQL> select null dummy, event idle_event,

  2  round(time_waited/100) seconds_waited

  3  from v$system_event

  4  where wait_class = 'Idle'

  5  and round(time_waited/100) > 0

  6  order by seconds_waited

  7  /

 

IDLE_EVENT                                                       SECONDS_WAITED

---------------------------------------------------------------- --------------

PX Deq: Parse Reply                                                           1

SGA: MMAN sleep for component shrink                                          1

single-task message                                                           5

PX Deq: Execution Msg                                                         7

JOX Jit Process Sleep                                                       108

PL/SQL lock timer                                                           260

PX Idle Wait                                                               6000

jobq slave wait                                                           11025

VKRM Idle                                                                 14400

Streams AQ: waiting for time management or cleanup tasks                  75954

Space Manager: slave idle wait                                            77450

smon timer                                                                77459

pmon timer                                                                77657

Streams AQ: waiting for messages in the queue                             77671

Streams AQ: qmn slave idle wait                                           77729

Streams AQ: qmn coordinator idle wait                                     77740

DIAG idle wait                                                           155195

pipe get                                                                 241269

rdbms ipc message                                                       1319628

SQL*Net message from client                                            10756842

                                                                 --------------

                                                                       13046401

 

20 rows selected.

 

SQL>