Measuring Performance
Exadata is a huge leap forward, with respect to its architecture. Unlike traditional commodity hardware configurations, Exadata provides a matched set of hardware and software. However, it is running the same Oracle 11.2.0.3 you can install on any non-Exadata platform. Thus, the same basic performance rules apply; the difference is the additional functionality of Smart Scans, the Smart Flash Cache, and offloading, to name a few. Because the purpose of this book is to make you familiar with, and work knowledgeably on, Exadata, it is the Exadata-specific performance metrics we will be covering. Additionally, we will cover Exadata-related performance topics and relevant internals.
Exadata provides plenty of performance metrics to use. That’s the good news. Unfortunately, Exadata provides plenty of performance metrics to use. That’s the bad news. It is good to have a vast array of metrics to examine in the quest for performance; the downside of that is you can get so lost in the wealth of performance data, you forget what you are looking for. Knowing what to look for, how to tell when things are good, and, more important, when things are bad can be difficult. The key to monitoring performance is not looking at every metric available but choosing metrics wisely. Why you are choosing specific metrics and what values you should be seeing are much more important.
The end user doesn’t care how many I/Os per second Exadata can provide; she measures performance in terms of response time. Basically speaking, the faster things go, the happier the end user will be. Addressing performance issues should be a process of monitoring and optimizing the response time. This is where the Oracle Wait interface becomes a very useful tool. These wait events are discussed in Chapter 7. Exadata also supplies other metrics to provide additional information that can allow you to further enhance performance. Data such as the number of bytes saved by a Smart Scan, the I/O avoided by use of a storage index, and Smart Flash Cache statistics provide keys to improving performance. This chapter will discuss such metrics, how to get the data, and what the numbers mean. We will also discuss monitoring and troubleshooting Exadata performance problems using this information.
Measure Twice, Cut Once
Before we dive into the Exadata performance metrics, we have to review some key pieces of the Exadata system. Whether or not a query or statement uses a Smart Scan, the database servers ask the storage cells to actually retrieve the requested data. When a Smart Scan is in use, the storage cells also assist in processing that data through column projection and predicate filtering. These processes involve the cells reading the data or index blocks, filtering the desired rows, and extracting only the columns of interest. Storage indexes also contribute to this process, by allowing the storage cells to bypass 1MB units of data that do not contain the column values of interest. From the database server perspective, the storage cells are black boxes from which the desired data is dispensed. These “black boxes” also pass back to the database servers metrics that record how much work those storage cells have performed. The storage cells themselves provide a rich palette of performance metrics, many of which are not passed back to the database servers. However, you will find sufficient information from the storage cells available at the database server level to troubleshoot many performance issues.
Smart Scans are the lifeblood of Exadata; they are key to achieving performance levels beyond common commodity hardware configurations. It is important that you understand the performance metrics for Smart Scans, and with that thought in mind, we will discuss, again, some aspects of them.
Smart Scans, Again
In Chapter 2 we discussed Smart Scans and offloading at length; we won’t go into that level of detail in this chapter. We will discuss important aspects you should know, in order to properly analyze the supplied metrics to make an informed diagnosis of performance issues.
Smart Scans require direct-path reads and full-table scans, index fast full scans or index full scans. The first step in troubleshooting performance issues regarding Smart Scans is to see if one of the qualifying full scans was used. The execution plan will give you that information, so it’s the logical place to begin. Going back to a previous example, we can see the qualifying step in the following plan:
SQL> select *
2 from emp
3 where empid = 7934;
EMPID EMPNAME DEPTNO
---------- ---------------------------------------- ----------
7934 Smorthorper7934 15
Elapsed: 00:00:00.21
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 6361 (1)| 00:00:01 |
|* 1 | TABLE ACCESS STORAGE FULL| EMP | 1 | 28 | 6361 (1)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("EMPID"=7934)
filter("EMPID"=7934)
Statistics
----------------------------------------------------------
1 recursive calls
1 db block gets
40185 consistent gets
22594 physical reads
168 redo size
680 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
As we stated in Chapter 2, simply because STORAGE is in the plan step, that is not a guarantee that a Smart Scan was executed. You have to dig deeper and see if direct-path reads were used. Other criteria also apply before it can be determined if a Smart Scan was used, such as the presence of column-projection data in the plan output. Predicate filtering also can take place with a Smart Scan. Storage indexes can also provide benefit, by allowing Oracle to bypass 1MB data segments that do not contain the column values of interest. Notice in the supplied plan that the full-scan step includes the keyword STORAGE and that the predicate information includes a storage() entry. Again, these do not guarantee that a Smart Scan was executed, only that the statement qualifies for Smart Scan execution. The absence of these items in a query plan does, however, indicate that a Smart Scan cannot occur. You may also see plans where the STORAGE keyword is present but the storage() predicate information is missing, indicating that no predicate offload was executed. In such cases, you may find that the query still benefited from column projection.
It is possible to have all of the previously mentioned criteria in an execution plan and still not execute a Smart Scan. Why? Remember that Smart Scans require direct-path reads; if no direct-path reads are executed, then no Smart Scan will be executed. Direct-path reads occur any time parallel execution is used but can also occur serially, especially if the _serial_direct_read parameter is set to TRUE. It is necessary when investigating Smart Scan performance to check the execution plan and any metrics indicating direct-path reads may have been used. Such metrics were discussed in Chapter 2, and the associated wait events were discussed in Chapter 6. Both sources of information will be used to troubleshoot Smart Scan performance.
Performance Counters and Metrics
The V$SQL and GV$SQL views provide two metrics we have used to prove Smart Scan execution, io_cell_offload_eligible_bytes, and io_cell_offload_returned_bytes. These are used to compute the percentage of data not read because a Smart Scan was executed. We provided this script in Chapter 2, but it is good to look at it again and understand what it reports:
SQL> select sql_id,
2 io_cell_offload_eligible_bytes qualifying,
3 io_cell_offload_eligible_bytes - io_cell_offload_returned_bytes actual,
4 round(((io_cell_offload_eligible_bytes - io_cell_offload_returned_bytes)/io_cell_offload_eligible_bytes)*100, 2) io_saved_pct,
5 sql_text
6 from v$sql
7 where io_cell_offload_returned_bytes> 0
8 and instr(sql_text, 'emp') > 0
9 and parsing_schema_name = 'BING';
SQL_ID QUALIFYING ACTUAL IO_SAVED_PCT SQL_TEXT
------------- ---------- ---------- ------------ -------------------------------------
gfjb8dpxvpuv6 185081856 42510928 22.97 select * from emp where empid = 7934
SQL>
As a review, the io_cell_offload_eligible_bytes column reports the bytes of data that can be offloaded to the storage cells during query execution. The io_cell_offload_returned_bytes column reports the number of bytes returned by the regular I/O path. The difference between these two values provides the bytes actually offloaded during query execution. Using these two metrics can prove that a Smart Scan was executed. Two other columns can also provide information on Smart Scan and storage index usage. These are cell physical IO bytes saved by storage index and cell physical IO interconnect bytes returned by smart scan. Covered in Chapter 3, cell physical IO bytes saved by storage index reports exactly what it says, the total bytes that weren’t read during a Smart Scan because the storage index allowed Oracle to bypass those 1MB blocks. An example from Chapter 3 follows as a refresher:
SQL> select *
2 from v$mystat
3 where statistic# = (select statistic# from v$statname where name = 'cell physical IO bytes saved by storage index'),
SID STATISTIC# VALUE
---------- ---------- ----------
1107 247 1201201152
SQL>
This value is reported at the session level from V$MYSTAT, and it’s necessary to remember that this statistic is cumulative for the duration of the session. Querying V$MYSTAT before and after query execution will provide a delta showing the bytes saved for that particular query.
The cell physical IO interconnect bytes returned by smart scan reports the actual bytes returned from a Smart Scan operation. V$SQLSTATS reports this value, along with two other values, physical_read_bytes and physical_write_bytes, which can be used to provide a Smart Scan “efficiency,” for want of a better term. This topic is covered in more depth in Chapter 10, but be aware that this “efficiency” can exceed 100 percent when sorts or index access paths that don’t qualify for Smart Scan execution are used.
Knowing what the statement was waiting on also provides information on Smart Scan execution. Direct-path reads usually indicate Smart Scan activity, but not always. LOBs, index-organized tables, index scans that are not full or fast full scans, along with table fetch by rowid, may also use direct-path reads but do not qualify for Smart Scans. Thus, even though storage access has been noted in the execution plan and direct-path read waits have been recorded, a Smart Scan may not be the access method used by Oracle to process the query or statement.
Exadata wait events that signal that Smart Scans were not used are cell multiblock physical read and cell single-block physical read. The cell multiblock physical read wait applies not only to tables but to LOBs where the chunk size is greater than the database block size and Securefile reads. When the chunk size is less than or equal to the database block size, cell single block physical reads will be used. As discussed in Chapter 7, the cell single block physical read can apply to index access paths, but because such waits do not occur during index full scans and index fast full scans, a Smart Scan was not executed.
Users care about time, with respect to response time, so the first area where performance tuning should begin is the Exadata wait interface. Seeing where time is being spent by queries and statements can help pinpoint areas of poor performance. Knowing where the time sinks are gives you initial direction in addressing such performance problems and can possibly prove that the database is not at fault.
The V$SYSSTAT and V$SESSTAT views provide a wealth of dynamic performance counters to aid you in diagnosing performance issues. Depending on where the session is spending its time, these views can help pinpoint areas that may require attention. As an example, if Oracle is using considerable CPU resources, one possible avenue of attack would be to see how the logical reads count increases for a given session. V$SYSSTAT provides the following list of read-related counters:
SQL> select name, value
2 from v$sysstat
3 where name like '%read%'
4 /
NAME VALUE
---------------------------------------------------------------- ---------------
session logical reads 82777757613
session logical reads in local numa group 0
session logical reads in remote numa group 0
physical read total IO requests 21591992
physical read total multi block requests 12063669
physical read requests optimized 3355758
physical read total bytes optimized 41508200448
physical read total bytes 11935356259328
logical read bytes from cache 669321109651456
physical reads 1456537556
physical reads cache 43060982
physical read flash cache hits 0
physical reads direct 1413476574
physical read IO requests 21382598
physical read bytes 11931955658752
recovery blocks read 0
recovery blocks read for lost write detection 0
physical reads direct temporary tablespace 26335818
DBWR thread checkpoint buffers written 11428910
recovery array reads 0
recovery array read time 0
physical reads cache prefetch 36901401
physical reads prefetch warmup 233120
physical reads retry corrupt 0
physical reads direct (lob) 14262
cold recycle reads 0
physical reads for flashback new 0
flashback cache read optimizations for block new 0
flashback direct read optimizations for block new 0
redo blocks read for recovery 0
redo k-bytes read for recovery 0
redo k-bytes read for terminal recovery 0
redo KB read 0
redo KB read (memory) 0
redo KB read for transport 0
redo KB read (memory) for transport 0
gc read wait time 373
gc read waits 5791
gc read wait failures 0
gc read wait timeouts 180
gc reader bypass grants 0
Number of read IOs issued 5714868
read-only violation count 0
Batched IO vector read count 1455
transaction tables consistent reads - undo records applied 262
transaction tables consistent read rollbacks 15
data blocks consistent reads - undo records applied 16126989
no work - consistent read gets 77857741416
cleanouts only - consistent read gets 19160349
rollbacks only - consistent read gets 187199
cleanouts and rollbacks - consistent read gets 1563685
table scans (direct read) 441631
lob reads 9246167
index fast full scans (direct read) 558680
securefile direct read bytes 0
securefile direct read ops 0
securefile inode read time 0
cell flash cache read hits 3355758
58 rows selected.
SQL>
V$SYSSTAT provides a view into the system-wide statistics, so pinpointing problems with a particular session will be difficult using this view. V$SESSTAT provides session-level statistics that will be much more useful when a particular session is a problem. Looking at the non-zero statistics for SID 1141 in one of our databases, we get the following output:
SQL> select sn.name, ss.value
2 from v$sesstat ss join v$statname sn on sn.statistic# = ss.statistic#
3 and ss.sid in (select sid from v$session where status ='ACTIVE')
4 and ss.value > 0
5 and ss.sid=1141
6 order by 1, 2
7 /
NAME VALUE
----------------------------------------- ---------------
cell flash cache read hits 246424
cell physical IO interconnect bytes 4037787648
cluster wait time 2
enqueue releases 246444
enqueue requests 246444
ges messages sent 758
in call idle wait time 13559183
logons cumulative 1
logons current 1
messages received 4
messages sent 34
non-idle wait count 2902944
non-idle wait time 57613
physical read requests optimized 246424
physical read total IO requests 246441
physical read total bytes 4037689344
physical read total bytes optimized 4037410816
physical write total IO requests 2
physical write total bytes 32768
session pga memory 7841624
session pga memory max 7841624
session uga memory 180736
session uga memory max 180736
user calls2
24 rows selected.
SQL>
There are many more statistics available for a given session; the query filtered out those with a 0 value.
When to Use Them, and How
Now that you know where to find useful performance metrics and wait statistics, the next step is knowing how and when to use them to diagnose performance issues. Despite all of the available statistics and metrics you can use to dig deep into performance issues, the main concern, and driving force, behind performance evaluation and tuning is time. Users measure performance in how long a task takes to complete once started, and all of the I/O statistics in the world won’t mean much to most of them. The first stop on the journey to performance is through an AWR report for the period in question. AWR provides information you’ll need to find problems and begin the diagnosis process.
AWR reports are generated from regularly occurring snapshots of performance metrics and wait-event timings. This provides a way to generate reports based on the changes recorded between snapshots. With the information provided in an AWR report, it’s possible to find long-running SQL statements, along with their associated SQL_id, so plans can be generated and analyzed. Additionally, the V$SESSTAT, V$SYSTEM_EVENT, and V$SYSTAT views can be used to drill down into the wait events and metrics to possibly uncover the underlying reason for the performance degradation. It’s good to look at the “SQL Ordered by Elapsed Time” report to see problem statements and queries. It’s not simply a matter of elapsed time but of elapsed time and number of executions, as long elapsed times, coupled with a high execution count, make the per-execution time small. For example, if the elapsed time is 7734.27 seconds and the number of executions is 1281, the average elapsed time per execution is around 6 seconds, a reasonable time by most standards. If, on the other hand, the elapsed time is 3329.17 and the number of executions is 3, then the average elapsed time per execution is approximately 1110 seconds. An elapsed time per execution of that magnitude is worthy of closer examination. This is where the V$SESSTAT, V$SYSTEM_EVENT, and V$SYSTAT views can provide additional detail.
Another good source of performance information is the 10046 trace; either in its raw form or in a report formatted by the tkprof utility. We often prefer tkprof-formatted output, as it provides an execution plan and the relevant wait events and times.
Note Wait information in a tkprof report is only provided when the 10046 trace event is set at level 8 or higher. Lower levels won’t capture the wait statistics.
Looking at a portion of a tkprof-formatted trace file where waits were provided, the following data is available:
********************************************************************************
SQL ID: 23bfbq45y94fk Plan Hash: 2983102491
DELETE FROM APP_RULE_TMP_STAGE
WHERE
TMP_RULE_NAME = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 22 9.38 125.83 25833 4732 493424 346425
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 23 9.38 125.83 25833 4732 493424 346425
Misses in library cache during parse: 1
Misses in library cache during execute: 4
Optimizer mode: ALL_ROWS
Parsing user id: 113 (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 DELETE APP_RULE_TMP_STAGE (cr=420 pr=3692 pw=0 time=26339915 us)
44721 44721 44721 INDEX RANGE SCAN APP_RULE_TMP_STG_IDX2 (cr=377 pr=353 pw=0 time=1603541 us cost=36 size=7291405 card=3583)(object id 196496)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
library cache lock 1 0.00 0.00
row cache lock 18 0.00 0.00
library cache pin 1 0.00 0.00
Disk file operations I/O 6 0.00 0.00
cell single block physical read 25783 1.30 114.29
gc cr grant 2-way 1587 0.00 0.18
gc current grant 2-way 14205 0.00 1.53
gc current grant congested 69 0.00 0.00
gc cr grant congested 14 0.00 0.00
cell list of blocks physical read 20 0.31 0.48
gc current multi block request 12 0.00 0.00
log buffer space 2 0.83 0.86
********************************************************************************
The wait of interest in this statement is cell single block physical read, given its total elapsed time, which is reported in seconds. The execution plan gives us a clue that this could be a wait to consider with the INDEX RANGE SCAN step. Remember that using an INDEX RANGE SCAN disqualifies the statement for Smart Scan execution. Notice also that no parallel execution is used. Given the elapsed time for the statement, it would qualify for Auto Degree of Parallelism. Using parallel execution would likely have improved performance for this statement, by spreading out the I/O across multiple parallel query slaves, reducing the elapsed wait time.
Let Me Explain
We have covered query plans in relation to Smart Scans in Chapter 2. A review of that material can always be helpful.
There are three plan steps that can indicate possible Smart Scan activity:
- TABLE ACCESS STORAGE FULL
- INDEX STORAGE FULL SCAN
- INDEX STORAGE FAST FULL SCAN
Earlier in this chapter, we mentioned that the word STORAGE in a plan step only indicates the possibility of Smart Scan execution, because without direct-path reads, Smart Scans won’t be triggered. Execution plans, however, can give you a good starting point for performance tuning. The absence of STORAGE in execution plan steps indicates that Smart Scans did not occur. If you examine the plan for a given statement and find the keyword STORAGE in one of the table or index access steps, you can use data found in V$SQL and GV$SQL to verify Smart Scan execution. As mentioned in Chapter 2, there are at least two columns in these views providing information on how beneficial the Smart Scan was to the query or statement. The two columns we covered are io_cell_offload_eligible_bytes and io_cell_offload_returned_bytes. We also supplied a query that can generate the I/O savings percentage realized, which was illustrated again earlier in this chapter, in the section “Performance Counters and Metrics.”
Performance Counter Reference
There are many counters updated by Exadata, but the complete list will not be discussed here. We have chosen to describe those counters that we feel are the most “interesting” in terms of performance and troubleshooting. It is very likely that you won’t have to dig this deep to solve most performance issues, as the wait interface and the SQL monitor, discussed in the next section, are usually sufficient diagnostic tools. It is good to be aware of these counters, which is why we provide them. We will list them by name and describe their purpose and the mechanisms behind them, to give you some insight into how Exadata does what it does.
cell blocks helped by commit cache
The commit cache is part of the consistent-read mechanism Oracle has used for years, except that this is implemented at the storage-cell level rather than the database-server level. As a review, consistent reads in a standard Oracle database on non-Exadata hardware involve reconstructing the row as of the time the query began, by using available undo data, based on the lock byte being set. Two conditions are possible during consistent-read processing, the first being that the commit SCN is lower than the snapshot SCN (established at the start time of the query). In this case, Oracle can determine that the data in the block needs no reconstruction and can continue processing other blocks.
The second condition is that the commit SCN is greater than the snapshot SCN, and in this case, Oracle knows a rollback is necessary to return consistent data and, on non-Exadata systems, does this through the available undo records. To complicate matters further, commits don’t always clean out every block that they touch; a threshold is set to limit block cleanout activity immediately after a commit. (This limit is 10 percent of the block buffer cache; any blocks in excess of that limit must wait for the next transaction that touches them to get cleaned out.)
This leaves data blocks in a state where the status of the transaction has to be established. Normally on non-Exadata systems, the database layer performs this processing. Imagine having to send these blocks back from the storage cells to the database layer to perform standard consistent-read processing via the undo records. For a few blocks, this might work on Exadata, but the possibility exists that an extremely large number of blocks would have to be processed in this manner. Performance would be slow, robbing Exadata of one of its key features. Because the undo data is not available to the storage cells (the storage cells have no access to the database buffer cache) and no storage cell communicates with any of the other storage cells, another mechanism has to be employed.
Exadata employs an optimization where the need for the database layer to process consistent-read requests is minimized. It is called the commit cache. The commit cache keeps track of which transaction ids have committed and which have not. By extracting the transaction id from the Interested Transaction List (ITL) in the data block, the storage cells can access this commit cache and see whether or not the referenced transaction has been committed. If information for that particular transaction id is not available in the commit cache, the storage cell requests that status from the database layer. Once received, it adds it to the commit cache, so the remaining storage cells need not make the request again.
Each time a storage cell finds transaction information it needs in the commit cache, the cell blocks helped by commit cache counter is increased by 1. Monitoring this counter during periods of high transactional activity can be very helpful when the wait interface and SQL monitor don’t provide enough data to diagnose a performance problem. Seeing this counter increase during Smart Scans indicates that delayed block cleanout is being performed by the cells, required because the volume of data in an insert, update, or delete is greater than the cleanout threshold. The commit cache was designed for this very purpose and keeps the storage cells from continuously communicating with the database layer. It also significantly reduces the logical I/O at the database layer during Smart Scans.
cell blocks helped by minscn optimization
Another Exadata-specific consistent-read optimization is the Minimum Active SCN optimization, which keeps track of the lowest SCN for still-active transactions. This improves consistent-read performance by allowing Exadata to compare the transaction SCNs from the ITL with the lowest SCN of the oldest still-active transaction in the database. The database layer sends this information to the cell at the start of a Smart Scan operation. When the commit SCN is lower than the Minimum Active SCN passed to the cells, unnecessary communication between the database layer and the cells is avoided. Any transaction SCN found in the block’s ITL that is lower than the Minimum Active SCN is known by the storage cell to be committed. Each block that gets helped by this mechanism increments the cell blocks helped by minscn optimization counter. With reference to performance, this optimization also reduces checks to the commit cache discussed previously. Because this is Exadata, and most likely the databases run on it are RAC databases, the Minimum Active SCN is RAC-aware. Actually known as the Global Minimum SCN, the MMON processes in each instance keep track of this SCN and update each node’s SGA. The X$TUMASCN table contains the current Global Minimum SCN for the RAC cluster, as follows:
SQL> select min_act_scn
2 from x$ktumascn;
MIN_ACT_SCN
-----------
20695870
SQL>
The cell blocks helped by minscn optimization counter isn’t one you will use on a regular basis, if at all. However, it is a good place to start looking when Smart Scans are interrupted by frequently reverting to block I/O to get transaction information from the database layer.
cell commit cache queries
This counter is incremented each time a Smart Scan queries the cell commit cache for a transaction status. This is done once for each uncommitted transaction found per block when the MinActiveSCN optimization didn’t apply to the transaction SCN, meaning the transaction SCN was greater than or equal to the Minimum Active SCN. This counter and the cell blocks helped by minscn counter are closely related.
cell blocks processed by cache layer
This is one of four “layer” statistics collected by Exadata, reporting on the activity of the listed layer of processing. This particular statistic reports on the number of blocks actually processed by the storage cells for Smart Scan operations. When the cells pass back blocks to the database servers (block I/O mode), this statistic doesn’t get incremented. It does get incremented when the cells actually process the blocks during Smart Scans. When a cell opens a block for a consistent read, the block cache header is checked, to ensure that the correct block is being read and that it is valid and not corrupted. The cache-layer process (Kernel Cache Buffer management, or KCB) performs these functions and reports the cell blocks processed by cache layer count back to the database.
When the database layer processes regular block I/O, one of two statistics, consistent gets from cache and consistent gets from cache (fastpath), can be updated. The storage cells, through CELLSRV, perform only consistent gets, not current mode gets. All current mode gets that are recorded in the db block gets from cache or the db block gets from cache (fastpath) come from the database layer. Any counts recorded in the cell blocks processed by cache layer counter provide data on how many logical reads the storage cells performed, either system-wide from V$SYSSTAT or at the session level through V$SESSTAT.
It is not unusual to find both database-layer and cell-layer processing in a query-execution plan. Smaller tables won’t likely qualify for a Smart Scan and, thus, will be processed by the database server. Multi-table joins using both small lookup tables and large processing tables can produce such plans.
cell blocks processed by data layer
While the previous counter registered cell cache read activity, this statistic records physical blocks read from tables or materialized views. The data layer module, Kernel Data Scan (KDS), extracts rows and columns from data blocks, passing them on for predicate filtering and column projection. When the storage cell can perform all of the necessary work without database block I/O, this counter is incremented.
When the session-level count from this statistic is added to the session-level cell blocks processed by index layer count, you can determine if the storage cell did all of the consistent reads. The sum of those counters will equal the session-level count for the cell blocks processed by cache layer counter, if every block processed was read at the storage layer. If the sum is a lower number, then the database layer performed regular block I/O processing. The difference should be the number of blocks processed by the database servers.
cell blocks processed by index layer
The storage cells can process index blocks in a similar manner as table and materialized view blocks. This statistic records the number of index blocks processed by a Smart Scan of B*Tree and bitmap index segments.
cell blocks processed by txn layer
Because this counter records the number of blocks processed by the transaction layer, it will be beneficial to know what goes on in that layer. Looking at a basic explanation of how Smart Scans process consistent reads, let’s go through and see what occurs during this process.
The cache layer, through KCB, opens the block and then checks the header, the SCN of the last modification, and the cleanout status. The transaction layer gets this block if the SCN of the last modification isn’t greater than the snapshot SCN, because it hasn’t been modified since the query started. If the SCN of the last modification is greater than the snapshot SCN, this indicates the block has been modified since the query started, requiring a rollback to the snapshot SCN. This causes the block to be passed back to the database layer for block I/O processing.
Should the block pass the first test, it is passed to the transaction layer to be processed by the Kernel Transaction Read Consistency process (KTR), which uses the Minimum Active SCN and the commit cache, reducing the amount of communication with the database layer. The transaction layer then performs processing of the data block to extract the requested information, in concert with the data and index layers, which are used to perform the consistent reads at the storage-cell level.
This counter was discussed in Chapter 4, so we’ll only briefly mention it here. This is another cumulative metric, at both the session level and the instance level. The easiest way to monitor this is with a script we have provided in Chapter 4. We provide it here as well, as monitoring the Smart Flash Cache is a key part of performance troubleshooting.
SQL> select statistic#, value
2 from v$mystat
3 where statistic# in (select statistic# from v$statname where name = 'cell flash cache read hits'),
STATISTIC# VALUE
---------- ----------
605 1
SQL>
SQL> select count(*)
2 from emp;
COUNT(*)
----------
7340032
SQL>
SQL> column val new_value endval
SQL>
SQL> select statistic#, value val
2 from v$mystat
3 where statistic# in (select statistic# from v$statname where name = 'cell flash cache read hits'),
STATISTIC# VAL
---------- ----------
605 857
SQL>
SQL>
SQL> select &endval - &beginval flash_hits
2 from dual;
FLASH_HITS
----------
856
SQL>
This counter also provides the number of physical I/O requests, because accessing the Smart Flash Cache is physical, not logical, I/O.
Each time a Smart Scan starts on a B*Tree or bitmap index segment, this counter is incremented. As this requires the use of the index fast full-scan step in the execution plan and also must use direct-path reads, both of these conditions must be met for this counter to be incremented.
A Smart Scan is a segment-level operation, so a query running serially, on a non-partitioned index, will increment once for each of the index segments accessed. For partitioned index segments accessed in parallel, this counter can increase once for each partitioned segment accessed by each parallel query slave. Because partition segments can differ in size, it is possible that, for a given partitioned index, the segments can be accessed by different methods. Larger segments are likely to be accessed by direct-path reads, and, thus, this counter will be incremented. Smaller partitions may not trigger direct-path reads, and in those cases, Smart Scans won’t be executed and the counter value won’t increase.
If you are not using compression, you won’t see this statistic change, as it’s only incremented when Smart Scan compression offloading occurs. This statistic reports the actual uncompressed size of the compressed data. If, for example, you have 10MB of compressed data that would be 20MB when uncompressed, the cell IO uncompressed bytes counter would increment by 20MB when that data is offloaded. The physical read total bytes counter would increment by 10MB, because that is the segment size. It isn’t a problem to see the cell IO uncompressed bytes counter increase at a faster rate than the physical read total bytes counter, because, as noted, the physical read total bytes counter increments according to the segment size read, and the cell IO uncompressed bytes counter reports the total uncompressed bytes processed.
cell num fast response sessions
Oracle can elect to defer a full Smart Scan execution by choosing to perform a few block I/O operations at the outset, in an attempt to satisfy a query. FIRST_ROWS, FIRST_ROWS_n, and where rownum queries trigger this behavior, and when Oracle decides to not immediately execute a Smart Scan, this counter is incremented. To improve the efficiency and speed of queries, Oracle can avoid setting up a Smart Scan, in favor of a few block I/O operations, to satisfy a query. This counter reports how many times Oracle has done that since the instance was started. Just because this counter was incremented doesn’t mean that a full Smart Scan wasn’t executed, simply that Oracle tried to do as little work as possible to return the desired results.
cell num fast response sessions continuing to smart scan
After Oracle decides to defer running a Smart Scan to reduce the possible work done, it may be decided that to return the correct results to the calling session, a Smart Scan must be executed. When that switch is made, this counter is incremented. Because this occurs after Oracle initially decides to not run a full Smart Scan, the same triggering conditions apply—FIRST_ROWS, FIRST_ROWS_n, and where rownum queries are being run. Also, when this statistic is incremented, you may notice some db file sequential read waits. Even though most reads of this nature are reported as cell single block physical read wait events, the older named event can still be incremented on Exadata.
cell num smart IO sessions using passthru mode due to _______
There are three of these counters, where ______ can be either user, CELLSRV, or time zone. These counters indicate how many times a Smart Scan was initiated but converted to regular block I/O mode. When this happens, the blocks are passed through to the database servers and bypass cell processing. Smart Scan is actually reading the blocks but doing no local cell processing on them, sending them on to the database server for processing. Unless there are memory issues on the cells, you shouldn’t see these counters increase, as pass-through processing shouldn’t be occurring. If these counters start incrementing, it is a good sign that there are issues at the storage-cell level that need to be investigated.
cell physical IO bytes sent directly to DB node to balance CPU usage
This counter records how many times the cell CPU usage was so high that Oracle chose to fall back to pass-through mode, making the database servers perform regular block I/O to reduce the CPU load on the storage cells. Normally, you shouldn’t see this counter increase. Again, if it does start incrementing, it’s a sign of problems at the storage-cell level that have to be diagnosed. In older versions of Exadata, this counter was named cell physical IO bytes pushed back due to excessive CPU on cell.
chained rows processed by cell
Chained rows can be a problem in any database but especially so in Exadata, where disk groups are striped across all available disks and accessed by every available storage cell. In a non-Exadata database not using ASM, a chained or continued row will be in another block but will likely be on the same disk as the head of the chain. With ASM, and Exadata, the next piece of the row could be on another disk accessed by another storage cell. Remember that the storage cells do not communicate with each other, so when Oracle encounters a chained row, a Smart Scan will fall back to regular block I/O at the database layer, so the chained row can be processed. This is more efficient for chained-row processing, but it does slow down Smart Scans. The more chained rows there are, the slower the Smart Scan will be. If a Smart Scan can get the data it needs from the head row piece, then the Smart Scan won’t lose any speed, and processing will appear to flow as it normally does. Should Oracle need to access any other piece of that chained row, then regular block I/O will be necessary, slowing down the Smart Scan.
This statistic, chained rows processed by cell, reports on the chained rows that were processed within a storage cell, that is, where the row pieces (or at least the ones Oracle needed to return results) are located within the storage accessed by a given storage cell. This is known as inter-block chaining, where the row piece is located in the same data block as the head row. This is a special case of row chaining for rows with more than 255 columns. The next piece is in the same block as the head and can be fetched with no additional effort.
chained rows rejected by cell
This counter records the chained rows where the next piece is not in the same block or cell, as described in the previous section. This statistic is incremented in, apparently, special cases, as we have not seen it incremented very often in the systems we manage. When this is incremented, the Smart Scan falls back to regular block I/O to process the row.
chained rows skipped by cell
This is the statistic most often incremented when a Smart Scan has to revert to regular block I/O. Again, it’s incremented when a chained row is found and the remaining pieces reside across the entire storage stripe, spanning storage cells in the process. It isn’t clear when the previous counter or this counter should be incremented in such situations. We have found that this counter is most often incremented when Smart Scans revert to regular block I/O to process chained rows.
SQL Statement Performance
It is important to know where an SQL statement spends its time during execution. V$SQL, V$SQL_MONITOR, V$SQL_PLAN_MONITOR, and V$ACTIVE_SESSION_HISTORY are good views to use to analyze SQL statement performance. An example using V$SQL follows, showing how the query benefited from Smart Scan optimizations:
SQL>select sql_id,
2 io_cell_offload_eligible_bytes qualifying,
3 io_cell_offload_eligible_bytes - io_cell_offload_returned_bytes actual,
4 round(((io_cell_offload_eligible_bytes - io_cell_offload_returned_bytes)/io_cell_offload_eligible_bytes)*100, 2) io_saved_pct,
5 sql_text
6 from v$sql
7 where io_cell_offload_returned_bytes> 0
8 and instr(sql_text, 'emp') > 0
9 and parsing_schema_name = 'BING';
SQL_ID QUALIFYING ACTUAL IO_SAVED_PCT SQL_TEXT
------------- ---------- ---------- ------------ -------------------------------------
gfjb8dpxvpuv6 185081856 42510928 22.97 select * from emp where empid = 7934
SQL>
Note the following columns used from V$SQL:
- io_cell_offload_eligible_bytes
- io_cell_offload_returned_bytes
These two columns tell you how many bytes qualified for offload and how many bytes were actually returned as a result of Smart Scan offloading. The query provided computes the I/O bytes saved as a percentage of the total eligible bytes, so you can see how efficient the Smart Scan execution was for the query in question.
Things to Know
The intent of this chapter was not an exhaustive performance-tuning reference for Exadata, but, rather, an overview of what metrics and counters are available, what they mean, and when you should consider using them.
The cell metrics provide insight into statement performance and how Exadata executes statements. Dynamic counters such as cell blocks processed by data layer and cell blocks processed by index layer show how efficient the storage cells are in their processing. These counters are incremented every time a storage cell can complete data-layer and index-layer processing without passing data back to the database layer. Two reasons for the cells to pass data back to the database servers are consistent-read processing requiring undo blocks (regular block I/O) and chained-row processing where the chained pieces span storage cells. While the first condition can’t be controlled entirely (transactions can be so large as to exceed the automatic block-cleaning threshold), the second, chained rows, can be addressed and possibly corrected.
The cell num fast response sessions and cell num fast response sessions continuing to smart scan counters reveal the number of times Oracle chose to defer a Smart Scan in favor of regular block I/O as an attempt to return the requested data with a minimum of work (the first listed counter) and how many times Oracle actually started a Smart Scan after the fast response session failed to return the requested data. These can give you insight into the nature of the statements submitted to your Exadata databases, by showing how often Smart Scans were averted because a small regular block I/O operation returned the requested data.
V$SQL also provides data that can be used to determine statement efficiency in the io_cell_offload_eligible_bytes and io_cell_offload_returned_bytes columns. These two columns can be used to calculate the percentage savings effected by a Smart Scan for a given query.