Do not worry about the other statistics shown here—they are part of this chapter as well. Note that cell physical IO bytes eligible for predicate offload simply counts the physical size of the segment in the data blocks in data files and not the “eventual” data size after any decompression, filtering, or projection.

If this number does not increase for your session’s V$SESSTAT (or Statspack/AWR data when looking at the whole instance), then this is another indicator that Smart Scans are not used. Any block ranges scanned through, or even skipped (thanks to storage index optimizations), by a Smart Scan session should increment this statistic. Another fact worth knowing is that when a Smart Scan falls back to passthrough (full-block shipping) mode (described earlier), the cell physical IO bytes eligible for predicate offload statistic is incremented regardless, although there is no predicate offloading and Smart Scan filtering done in the cell in passthrough mode.

cell physical IO bytes saved by storage index

This is another important statistic, which shows how many bytes the Smart Scan sessions could simply skip reading chunks of data from disk, thanks to the in-memory storage index in cellsrv. This statistic, cell physical IO bytes saved by storage index is closely related to cell physical IO bytes eligible for predicate offload. If the ratio of the two is close to 1, you have a clear indication that Smart Scans greatly benefit from storage indexes and have avoided a lot of I/O thanks to that. Remember from Chapter 4 that storage indexes are not a persistent structure: They can evolve over time and are not guaranteed to always be available.

Please also be aware that the statistic is cumulative. If you would like to investigate how many bytes could be skipped thanks to the storage index, you need to get the current value of the statistic prior to the execution of the SQL statement and right after it finished to calculate the difference between the two. The statistic is also rolled up into physical read requests optimized and physical read total bytes optimized.

cell physical IO bytes sent directly to DB node to balance CPU

If this statistic shows up—for example, during a run of Snapper—it is a sign of problems on the storage servers. Under certain conditions, such as when the cells are heavily CPU bound and there are spare CPU cycles in the RDBMS layer, the latter can take care of decompressing CUs for it. There is a certain amount of communication between the RDBMS and the storage layer, including exchanges of CPU-related information. If a cell is CPU bound, it may send columns or entire CUs back uncompressed.

Seeing counters increment for this statistic is a sign of problems on the system, and you should investigate why the cells are so CPU bound. The use of dcli is a good starting point to investigate CPU load. If the problem is local to a cell, it is worth connecting to it and performing additional troubleshooting. Note that this is purely a CPU problem and not necessarily disk/memory related. Different statistics exist for these.

cell physical IO interconnect bytes

This is a simple, but fundamental statistic, which shows how many bytes worth of any data have been transmitted between the storage cells and your database sessions. This includes all data—both sent and received by the database—the Smart Scan result sets, full blocks read from the cells, temporary I/O reads and writes, log writes, any supplementary iDB traffic, and so on. So, this statistic shows all traffic (in bytes), regardless of its direction, contents, or nature.

When measuring the write I/O metrics, it is completely normal to see the cell physical I/O interconnect bytes statistic two or three times higher than the physical write total bytes statistic. This is because the latter statistic is measured at the Oracle database level, but the cell physical I/O interconnect bytes is measured at the cell level, after ASM mirroring has been done. If, for example, LGWR writes 1MB to an ASM disk group with high redundancy (triple mirroring), a total of 3MB of data would be sent over the interconnect.

cell physical IO interconnect bytes returned by smart scan

This important statistic shows how many bytes of data were returned to the database layer by Smart Scans. For Smart Scans to be most efficient, the number of bytes actually returned should be far less than the bytes scanned (in other words, read from disk). This is the main point of the Exadata Smart Scan feature—the cells may read gigabytes of data every second, but as they perform early filtering thanks to predicate offloading, they may send only a small part of the rows back to the database layer. Additionally, owed to projection offloading, the Smart Scans only return the requested columns back, not full rows. Of course, if the application uses SELECT * for fetching all the columns of a table, projection offloading would not help, but the early filtering using predicate offloading can still be very useful.

This statistic is a subset of the cell physical I/O interconnect bytes statistic, but it counts only the bytes that are returned by Smart Scan sessions and no other traffic. You may see cell physical I/O interconnect bytes reported greater than cell physical I/O interconnect bytes returned by smart scan in case of sorting to disk, for example.

cell scans

This statistic is similar in nature to cell index scans, but cell scans shows the number of Smart Scans done on table and materialized view segments, including their partitions. With serial execution, this statistic is incremented once at the beginning of every segment scan. When scanning through a partitioned table, in which each partition is a separate segment, this statistic would be incremented for each partition. With parallel scans, the cell scans statistic will increment even more, as parallel slaves perform their scans on block ranges (PX granules) handed over to them by the query coordinator. Hence, the scan on each block range is reported as a separate cell scan. The presence of the table scans (rowid ranges) statistic indicates that PX scans on block ranges are occurring.

cell smart IO session cache hits

This statistic shows how many times a database session managed to reuse a previously initialized Smart Scan session in the cell. This statistic shows up when a single execution plan scans through multiple segments (like with partitioned tables) or revisits the same segment during a single execution.

cell smart IO session cache lookups

This statistic is incremented every time a database session tried to reuse a previously initialized Smart Scan session in the cell. If the cell smart IO session cache hits statistic increments, too, the lookup was successful and a previous session context can be reused. The smart I/O session caching works only within an execution (and subsequent fetches) of an open cursor. Once the execution finishes, the next executions, even of the same cursor, would have to set up new Smart Scan sessions and communicate the new consistent-read snapshot SCN to the cells, too.

cell transactions found in commit cache

This statistic is related to the consistent-read (CR) mechanism Oracle has to guarantee, even on Exadata. It shows how many times the Smart Scan sessions checked the cell commit cache to decide whether a CR rollback is needed or not, and found the transaction status information in the cell commit cache. This avoids a round trip to the database layer to check that transaction’s status using undo data available there. You can read more about how the consistent reads work with Exadata Smart Scans in the cell blocks helped by commit cache statistic section.

chained rows processed by cell

Before explaining what this specific statistic means, let’s look at what chained rows are and how Smart Scans deal with chained rows. There are a few special cases where rows move from one block to another, or “span” blocks. Oracle has to manage rows that are too big to fit into a block. And it also has to deal with rows that grow, for example, by updates on a varchar2-column that initially held only a few characters but is updated to a few hundred.

Row chaining implies that a row is distributed or spread across more than one block. Technically speaking, a row is divided into row pieces. In many cases, the head piece and the rest of the row are in the same block, which benefits Exadata processing. Row chaining happens most often with large rows and is the price for processing a lot of data in a row. With a chained row, the head piece is in, say, block x and the rest of the row is in blocks y and z. Each piece of the row has a pointer to the next one, called an NRID (next ROWID). This is quite logical: If you want to store 100kb-rows, then you simply cannot squeeze these into an 8k block, and row chaining is unavoidable. You can see this in a block dump. The table has been created using this statement:

CREATE TABLE chaines2
(
  id, a, b, c, d, e
) as
WITH v1 as (
  SELECT rownum n FROM dual CONNECT BY level <= 10000
)
SELECT  rownum id,
rpad('a',1980,'*'),
rpad('b',1980,'*'),
rpad('c',1980,'*'),
rpad('d',1980,'*'),
rpad('e',1980,'*')
FROM v1,
  v1
WHERE rownum <= 100000;

Checking the rows, you see that they are spread across blocks:

Start dump data blocks tsn: 5 file#:5 minblk 2132987 maxblk 2132987
[...]
block_row_dump:
tab 0, row 0, @0x22
tl: 7225 fb: --H-F--N lb: 0x0  cc: 5
nrid:  0x01608bfc.0
col  0: [ 2]  c1 02
col  1: [1980]
 61 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a
[...]
col  2: [1980]
 62 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a
[...]
col  3: [1980]
 63 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a
[...]
col  4: [1261]
64 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a
end_of_block_dump

Not all of the columns fit into the row, only up to two-thirds of column d. The row has to be continued in a different block since the remainder of column d as well as column e needs to be represented. This is expressed in the row header information, more specifically in the flag: --H-F--N. It translates into the (H)ead piece, the (F)irst piece and (N)ext pieces and reads: The row’s head piece and first pieces are in this block, but there is another part of the row in a different block. How does Oracle find that next block? The information is encoded in the NRID, which is the block address. The NRID in this example is 0x01608bfc.0. The first bit of the hexadecimal number is the block address; the last part (.0) is the nth row in that block. DBMS_UTILITY has a set of functions allowing us to decode the NRID to a file and block address:

SQL> select
  2    dbms_utility.data_block_address_file(to_number('01608bfc','xxxxxxxxxxxxx')) fno,
  3    dbms_utility.data_block_address_block(to_number('01608bfc','xxxxxxxxxxxx')) blockno
  3  from dual;

       FNO    BLOCKNO
---------- ----------
         5    2132988

If you dump that block as well, you will see that the row continues:

Start dump data blocks tsn: 5 file#:5 minblk 2132988 maxblk 2132988
[...]
block_row_dump:
tab 0, row 0, @0x14ec
tl: 2708 fb: -----LP- lb: 0x0  cc: 2
col  0: [719]
[...]
col  1: [1980]
[...]
tab 0, row 1, @0x18
tl: 4527 fb: --H-F--N lb: 0x0  cc: 4
nrid:  0x01608bfd.0
col  0: [ 2]  c1 03

Row migration is a different case and has to deal with situations where an update forces a row out of its current location and into another block. Only the head piece remains in the old locations, and it has a forward pointer to the rest of the row. Very often, updates are the culprits for migrated rows. Before the update, you could see that the entire row was co-located in the block:

Start dump data blocks tsn: 5 file#:5 minblk 131 maxblk 131
block_row_dump:
tab 0, row 0, @0x1f7a
tl: 6 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 2]  c1 02
tab 0, row 1, @0x1f71

After the update, the same block looks quite different:

Start dump data blocks tsn: 5 file#:5 minblk 131 maxblk 131
block_row_dump:
tab 0, row 0, @0x1f77
tl: 9 fb: --H----- lb: 0x2  cc: 0
nrid:  0x01603167.0
tab 0, row 1, @0x1f6e

The migrated row does not even have a single column in the original location (cc: 0); only a NRID remains.

Chained rows pose a problem for Smart Scans. The chained row’s “next” row pieces may be anywhere in the segment. Thanks to ASM striping, it is not guaranteed that the next row pieces of a chained row are in the same cell where the row’s head piece is located. So, a chained row may be physically split across multiple different cells. Given that cells never communicate with each other during Smart Scans, how would you be able to construct the full row when needed? The way cellsrv currently solves this problem is that whenever the Smart Scan hits a chained row (and realizes it has to fetch its next row piece), cellsrv falls back to regular block I/O for that row and sends the block back to the database layer for normal processing if it cannot locate the NRID in the chunk of data it is currently scanning. In this case-NRID found-the statistic chained rows processed by cell is incremented.

If the NRID is not in the current chunk of data Smart Scan is processing, the block must be sent to the RDBMS layer for processing. Once it has been received, the database layer can extract the data block address of the next row piece from the row head piece and issue the block read I/O to the appropriate cell where the ASM striping has physically put that block. The reasoning and fundamental problem behind this optimization is similar to why consistent-read rollbacks have to be done in the database layer as opposed to a cell—some of the data blocks required for this operation may just happen to be located in another cell, and cells never talk to other cells. The RDBMS layer must then issue a single block read to the block indicated with the NRID and process it further.

This behavior means that your Smart Scan performance may drop if it hits a lot of chained rows and has to fetch their next row pieces. If you get lucky and access only the columns that are present in the head piece of the row, you do not have to fall back to database block I/O mode for these blocks and your Smart Scans will be fast.

If you have to fetch the next row pieces constantly, then your Smart Scan will be constantly interrupted, falling back to block I/O, and the database layer starts doing logical reads (and possibly single-block physical reads as well, if these blocks are not cached in buffer cache). This means that your query ends up waiting most of its time for random single-block reads as opposed to high-performance Smart Scanning. Here is a five-second snap(er) of a session that has to fight with a lot of chained rows in a contrived and unrealistic worst-case scenario:

  sid username     statistic                                                             delta
  297 MARTIN       table scan rows gotten                                               22.47k
  297 MARTIN       table fetch continued row                                             4.95k
  297 MARTIN       cell blocks processed by cache layer                                 27.43k
  297 MARTIN       cell blocks processed by txn layer                                   27.43k
  297 MARTIN       cell blocks processed by data layer                                  24.73k
  297 MARTIN       cell blocks helped by minscn optimization                            27.43k
  297 MARTIN       chained rows skipped by cell                                         14.84k
  297 MARTIN       chained rows processed by cell                                           11
  297 MARTIN       chained rows rejected by cell                                            19

--------------------------------------------------------------------------------------------
Active% | INST | SQL_ID          | SQL_CHILD | EVENT                            | WAIT_CLASS
--------------------------------------------------------------------------------------------
    58% |    1 | 3bm2yp12vtyja   | 0         | cell single block physical read  | User I/O
     5% |    1 | 3bm2yp12vtyja   | 0         | ON CPU                           | ON CPU
     5% |    1 | 3bm2yp12vtyja   | 0         | cell smart table scan            | User I/O

This chained-row performance problem applies only to regular data blocks and those compressed with regular block-level compression (BASIC or ADVANCED). Luckily, it is not a problem for HCC compressed tables at all, as in EHCC the rows and columns are physically organized differently. Also, this issue does not apply to migrated rows when full-scanning through a segment. The full scan/Smart Scan just ignores the head pieces of migrated rows, as the entire row is physically elsewhere. Note that updates can have side effects for HCC compressed tables, as explained in Chapter 3, even though chained rows are not a problem.

Another interesting case of row-chaining peculiarities is when you have over 255 columns in a table. Even when the total row size is small enough to fit inside a single block, with over 255 columns, Oracle would still do intra-block chaining, in which the row is chained but all the row pieces are physically inside the same block. This was needed because Oracle wanted to maintain backward compatibility when it increased the column limit from 255 to 1000 in Oracle 8.0. The “column count” byte in a row piece is just one byte, allowing 255 columns per row piece, but thanks to chaining you can have more columns in next row piece(s). Co-author Tanel Poder has an interesting case study related to this situation on his blog at blog.tanelpoder.com.

chained rows rejected by cell

This statistic shows how many chained rows were not processed in the cell. This statistic should be rarely incremented. In Smart Scan processing, the cells will place the result of the sliced and diced block in an output buffer before sending the result to the querying session’s PGA. Whenever the result of chained row processing on the cell does not fit into this output buffer, the statistic is incremented by one.

chained rows skipped by cell

This is the most important statistic when it comes to troubleshooting chained rows processing on Exadata, together with table fetch continued row. A simple test can demonstrate this better. This is a SQL query against a table with row chaining in every block:

SQL> select id, count(e) from chaines_big where a like 'a%' and id < 11 group by id;

On anther session, a five-second snap has been executed specifically to include only the two statistics to be covered in this section:

SQL> @snapper4 all,gather=s,sinclude=fetch|chain|consistent|cell 5 1 297

The result can be seen here, reformatted:

  sid username     statistic                                                             delta
  297 MARTIN       cell physical IO interconnect bytes                                 167.52M
  297 MARTIN       consistent gets                                                      45.85k
  297 MARTIN       consistent gets from cache                                            7.68k
  297 MARTIN       consistent gets direct                                               38.17k
  297 MARTIN       cell physical IO bytes eligible for predicate offload               318.92M
  297 MARTIN       cell physical IO interconnect bytes returned by smart scan          106.65M
  297 MARTIN       no work - consistent read gets                                        7.68k
  297 MARTIN       table fetch continued row                                             7.68k
  297 MARTIN       cell blocks processed by cache layer                                 43.75k
  297 MARTIN       cell blocks processed by txn layer                                   43.75k
  297 MARTIN       cell blocks processed by data layer                                  38.54k
  297 MARTIN       cell blocks helped by minscn optimization                            43.76k
  297 MARTIN       chained rows skipped by cell                                         28.03k
  297 MARTIN       chained rows processed by cell                                           34
  297 MARTIN       chained rows rejected by cell                                            21
  297 MARTIN       cell IO uncompressed bytes                                          316.74M
  297 MARTIN       cell flash cache read hits                                            8.09k

--  End of Stats snap 1, end=2014-08-12 06:50:13, seconds=4.8


----------------------------------------------------------------------------------------------
Active% | INST | SQL_ID          | SQL_CHILD | EVENT                             | WAIT_CLASS
----------------------------------------------------------------------------------------------
    93% |    1 | 16srfqdur1bxg   | 0         | cell single block physical read   | User I/O
     7% |    1 | 16srfqdur1bxg   | 0         | ON CPU                            | ON CPU

--  End of ASH snap 1, end=2014-08-12 06:50:13, seconds=5, samples_taken=44

As you can see from the above output, Oracle has skipped about 28,000 chained rows in the cell and sent them to RDBMS for regular processing. Another indicator of the problem is the relatively high number for table fetch continued row.

Also note how the consistent gets value, which counts all CR gets done both in the database and in cell by Smart Scan, is a little more than 45,000. At the same time, the subcounters, consistent gets from cache (CR gets performed by the database layer, from buffer cache), and consistent gets direct (consistent gets bypassing the buffer cache) indicate how much of the CR work was done at the cell level and how much was additionally done at the database level.

As a conclusion about chained rows, the next time you see that your Smart Scan is waiting for lots of cell single block physical reads and is doing logical I/O inside the database layer (consistent gets from cache), one of the things to check is the abovementioned statistics to see whether you are hitting chained rows and have to process them in the database. Of course, do not forget that the Smart Scans offload only the large segment scan workload, but if your query plan contains other row sources, index range scans, or small, cached table scans, seeing logical I/Os and single-block physical reads is expected. You can use wait interface data in ASH, V$SESSION, or SQL Trace—depending on your licenses—to see against which objects these single-block reads accessed. The current_obj# column in ASH, and obj# field in raw SQL trace file refer to the object_id of the table (or index or partition) the session is reading from.

EHCC Related Counters

As you read in the introduction to this chapter, it is not possible to cover every single HCC related counter in this section as there are simply too many. Instead, HCC processing is put into perspective with the rest of the contents. In summary, there are two scenarios for HCC processing in Exadata. The first case is a Smart Scan where the cells take over the decompression of CUs and pass only relevant information to the RDBMS layer. In this scenario, you will find counters named cell CU% incremented. These are incremented on the cell level. Additionally, you will notice counters named EHCC% being incremented as well, which can lead to double counting as you can see later. These EHCC% counters are related to HCC processing on the RDBMS layer.

If you find EHCC% counters incremented but not cell CU%, your query was not offloaded. In other words, there was no Smart Scanning involved. The HCC processing, therefore, needs to happen entirely on the RDBMS layer. Oracle has instrumented the compression and decompression of HCC data quite well. Consider, for example, the creation of a HCC compressed table. Logging into a new session has reset the EHCC% counters.

SQL> create table t1_qh column store compress for query high as select * from t1;

A quick query reveals the result:

SQL> !cat hcc_stats.sql
select name, value value_bytes, round(value/power(1024,2),2)  value_mb
from v$statname natural join v$mystat
where (name like '%EHCC%' or name like 'cell CU%')
and value <> 0;
SQL> @hcc_stats

NAME                                                                 VALUE_BYTES        VALUE_MB
---------------------------------------------------------------- --------------- ---------------
EHCC CUs Compressed                                                         2148               0
EHCC Query High CUs Compressed                                              2148               0
EHCC Compressed Length Compressed                                       67439392           64.32
EHCC Decompressed Length Compressed                                  10709897349        10213.75
EHCC Rows Compressed                                                    10349046            9.87
EHCC CU Row Pieces Compressed                                               9899             .01
EHCC Analyzer Calls                                                            1               0

7 rows selected.

Translated into English, this means that 2148 CUs were compressed, all of them with the Query High algorithm. The data could be shrunk to about 64MB as opposed to 10,214MB uncompressed, which is a nice saving. The statistics indicated that a little more than one million rows were compressed into 9899 CU row pieces. The compression analyzer—explained in Chapter 3 in detail—was invoked once, because there is only one nonpartitioned table to compress.

Querying data shows a different picture as opposed to the table creation just demonstrated. Here is an example of a segment scan that was not offloaded (the cell CU% counters have already been discussed and will not be shown here):

SQL> select /* gather_plan_statistics test002 */ count(id),id
   2   from t1_qh group by id having count(id) > 100000;

no rows selected

SQL> @hcc_stats

NAME                                                             VALUE_BYTES   VALUE_MB
---------------------------------------------------------------- ----------- ----------
EHCC CUs Decompressed                                                   4129          0
EHCC Query High CUs Decompressed                                        4129          0
EHCC Compressed Length Decompressed                                130329591     124.29
EHCC Decompressed Length Decompressed                             2.0697E+10    19738.6
EHCC Columns Decompressed                                               4129          0
EHCC Total Columns for Decompression                                   24774        .02
EHCC Total Rows for Decompression                                   20000000      19.07
EHCC Pieces Buffered for Decompression                                  4161          0
EHCC Total Pieces for Decompression                                    19829        .02
EHCC Turbo Scan CUs Decompressed                                        4129          0

10 rows selected.

Here you can see that 4129 CUs were decompressed, all of them using the Turbo Scan decompression. There were 19,829 pieces to be decompressed, resulting in 20,000,000 rows, which is the row count of the entire table. Fewer columns were actually decompressed than there were columns in all CUs: 4129 vs. 27,774, which nicely demonstrates the capability to do less work, thanks to the column-oriented approach. You can derive the effectiveness of the decompression by comparing the EHCC Decompressed Length Decompressed to EHCC Compressed Length Decompressed.

There is one caveat to be aware of with the EHCC statistics: There is some double counting on cell and RDBMS layer. Consider the following example. It makes use of Adrian Billington’s “mystats” script to capture the changes in session counters during the execution of a SQL statement. The tool can be downloaded from oracle-developer.net, and I recommend you have a look at it. Only the relevant information has been taken from the output:

SQL> @mystats start

SQL> select /* test006 */ count(id),id
  2  from BIGTAB_QH group by id having count(id) > 100000;

SQL> @mystats stop t=1

STAT    cell CUs processed for uncompressed                                        104,289
STAT    cell CUs sent uncompressed                                                 104,289
STAT    cell IO uncompressed bytes                                          71,828,529,728
STAT    cell blocks helped by minscn optimization                                  404,724
STAT    cell blocks processed by cache layer                                       404,724
STAT    cell blocks processed by data layer                                        404,724
STAT    cell blocks processed by txn layer                                         404,724
STAT    cell flash cache read hits                                                   2,991
STAT    cell scans                                                                       1

STAT    EHCC CUs Decompressed                                                      208,578
STAT    EHCC Columns Decompressed                                                  208,578
STAT    EHCC Compressed Length Decompressed                                  4,744,301,064
STAT    EHCC Decompressed Length Decompressed                              143,641,134,208
STAT    EHCC Pieces Buffered for Decompression                                     208,872
STAT    EHCC Query High CUs Decompressed                                           104,289
STAT    EHCC Total Columns for Decompression                                     1,668,624
STAT    EHCC Total Pieces for Decompression                                        542,756
STAT    EHCC Total Rows for Decompression                                      512,000,000
STAT    EHCC Turbo Scan CUs Decompressed                                           104,289

------------------------------------------------------------------------------------------
3. About
------------------------------------------------------------------------------------------
- MyStats v2.01 by Adrian Billington (http://www.oracle-developer.net)
- Based on the SNAP_MY_STATS utility by Jonathan Lewis

In the above output, you see an example for double counting. The cell reports that it processed 104,289 CUs during this Smart Scan (cell CUs sent uncompressed). When the data arrives on the RDBMS layer, it is accounted for in Turbo Scan CUs decompressed counter, matching the number reported on the cells. Even though the data is already decompressed by the time it arrives at the RDBMS layer, it seems likely that it traverses the same code path again, causing some statistics to be incremented once more. This is visible in EHCC CUs Decompressed and EHCC Columns Decompressed as well as EHCC Pieces Buffered for Decompression.

Another case of double counting is visible in cell IO uncompressed bytes and EHCC Decompressed Length Decompressed, which is double the value of the former.

physical read requests optimized

This statistic shows how many I/O requests to disk were avoided either by reading the data from Flash Cache instead of disks and/or thanks to the storage index I/O elimination. This statistic is also propagated to V$SQL/V$SQLSTATS and V$SEGMENT_STATISTICS views.

physical read total bytes optimized

This statistic shows how many bytes worth of physical disk drive I/O was avoided either by reading it from Flash Cache and/or thanks to storage index I/O elimination. When you also see the statistic cell physical I/O bytes saved by storage index equally increase, this means that some I/O could be avoided completely thanks to storage indexes. If the storage index savings are smaller than the total optimized bytes, the rest of the I/O was optimized thanks to reading it from Flash Cache, instead of the good old spinning disks. In this case, you can expect to see Flash Cache read hits as well.

table fetch continued row

This statistic is not Exadata-specific, but it is relevant when troubleshooting unexpected single-block reads done by the database while a Smart Scan is used. This statistic counts how many times Oracle had to fetch a next row piece of a chained row using a regular single-block read if it cannot be found in the buffer cache. Refer to the description about chained rows earlier in the chapter for more in-depth information.

table scans (direct read)

This statistic is not Exadata-specific; it is seen in any Oracle database performing full table scans on table segments using direct path reads. During serial execution, this statistic is incremented at the beginning of the table or segment scan. However, with parallel execution, it is incremented each time a slave starts scanning a new ROWID-range distributed to it. Direct path reads are a prerequisite for Smart Scans to happen. One quick troubleshooting option when you do not see Smart Scan when you expected them is to check if a direct path read happened using this statistic. Another quick tip: When using Snapper to troubleshoot a query already executing that does not scan multiple partitions, you might not see an entry for this statistic. That does not imply that there was no direct path read—it might be that you started troubleshooting the session after the counter has been increased.

table scans (long tables)

This is a similar statistic to the previous one, but it shows whether the table scanned was considered to be large or not. Actually, Oracle considers this separately for each segment, so some partitions of a table may be considered small, some large. A segment that is considered small by Oracle increments the table scans (short tables) counter. If the segment, which is always read up to the high water mark during full scans, is bigger than 10 percent of the buffer cache, the table is considered large and direct path reads are considered even for serial full segment scans. Note that this decision logic takes other things into account, which have been explained in more detail in Chapter 2. The ten-percent-of-the-buffer-cache rule actually comes from the _small_table_threshold parameter. This parameter defaults to two percent of buffer cache size (in blocks), but Oracle uses 5 × _small_table_threshold as its direct-path-scan decision threshold (depending on the number of blocks in the buffer cache and some other factors) in early releases of Oracle 11.2. In current releases, including 11.2.0.3, a table can be eligible for a Direct Path Read/Smart Scan even if it is just a little bit larger than _small_table_threshold. Again, the logic is covered in Chapter 2.

It is also worth pointing out that a single-block I/O to the segment header no longer determines the size of a segment. Oracle 11.2.0.2 and later uses dictionary information about the table instead.

Understanding SQL Statement Performance

This section focuses on the SQL statement’s performance metrics and understanding where a statement is spending its time and where its bottlenecks are. Metrics covered in Chapter 10 will be reviewed with a focus on how and when to use them. The bulk of the various SQL performance monitoring tools will be covered in the next chapter. Most Exadata-specific performance statistics of individual SQL statements may be monitored primarily using the following views:

• V$SQL and V$SQLAREA
• V$SQLSTATS and V$SQLSTATS_PLAN_HASH
• V$SQL_MONITOR and V$SQL_PLAN_MONITOR
• V$ACTIVE_SESSION_HISTORY and the DBA_HIST_ACTIVE_SESS_HISTORY persisted into AWR repository

Accessing the AWR-related views requires you to be properly licensed. Note that all the Exadata-specific metrics you see in V$SQL% views are really the same ones you can see from V$SESSTAT views. They originate from the same sources but are just accumulated differently. V$SESSTAT accumulates stats for a session, regardless of which SQL statement or command incremented them, while the views with a V$SQL prefix aggregate stats for different SQL statements, regardless of the sessions executing them. So, it is possible to see some Exadata metrics aggregated by a SQL statement.

Here is an example of the V$SQL% view’s columns. (There are many more columns in V$SQL views; here you are shown those that matter in the context of Smart Scans.)

SQL> desc v$sql
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SQL_TEXT                                           VARCHAR2(1000)
 SQL_FULLTEXT                                       CLOB
 SQL_ID                                             VARCHAR2(13)
 SHARABLE_MEM                                       NUMBER
 PERSISTENT_MEM                                     NUMBER
 RUNTIME_MEM                                        NUMBER
[...]
 IO_CELL_OFFLOAD_ELIGIBLE_BYTES                     NUMBER
 IO_INTERCONNECT_BYTES                              NUMBER
 PHYSICAL_READ_REQUESTS                             NUMBER
 PHYSICAL_READ_BYTES                                NUMBER
 PHYSICAL_WRITE_REQUESTS                            NUMBER
 PHYSICAL_WRITE_BYTES                               NUMBER
 OPTIMIZED_PHY_READ_REQUESTS                        NUMBER
 LOCKED_TOTAL                                       NUMBER
 PINNED_TOTAL                                       NUMBER
 IO_CELL_UNCOMPRESSED_BYTES                         NUMBER
 IO_CELL_OFFLOAD_RETURNED_BYTES                     NUMBER
 CON_ID                                             NUMBER
 IS_REOPTIMIZABLE                                   VARCHAR2(1)
 IS_RESOLVED_ADAPTIVE_PLAN                          VARCHAR2(1)
 IM_SCANS                                           NUMBER
 IM_SCAN_BYTES_UNCOMPRESSED                         NUMBER
 IM_SCAN_BYTES_INMEMORY                             NUMBER

The columns in bold typeface are specific to Exadata processing, but not specific to Exadata. If you describe V$SQL in a non-Exadata environment, you will get exactly the same columns. Table 11-1 lists the most interesting columns, explicitly excluding some of the physical read columns for readability.

Here is example output from a query on an EHCC compressed table where the table scan was offloaded to the cell. The V$SQL table output is pivoted and reduced to the relevant detail for better readability:

SQL_TEXT                      : select /* hccquery001 */ ...
SQL_ID                        : 5131dsd26qfc5
DISK_READS                    : 1304924
BUFFER_GETS                   : 1304934
IO_CELL_OFFLOAD_ELIGIBLE_BYTES: 10689937408
IO_INTERCONNECT_BYTES         : 84656896
PHYSICAL_READ_REQUESTS        : 10258
PHYSICAL_READ_BYTES           : 10689937408
PHYSICAL_WRITE_REQUESTS       : 0
PHYSICAL_WRITE_BYTES          : 0
OPTIMIZED_PHY_READ_REQUESTS   : 10110
LOCKED_TOTAL                  : 1
PINNED_TOTAL                  : 2
IO_CELL_UNCOMPRESSED_BYTES    : 27254591356
IO_CELL_OFFLOAD_RETURNED_BYTES: 84656896
-----------------

PL/SQL procedure successfully completed.

In this case, the IO_CELL_OFFLOAD_RETURNED bytes is much smaller than the IO_CELL_OFFLOAD_ELIGIBLE bytes; thus, the Smart Scan definitely did help to reduce the data flow between the cells and the database. The latter column is a good indication about how many Smart Scans have been performed in the database:

SQL> !cat sscan.sql
WITH offloaded_yes_no AS
  (SELECT inst_id,
    CASE
      WHEN (IO_CELL_OFFLOAD_ELIGIBLE_BYTES > 0)
      THEN 'YES'
      ELSE 'NO'
    END sscan
  FROM gv$sql
  )
SELECT COUNT(sscan),
  sscan as smart_scan,
  inst_id
FROM offloaded_yes_no
GROUP BY sscan,
  inst_id;

Furthermore, going back to the previous example, the IO_CELL_UNCOMPRESSED_BYTES is significantly larger than the PHYSICAL_READ_BYTES, which indicates that the HCC helped to reduce the number of bytes that had to be read from disk by the cells, thanks to compression. Note that the IO_INTERCONNECT_BYTES is not much greater than the IO_CELL_OFFLOAD_RETURNED_BYTES, which indicates that for this SQL, almost all the traffic was due to the data returned by Smart Scans. There was no extra traffic due to other reasons such as temp-tablespace reads/writes caused by non-optimal sorts, and there were no hash joins or other work-area operations or database block I/Os, caused by chained rows or in-database consistent-read processing.

While this example used the V$SQL view, which shows SQL child cursor level statistics, you could also use V$SQL_PLAN_MONITOR (the columns PLAN_LINE_ID, PLAN_OPERATION, and so on) to measure these metrics for each execution plan line. This is useful because a single execution plan usually accesses and joins multiple tables, and different tables may benefit from the Smart Scan offloading differently. Some more scripts and tools that use this data are introduced in Chapter 12.

Querying cellsrv Internal Processing Statistics

In earlier versions of the Exadata software, it was not always easy to gain insights into Exadata processing. In most cases, the performance analyst had to connect to the cell itself and then dump events into a trace file to gain access to these metrics. An easier way is to access the V$CELL family of views. This section will explain some of these and why querying them can provide you with interesting insights in the various processing steps on the cell server-without having to quit SQL*Plus! The V$CELL family includes these views for which Oracle provides an API. If you query V$FIXED_VIEW_DEFINITION for views beginning with GV%CELL%, you will notice that the V$CELL views are based on X$ tables named X$KCFIS%, Kernel Cache File Intelligent Storage. Not all of these X$ tables have a corresponding “official” V$ view.

This section will detail the V$CELL% views in addition to another tool on the cell not yet covered in the book: cellsrvstat.

The V$CELL Family of Views

The number of V$CELL% views has steadily increased with every release, and 12c is no exception. Oracle 12.1.0.2 lists these:

SQL> select table_name from dict where regexp_like(table_name, 'DBA.*(ASM|CELL)|^V$CELL'),

TABLE_NAME
--------------------------------------------------------------------------------
DBA_HIST_ASM_BAD_DISK
DBA_HIST_ASM_DISKGROUP
DBA_HIST_ASM_DISKGROUP_STAT
DBA_HIST_CELL_CONFIG
DBA_HIST_CELL_CONFIG_DETAIL
DBA_HIST_CELL_DB
DBA_HIST_CELL_DISKTYPE
DBA_HIST_CELL_DISK_NAME
DBA_HIST_CELL_DISK_SUMMARY
DBA_HIST_CELL_GLOBAL
DBA_HIST_CELL_GLOBAL_SUMMARY
DBA_HIST_CELL_IOREASON
DBA_HIST_CELL_IOREASON_NAME
DBA_HIST_CELL_METRIC_DESC
DBA_HIST_CELL_NAME
DBA_HIST_CELL_OPEN_ALERTS
V$CELL
V$CELL_CONFIG
V$CELL_CONFIG_INFO
V$CELL_DB
V$CELL_DB_HISTORY
V$CELL_DISK
V$CELL_DISK_HISTORY
V$CELL_GLOBAL
V$CELL_GLOBAL_HISTORY
V$CELL_IOREASON
V$CELL_IOREASON_NAME
V$CELL_METRIC_DESC
V$CELL_OFL_THREAD_HISTORY
V$CELL_OPEN_ALERTS
V$CELL_REQUEST_TOTALS
V$CELL_STATE
V$CELL_THREAD_HISTORY

33 rows selected.

Quite a few more compared to 11.2.0.4:

SQL> select table_name from dict where regexp_like(table_name, 'DBA.*CELL|^V$CELL'),

TABLE_NAME
------------------------------
V$CELL
V$CELL_CONFIG
V$CELL_OFL_THREAD_HISTORY
V$CELL_REQUEST_TOTALS
V$CELL_STATE
V$CELL_THREAD_HISTORY

6 rows selected.

Since this chapter is already very long, a careful selection has been made to include only the most important of these views. The AWR versions of the views are left out as they essentially allow a longer-term archival of the information on the SYSAUX tablespace.

V$CELL

The first view that comes to mind is V$CELL. In Oracle 12.1.0.2, the view definition is as shown here:

SQL> desc v$cell
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CELL_PATH                                          VARCHAR2(400)
 CELL_HASHVAL                                       NUMBER
 CON_ID                                             NUMBER
 CELL_TYPE                                          VARCHAR2(400)

CON_ID and CELL_TYPE are new in 12c; Oracle 11.2 only shows the cell path (an IP address) and the cell has value. You are likely to use this view to map a cellhash to a cell, as found in the Smart Scan related wait events discussed in Chapter 12.

V$CELL_OFL_THREAD_HISTORY

This interesting view records a ten-minute history of what cellsrv threads were doing, conceptually something like ASH for storage cells. This view is similar to V$CELL_THREAD_HISTORY but has additional columns allowing for an ASH-like versioning of the information. Here is the view definition for 12.1.0.2:

SQL> desc V$CELL_OFL_THREAD_HISTORY
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CELL_NAME                                          VARCHAR2(1024)
 GROUP_NAME                                         VARCHAR2(1024)
 PROCESS_ID                                         NUMBER
 SNAPSHOT_ID                                        NUMBER
 SNAPSHOT_TIME                                      DATE
 THREAD_ID                                          NUMBER
 JOB_TYPE                                           VARCHAR2(32)
 WAIT_STATE                                         VARCHAR2(32)
 WAIT_OBJECT_NAME                                   VARCHAR2(32)
 SQL_ID                                             VARCHAR2(13)
 DATABASE_ID                                        NUMBER
 INSTANCE_ID                                        NUMBER
 SESSION_ID                                         NUMBER
 SESSION_SERIAL_NUM                                 NUMBER
 CON_ID                                             NUMBER

As you can see, the view shows the cell processes, which internally map to cellsrv threads. For each of these, you see job type such as PredicateOflFilter during a Smart Scan and a state. Even more interesting is the fact that you see the SQL ID causing the load, the database ID, and instance number as well as the session SID and serial#. Be careful though—if it is not available, the SQL_ID is made up of 13 white spaces, not null or the empty string:

SQL> select count(''''||sql_id||''''),''''||sql_id||''''
  2   from v$cell_ofl_thread_history
  3  group by ''''||sql_id||'''';

COUNT(''''||SQL_ID||'''') ''''||SQL_ID||'
------------------------- ---------------
                        2 'f254uv2p53y7j'
                   126363 '             '

2 rows selected.

In other words, you can see who caused how many worker threads to be busy at a given point in time. Here is an example on how to get the current information from the system:

SELECT CELL_NAME ,
  GROUP_NAME ,
  SNAPSHOT_ID ,
  JOB_TYPE ,
  WAIT_STATE ,
  WAIT_OBJECT_NAME ,
  SQL_ID
FROM
  (SELECT CELL_NAME ,
    GROUP_NAME ,
    SNAPSHOT_ID ,
    JOB_TYPE ,
    WAIT_STATE ,
    WAIT_OBJECT_NAME ,
    SQL_ID ,
    DATABASE_ID ,
    INSTANCE_ID ,
    SESSION_ID ,
    SESSION_SERIAL_NUM ,
    CON_ID,
    MAX (SNAPSHOT_ID) over (partition BY cell_name) max_snap
  FROM V$CELL_OFL_THREAD_HISTORY
  )
WHERE snapshot_id = max_snap;

As the view name suggests, historic information is available, not just a snapshot of the current state. If you correlate the session information and SQL ID with ASH monitoring information, you should be able to draw a very accurate picture of the cell’s load at a given point in time.

V$CELL_STATE

This view is the source of a lot of information about the cell. Similar to V$CELL_CONFIG, it hinges on a column describing an XML field. In this case, it’s STATISTICS_TYPE. The 12.1.0.2 definition of the view is shown here:

SQL> desc v$cell_state
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CELL_NAME                                          VARCHAR2(1024)
 STATISTICS_TYPE                                    VARCHAR2(15)
 OBJECT_NAME                                        VARCHAR2(1024)
 STATISTICS_VALUE                                   CLOB
 CON_ID                                             NUMBER

The different statistics you can query very depending on the RDBMS/cell versions. For 12.1.0.2 and Exadata version 12.1.2.1.0 you can investigate the following metrics:

• IOREASON: breaks down I/O on the cell into every category imaginable
• RCVPORT: contains detail about network traffic received
• FLASHLOG: very detailed information about the use of the FLASHLOG feature
• SENDPORT: contains detail about network traffic sent
• PREDIO: contains information about how Exadata dealt with Smart Scans
• NPHYSDISKS: lists number of physical disks per cell
• CELL: similar information as in the cellsrvstat output
• THREAD: thread-related information about cellsrv’s worker threads
• PHASESTAT: information about the various phases of a Smart Scan
• CAPABILITY: cell software capabilities
• LOCK: breaking down mutex waits per object type in the cell
• OFLGROUP: offload server statistics

The I/O Reasons are so interesting that Oracle decided to give them their own view in 12c, named V$CELL_IOREASON. The trick with this view is that you have to parse the output again depending on the statistic type. It helps to select just the STATISTICS_VALUE for a given type and develop a strategy on how to parse the XML data. Here is an example to list all IOREASONS on 12.1.0.2 for a given cell:

SELECT x.cell_name, x.statistics_type, x.object_name, stats.*
FROM  V$CELL_STATE x,
      XMLTABLE ('/ioreasongroup_stats'
        PASSING xmltype(x.STATISTICS_VALUE)
        COLUMNS ioreasons XMLTYPE PATH '*'
      ) xt,
      xmltable ('/stat'
      passing xt.ioreasons
      columns name path './@name',
              value path '/stat'
    ) stats
    where x.statistics_type = 'IOREASON'
      and stats.name <> 'reason'
      and x.cell_name = '192.168.12.8'
/

The output-abbreviated-is shown here:

CELL_NAME            STATISTICS_TYPE OBJECT_NAME          NAME            VALUE
-------------------- --------------- -------------------- --------------- ---------------------
192.168.12.8         IOREASON        UNKNOWN              reads           369113
192.168.12.8         IOREASON        UNKNOWN              writes          170592
192.168.12.8         IOREASON        RedoLog Write        reads           0
192.168.12.8         IOREASON        RedoLog Write        writes          51493
192.168.12.8         IOREASON        RedoLog Read         reads           1152
192.168.12.8         IOREASON        RedoLog Read         writes          0
192.168.12.8         IOREASON        ArchLog Read         reads           0
192.168.12.8         IOREASON        ArchLog Read         writes          0
192.168.12.8         IOREASON        MediaRecovery Write  reads           0
192.168.12.8         IOREASON        MediaRecovery Write  writes          0

The most comprehensive output is available for the CELL statistic; it produces information that otherwise would have been available from a system state dump. Some of it is actually too complex to parse in its entirety, in which case a dump into an XML file can help. Simply cast the STATISTICS_VALUE into an XMLType and select from the dynamic performance view, paste the output into a text file, and open it with your favorite browser. The PREDIO and CELL statistics are good examples for where this really helps.

The cellsrvstat utility

For quite some time, Oracle has shipped the cellsrvstat utility as part of the cell software distribution. It is a useful tool for in-depth troubleshooting and research into how the cell software works. The tool has a built-in help, but is otherwise not really documented by Oracle. You can find several references in publications and some blogs. To give you an idea about the tool’s capabilities, see the following:

Usage:
cellsrvstat [-stat_group=<group name>,<group name>,]
            [-offload_group_name=<offload_group_name>,]
            [-database_name=<database_name>,]
            [-stat=<stat name>,<stat name>,] [-interval=<interval>]
            [-count=<count>] [-table] [-short] [-list]

stat                    A comma separated list of short strings representing
                         the stats. Default is all. (unless -stat is specified).
                         The -list option displays all stats.
                         Example: -stat=io_nbiorr_hdd,io_nbiowr_hdd
stat_group              A comma separated list of short strings representing
                         stat groups. Default: all except database
                         (unless -stat_group is specified).
                         The -list option displays all stat groups.
                         The valid groups are: io, mem, exec, net,
                         smartio, flashcache, offload, database.
                         Example: -stat_group=io,mem
offload_group_name      A comma separated list of short strings representing
                         offload group names.
                         Default: cellsrvstat -stat_group=offload
                         (all offload groups unless -offload_group_name is specified).
                         Example: -offload_group_name=SYS_121111_130502
database_name           A comma separated list of short strings representing
                         database group names.
                         Default: cellsrvstat -stat_group=database
                         (all databases unless -database_name is specified).
                         Example: -database_name=testdb,proddb
interval                At what interval the stats should be obtained and
                         printed (in seconds). Default is 1 second.
count                   How many times the stats should be printed.
                         Default is once.
list                    List all metric abbreviations and their descriptions.
                         All other options are ignored.
table                   Use a tabular format for output. This option will be
                         ignored if all metrics specified are not integer
                         based metrics.
short                   Use abbreviated metric name instead of
                         descriptive ones.
error_out               An output file to print error messages to, mostly for
                         debugging.

When researching the mechanics of Smart Scans, it proved useful to narrow the scope to io, smartio, or offload. In best UNIX tradition, a single call to the tool prints all the stats since collection began. If you are interested in the current statistics, you should specify the interval and count parameters. An interval of five seconds proved effective with a count of at least two. Just as with vmstat and iostat, you can safely ignore the first batch of output and focus on the second one, as this one represents the current statistics on the cell. Following is an example output for the io statistics group during a single Smart Scan against an 80GB table in serial mode. The figure following the statistic name is the delta since the last snapshot; the large number following it is the cumulative number of events since the cell started recording:

== Input/Output related stats ==
Number of hard disk block IO read requests                   1860       47269919
Number of hard disk block IO write requests                    18        1481441
Hard disk block IO reads (KB)                             1881618    46815620582
Hard disk block IO writes (KB)                                170      210753174
Number of flash disk block IO read requests                135071        6106538
Number of flash disk block IO write requests                    7        2761123
Flash disk block IO reads (KB)                            8641696      372843144
Flash disk block IO writes (KB)                               188       52813784
Number of disk IO errors                                        0              4
Number of latency threshold warnings during job                 0              2
Number of latency threshold warnings by checker                 0              0
Number of latency threshold warnings for smart IO               0              0
Number of latency threshold warnings for redo log writes        0              0
Current read block IO to be issued (KB)                         0              0
Total read block IO to be issued (KB)                         202       42461566
Current write block IO to be issued (KB)                        0              0
Total write block IO to be issued (KB)                        249      132637393
Current read blocks in IO (KB)                                  0              0
Total read block IO issued (KB)                               202       42461566
Current write blocks in IO (KB)                                 0              0
Total write block IO issued (KB)                              249      132637393
Current read block IO in network send (KB)                      0              0
Total read block IO in network send (KB)                      202       42461566
Current write block IO in network send (KB)                     0              0
Total write block IO in network send (KB)                     249      132637393
Current block IO being populated in flash (KB)                  0              0
Total block IO KB populated in flash (KB)                       0         401680

If you followed this chapter and tried the examples in your environment, the output of cellsrvstat looks very familiar. You get a lot of the information provided by the command line utility from the V$CELL views, especially from V$CELL_STATE.

Summary

The emphasis of this chapter was on understanding Exadata performance and the various related metrics Oracle offers the performance analyst and researcher. It is important to remember that the Exadata Smart Scan potentially speeds up your data retrieval, but Smart Scans happen only when direct path reads and full segment scans are used. Also, remember that it is not possible to determine whether a Smart Scan actually occurred by just considering the execution plan in isolation.

You should always check additional metrics, like whether you see the cell smart table/index scan wait events in your session and whether the IO_CELL_OFFLOAD_ELIGIBLE_BYTES (in V$SQL) or cell physical I/O bytes eligible for predicate offload statistic (in V$SESSTAT) increases while you run your SQL. Tracing on Wait Events, as explained in Chapter 10, is another method you have at your disposal to confirm if Smart Scans have been used or not. Many of the other metrics explained will hopefully be helpful for understanding and troubleshooting advanced performance issues, such as when a Smart Scan kicks in but is throttled by a multitude of special conditions like chained rows, consistent-read rollbacks, or just running out of cell server resources. In Chapter 10, we will see how to use this knowledge in monitoring and troubleshooting Exadata performance, and we will look deeper into the cell-level performance metrics from cellsrv and the operating system, too.