Post-Install Database Tasks - Oracle Exadata Recipes: A Problem-Solution Approach - page 608

Note that the _parallel_cluster_cache_pct parameter determines the percentage of the aggregate buffer cache size
that is reserved for In-Memory PX; if segments are larger than 80% the size of the aggregate buffer cache, by default,
queries using these tables will not qualify for In-Memory PX.
Once you have enabled Auto DOP and confirmed that In-Memory PX is enabled, use Oracle AWR data to find SQL
statements that executed with parallel query option and utilized In-Memory PX by running the script in Listing 21-14:

Listing 21-14. lst21-14-autodop-impx.sql

SQL> select ss.sql_id,
sum(ss.PX_SERVERS_EXECS_total) px_servers,
decode(sum(ss.io_offload_elig_bytes_total),0,'No','Yes') offloadelig,
decode(sum(ss.io_offload_elig_bytes_total),0,'Yes','No') impx,
sum(ss.io_offload_elig_bytes_total)/1024/1024 offloadbytes,
sum(ss.elapsed_time_total)/1000000/sum(ss.px_servers_execs_total) elps,
dbms_lob.substr(st.sql_text,60,1) st
from dba_hist_sqlstat ss, dba_hist_sqltext st
where ss.px_servers_execs_total > 0
and ss.sql_id=st.sql_id
and upper(st.sql_text) like '%IN-MEMORY PX T1%'
group by ss.sql_id,dbms_lob.substr(st.sql_text,60,1)
order by 5
/

PX Servers In-Memory IO Offload Elps Sec
SQL ID Per Exec Offloaded PX MB Per Exec SQL
----------- --------- --------- --------- ---------- -------- ----------------------------
5ccq67fmtr7ud 4 No Yes .00 15.67 select /* in-memory px t1 */
count(1) from RTL.R_DWB_RTL_TRX
2mg9wcry8cwn1 4 No Yes .00 16.23 select /* in-memory px t1 */
count(1) from RTL.R_DWB_RTL_TN
4a9kcdqxdzvn1 50 Yes No 101289.44 20.38 select /* in-memory px t1 */
count(1) from D14.DWB_RTL_TRX
g0p8nhj3959hs 50 Yes No 101530.05 21.07 select /* in-memory px t1 */
count(1) from D14.DWB_RTL_TNDR_
8tsyw5tnxa521 74 Yes No 143567.71 27.52 select /* in-memory px t1 */
count(1) from RTL.R_DWB_RTL_SLS
6s2qvx403y097 96 Yes No 903247.23 184.01 select /* in-memory px t1 */
count(1) from D14.DWB_RTL_SLS_R

SQL>

In the output above, please note the following:

• We are retrieving SQL statistics from AWR for SQL statements that match a specific string,

which we have limited based on knowledge of our workload;

• We have limited the output to include only cursors that used parallel execution;

• The second column displays the number of parallel query servers used for the cursor;

• The third column indicates whether the SQL statement was offloaded to the storage cell;

• The fourth column indicates whether the cursor was satisfied using In-Memory PX; if the

number of parallel servers is greater than zero but the bytes eligible for predicate offload is
zero, it's a good indication that In-Memory PX was in use.