Waits, Enqueues, and Latches - Expert Oracle RAC Performance Diagnostics and Tuning - page 579

Step 5
It's good to understand the details of SIDs holding the buffer. A query against the GV$SESSION and GV$PROCESS views
would help list the currently active sessions and the SQL_ID for the statements executed.
Script: MVRACPDnTap_whoractivep.sql
select
s.inst_id,
s.username,
' '||to_char(s.SID)||','|| to_char(s.serial#) "SID_SER",
to_char(s.logon_time,'mm/dd hh24:mi ') logon_time,
SUBSTR(s.status,1,1) status,
s.service_name,
p.pid ppid,
p.spid,
s.process,
s.sql_id,
s.blocking_session,
s.event,
s.machine
from gv$session s,
gv$process p
where s.paddr = p.addr
and s.inst_id = p.inst_id
and s.username is not null
and s.username not in ('SYS','SYSTEM')
and s.status ='ACTIVE'
and s.type <> 'BACKGROUND'
order by logon_time,s.inst_id
;
From the preceding output, the session details and the SQL_ID for the SQL operations could be obtained; and for
further analysis, an XPLAN (Explain Plan) could be generated. Based on the XPLAN generated and the type of SQL
being executed, appropriate optimization could be done.
gc element
In a RAC environment, there are global operations. Global operations may require changes to be made to the
distributed lock manager (DLM) lock elements. When such changes are made, the lock element array is protected by
the gc element latch. Due to the nature of the architecture, with high global operations, there are bound to be several
latch requests. Closer understanding of the transaction behavior of the application would help determine the amount