Oracle Exadata is a big step forward from the traditional database server architecture; however, despite all the innovative features, it is still running the standard Oracle Database software. Most of the well-versed database performance rules still apply, with the addition of some that recognize the advantage of Exadata functionality like Smart Scans, cell join filtering, and the Flash Cache. In this chapter, you can read more about the Exadata-specific and related performance topics, metrics, and some relevant internals.

Thankfully, Oracle, both at the database layer and in cells, provides lots of performance metrics for our use. However, when looking into any metric, you should know why you are monitoring this and what numbers are you looking for. In other words, how do you know when everything is OK and no action is needed, and how do you know when things are bad and action is needed? In order to avoid wasting effort on fixing the wrong problem, it is really important to measure what matters. For database performance, nothing matters more than response time, the actual time the end user (or connected system) has to wait for the response. So, if you want to make something faster in the database, you should focus on measuring and then optimizing the response time. All the other metrics and indicators, like the number of I/O requests or cache hits, are secondary. End users, who are waiting for their report to be generated, care about the time they have to wait only, not secondary metrics like CPU utilization or I/O rate. Nevertheless, often these secondary metrics become very useful for understanding and explaining performance issues.

The key metrics for breaking down database response time are the Oracle wait interface’s events. Some key wait events are discussed in Chapter 10, and you will be introduced to performance monitoring tools that make use of them in Chapter 12. However, there are additional useful metrics Exadata provides, such as the number of bytes of data returned by Smart Scans and the actual amount of I/O avoided thanks to storage indexes, and many more. Such metrics give very important additional information about what is happening in the database and storage cells during SQL execution. In this chapter, you will read more about these metrics, and you will learn how to access and display them and, most importantly, what they mean. Even if you are not interested in knowing what each metric means, we still recommend you read this chapter, as it explains some important internals and design decisions in the Exadata-specific parts of the database code and the storage servers.

The information in this chapter should give you a good understanding about some key internal workings of Exadata databases and storage servers, and it should prepare you for Chapter 12, where this knowledge will be put to good use when monitoring and troubleshooting Exadata performance.

Measuring Exadata’s Performance Metrics

Before examining Exadata-specific performance metrics, let’s examine some internals and review some important elements of Exadata-specific features and metrics. One must understand what the performance numbers actually stand for before trying to monitor or optimize anything with this info.

By now, you know that Exadata database nodes do not perform physical disk I/O themselves, but ask the cells to execute the requests for them via the ASM layer. In the case of Smart Scans, the cell servers will also process the blocks read: extract their contents, filter rows, and so on. So, conceptually, the cells appear just like a black box from database’s point of view. The database layer requests blocks or ranges of blocks of data, and the cells perform the physical I/O work transparent to the database layer and return the requested columns of matching rows. Thankfully, the Oracle Exadata architects, engineers, and developers have put a surprisingly large amount of instrumentation into cellsrv and related components. The cells keep track of how much work they have done and, they can also send the metrics back to the database layer along the results. This allows us—DBAs, developers, and troubleshooters—to have an end-to-end overview of what happened in the database and the cells when servicing a user’s request or running a query. Try to get this amount of performance detail from your existing storage infrastructure as a DBA and you will quickly appreciate Exadata even more.

For example, when a query is executed via Smart Scan, you will still see familiar statistics such as physical_reads when you query V$SQLSTATS in the database layer, even though the database layer itself did not do any physical reads directly. Another example is the cell physical IO bytes saved by storage index statistic, which is counted in the cell level and not in the database. These numbers are visible in the database layer thanks to the cells sending back useful performance metrics in addition to the data queried. Some metrics currently report data at the cell and database level, and you can see some double counting on these.

In addition to the standard Oracle performance tools, this chapter will introduce two custom-built tools discussed in this chapter and Chapter 12. They are more suitable for flexible and advanced performance analysis and allow you to go beyond the standard wait events and SQL statement level statistics. The first tool, Oracle Session Snapper, is a script containing just an anonymous PL/SQL block, which measures detailed performance metrics from V$SESSION, V$SESSION_EVENT, V$SESS_TIME_MODEL, V$SESSTAT, among others. The last performance view from that list, V$SESSTAT, is especially important for advanced performance analysis—it contains hundreds of dynamic performance counters (over 600 in 11.2.0.2 and 1178 in Oracle 12.1.0.2) for each session in the instance. In addition to the usual monitoring using wait events, diving into V$SESSTAT gives us a much better idea of what kind of work Oracle sessions are doing, such as how many I/O requests per second they are doing, how many full segment scans per second, how many migrated/chained rows had to be fetched during a scan, and many more.

Revisiting the Prerequisites for Exadata Smart Scans

In this section, you are going to be gently introduced to the metrics the Oracle Database kernel’s instrumentation provides us in more detail. The focus is on the Exadata-related performance statistics. Details of the Exadata wait events are not covered here, as these are already explained in Chapter 10. Where applicable, a review of how to use wait events for a better understanding of database performance is added, and you may find some of the offloading and Smart Scan material already covered in Chapter 2. This is a refresher for the benefit of understanding the chapter and without having to go back many pages. Understanding the details is important in the context of the chapter and in the context of monitoring and troubleshooting Exadata performance.

One of the primary performance boosters for data warehousing and reporting workloads on the Exadata platform is the Exadata Smart Scan. OLTP workloads benefit greatly from the use of Exadata Smart Flash Cache. The first part of this section details how to measure whether your workload is benefitting from both of these features. This knowledge forms the building blocks for later database and query performance monitoring in subsequent sections. In the second part, you can read more about some Exadata internal performance counters.

Exadata Smart Scan Performance

Since Smart Scans are such an exciting feature, their metrics come first. Before discussing any of the metrics, let’s review how the decision to do a Smart Scan is made in Oracle. Note that a Smart Scan can be used on regular table segments and also on materialized view segments—which are physically no different from regular tables. Smart Scans can also be used for full scanning through B*Tree index segments (Index Fast Full Scan) and also bitmap index segments (Bitmap Index Fast Full Scan). Scanning through index segments using the “brute force” multi-block reads as opposed to the single block I/O approach otherwise employed is very similar to how a full table scan is executed against a table. The major difference is that index segments have an additional structure called index branch blocks, which have to be skipped and ignored, in addition to ASSM bitmap blocks, which have to be skipped both in table and index scans. Smart Scans on partitions and subpartitions of tables and indexes are internally no different from scans on nonpartitioned objects. A partitioned segment really is just a logical grouping of smaller segments. Remember that the Smart Scans can scan a variety of segments.

Regardless of segment type, a Smart Scan always requires direct path reads to be chosen by the SQL execution engine during execution time, which is not an optimizer decision. So, when troubleshooting why a Smart Scan was not used, you will have to first check whether direct path reads were used or not. You should check the execution plan first, to see whether a full scan is reported there at all. Here you see simple examples showing full segment scans happening on different segment types, starting with a full table scan:

SELECT AVG(line) FROM t WHERE owner LIKE 'S%'

-----------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |       |       |   295K(100)|          |
|   1 |  SORT AGGREGATE            |      |     1 |    11 |            |          |
|*  2 |   TABLE ACCESS STORAGE FULL| T    |  5743K|    60M|   295K  (1)| 00:59:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - storage("OWNER" LIKE 'S%')
       filter("OWNER" LIKE 'S%')

Note that the “STORAGE” in the TABLE ACCESS STORAGE FULL line here does not mean that Oracle is attempting to do a Smart Scan. This keyword merely indicates that Oracle knows that this segment is residing on a storage cell, not something else like an NFS mount or iSCSI device, and it is using the table scan codepath capable of performing a Smart Scan. Whether a Smart Scan is actually used depends on multiple other factors, which were discussed in Chapter 2 and will be also demonstrated here briefly. Whether or not the storage keyword is displayed in the execution plan also depends on the initialization parameter cell_offload_plan_display. Also, Smart Scan is a broad term, covering filtration (which may be able to take advantage of storage indexes), column projection, decompression of HCC compressed CUs, and hash join early elimination with bloom filters among others— all done in the cells. Even if you are not taking advantage of the filter predicate offloading to storage (filtration), the cells may be able to reduce the amount of data returned to the database. If Smart Scan is used, the column projection is done in the cells, and they return only the required columns instead of entire blocks containing the full-length rows.

In addition to finding the storage keyword in the execution plan, it is important to check whether a storage() predicate is shown in the “Predicate Information” section below the execution plan. This is a good indicator of whether the execution plan is capable of doing a Smart Scan’s predicate offload (smart filtration). Unfortunately, this does not mean that a Smart Scan predicate offload was actually attempted. There are cases where even the presence of a storage() predicate does not guarantee that a predicate offload will take place. This is where Oracle performance metrics will be helpful, but more about them shortly.

In summary, a full segment scan access path with the STORAGE keyword and the storage() predicate must be present in the execution plan in order for it to be capable of doing a Smart Scan’s predicate offload at all. If you do see the STORAGE lines in execution plan, but no storage() predicates under it, then predicate offload will not even be attempted, but you still may benefit from column projection offload, in which only the required columns are returned by the cells. You will probably see multiple storage predicates (and full scan operations) in real-life query plans, as you will be doing multi-table joins.

If you see the STORAGE option and storage() predicate in an execution plan, the odds are that a Smart Scan will be attempted. Predicate offload is possible in principle, but there is no guarantee that a Smart Scan happens every time you run the query. On the other hand, if you do not see a STORAGE keyword in the execution plan, then there is no way a Smart Scan could happen on a corresponding segment in a given execution plan step. When there is no STORAGE keyword in the execution plan line, it means that this rowsource operator is not capable of using the smart features of the storage cells, and thus is unable to push any storage() predicates into the cell either.

You read earlier that Smart Scans can be done on materialized view segments, too. This is possible because a materialized view segment is physically exactly the same as any regular table. Here is the corresponding excerpt from an execution plan:

select count(*) from mv1 where owner like 'S%'

--------------------------------------------------------------------
| Id  | Operation                     | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |        |   139K(100)|
|   1 |  SORT AGGREGATE               |      |      1 |            |
|*  2 |   MAT_VIEW ACCESS STORAGE FULL| MV1  |   2089K|   139K  (1)|
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - storage("OWNER" LIKE 'S%')
       filter("OWNER" LIKE 'S%')

Following are two examples where Smart Scans can be attempted. The first is when scanning through a regular B*Tree index segment, where an Index Fast Full Scan is requested:

SELECT /*+ INDEX_FFS(t2) */ AVG(LENGTH(owner)) FROM t2 WHERE owner LIKE'S%'

---------------------------------------------------------------------
| Id  | Operation                     | Name  | E-Rows | Cost (%CPU)|
---------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |        |  5165 (100)|
|   1 |  SORT AGGREGATE               |       |      1 |            |
|*  2 |   INDEX STORAGE FAST FULL SCAN| T2_I1 |    597K|  5165   (2)|
---------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - storage("OWNER" LIKE 'S%')
       filter("OWNER" LIKE 'S%')

In the second example, an Index Fast Full Scan is requested on a bitmap index segment:

SELECT /*+ INDEX_FFS(t1) */ AVG(LENGTH(owner)) FROM t1 WHERE owner LIKE'S%'

Plan hash value: 3170056527

-----------------------------------------------------------------------------------
| Id  | Operation                             | Name        | E-Rows | Cost (%CPU)|
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |             |        |   505 (100)|
|   1 |  SORT AGGREGATE                       |             |      1 |            |
|   2 |   BITMAP CONVERSION TO ROWIDS         |             |    597K|   505   (0)|
|*  3 |    BITMAP INDEX STORAGE FAST FULL SCAN| BI_T1_OWNER |        |            |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - storage("OWNER" LIKE 'S%')
       filter("OWNER" LIKE 'S%')

In both cases, you see that the segments were scanned using a fast full scan, which is just like a full table scan but on index segments, and the presence of the STORAGE option and storage() predicates on the full scan operations shows that a Smart Scan predicate offload can be attempted.

You may wonder why you are repeatedly reading, “Smart Scan predicate offload can be attempted.” The reason for the considerate wording is simple: there are cases where a Smart Scan either is not employed or is started but does not complete during runtime. Yes, the execution plan structure may lead you to believe a Smart Scan will be used for a query, but whether the Smart Scan is actually executed depends first on whether a direct path read is chosen to scan the segment or not. Moreover, even if a direct path read is chosen and a Smart Scan is executed, then somewhere during (or at the beginning of) Smart Scan execution, a different decision may be made. This depends on multiple factors, and we will cover some of them here. At that point, the session statistics really shine and help the performance analyst to determine reasons for the observed behavior.

This rather abstract sounding paragraph is best explained with an example. You could try Smart Scan on an Index-Organized Table (IOT) segment first to see how valuable the additional Oracle metrics are. Note that as of the current Oracle version at the time of writing, Oracle has not implemented Smart Scan functionality on Index Organized Table segments yet. That is why this is a good example for practicing using the Smart Scan-related metrics.

Review the execution plan shown here; it is from a query using an index fast full scan on an Index Organized Table’s index segment:

select avg(length(state)) from t1_iot where date_created >
to_date('07.11.2013','dd.mm.yyyy')

Plan hash value: 379056979

-------------------------------------------------------------------------------------------
| Id  | Operation             | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                   |       |       |   402 (100)|          |
|   1 |  SORT AGGREGATE       |                   |     1 |    17 |            |          |
|*  2 |   INDEX FAST FULL SCAN| SYS_IOT_TOP_19445 |   740K|    12M|   402   (6)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("DATE_CREATED">TO_DATE(' 2013-11-07 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))

Unlike in plans listed earlier, there is no STORAGE option listed in the execution plan row source (line 2), and there is no storage() predicate, indicating an opportunity to push a filter predicate to the storage cells. This plan is not capable of using any Smart Scan functionality; it will do good old block I/O. Looking at additional Exadata and cell metrics can be skipped straight away, as the execution plan itself shows that it cannot use a Smart Scan-compatible row source codepath.

Understanding Exadata Smart Scan Metrics and Performance Counters

When you have an Oracle execution plan using the storage-aware row sources, you still cannot be entirely sure whether a Smart Scan really is attempted and does what you expect. The wait interface introduced in Chapter 10 is a trustworthy source of performance-related information. During the execution of your query, consult the relevant V$-views to determine what your session is waiting for. Consider the following possibilities:

• CPU usage only: This seems to mean that a buffered data access is used (not direct path), as visible in the absence of I/O wait events when traversing the buffer cache and all the data happens to be cached. Careful though: ASH and tools taking performance data from it uses a one-second sample interval and might miss very short physical IO events. The venerable SQL Trace or usage of snapper to capture information from v$sesstat/v$mystat get their data from a different source than ASH.
• cell multiblock physical read: Apparently buffered multi-block reads are used (looks like a full segment scan), but multi-block read waits can be reported also for LOB and SecureFile read operations, where in case of LOBs, the LOB chunk size is bigger than the block size. Otherwise, single-block reads would be reported for LOB access. With Exadata cell software 12.1.1.1.1+, inline LOBs can be offloaded.
• cell single block physical read: Apparently buffered single block reads are used. If these are the only I/O wait events you see (and not together with multi-block reads), then it appears you are not using a full segment scan at all. Sometimes single-block reads show up due to other operations in the execution plan (like some index range scan) or due to chained rows in data blocks.

If you see regular cell multiblock physical read wait events in your session, then direct path reads were clearly not used. This may happen mainly for serially executed operations, as if you are using parallel_degree_policy = MANUAL or LIMITED. Parallel Execution slaves will quite likely perform direct path read scans, which will then be offloaded and executed as Smart Scans. On the other hand, when you are using the new automatic parallel degree policy (parallel_degree_policy = AUTO or ADAPTIVE) and Oracle decides to perform an in-memory parallel query, Oracle will use reads using the buffer cache even for parallel operations, for which the wait events will show “buffered” reads as a result.

In addition to these issues, there are more reasons and special cases where Smart Scans just silently are not used or fall back to regular block I/O mode—potentially slowing down your queries and workload more than you expect. Thankfully, Oracle Exadata is very well instrumented and by using the performance framework, the analyst can review underperforming queries and optimize accordingly.

Exadata Dynamic Performance Counters

While the Oracle wait interface’s wait events provide us with crucial information about where the database response time is spent, the Exadata dynamic performance counters take us one step further and explain what kind of operations or tasks the Oracle kernel is performing—and how many of them. Wait events and performance counters complement each other and should not really be used alone. Oracle dynamic performance counters are also known as V$SESSTAT or V$SYSSTAT statistics (or counters), as these views are used for accessing them. When using 12c Multi-Tenant databases, you might find V$CON_SYSSTAT interesting as well.

When and How to Use Performance Counters

When troubleshooting performance, you should always begin the troubleshooting process by considering the wait events and SQL ID-level activity measurements. These keep track of the time, which end users care about. If additional detail is needed, then proceed to reviewing performance counters. If standard wait event information does not provide enough information, the performance counters provide a very detailed insight into what Oracle sessions are doing. For example, if your session seems to be burning lots of CPU, you can see whether the session logical reads or parse count (hard) counters increase for a session more than normally. Or if you see some unexpected single-block reads during a full table scan, you can check whether the table fetch continued row or some statistic like data blocks consistent reads – undo records applied increases, which indicate either a chained/migrated row or consistent read (CR) buffer cloning plus rollback overhead. Another useful metric is user commits, which gives you an understanding of how many database transactions are done inside an instance (or in chosen sessions). So, the next time a session seems to be waiting for a log file sync wait event, you can check its user commits counter value from V$SESSTAT to see how frequently this session is committing its work.

Unfortunately, Oracle does not document all performance counters. The ones documented can be found in the Oracle 12c Reference, Appendix E “Statistics Descriptions.” Some cell-related performance counters are documented in the Exadata documentation set in the Exadata Storage Server Software User’s Guide. However, the level of detail in the documentation is not always enough to understand a given performance counter. Experience will teach you which ones are important and which ones are not.

Although performance statistics can also be found in AWR reports, they are most useful when used while a problem is occurring. When calculating the rate of change (“delta”) of these counters, it is possible to see what your session is doing in a lot more detail.

You read earlier in this chapter that performance counters are assigned to a class. But even within these classes, you can make out groups of related counters. Think of physical I/O, for example. There are statistics for reads and writes, with the different reads shown here:

SQL> select name from v$statname where name like 'physical reads%' order by name;

NAME
----------------------------------------------------------------
physical reads
physical reads cache
physical reads cache for securefile flashback block new
physical reads cache prefetch
physical reads direct
physical reads direct (lob)
physical reads direct for securefile flashback block new
physical reads direct temporary tablespace
physical reads for flashback new
physical reads prefetch warmup
physical reads retry corrupt

11 rows selected.

Now, if you were to execute a SQL statement and capture the value of the counters at the query’s start and end, you could calculate the difference between end and start. In the next example, this has been done for physical reads. The session executed the following statement:

SQL> select count(*) from bigtab union all select count(*) from bigtab;

The following statistic counters related to physical reads have changed while the query executed:

physical read bytes                                       : 27603034112
physical read IO requests                                 : 26379
physical read requests optimized                          : 23671
physical reads                                            : 3369127
physical reads direct                                     : 3369127
physical read total bytes                                 : 27528650752
physical read total bytes optimized                       : 24767488000
physical read total IO requests                           : 26310
physical read total multi block requests                  : 26286

This should give you a very exact picture over the physical reads as reported by the database layer for this particular session and query. Note that these are not the only physical reads accounted for—you will be introduced to the full spectrum of IO events relevant to Smart Scan processing in later sections.

The wait interface would have given you the following information, taken from a tkprof’d trace file:

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                        2        0.00          0.00
  SQL*Net message to client                       2        0.00          0.00
  enq: KO - fast object checkpoint                6        0.00          0.00
  reliable message                                2        0.00          0.00
  cell smart table scan                        2969        0.01          2.55
  SQL*Net message from client                     2        1.49          1.49

Both of these—statistic counters and wait interface—give you information about the session’s activity. What should have become apparent, though, is how the session statistics give you more detailed information about the disk reads. Other statistics—remember that Oracle maintains 1178 for your session in 12.1.0.2—give you more insights into other aspects of the processing performed. By just looking at the wait information, you cannot determine if Flash Cache has been accessed to provide the relevant data. The performance counters allow you to confirm that Flash Cache was used for 23,671 out of 26,379 physical read requests. Storage indexes were specifically disabled for that query in the current session and, therefore, did not play a role (although, strictly speaking, this was not needed due to the lack of a where clause).

Dynamic performance counters provide important clues, which allow you to direct your troubleshooting efforts better. Note that tools like Statspack and AWR reports rely heavily on V$SYSSTAT counters. They just store values from these ever-increasing numbers (since instance start) in their repository tables. So, whenever you run a Statspack/AWR report, just deltas between values in chosen snapshots are reported. Statspack and AWR reports are all about showing you deltas between V$SYSSTAT (and other views) numbers from different snapshots of time.

While the V$SYSSTAT view is fine for monitoring and troubleshooting instance-wide performance (like AWR and Statspack reports do), its problem is that you cannot possibly use systemwide statistics to troubleshoot a single session’s problem. System-level statistics aggregate all your (potentially thousands of) sessions’ metrics together into one set of counters. That is why Oracle also has V$SESSTAT, which keeps track of all these individual counters for each session separately! Every single session in the instance has its own hundreds or thousands of performance counters, keeping track of only its activity. This dynamic performance view truly is a goldmine—if only a few sessions (or users) have a problem in the instance, you can monitor only their activity without being distracted by all the other users’ noise in the database.

As said earlier, V$SYSSTAT accumulates instancewide performance counters; they start from zero and only increase throughout the instance lifetime. Most of the V$SESSTAT counters always increase (cumulative statistics) with some exceptions, for example logons current and session pga/uga memory. In any case, when examining the counter values, you should not just look at the current value of a counter, especially if your session has been connected for a while. The problem is that even if you see a big-looking number for some counter in V$SESSTAT of a long-running connection pool’s session, how do you know what portion of that was incremented or added today, right now, when you have the problem, as opposed to a few weeks ago when that session logged on? In other words, when troubleshooting a problem happening right now, you should look at performance metrics for right now, in that particular time interval of the problem. A similar rule applies when troubleshooting issues of the past.

This is why coauthor Tanel Poder has written a “little” helper tool called Oracle Session Snapper, which allows its user to easily display the sessions’ current activity from V$SESSTAT and various other session-level performance views. An important aspect about this tool is that it is “just” an anonymous PL/SQL block, parsed on the fly; it does not require any installation or DDL privileges in the database. This should make it easy to deploy and use. The current Snapper version is available online at ExpertOracleExadata.com. Following is one example of how to run Snapper to measure SID 789’s activity (for a single five-second interval). In this example, the script has been renamed to snapper4.sql to distinguish it from previous versions. Read the Snapper header for instructions and detailed documentation. In this example, Snapper has been instructed to report any difference for the performance counters for session 789. Additionally, it samples wait-event related information from V$SESSION and presents it in an ASH-like format.

  sid username     statistic                                                             delta
  789 MARTIN       Requests to/from client                                                   1
  789 MARTIN       opened cursors cumulative                                                 1
  789 MARTIN       user calls                                                                2
  789 MARTIN       pinned cursors current                                                    1
  789 MARTIN       session logical reads                                                 2.59M
  789 MARTIN       user I/O wait time                                                      166
  789 MARTIN       non-idle wait time                                                      166
  789 MARTIN       non-idle wait count                                                   8.14k
  789 MARTIN       session uga memory                                                    6.23M
  789 MARTIN       session pga memory                                                     8.85M
  789 MARTIN       enqueue waits                                                              3
  789 MARTIN       enqueue requests                                                          2
  789 MARTIN       enqueue conversions                                                       4
  789 MARTIN       enqueue releases                                                          2
  789 MARTIN       global enqueue gets sync                                                  6
  789 MARTIN       global enqueue releases                                                   2
  789 MARTIN       physical read total IO requests                                      20.24k
  789 MARTIN       physical read total multi block requests                             20.22k
  789 MARTIN       physical read requests optimized                                     18.31k
  789 MARTIN       physical read total bytes optimized                                  19.17G
  789 MARTIN       physical read total bytes                                            21.18G
  789 MARTIN       cell physical IO interconnect bytes                                 416.83M
  789 MARTIN       ges messages sent                                                         3
  789 MARTIN       consistent gets                                                       2.59M
  789 MARTIN       consistent gets from cache                                               13
  789 MARTIN       consistent gets from cache (fastpath)                                    13
  789 MARTIN       consistent gets direct                                                2.59M
  789 MARTIN       logical read bytes from cache                                        106.5k
  789 MARTIN       physical reads                                                        2.59M
  789 MARTIN       physical reads direct                                                 2.59M
  789 MARTIN       physical read IO requests                                            20.24k
  789 MARTIN       physical read bytes                                                  21.19G
  789 MARTIN       calls to kcmgcs                                                          13
  789 MARTIN       calls to get snapshot scn: kcmgss                                         1
  789 MARTIN       file io wait time                                                    53.16k
  789 MARTIN       cell physical IO bytes eligible for predicate offload                21.19G
  789 MARTIN       cell physical IO interconnect bytes returned by smart scan          417.12M
  789 MARTIN       cell num smartio automem buffer allocation attempts                       1
  789 MARTIN       table scans (long tables)                                                 1
  789 MARTIN       table scans (direct read)                                                 1
  789 MARTIN       table scan rows gotten                                               15.54M
  789 MARTIN       table scan blocks gotten                                              2.59M
  789 MARTIN       cell scans                                                                1
  789 MARTIN       cell blocks processed by cache layer                                  2.59M
  789 MARTIN       cell blocks processed by txn layer                                    2.59M
  789 MARTIN       cell blocks processed by data layer                                   2.59M
  789 MARTIN       cell blocks helped by minscn optimization                             2.59M
  789 MARTIN       cell IO uncompressed bytes                                           21.24G
  789 MARTIN       session cursor cache hits                                                 1
  789 MARTIN       session cursor cache count                                                1
  789 MARTIN       workarea memory allocated                                             3.09k
  789 MARTIN       parse count (total)                                                       1
  789 MARTIN       execute count                                                             1
  789 MARTIN       bytes sent via SQL*Net to client                                          1
  789 MARTIN       bytes received via SQL*Net from client                                  298
  789 MARTIN       cell flash cache read hits                                           18.41k

The simulated ASH information does fit within the limits and is shown here:

SYS@DBM011:1> @snapper4 ash 5 1 789
Sampling SID 789 with interval 5 seconds, taking 1 snapshots...

-- Session Snapper v4.12 BETA - by Tanel Poder ( http://blog.tanelpoder.com )
-- Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)

----------------------------------------------------------------------------------------------
Active% | INST | SQL_ID          | SQL_CHILD | EVENT                              | WAIT_CLASS
----------------------------------------------------------------------------------------------
    50% |    1 | cdjur50gj9h6s   | 0         | ON CPU                             | ON CPU
    28% |    1 | cdjur50gj9h6s   | 0         | cell smart table scan              | User I/O

--  End of ASH snap 1, end=2014-08-06 09:04:30, seconds=5, samples_taken=36


PL/SQL procedure successfully completed.

As you can see from the output, the statement captured during the five-second interval clearly performed a Smart Scan, as indicated in the simulated ASH section and confirmed in the section above with cell scans indicated as 1 (there was a single segment scan). No need to worry about ASH in this context. Quoting from the Snapper documentation, “The ‘ASH’ functionality in Snapper just samples GV$SESSION view, so you do NOT need Diagnostics Pack licenses to use Snapper’s ‘ASH’ output.“

This output also shows that session 789 performed 2.59M logical reads. It issued a total of 20.24k read IO requests to read 21.18G of data, almost entirely satisfied by multi-block I/O requests. In this example, the human-readable delta values have been chosen, but Snapper can, of course, print the exact values as well.

The Meaning and Explanation of Exadata Performance Counters

After the introduction about their usefulness, it is time to explore the meaning of performance counters. No matter how pretty the charts or pictures a performance tool draws using these metrics, if you are not aware of their meaning, they will be of limited use for troubleshooting. When writing this chapter, we faced a dilemma: There are so many interesting performance counters out there that are each worth a dedicated section. But, if we did that, this chapter would be in excess of 100 pages. To keep the chapter within reasonable limits, mostly Exadata-specific statistics are covered, and only the most relevant ones. Keep an eye out on the authors’ blogs for information on the events that did not make the chapter.

Here is a script that lists all statistics related to storage cells from V$STATNAME, with the statistic class, which indicates the purposes for which Oracle kernel engineers have expected to use these counters

SQL> SELECT
  2      name
  3    , TRIM(
  4        CASE WHEN BITAND(class,  1) =   1 THEN 'USER  ' END ||
  5        CASE WHEN BITAND(class,  2) =   2 THEN 'REDO  ' END ||
  6        CASE WHEN BITAND(class,  4) =   4 THEN 'ENQ   ' END ||
  7        CASE WHEN BITAND(class,  8) =   8 THEN 'CACHE ' END ||
  8        CASE WHEN BITAND(class, 16) =  16 THEN 'OSDEP ' END ||
  9        CASE WHEN BITAND(class, 32) =  32 THEN 'PARX  ' END ||
 10        CASE WHEN BITAND(class, 64) =  64 THEN 'SQLT  ' END ||
 11        CASE WHEN BITAND(class,128) = 128 THEN 'DEBUG ' END
 12       ) class_name
 13  FROM
 14      v$statname
 15  WHERE
 16      name LIKE '%cell%'
 17  ORDER BY
 18      name
 19 /

On an Oracle 12.1.0.2 system, the above query produced the following output:

NAME                                                              CLASS_NAME
----------------------------------------------------------------- ---------------
cell CUs processed for compressed                                 SQLT
cell CUs processed for uncompressed                               SQLT
cell CUs sent compressed                                          SQLT
cell CUs sent head piece                                          SQLT
cell CUs sent uncompressed                                        SQLT
cell IO uncompressed bytes                                        SQLT
cell XT granule bytes requested for predicate offload             DEBUG
cell XT granule predicate offload retries                         DEBUG
cell XT granules requested for predicate offload                  DEBUG
cell blocks helped by commit cache                                SQLT
cell blocks helped by minscn optimization                         SQLT
cell blocks processed by cache layer                              DEBUG
cell blocks processed by data layer                               DEBUG
cell blocks processed by index layer                              DEBUG
cell blocks processed by txn layer                                DEBUG
cell commit cache queries                                         SQLT
cell flash cache read hits                                        CACHE
cell index scans                                                  SQLT
cell interconnect bytes returned by XT smart scan                 DEBUG
cell logical write IO requests                                    USER
cell logical write IO requests eligible for offload               USER
cell num block IOs due to a file instant restore in progress      SQLT
cell num bytes in block IO during predicate offload               SQLT
cell num bytes in passthru during predicate offload               SQLT
cell num bytes of IO reissued due to relocation                   SQLT
cell num fast response sessions                                   SQLT
cell num fast response sessions continuing to smart scan          SQLT
cell num smart IO sessions in rdbms block IO due to big payload   SQLT
cell num smart IO sessions in rdbms block IO due to no cell mem   SQLT
cell num smart IO sessions in rdbms block IO due to open fail     SQLT
cell num smart IO sessions in rdbms block IO due to user          SQLT
cell num smart IO sessions using passthru mode due to cellsrv     SQLT
cell num smart IO sessions using passthru mode due to timezone    SQLT
cell num smart IO sessions using passthru mode due to user        SQLT
cell num smart file creation sessions using rdbms block IO mode   SQLT
cell num smartio automem buffer allocation attempts               SQLT
cell num smartio automem buffer allocation failures               SQLT
cell num smartio permanent cell failures                          SQLT
cell num smartio transient cell failures                          SQLT
cell overwrites in flash cache                                    CACHE
cell partial writes in flash cache                                CACHE
cell physical IO bytes eligible for predicate offload             SQLT
cell physical IO bytes saved by columnar cache                    CACHE
cell physical IO bytes saved by storage index                     CACHE
cell physical IO bytes saved during optimized RMAN file restore   SQLT
cell physical IO bytes saved during optimized file creation       SQLT
cell physical IO bytes sent directly to DB node to balance CPU    SQLT
cell physical IO interconnect bytes                               SQLT
cell physical IO interconnect bytes returned by smart scan        SQLT
cell physical write IO bytes eligible for offload                 USER
cell physical write IO host network bytes written during offloa   USER
cell physical write bytes saved by smart file initialization      CACHE
cell scans                                                        SQLT
cell simulated physical IO bytes eligible for predicate offload   SQLT  DEBUG
cell simulated physical IO bytes returned by predicate offload    SQLT  DEBUG
cell smart IO session cache hard misses                           SQLT
cell smart IO session cache hits                                  SQLT
cell smart IO session cache hwm                                   SQLT
cell smart IO session cache lookups                               SQLT
cell smart IO session cache soft misses                           SQLT
cell statistics spare1                                            SQLT
cell statistics spare2                                            SQLT
cell statistics spare3                                            SQLT
cell statistics spare4                                            SQLT
cell statistics spare5                                            SQLT
cell statistics spare6                                            SQLT
cell transactions found in commit cache                           SQLT
cell writes to flash cache                                        CACHE
chained rows processed by cell                                    SQLT
chained rows rejected by cell                                     SQLT
chained rows skipped by cell                                      SQLT
error count cleared by cell                                       SQLT
sage send block by cell                                           SQLT

73 rows selected.

If you own the first edition of this book, you will notice that there are many more cell-related counters in 12.1.0.2 than there were in 11.2.0.2, which was the standard production release at the time of the first edition’s writing. Using a similar query to the one shown above, it is possible to focus on the statistics related to the HCC feature, covered in Chapter 3:

NAME                                                              CLASS_NAME
----------------------------------------------------------------- ---------------
EHCC Analyze CUs Decompressed                                     DEBUG
EHCC Analyzer Calls                                               DEBUG
EHCC Archive CUs Compressed                                       DEBUG
EHCC Archive CUs Decompressed                                     DEBUG
EHCC Attempted Block Compressions                                 DEBUG
EHCC Block Compressions                                           DEBUG
EHCC CU Row Pieces Compressed                                     DEBUG
EHCC CUs Compressed                                               DEBUG
EHCC CUs Decompressed                                             DEBUG
EHCC CUs all rows pass minmax                                     DEBUG
EHCC CUs no rows pass minmax                                      DEBUG
EHCC CUs some rows pass minmax                                    DEBUG
EHCC Check CUs Decompressed                                       DEBUG
EHCC Columns Decompressed                                         DEBUG
EHCC Compressed Length Compressed                                 DEBUG
EHCC Compressed Length Decompressed                               DEBUG
EHCC Conventional DMLs                                            DEBUG
EHCC DML CUs Decompressed                                         DEBUG
EHCC Decompressed Length Compressed                               DEBUG
EHCC Decompressed Length Decompressed                             DEBUG
EHCC Dump CUs Decompressed                                        DEBUG
EHCC Normal Scan CUs Decompressed                                 DEBUG
EHCC Pieces Buffered for Decompression                            DEBUG
EHCC Preds all rows pass minmax                                   DEBUG
EHCC Preds no rows pass minmax                                    DEBUG
EHCC Preds some rows pass minmax                                  DEBUG
EHCC Query High CUs Compressed                                    DEBUG
EHCC Query High CUs Decompressed                                  DEBUG
EHCC Query Low CUs Compressed                                     DEBUG
EHCC Query Low CUs Decompressed                                   DEBUG
EHCC Rowid CUs Decompressed                                       DEBUG
EHCC Rows Compressed                                              DEBUG
EHCC Rows Not Compressed                                          DEBUG
EHCC Total Columns for Decompression                              DEBUG
EHCC Total Pieces for Decompression                               DEBUG
EHCC Total Rows for Decompression                                 DEBUG
EHCC Turbo Scan CUs Decompressed                                  DEBUG
EHCC Used on Pillar Tablespace                                    DEBUG
EHCC Used on ZFS Tablespace                                       DEBUG

39 rows selected.

All the statistics starting with cell% are, as the name suggests, related to storage cells. Some of these stats are measured and maintained by cells themselves and then sent back to the database sessions during any interaction over iDB protocol. Some are maintained in the Exadata-specific portion of the Oracle database kernel. Those statistics with “XT” in their name are related to a different product and out of this chapter’s scope. Every database session receives the cell statistics along with the replies from their corresponding cell sessions and then updates the relevant database V$-views with it. This is how the Oracle database layer has insight into what is going on in the “black box” of a cell, like the real number of I/O operations done and the number of cell Flash Cache hits. Note that there are a few chained rows [...] cell statistics, which apparently use a different naming convention, having the “cell” in the end of the statistic name.

Statistics starting with EHCC are related to Hybrid Columnar Compression. You always see the values increasing during Smart Scans on HCC segments. During a Smart Scan, the cell CU%-counters are incremented on the cells while the worker threads tear through the compressed data on disk. Whenever the Smart Scan produces a match with the predicates in the query, the cells will decompress the column in the CU (not the whole Compression Unit!) and pass it to the RDBMS layer. It is during RDBMS layer processing that the EHCC counters are increased, even if the data is already uncompressed. Unfortunately, it is impossible to discuss each of the EHCC% counters in this chapter, but a quick categorization of the counters does fit in here.

Performance Counter Reference for a Selected Subset

This section explains some of the more important and interesting statistics listed in the output earlier. Since the chapter had too much content in the first edition of the book already, a careful selection was necessary to cut down on the number of counters described to make some room for the new features worth covering. We hope to have made a reasonable selection.

In many cases of performance troubleshooting, you probably do not have to delve this deeply. The wait interface and Real Time SQL Monitoring features you will read about in Chapter 12 should provide enough information. Nevertheless, understanding what happens behind the scenes and why these statistics are incremented will give you further insight into Exadata internals and enable you to troubleshoot unusual performance problems more effectively. The noteworthy statistics are covered in alphabetical order.

cell CUs sent compressed

This first statistic counter to be covered here is incremented on the cell. If you see the numbers go up here, you are witnessing memory stress on the cells. It is not the worst form of memory pressure instrumented (see next section), but, due to a memory shortage, some work the cells would do when scanning HCC compressed data cannot be performed on a storage cell. Filtering is still being done and predicates are evaluated. It’s just that the column information is not decompressed on the cell. Here again, the rule laid out in Chapter 3 has a huge effect: When using HCC, you should really only ever reference the columns you intend to use, as opposed to the ubiquitous select * from table... The more columns are sent uncompressed to the RDBMS layer, the more work your session has to perform.

cell CUs sent head piece

If the cell is under more memory stress and memory allocations fail, it might not be possible for the cell to decompress anything and the whole CU must be sent to the RDBMS layer for processing. This is probably the worst-case scenario during Smart Scans of compressed data.

If you want to see the memory statistics on a cell, you can use the cellsrvstat utility (discussed later in this chapter) to check for a statistic group named mem. This chapter explains cellsrvstat toward the end; the memory-related statistics can be queried using cellsrvstat -stat_group=mem.

cell CUs sent uncompressed

After the previous counters that meant trouble, this one is what you want to see instead during Smart Scans. This is the equivalent to the ORA-00000: normal, successful completion. If you see this counter increase, the CU has been processed and was decompressed on the cell. Again, you will only see this statistic counter increase during a Smart Scan when the cells decompressed CUs. Remember that CUs span multiple Oracle blocks. When checking related statistics, you might want to check the size of the CU first.

cell blocks helped by commit cache

During a Smart Scan in the storage cell, the normal data consistency rules still need to be applied, sometimes with help of undo data. An important concept to remember is that Smart Scans produce consistent reads. Therefore, consistent read guarantees must work also for Smart Scans. There is just one difficulty: The Smart Scan works entirely in the storage cells, where it does not have access to any undo data in the database instances’ buffer caches. Undo can become necessary to revert changes to a block and to get it back to a SCN from where it is safe to read, as you will see in a moment.

Remember that cells do not communicate with other cells by design during Smart Scans. An individual cell is unable to read the undo data from undo segments striped across other cells. Consistent read buffer cloning and rollbacks, whenever needed, would have to be done inside the database layer. Whenever the Smart Scan hits a row, which still has its lock byte set (the row/block has not been cleaned out for reasons explained a little later), it has to switch into block I/O mode for this particular block and send the entire data block back to the database layer for normal consistent read processing—with the help of undo data available there. The lock-byte is set on the row level in a block, as seen in this excerpt from a block dump:

tab 0, row 0, @0x1b75
tl: 1035 fb: --H-FL-- lb: 0x2  cc: 6
col  0: [ 2]  c1 0b
col  1: [999]
 31 20 20 20 20...

This is the way Oracle implements row-level locking. The lock byte (lb) points to an entry in the block’s Interested Transaction List (ITL). In this example, it is the second one:

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.01d2ab27
0x02   0x000a.001.0000920f  0x00009f97.26c9.45  --U-    6  fsc 0x0000.01d2b741
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

Without going into too much detail, the second ITL entry shows a commit (Flag = U), and the transaction indicated by the Xid-column affects six rows in total, equaling the total number of rows in the block (nrows=6 in the block dump, not shown here). It also points to the undo record address (Uba) which is required to revert that change out of the block, but not available to the cell.

Note that when a block is cleaned out correctly (in other words, the lock bytes are cleared) and its cleanout SCN in the block header is from an earlier SCN than the query start time SCN (snapshot SCN), the cell knows that a rollback of that block would not be needed. The cleanout SCN is named csc in the block dump and indicates the last time a block was subject to a proper cleanout:

Block header dump:  0x017c21c3
 Object id on Block? Y
seg/obj: 0x12075  csc: 0x00.1d2ab27  itc: 3  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x17c21c0 ver: 0x01 opc: 0
     inc: 0  exflg: 0

If the latest change to that block happened before the query started, the block image in the cell is valid, a good enough block to satisfy the query with the given SCN. How does the cell know the starting SCN of a query executed in the database layer? That is the task of the storage-aware row sources in the execution plans, which communicate the SCN to cells over iDB when setting up Smart Scan sessions for themselves.

Now, when some of the rows do have some lock bytes set in the block or when the cleanout SCN in a block header happens to be higher than the query’s snapshot SCN, the cells cannot determine the validity of the block/data version themselves and need to ship the block back to the database layer for regular, non-Smart Scan processing. This would considerably slow down the Smart Scan processing if such check had to be done for many locked rows and not cleaned-out blocks. However, there is an optimization that helps reducing the number of times the cell has to fall back to block I/O processing in the database layer.

Whenever a Smart Scan finds a locked row during a segment scan, it will check which transaction locked that row. This can easily be achieved by reading the transaction’s ITL entry in the current data block header pointed to by the lock byte. Note that bitmap index segment blocks and HCC compressed blocks do not have a lock byte for each single row in the block, but the idea remains the same: Oracle is able to find out the transaction ID of the locked row(s) from the block at hand itself.

If the locked transaction has not committed yet, the Smart Scan falls back to block I/O mode for that block and the database layer will have to go through the normal consistent read buffer cloning/rollback mechanism, and there is no workaround for that. As you can see in the example that follows, having to fall back to block mode can have a noteworthy performance impact on the first execution of the query against the table with uncommitted transactions. Subsequent queries will not have to use physical I/O anymore to read the blocks from disk but can rely on the blocks available in the buffer cache. Nevertheless, the consistent read processing requires blocks to be rolled back.

If the transaction has already committed, but has left the lock bytes not cleaned out in some blocks (this usually happens after large updates and is called delayed block cleanout), the Smart Scan does not have to fall back to block I/O and an in-database, consistent read mechanism. It knows that this row is not really locked anymore, as the locking transaction has committed already, even though the lock byte is still in place.

How does the cell know if a given transaction has committed on RDBMS layer? This is achieved by caching the number of recently committed transactions in what is referred to as the commit cache. Without the commit cache, performance could suffer if you had a Smart Scan going over lots of rows with their lock byte still set. You definitely do not want the Smart Scan to ship blocks to the database layer every time it hits another locked row for consistent read processing. The commit cache might probably be just an in-memory hash-table, organized by transaction ID, and it keeps track of which transactions are committed and which are not. When the cell Smart Scan encounters a locked row, it will extract the transaction ID (from the ITL section of the data block), and it checks whether there is any information about that transaction in the commit cache. This check will increment the statistic cell commit cache queries by one. If there is no such transaction in the commit cache, then the Smart Scan is out of luck and has to perform consistent read processing by reverting to comparatively slow single block I/O processing. A cache hit, on the other hand, will increment the statistic cell blocks helped by commit cache by one.

To demonstrate the effect of the commit cache on queries, we have conducted a little test. To ensure that the test results were reproducible, we had to exert a little bit of force—the situation is exaggerated and you are unlikely to see similar effects in real production environments. (Who updates a very large table without a where clause?) First, a reasonably large table has been created with a size that would guarantee a Smart Scan when querying it. Next, an update in session 1 modified all blocks of this table to ensure delayed block cleanout, followed by a command to flush the buffer cache to disk. The blocks being forced to disk are not completely “cleared” for reasons explained in the next few paragraphs. Suffice to say at this point that the blocks are “dirty” and will require processing. Additionally, the active transaction that modified the blocks has not committed yet.

If anyone would query that table in its current state (for the first time), query elapsed time will be quite high. In the example, it took about 25 seconds:

SQL> select count(*) from t;

  COUNT(*)
----------
   1000000

Elapsed: 00:00:25.54

The reason is quickly identified in single-block reads. The table has been created with the storage clause set to explicitly not cache blocks in the Smart Flash Cache to ensure consistent read times. In current production systems, it is highly likely that the Flash Cache satisfies single-block reads though. Here are some important statistics for that query:

Statistic Name                                                               Value
----------------------------------------------------------------  ----------------
CPU used by this session                                                        22
active txn count during cleanout                                           166,672
cell blocks helped by minscn optimization                                        4
cell blocks processed by cache layer                                       167,961
cell blocks processed by data layer                                              4
cell blocks processed by txn layer                                               4
cell commit cache queries                                                  167,957
cell physical IO bytes eligible for predicate offload                1,365,409,792
cell physical IO interconnect bytes                                  1,528,596,408
cell physical IO interconnect bytes returned by smart scan           1,365,854,136
cell scans                                                                       1
cleanouts and rollbacks - consistent read gets                             166,672
consistent gets                                                          2,044,459
consistent gets direct                                                     166,676
data blocks consistent reads - undo records applied                      1,710,729
physical read total IO requests                                             22,459
physical read total multi block requests                                     1,838

These show that there was a lot of read-consistency processing involved. The number of multi-block reads was also quite low. Out of 22,459 I/O requests only 1,838 were multi-block reads—the rest, therefore, single-block I/O. You can also see that the cache layer opened 167,961 blocks in preparation for Smart Scan processing, but had to abandon that in all but four cases. (These four blocks were “helped” by minscn optimization, which you can read more about in the next section.) Also note that the number of bytes returned by Smart Scan is almost identical to the number of bytes eligible for predicate offload. In other words, there is no saving at all in doing I/O despite using a Smart Scan to access the data (the statistic “cell scans“ is 1).

Subsequent executions of the same statement will no longer have to perform single-block I/O to read the “dirty” blocks from disk, but can make use of the blocks already in the buffer cache. Execution time for the second query against the table was down to 2.96 seconds. The same consistent read processing was required and still only four blocks were processed via Smart Scan. However, since the blocks have been read into the buffer cache, at least the physical I/O could be skipped. The recorded “CPU used for this session” was down from 499 to 277, and there were just a few single-block I/O requests visible. The number of block “cleanouts and rollbacks - consistent read gets” has not changed substantially, which was to be expected since the transaction has not committed.

When the user in session 1 commits, things improve for the queries against that table executed on Exadata. Non-Exadata platforms will not see a benefit here. The first execution of the query against the table then exhibits the following characteristics:

SQL> select count(*) from t;

  COUNT(*)
----------
   1000000

Elapsed: 00:00:01.52

The most relevant stats are shown here:

Statistic Name                                                               Value
----------------------------------------------------------------  ----------------
CPU used by this session                                                        22
cell blocks helped by commit cache                                         166,672
cell blocks helped by minscn optimization                                        4
cell blocks processed by cache layer                                       166,676
cell blocks processed by data layer                                        166,676
cell blocks processed by txn layer                                         166,676
cell commit cache queries                                                  166,672
cell physical IO bytes eligible for predicate offload                1,365,409,792
cell physical IO interconnect bytes                                     26,907,936
cell physical IO interconnect bytes returned by smart scan              26,907,936
cell scans                                                                       1
cell transactions found in commit cache                                    166,672
consistent gets                                                            167,058
consistent gets direct                                                     166,676
physical read total IO requests                                              1,308
physical read total multi block requests                                     1,308

In comparison to the previous two executions of the query, you can see that Smart Scans occur for all blocks—thanks to the commit cache (166,672) and the four blocks benefiting from the minscn optimization. CPU usage is down to 22 from 499 previously, and all consistent gets are in direct mode. All I/O requests are multi-block reads, and there is a substantial saving in using the Smart Scan: Out of the approximately 1.3 GB eligible for predicate offload, only 26.9 MB are returned to the RDBMS layer.

In summary, an increase in the value of cell blocks helped by commit cache statistic when your sessions are performing a Smart Scan indicates that the cells have some overhead due to checking whether locked rows are really still locked in the commit cache. But, at the same time, you will not see a performance degradation. Without this optimization, the whole Smart Scan would slow down, as it must interact with the database layer. You would also see more logical I/O being done at the database layer (see the statistics starting with consistent gets from cache), as opposed to only a single LIO per segment for reading the extent locations and number of blocks under the high water mark out from the segment header.

If you are not familiar with the delayed block cleanout mechanism in Oracle, you might have been wondering how Oracle blocks can have rows with their lock bytes still set after the transaction has already committed. This is how Oracle is different from most other mainstream commercial RDBMS products. Oracle does not have to keep the blocks with uncommitted rows cached in memory; database writer (DBWR) is free to write them out to disk and release the memory for other data blocks. Now, when committing the transaction, it would be inefficient to read all the transactions back from the disk just to clean up the lock bytes. If there were many such blocks, your commit time would increase to unacceptable levels. Instead, Oracle just marks the transaction complete in its undo segment header slot. Any future block readers can just check whether the transaction in that undo segment header slot is still alive or not. If you perform block I/O to read the data blocks to the database layer later on, the reading session would clean up the block (clear the lock bytes of rows modified by committed transactions), so no further transaction status checks would be needed in future reads. This is why during some queries you can see redo being generated.

However, storage cells do not perform block cleanouts—cells do not modify data blocks on their own because database block modifications require writing of redo operations. But how would a cell write to a redo log file that is managed and striped over many cells at the database layer? There is an interesting side effect to block cleanouts and direct path reads on non-Exadata platforms as well, since direct path reads do not make use of the buffer cache.

Note that for small transactions modifying only a few blocks, with most of the modified blocks still in the buffer cache, Oracle can perform block cleanout right during the commit time. Also, the issues just discussed do not apply to databases (data warehouses usually) where tables are loaded using direct path load inserts (and index partitions are built after the table partitions are loaded) because, in the case of direct path loads, the table rows are not locked in the newly formatted table blocks. The same applies to index entries in leaf blocks if an index is created after the data load.

cell blocks helped by minscn optimization

Exadata cell server has another optimization designed to improve consistent read efficiency even more. It is called the Minimum Active SCN optimization, and it keeps track of the lowest SCN of any still active (uncommitted) transaction in the database. This allows Oracle to easily compare the SCN in the ITL entries of the locking transactions with the lowest SCN of the “oldest” active transaction in the database.

As the Oracle database is able to send this MinSCN information to the cell when starting a Smart Scan session, the cells can avoid exchanging data with the database layer whenever the known minimum active SCN passed to the cell is higher than the SCN in the transaction’s ITL entry in the block. Whenever the Smart Scan processes a block and finds a locked row with an active transaction in the ITL slot, it can conclude that the transaction must have committed. Thanks to the MinSCN passed by the database session, the cell blocks helped by minscn optimization statistic is incremented (once for each block).

Without this optimization, Oracle would have to check the commit cache (described in the cell blocks helped by commit cache statistic section). If it finds no information about this transaction in the commit cache, it would interact with the database layer to find out whether the locking transaction has already committed or not. This optimization is RAC-aware; in fact, the Minimum SCN is called Global Minimum SCN, and the MMON processes in each instance will keep track of the MinSCN and keep it synced in an in-memory structure in each node’s SGA. You can query the current known global Minimum Active SCN from the x$ktumascn fixed table as shown here (as SYS):

SQL> COL min_act_scn FOR 99999999999999999
SQL>
SQL> SELECT min_act_scn FROM x$ktumascn;

       MIN_ACT_SCN
------------------
     9920890881859

This cell blocks helped by minscn optimization statistic is also something you should not be worried about, but it can come in handy when troubleshooting advanced Smart Scan issues, or even bugs, where Smart Scans seem to get interrupted because they have to fall back to block I/O and talk to the database too much.

cell blocks processed by cache layer

The cell blocks processed by ... layer statistics are good indicators of the depth of offload process in the cells. The main point and advantage of the Exadata storage servers is that part of the Oracle kernel code has been ported into the cellsrv executable running in the storage cells. In other words, processing and intelligence is brought to storage. This is what allows the Oracle database layer to offload the data scanning, filtering, and projection work into the cells. In order to do that, the cells must be able to read and understand Oracle data block and row contents, just as the database does. The cell blocks processed by cache layer statistic indicates how many data blocks the cells have processed (opened, read, and used for Smart Scan) as opposed to just passing the blocks read up to the database layer.

When a cell just passes the blocks back to the database in block I/O mode, this statistic is not updated. But when the cell itself uses these blocks for Smart Scan, one of the first things that is done when opening a block for a consistent read is to check the block cache layer header. This is to make sure it is the correct block, is not corrupt, and is valid and coherent. These tests are done by cache layer functions (KCB for Kernel Cache Buffer management) and reported back to the database as cell blocks processed by cache layer.

In the database layer, with regular block I/O, the corresponding statistics are consistent gets from cache and consistent gets from cache (fastpath) depending on which buffer pinning code path is used for the consistent buffer get. Note that cellsrv does only consistent mode buffer gets (CR reads) and no current mode block gets. So all the current mode gets you see in stats are done in the database layer and are reported as db block gets from cache or db block gets from cache (fastpath). This statistic is a useful and simple measure of how many logical reads the cellsrv does for your sessions.

Note that it is entirely OK to see some database layer I/O processing during a SQL plan execution, as the plan is probably accessing multiple tables (and joining them). So, when doing a ten-table join between a large fact and nine dimension tables, you may well see that all of the dimensions are scanned using regular, cached block I/O (and using an index, if present), and only the large fact table access path will take advantage of the Smart Scan.

cell blocks processed by data layer

While the previous statistic counts all the block gets performed by the cache layer (KCB), this statistic is similar, but counts the blocks processed in the cell by the data layer. This statistic applies specifically to reading table blocks or materialized view blocks (which are physically just like table blocks). Information is collected using a data layer module, called KDS for Kernel Data Scan, which can extract rows and columns out of table blocks and pass them on to various evaluation functions for filtering and predicate checks. As with the database layer, the data layer in the cells is able to read a block and extract the relevant parts. It is also responsible for writing the result of the operation into an Exadata-specific format to be sent up to the database layer for processing using column simple projection and filtering techniques.

If the cell Smart Scan can do all of its processing in the cell without having to fall back to database block I/O mode, this processed by data layer statistic plus the processed by index layer statistic should add up to the processed by cache layer value for most Smart Scan processing. This means that every block actually opened made its way through the cache and transaction layer checks and was passed to the data or index layer for row and column extraction. If the processed by data layer plus processed by index layer statistics add up to a smaller value than the processed by cache layer statistic, it means that the rest of the blocks were not fully processed by the cell and had to be sent back to the database for regular block I/O processing.

cell blocks processed by index layer

This statistic is just like the preceding cell blocks processed by data layer, but it is incremented when Smart Scanning through B*Tree or bitmap index segment blocks. The code path for extracting rows out of index blocks is different from the code path executed for extracting rows from tables. The cell blocks processed by index layer counts how many index segment blocks were processed by a Smart Scan.

cell blocks processed by txn layer

This statistic shows how many blocks were processed in the cell by the transaction layer. Here is a simplified explanation of the sequence of actions during a consistent read for Smart Scan in a storage cell:

1. The cache layer (KCB) opens the data block and checks its header, last modification SCN, and cleanout status.
2. If the block in the cell has not been modified after the snapshot SCN of the query running the current Smart Scan, this block can be passed to the transaction layer for processing. However, if the block image on disk (cell) has been modified after the query’s snapshot SCN, the cache layer already knows that this block has to be rolled back for consistent read. In this case, the block is not passed into the cell transaction layer at all, but the cell falls back to block I/O and passes that block to the database layer for normal consistent-read processing.
3. If the block is passed to the transaction layer (KTR) by the cache layer, the transaction layer can use the commit cache and MinActiveSCN optimization to avoid performing consistent-read processing to reduce the amount of communication with the database layer if it hits locked rows and not cleaned-out blocks of committed transactions. When there is no need to perform consistent-read processing in the database layer, the consistent reads will be performed by the data layer or index layer code inside the storage cell. However, if the consistent read cannot be completed within the cell, the entire data block at hand must be transported back to the database layer and the consistent read will be performed there.

The point of this explanation is that if the Smart Scans work optimally, they do not have to interrupt their work and exchange data with the database layer during the Smart Scan processing. Ideally, all the scanning work is done in the storage cell and, once enough rows are ready to be returned, they are sent to the database in a batch. If this is the case, then the cell blocks processed by data layer (or index layer) statistic will equal to the cell blocks processed by cache layer (and txn layer), showing that all the blocks could be fully processed in the cell and rows extracted from them without having to fall back to database-layer block I/O and consistent reads.

Remember that all this complexity related to consistent reads in storage cells matters only when doing a Smart Scan. When doing regular block I/O, cells just pass the blocks read directly back to the database layer, and the consistent read logic is executed in the database layer as usual. You should not really worry about these metrics unless you see that your Smart Scan wait events tend to be interspersed with cell single block physical reads, consuming a significant part of your query response time.

cell commit cache queries

This is the number of times the cell Smart Scan looked up a transaction status from the cell commit cache hash table. A lookup from commit cache is normally done once per uncommitted transaction found per block scanned by Smart Scan— where the MinActiveSCN optimization has not yet kicked in and eliminated the need to check for individual transaction statuses. This is closely related to the previously discussed cell blocks helped by commit cache statistic.

cell flash cache read hits

This statistic shows how many I/O requests were satisfied from the Cell Flash Cache so that a hard disk read was not necessary. Emphasis here is on “hard disk read,” not just physical read. Reads from the PCIe flash cards also require physical reads (system calls resulting in flash card I/O), just like any read to a block device does in Linux. When you see this number, it means that the required blocks were not in the database layer buffer cache (or the access path chose to use a direct path read), but luckily some or even all the blocks required by an I/O request were in Cell Flash Cache. (The official term is Exadata Smart Flash Cache.)

Note that this number shows the number of I/O requests, not the number of blocks read from Cell Flash Cache. Remember that Cell Flash Cache is usable both by regular block reads and cell Smart Scans. For best performance, especially if you run an OLTP database on Exadata, you should attempt to satisfy most single-block reads from either the database buffer cache or, failing that, the Cell Flash Cache. You can read more about the Exadata Flash Cache in Chapter 5. Oracle decided that starting from cell version 11.2.3.3.x and later, the cells will Smart Scan from Flash Cache and disk concurrently by default and, if possible, without any change to the configuration.

cell index scans

This statistic is incremented every time a Smart Scan is started on a B*Tree or bitmap index segment. Note that in order to use Smart Scans on index segments, the index fast full scan execution plan row source operator must be used together with direct path reads. This statistic is updated at the start of a Smart Scan session. As a result, if you monitor its value for a session that has been executing a long-running query for a while, you might not see this statistic incrementing for your session.

When running just a serial session with Smart Scan on a nonpartitioned index segment, this statistic would be incremented by one. However, when running a Smart Scan on a partitioned index segment, the cell index scans statistic would be incremented for each partition scanned using Smart Scan. The decision whether a Smart Scan is attempted is evaluated for each segment of a partitioned or subpartitioned segment. The decision is made at runtime, for each segment (table, index or partition). Since Smart Scans require direct path reads to the PGA, and the direct path reads decision in turn is made based on the scanned segment size and other factors, different partitions of the same table accessed may be scanned using different methods. You might find that some partitions in your multipartition table or index are not scanned with Smart Scan/direct path reads, as Oracle has decided to use buffered reads for them thanks to their smaller size. In this case, the cell index scans statistic would not increment as much, and you would see the cell multiblock physical read wait event pop up at the table/index scan row source path in an ASH or SQL Monitoring report.

cell IO uncompressed bytes

This statistic shows the uncompressed size of the data scanned in the cell and is useful when scanning HCC compressed data. The statistic is best understood with an example. Consider the following table:

SQL> select segment_name, segment_type, bytes/power(1024,2) m, s.blocks,
  2  t.compression, t.compress_for
  3  from user_segments s, user_tables t
  4  where s.segment_name = t.table_name
  5  and segment_name = 'BIGTAB_QH';

SEGMENT_NAME                   SEGMENT_TYPE                M     BLOCKS COMPRESS COMPRESS_FOR
------------------------------ ------------------ ---------- ---------- -------- ------------
BIGTAB_QH                      TABLE                10209.75    1306848 ENABLED  QUERY HIGH

As you can see a table, named BIGTAB_QH is HCC compressed using the Query High algorithm. It takes about 10GB of disc space (compressed). For reference, the uncompressed table is about 20G in size. Considering a Smart Scan against this table reveals the following statistic counters relevant to this discussion. The statistics were captured with Snapper in a five-second interval. Reduced to the minimum information required:

  sid username     statistic                                                             delta
  297 MARTIN       physical read total IO requests                                       9.68k
  297 MARTIN       physical read total multi block requests                              9.68k
  297 MARTIN       physical read requests optimized                                      9.47k
  297 MARTIN       physical read total bytes optimized                                    9.9G
  297 MARTIN       physical read total bytes                                            10.13G
  297 MARTIN       physical reads                                                        1.24M
  297 MARTIN       physical reads direct                                                 1.24M
  297 MARTIN       physical read IO requests                                             9.68k
  297 MARTIN       physical read bytes                                                  10.13G
  297 MARTIN       cell scans                                                                1
  297 MARTIN       cell blocks processed by cache layer                                  1.24M
  297 MARTIN       cell blocks processed by txn layer                                    1.24M
  297 MARTIN       cell blocks processed by data layer                                   1.24M
  297 MARTIN       cell blocks helped by minscn optimization                             1.24M
  297 MARTIN       cell CUs sent uncompressed                                            4.46k
  297 MARTIN       cell CUs processed for uncompressed                                   4.46k
  297 MARTIN       cell IO uncompressed bytes                                           26.57G

Hence, if you scan through a 10GB compressed segment, the physical read total bytes statistic increases by approximately 10GB, but the cell I/O uncompressed bytes increases by 26.57 GB, reflecting the total uncompressed size of the data scanned. This statistic is incremented only when performing a Smart Scan compression offloading, not when you read the compressed blocks directly to the database layer with block I/O. Interestingly, the statistic is populated when Smart Scanning uncompressed segments, too.

cell num fast response sessions

This statistic shows how many times Oracle started the Smart Scan code but then chose not to set up the Smart Scan session immediately. Instead, chose to do a few block I/O operations first, hoping to find enough rows to satisfy the database session. This optimization is used for FIRST ROWS execution plan options, either when using a FIRST_ROWS_n hint (or equivalent init.ora parameter) or a WHERE rownum < X condition, which may also enable the first rows option in execution plans. The idea is that if fetching only a few rows, Oracle hopes to avoid the overhead of setting up a cell Smart Scan session (with all the cells, thanks to ASM striping), but it will do a few regular block I/O operations first. Following is an example of a first-rows optimization using the ROWNUM predicate:

select * from t3 where owner like 'S%' and rownum <= 10

Plan hash value: 3128673074

----------------------------------------------------------------------------
| Id  | Operation                              | Name | E-Rows | Cost (%CPU)|
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |      |        |     4 (100)|
|*  1 |  COUNT STOPKEY                         |      |        |            |
|*  2 |   TABLE ACCESS STORAGE FULL FIRST ROWS | T3   |     11 |     4   (0)|
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=10)
   2 - storage("OWNER" LIKE 'S%')
       filter("OWNER" LIKE 'S%')

If you ran Snapper at the same time as this query, you are likely to see the cell num fast response sessions incremented, as Oracle has tried to avoid the Smart Scan session setup:

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
cell num fast response sessions                                           1
cell num fast response sessions continuing to smart scan                  0

The cell fast response feature is controlled by the _kcfis_fast_response_enabled parameter and enabled by default.

cell num fast response sessions continuing to smart scan

This statistic shows how many times the cell Smart Scan fast response session was started, but Oracle had to switch to the real Smart Scan session because it did not find enough matching rows with the first few I/O operations. The next example builds on the previous one, but adds an additional predicate to the query:

select * from t3 where owner like 'S%' and object_name LIKE '%non-existent%'
and rownum <= 10

Plan hash value: 3128673074

----------------------------------------------------------------------------
| Id  | Operation                             | Name | E-Rows | Cost (%CPU)|
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |      |        |     9 (100)|
|*  1 |  COUNT STOPKEY                        |      |        |            |
|*  2 |   TABLE ACCESS STORAGE FULL FIRST ROWS| T3   |     10 |     9   (0)|
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=10)
   2 - storage(("OBJECT_NAME" LIKE '%non-existent%' AND "OWNER" LIKE
              'S%' AND "OBJECT_NAME" IS NOT NULL))
       filter(("OBJECT_NAME" LIKE '%non-existent%' AND "OWNER" LIKE
              'S%' AND "OBJECT_NAME" IS NOT NULL))

Watching the statistics with Snapper shows that the number of fast response sessions continuing to Smart Scan increased:

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
cell num fast response sessions                                           1
cell num fast response sessions continuing to smart scan                  1

cell num smart IO sessions using passthru mode due to reason

There are three related statistics counters where reason can be either user, cellsrv, or timezone to indicate how many times the Oracle database initiated a Smart Scan but then failed to execute it. In 11.2.0.4 and 12.1.0.2, you get the actual amount of data sent via passthrough mode as well. It is recorded in cell num bytes in passthru during predicate offload. In such a case, cellsrv did not start a Smart Scan and fell back entirely to block I/O mode. The blocks read are just passed through to the database, instead of processing them within the cell. This means that while you still see the cell Smart Scan wait events and cell physical IO interconnect bytes returned by smart scan increasing (which indicates that a Smart Scan is happening), the full power of Smart Scan is not utilized, as the cells just read data blocks and return the blocks back to the database layer and into the session’s PGA. In other words, in passthrough mode, the cells do not open data blocks and extract only the required columns of matching rows, but return all the physical blocks of the segment as they are. Note that storage indexes can be used to eliminate I/O in passthrough mode, but remember that these indexes must first be populated by a regular Smart Scan. With the possibility that there is no Smart Scan in the first place, this will be difficult. If the segment being scanned is cached on Flash Cache, you will see it being used.

You should not see any passthrough Smart Scans happening on the latest database and Exadata cell versions unless you have problems like cells running out of memory. You can test what happens with passthrough mode in a test environment by setting _kcfis_cell_passthru_enabled to TRUE in your session and running a Smart Scan. You will still see cell smart scan wait events for your Smart Scans, but they are slower because they are returning all the blocks to the database for processing. The only time we saw this problem systematically was when 12c RDBMS was released and certified on Exadata, but on-cell software version did not support any offloading.

We also saw a cell num smart IO sessions using passthru mode due to timezone once where the timezone upgrade failed and was stuck. If cellsrv is almost out of memory, you will see counters increasing where the reason is cellsrv.

cell overwrites in flash cache

This particular session statistic has been introduced in Oracle 11.2.0.4. It is also visible in Oracle 12.1.0.2, but not in 12.1.0.1. Up until the introduction of Write-Back Flash Cache (WBFC) in 11.2.3.2.x, there was no need to worry about writes to Flash Cache. The Cell Smart Flash Cache was primarily used to speed up reads in OLTP-style workloads and, beginning with cell version 11.2.3.3.3.x, it was additionally and systematically used for Smart Scans as well. If you wanted to measure the benefit of Flash Cache on your workload, you could check the value for cell flash cache read hits, described earlier. Alternatively, you could consider physical read requests optimized as well as physical read total bytes optimized, but these two statistics would include information from storage indexes as well.

Writes are different. For quite some time after the introduction of WBFC, there was no statistic available to measure writes to Flash Cache. This changed with 11.2.0.4, and a few new statistics were introduced such as the following:

• Cell overwrites in Flash Cache
• Cell partial writes in Flash Cache
• Physical writes optimized

Considering a 12.1.0.2 system with WBFC enabled on all the cells, you can see that Oracle background processes are responsible for a lot of these writes. If you want to capture user sessions writing to WBFC, you need to do so before they disconnect. Here is an example on our 12.1.1.1.1 cell/12.1.0.2 RDBMS system:

SQL> select se.sid, sn.name, s.value, se.program
  2  from v$sesstat s natural join v$statname sn
  3  left join v$session se on (s.sid = se.sid)
  4  where sn.name in (
  5    'physical write requests optimized',
  6    'cell writes to flash cache',
  7    'cell overwrites in flash cache')
  8  and s.value <> 0
  9  order by s.sid,name;

       SID NAME                                     VALUE PROGRAM
---------- ----------------------------------- ---------- -----------------------------------
         1 cell overwrites in flash cache            6258 [email protected] (DBW1)
         1 cell writes to flash cache               10848 [email protected] (DBW1)
         1 physical write requests optimized         5405 [email protected] (DBW1)
        66 cell overwrites in flash cache            9894 [email protected] (DBW2)
        66 cell writes to flash cache               15218 [email protected] (DBW2)
        66 physical write requests optimized         7593 [email protected] (DBW2)
       132 cell overwrites in flash cache              94 [email protected] (LGWR)
       132 cell writes to flash cache                 218 [email protected] (LGWR)
       132 physical write requests optimized           38 [email protected] (LGWR)
       197 cell overwrites in flash cache           62991 [email protected] (CKPT)
       197 cell writes to flash cache               62991 [email protected] (CKPT)
       197 physical write requests optimized        20997 [email protected] (CKPT)
       262 cell writes to flash cache                2300 [email protected] (LG00)
       262 physical write requests optimized           76 [email protected] (LG00)
       392 cell writes to flash cache                   3 [email protected] (LG01)
       392 physical write requests optimized            1 [email protected] (LG01)
       782 cell overwrites in flash cache            3510 [email protected] (MMON)
       782 cell writes to flash cache                3552 [email protected] (MMON)
       782 physical write requests optimized         1184 [email protected] (MMON)
       977 cell overwrites in flash cache              63 [email protected] (LMON)
       977 cell writes to flash cache                  63 [email protected] (LMON)
       977 physical write requests optimized           21 [email protected] (LMON)
      1496 cell overwrites in flash cache           11502 [email protected] (DBW0)
      1496 cell writes to flash cache               17822 [email protected] (DBW0)
      1496 physical write requests optimized         8888 [email protected] (DBW0)
      1498 cell overwrites in flash cache              33 [email protected] (ARC0)
      1498 cell writes to flash cache                  33 [email protected] (ARC0)
      1498 physical write requests optimized           11 [email protected] (ARC0)
28 rows selected.

Note how the writes to Flash Cache are approximately twice the writes reported by RDBMS; this is caused by the ASM mirroring. The ASM disk groups on this system are created with normal redundancy.

cell physical IO bytes eligible for predicate offload

This performance counter holds one of the most important statistics for understanding Smart Scan. When you are Smart Scanning through a segment, this statistic shows how many bytes of that segment the Smart Scan would go through if returning every single bit of it. Essentially, this statistic covers all the bytes from the beginning of the segment all the way to its high water mark (as the scanning progresses through the entire segment). The catch is that this is the theoretical maximum number of bytes to scan through, but it does not account for storage indexes that potentially allow Smart Scan to skip data on disk.

Even if the storage index allows you to avoid scanning 80 percent of a 10GB segment, reducing the actual I/O amount to only 2GB, this statistic still shows the total size of the segment scanned, regardless of any optimizations. Experience from the field teaches that this is often the case. You need to keep an eye out on the cell physical IO bytes saved by storage index statistic, as shown here:

  sid username     statistic                                                             delta
  790 MARTIN       cell physical IO interconnect bytes                                   3.19M
  790 MARTIN       cell physical IO bytes eligible for predicate offload                21.85G
  790 MARTIN       cell physical IO bytes saved by storage index                          2.1M
  790 MARTIN       cell physical IO interconnect bytes returned by smart scan            3.19M
  790 MARTIN       cell num smartio automem buffer allocation attempts                       1
  790 MARTIN       cell scans                                                                1
  790 MARTIN       cell blocks processed by cache layer                                  2.67M
  790 MARTIN       cell blocks processed by txn layer                                    2.67M
  790 MARTIN       cell blocks processed by data layer                                   2.67M
  790 MARTIN       cell blocks helped by minscn optimization                             2.67M
  790 MARTIN       cell IO uncompressed bytes                                           21.84G
  790 MARTIN       cell flash cache read hits                                           18.95k