Do you have a wait problem?

SQL> select metric_name, value
2 from v$sysmetric
3 where metric_name in ('Database CPU Time Ratio',
4 'Database Wait Time Ratio') and
5 intsize_csec =
6 (select max(INTSIZE_CSEC) from V$SYSMETRIC);
METRIC_NAME VALUE
————————————------------ -----------
Database Wait Time Ratio 11.371689
Database CPU Time Ratio 87.831890

If the query shows a very high value for the Database Wait Time Ratio, or if the Database Wait Time Ratio is much greater than the Database CPU Time Ratio, the database is spending more time waiting than processing and you must dig deeper into the Oracle wait events to identify the specific wait events causing this.

top 5 database objects in use, along with the event associated with that database object, over the past 15 minutes

SELECT * FROM
(
SELECT o.object_name, o.object_type, s.event,
SUM(s.wait_time + s.time_waited) total_waited
FROM v$active_session_history s, dba_objects o
WHERE s.sample_time between sysdate - 1/24/4 and sysdate
AND s.current_obj# = o.object_id
GROUP BY o.object_name, o.object_type, s.event
ORDER BY 4 desc
) 

WHERE rownum <= 5;

top 5 sessions that were using the most CPU resources [or waiting for resources] within the last 15 minutes

SELECT * FROM
(
SELECT s.username, s.module, s.sid, s.serial#, count(*)
FROM v$active_session_history h, v$session s
WHERE h.session_id = s.sid
AND h.session_serial# = s.serial#
AND session_state= 'ON CPU' AND
sample_time > sysdate - interval '15' minute
GROUP BY s.username, s.module, s.sid, s.serial#
ORDER BY count(*) desc
)
where rownum <= 5;

If you wanted to see the sessions that are waiting for
resources, you could issue the same query as previously, with a SESSION_STATE of WAITING

Patch Set Updates for Oracle Products

854428.1

Tanel Poder Snapper, brilliant !!!!!

SQL> @snapper ash=sid+event+wait_class 5 1 all
Sampling SID all with interval 5 seconds, taking 1 snapshots...


-- Session Snapper v3.52 by Tanel Poder @ E2SN ( http://tech.e2sn.com )


------------------------------------------------------------------------
Active% |    SID | EVENT                               | WAIT_CLASS
------------------------------------------------------------------------
    43% |    139 | DFS lock handle                     | Other
    34% |    139 | ON CPU                              | ON CPU
     7% |    121 | ASM file metadata operation         | Other
     7% |    139 | control file sequential read        | System I/O
     5% |    155 | db file parallel write              | System I/O
     5% |    139 | local write wait                    | User I/O
     2% |    114 | ASM file metadata operation         | Other
     2% |    155 | control file sequential read        | System I/O
     2% |    139 | db file sequential read             | User I/O
     2% |    139 | control file parallel write         | System I/O


--  End of ASH snap 1, end=2012-01-31 14:37:38, seconds=5, samples_taken=44


SQL> @snapper stat 5 1 139


SQL> @snapper ash 5 1 139
Sampling SID 139 with interval 5 seconds, taking 1 snapshots...


-- Session Snapper v3.52 by Tanel Poder @ E2SN ( http://tech.e2sn.com )


---------------------------------------------------------------------------------
Active% | SQL_ID          | EVENT                               | WAIT_CLASS
---------------------------------------------------------------------------------
    30% | dwju4951auwat   | ON CPU                              | ON CPU
    28% | dwju4951auwat   | DFS lock handle                     | Other
     2% | dwju4951auwat   | db file single write                | User I/O
     2% | dwju4951auwat   | local write wait                    | User I/O


--  End of ASH snap 1, end=2012-01-31 14:33:14, seconds=5, samples_taken=46


SQL> @sqlid dwju4951auwat %
Show SQL text, child cursors and execution stats for SQLID dwju4951auwat child %


HASH_VALUE  CH# SQL_TEXT
---------- ---- ------------------------------------------------------------------------------------------------------------------------------------------------------
1118663001    0 INSERT INTO CNM1 SELECT * FROM V$SESSION


 CH# PARENT_HANDLE    OBJECT_HANDLE     PLAN_HASH     PARSES   H_PARSES EXECUTIONS    FETCHES ROWS_PROCESSED ROWS_PER_FETCH    CPU_SEC    ELA_SEC       LIOS       PIOS      SORTS USERS_EXECUTING
---- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- -------------- -------------- ---------- ---------- ---------- ---------- ---------- ---------------
   0 000000021A24C328 000000021A24C120  644658511         10          1      67894          0        3049130                12.3964742 304.288453    1240108       1371          0               1

Clusterware and Messi-ng around

[oracle@raclinux1 ~]$ ocrcheck
[oracle@raclinux1 ~]$ crsctl query css votedisk

Check control files:
/etc/oracle/scls_scr/racnode01/root

crsstart
cssrun
noclsvmon
cssfboot
crsdboot
oprocdpid
daemonpid
clsomonpid