Tuning the Database - Expert Oracle RAC Performance Diagnostics and Tuning - page 293

Indirectly, you can also verify whether there are any objects present in the result cache by using the following
query. The following query lists of all the objects currently stored in the result cache:
Script: MVRACPDnTap_rcobjectcount.sql
COL INT format 999
SELECT inst_id INT,
id,
TYPE,
status,
NAME,
object_no objno,
cache_id,
invalidations invals
FROM gv$result_cache_objects
WHERE inst_id = &&instnum;
Step 2
To get a better understanding of the result cache architecture, let's execute a normal query without any reference to
the result cache feature. For comparison purposes, let's also trace the query using a 10046 trace and, using tkprof ,
generate a report.
SELECT OL_NUMBER, SUM (OL_AMOUNT), SUM (OL_QUANTITY)
FROM ORDER_LINE OL, ORDERS ORD
WHERE OL.OL_O_ID = ORD.O_ID AND
OL.OL_W_ID = ORD.O_W_ID AND
OL.OL_D_ID = ORD.O_D_ID
GROUP BY OL_NUMBER;
The output from the tkprof report indicates that about 347,000 rows were traversed to get the final result of 300
rows that is sent to user who executed query.
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 21 33.04 96.12 346671 347172 0 300
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 23 33.06 96.14 346671 347172 0 300
Step 3
If another user executes the query discussed in Step 1 and again by another set of users, the optimizer will generate
the same plan and the server will have to do the same process of traversing through 347,000 rows to get the 300 rows
back to the user.