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

SELECT ss.inst_id INT,
sn.name,
sn.class,
ss.value
FROM gv$statname sn,
gv$sesstat ss
WHERE sn.inst_id = ss.inst_id
AND sn.statistic# = ss.statistic#
AND sn.name LIKE 'IM%'
AND ss.value > 0;

Figure 9-7 has two highlighted sections. One show the stats generated while populating the in-memory area and
the second section show stats while the SQL statements scan the in-memory area to get the required result sets.

■ Note in-memory stats are captured under class 128.

Similar to the statistics, wait events related to the in-memory operation can also be checked from the
V$SESSION_WAIT view.

Step 4
As illustrated in Figure 9-7 , since data from the data files are populated into both the database buffer cache and
in-memory area of the SGA, no specific query change is required to access the in-memory area of the SGA. Based on
the type of query being executed, the optimizer will decide if data should be read from the database buffer cache or
the in-memory columnar area.
The following query was executed after the data was loaded into the in-memory area (Step 3 above):

SELECT /*+ gather_plan_statistics */ d_name,
SUM(s_quantity)
FROM stock S,
district D
WHERE S.s_w_id = D.d_w_id
GROUP BY d_name;

In the plan output above, TABLE ACCESS INMEMORY FULL illustrates that the in-memory area was used for this
operation.