Smart Scan and Cell Offload - Oracle Exadata Recipes: A Problem-Solution Approach - page 452

3. When complete, turn tracing off and open your 10046 trace file using the viewer or editor

of your choice.

To locate Smart Scan wait events, search for lines that display the cell smart table scan
wait event:

4.

[oracle@cm01dbm01 trace]$ grep "cell smart table scan" visx1_ora_23639.trc |head −5
WAIT #140715485923920: nam='cell smart table scan' ela= 126 cellhash#=2520626383 ...
output truncated
WAIT #140715485923920: nam='cell smart table scan' ela= 112 cellhash#=88802347... output
truncated
WAIT #140715485923920: nam='cell smart table scan' ela= 136 cellhash#=398250101 ..
output truncated
WAIT #140715485923920: nam='cell smart table scan' ela= 502 cellhash#=2520626383 ...
output truncated
WAIT #140715485923920: nam='cell smart table scan' ela= 494 cellhash#=88802347 ... output
truncated
[oracle@cm01dbm01 trace]$

5. To find wait event lines corresponding to block I/O, execute the following command:

[oracle@cm01dbm01 trace]$ grep "cell" visx1_ora_23639.trc|grep block|head −5
WAIT #140715485923920: nam='cell single block physical read' ela= 607 cellhash#=88802347 ...
WAIT #140715485923920: nam='cell multiblock physical read' ela= 820 cellhash#=88802347 ...
WAIT #140715485922944: nam='cell single block physical read' ela= 515 cellhash#=2520626383 ...
WAIT #140715485923920: nam='cell single block physical read' ela= 529 cellhash#=88802347 ...
WAIT #140715485923920: nam='cell multiblock physical read' ela= 1632 cellhash#=88802347 ...
[oracle@cm01dbm01 trace]$

The bottom of a tkprof output will show a summary of wait events and their
corresponding statistics. The line in bold indicates Smart Scan operations:

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
cell single block physical read 104 0.00 0.05
cell smart table scan 8356 0.01 1.15
cell list of blocks physical read 11 0.00 0.01
... Output omitted for brevity

How It Works
As presented in Recipe 14-7, Smart Scan wait events begin with the cell smart% string. Any wait event that begins
with cell and has the text block inside it represents a traditional I/O-related wait event on Exadata.
Extended SQL trace is enabled by setting event 10046 for a session or SQL statement. There are a number of
ways to enable SQL trace with wait event information. As most experienced Oracle DBAs are familiar with extended
SQL trace and its application and use, a detailed explanation is outside the scope of this text. To learn more, consult
Oracle's documentation at docs.oracle.com/cd/E11882_01/server.112/e16638/sqltrace.htm#PFGRF94998 or
Google using the search term “extended sql trace with event 10046”.