We have 3 node Oralce 11.2.0.1 on Redhat 5.2. In single instance, I like to join a few dictionary views to find user session stats like
SELECT *
FROM v$session v LEFT JOIN gv$sqlarea Q on v.SQL_ID = Q.SQL_ID and v.INST_ID=Q.INST_ID
LEFT JOIN v$process vp on v.paddr = vp.addr and v.INST_ID=vp.INST_ID
LEFT JOIN v$transaction vt on v.saddr = vt.ses_addr and v.INST_ID=vt.INST_ID
where vt.start_date is not null or BLOCKING_SESSION is not null or wait_class<>'Idle'
This run prity well in single instance Oracle. In RAC, I replace the v$ views wth gv$ views. The query become very slow, takes~>500 seconds. I checked that the gv$process and gv$transaction have not many records. gv$sqlarea has 83000 rows and v$sqlarea around 1/3 of this. execution plan shows that the query use full table scan. Even so, 80,000 records are not a very big number for Oracle, and gv$ are memory views and no disk IO will be involved. I expect it is much faster (return in <50 seconds).
Is there a work around to make gc$sqlarea to forform better?