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

the data could be either transferred via the interconnect using cache fusion or, as in Oracle Database 11 g Release 2,
the data could be retrieved from storage. Again, not only does the process have to transfer all the data over the
interconnect or load it from storage, the process has to traverse through all the rows available in the buffer cache
before the result set is built.
Step 6
Cruising along with our workshop, let's execute the query on instance 3 ( SSKY3 ) and observe the details. The great
performance benefit of using the result cache feature in the Oracle RAC environment illustrated in Figure 9-4 is that
only the final result set will be transferred between the instances, reducing considerable resources for both the CPU
and network.
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 0.00 0.00 0 0 0 300
------- ------ -------- ---------- ---------- ---------- ---------- ---------
total 23 0.02 0.03 0 0 0 300
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 89 (TPCC)
Rows Row Source Operation
------- ---------------------------------------------------
300 RESULT CACHE 8fbjhchhd9zwh7uhn4mv7dhvga (cr=5 pr=0 pw=0 time=299 us)
0 HASH GROUP BY (cr=0 pr=0 pw=0 time=0 us cost=126413 size=4950 card=150)
0 HASH JOIN (cr=0 pr=0 pw=0 time=0 us cost=125703 size=680920944 card=20633968)
0 INDEX FAST FULL SCAN ORDERS_I2 (cr=0 pr=0 pw=0 time=0 us cost=2743 size=22694870
card=2063170)(object id 86234)
0 INDEX FAST FULL SCAN IORDL (cr=0 pr=0 pw=0 time=0 us cost=87415 size=453947296
card=20633968)(object id 86202)
Based on the statistics collected using the 10046-trace event, there is no data being retrieved locally nor is there
any traversing of rows in the buffer cache of the local instance. The execution plan indicates that there was fetch of just
the final result set. How and where did this come from? Oracle Database was able to retrieve the result set from the
result cache section of the shared pool in instance one and transfer the data over the interconnect. Isn't this neat? This
really cuts down so much on data processing and improves resource utilization.
Step 7
The memory structure of the result cache section of the shared pool is also identical to the structure found on the instance
where the query was executed for the first time. This proves two things: no additional memory or resource is utilized for the
second instance, and each Oracle RAC instance maintains its own copy of the result set in its local result cache.
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 251680K bytes (251680 blocks)
Maximum Result Size = 12584K bytes (12584 blocks)