Tools and Utilities - Expert Oracle RAC Performance Diagnostics and Tuning - page 194

■ the value of WAITING in the STATUS column in the GV$SESSION_WAIT view indicates that the session is
currently waiting for resources. in all other cases, it indicates that the session in utilizing CpU.

Note

Unlike the information in the GV$SYSTEM_EVENT , which is retained until the instance is bounced, the data
available in session views is lost after the session has disconnected from the database. Due to the dynamic nature of
these views and due to short span of a session's existence, collecting data from these views by querying them directly
may be difficult. Starting with Oracle Database10g, the session history information can be retrieved from ASH views.
The workaround to using this view is to capture the data in these views and store it in user-defined tables.
In order to retain the session statistics it is required that these statistics be written to a separate table before the
session disconnects from the database. This can be implemented using the BEFORE LOGOFF ON DATABASE trigger. The
BEFORE LOGOFF ON DATABASE trigger is created at the database level and is configured to capture all events from the
session-related views and insert them into their respective tables.

COL EVENT# FORMAT 9999
COL NAME FORMAT A30
COL PARAMETER1 FORMAT A12
COL PARAMETER2 FORMAT A12
COL PARAMETER3 FORMAT A12
SELECT
EVENT#,
NAME,
PARAMETER1,
PARAMETER2,
PARAMETER3
FROM GV$EVENT_NAME
WHERE (NAME LIKE 'db%'
OR NAME LIKE '%parse%'
OR NAME LIKE '%cpu%'
OR NAME LIKE '%global%'
OR NAME LIKE 'gc%')
/

The next step is to identify the actual files and the tablespaces that map to these events. Once the tablespaces are
identified, the next step is to identify which tables or indexes are contained in these tablespaces.
As illustrated below by querying the V$DATAFILE , V$TABLESPACE and DBA_TABLES view, the files are shared by
more than one table:

COL FILE# FORMAT 9999
COL FNAME FORMAT A44
COL TNAME FORMAT A25
COL TABLE_NAME FORMAT A30
SELECT
F.FILE#,
F.NAME FNAME,
T.NAME TNAME,
TABLE_NAME
FROM V$DATAFILE F,
V$TABLESPACE T,
DBA_TABLES