Workload Qualification - Oracle Exadata Recipes: A Problem-Solution Approach - page 75

• Use an Oracle AWR report to capture the top segments that are accessed via physical reads to

predict whether they will benefit from Smart Scan

• Assess whether your current workload will benefit from other Exadata Storage Server software

features such as Hybrid Columnar Compression, Storage Indexes, I/O Resource Management,
and so forth

The first task is to identify workload patterns that could qualify for Exadata Smart Scan. Smart Scan is arguably
the most important software feature bundled in the Exadata Storage Server software and generally provides the most
significant performance benefit. On Exadata, Smart Scans occur when Oracle's optimizer chooses a full table scan or
fast full index scan and when the query is able to access the data using serial or parallel direct reads.

■ Note please see recipe 15-10 for more information about oracle's direct read algorithm.

A good place to start in assessing Smart Scan workload qualification is with the Automatic Workload Repository
(AWR). In your current database, select a representative time interval and generate an AWR report. The first thing we
recommend to look at is the Top 5 Timed Foreground Events section, depicted in Figure 4-1 .

Figure 4-1. Top 5 Timed Foreground Events from AWR report

We see database CPU consuming 64.93% of our overall time, which alone doesn't necessarily qualify this workload
for Exadata, but we're also seeing direct path read and db file scattered read wait events. Both of these typically
indicate we're doing full scanning of some sort, and potentially these could be offloaded and optimized with Exadata
Smart Scan. Next, jump to the SQL Ordered by Physical Reads section to get a picture of which SQL statements are
doing physical I/O. Figure 4-2 displays an example of this section from the AWR report generated previously.

Figure 4-2. SQL ordered by Physical Reads