Friday, July 10, 2015

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
20 rows selected.

No comments: