Query on Dynamic View is slow ( v$ views)

 QUERY PERFORMANCE SLOW FOR V$ VIEWS


PROBLEM:
--------
Query performance containing v$ views (v$sql, v$session) are slow as compared
to same query using RULE hints.



DIAGNOSTIC ANALYSIS:
--------------------
select distinct sql_text, users_executing, executions, username
from   v$sql, v$session
where  users_executing>0
and    sql_address = address
and    sql_hash_value = hash_value

call     count       cpu    elapsed       disk      query    current       
rows
------- ------  -------- ---------- ---------- ---------- --------------------
Parse        1      0.01       0.01          0          0          0       0
Execute      1      0.00       0.03          0          0          0      0
Fetch        2      5.62       5.92          0          0          0       1
------- ------  -------- ---------- ---------- ---------- ---------- ----------
total        4      5.64       5.97          0          0          0          1


select /*+ rule */ distinct sql_text, users_executing, executions, username
from   v$sql, v$session
where  users_executing>0
and    sql_address = address
and    sql_hash_value = hash_value

call     count       cpu    elapsed       disk      query    current  rows
------- ------  -------- ---------- ---------- ---------- ----------  ---------
Parse        1      0.01       0.01          0          0          0          0
Execute      1      0.00       0.00          0          0          0        0
Fetch        2      0.80       0.86          0          0          0         1
------- ------  -------- ---------- ---------- ---------- ---------- ----------
total        4      0.82       0.88          0          0          0          1


select distinct sql_text, users_executing, executions, username
from   v$sql, v$session
where  users_executing>0
and    sql_address = address
and    sql_hash_value = hash_value
and STATUS = 'ACTIVE' and TYPE != 'BACKGROUND'

call     count       cpu    elapsed       disk      query    current   rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0         0
Execute    1      0.00       0.00          0          0          0          0
Fetch        2      0.00       0.00          0          0          0         1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.02        0.02          0          0          0          1


WORKAROUND:
-----------
use Rule hint.
or restrict records by predicate STATUS = 'ACTIVE' and TYPE != 'BACKGROUND'

Reference: Metalink -  Bug 7358330