Exadata Cell Wait Events
The Oracle wait interface was first expanded in Oracle Release 8.1.x, to provide a window into Oracle performance. In subsequent releases, this interface has been improved and expanded, making Oracle a very well-instrumented database. Keeping tabs on where Oracle spends its time and how much time it consumes per event, the wait interface is an invaluable tool with which the database administrator (DBA) can diagnose performance issues. With Exadata, this wait interface has been expanded even further to include Exadata-specific wait events in support of the operations unique to this system. These events, and the actual operations they monitor, will be covered in this chapter. We will also cover existing, non-Exadata wait events that apply to Exadata operations.
What is a wait event ? It is an event the database is waiting on, such as a disk read, that is timed and given a name. In release 11.2.0.3, there are 1150 named wait events listed in the V$EVENT_NAME view, 23 of which reference Exadata-specific operations. Of those, 17 are specific to the storage cells. These are the events we will cover in this chapter.
Available Nowhere Else
Because Exadata is using the same Oracle software available on non-Exadata systems, the list of wait events is the same, whether or not you’re using Exadata. The difference is that some of these events are populated only on Exadata systems, which is the only reason they are available nowhere else.
Cell Events
Cell events are, obviously, events that will not be populated on non-Exadata systems or systems not using Exadata storage. There are 17 cell-related events in V$EVENT_NAME. These are listed in Table 7-1.
Table 7-1. Cell Wait Events and Classes
| Cell worker idle | Idle |
| Cell manager cancel work request | Other |
| Cell smart flash unkeep | Other |
| Cell worker online completion | Other |
| Cell worker retry | Other |
| Cell manager closing cell | System I/O |
| Cell manager discovering disks | System I/O |
| Cell manager opening cell | System I/O |
| Cell smart incremental backup | System I/O |
| Cell smart restore from backup | System I/O |
| Cell list of blocks physical read | User I/O |
| Cell multiblock physical read | User I/O |
| Cell single block physical read | User I/O |
| Cell smart file creation | User I/O |
| Cell smart index scan | User I/O |
| Cell smart table scan | User I/O |
| Cell statistics gather | User I/O |
These events will be covered in detail in a later section of this chapter.
It is interesting to see which operations trigger the various cell wait events. This information is available in the DBA_HIST_ACTIVE_SESS_HISTORY view, as well as in V$SYSTEM_EVENT. It is likely that not every event listed in Table 7-1 will be reported in DBA_HIST_ACTIVE_SESS_HISTORY; however, one event may be triggered by a number of plan operations. The query we used to generate a list of events and associated operations follows:
SQL> select event , non_exa_event, operation ,count(*)
2 from
3 (select event,
4 decode(event, 'cell smart table scan','',
5 'cell smart index scan','',
6 'cell statistics gather','',
7 'cell smart incremental backup','',
8 'cell smart file creation','',
9 'cell smart restore from backup','',
10 'cell single block physical read','db file sequential read',
11 'cell multiblock physical read','db file scattered read',
12 'cell list of blocks physical read','db file parallel read',
13 'cell manager opening cell','',
14 'cell manager closing cell','',
15 'cell manager discovering disks','',
16 'cell worker idle','',
17 'cell smart flash unkeep','',
18 'cell worker online completion','',
19 'cell worker retry','',
20 'cell manager cancel work request','') non_Exa_event,
21 sql_plan_operation||' '||sql_plan_options operation
22 from dba_hist_active_sess_history
23 where event like 'cell%')
24 where operation <> ' '
25 group by event, non_exa_event, operation
26 order by 1
27 /
The listing also contains the equivalent non-Exadata event, if one exists. In the following section, these events, their equivalent non-Exadata events, their description, and various parameters they supply will be discussed.
Each of these events is associated with a wait class; there are four distinct wait classes for the cell events. The most populous wait class for cell events is the User I/O class, with seven of the seventeen events in this class. We will look at each event in that class in this section and see what it reports.
The cell smart table scan event records time against full-table scans that are offloaded to the storage cells. Because it can be used to determine if a statement utilized a Smart Scan, and thus benefited from offloading, it may be the most important wait event in the Exadata system. Remember that offloading only occurs during direct-path reads, so this event will, in general, replace the direct path read event. This is not to say that you won’t see a direct path read event on an Exadata system, but it is much more likely to see time recorded against the cell smart table scan event. As this is a direct-path read, it returns the data directly to the program global area (PGA) of the user process requesting it, which includes parallel slave processes.
The use of an InfiniBand network changes the actual mechanism for direct-path reads, in comparison to non-Exadata systems. Each direct-path read request to the storage cells also contains a reference to the select list, join predicates—if they exist—and the query predicates. This allows the storage servers to filter the data and perform column projection before “handing over” the results to the database servers. Each data block processed by a Smart Scan has these optimizations applied to it; the database server processes that request. The data also have access to the ASM extent map, so they can request the necessary allocation units, or AUs, from each storage cell. The storage cells then read these allocation units in the order of request and apply the Smart Scan optimizations to each AU, returning the projected columns from rows that satisfy the supplied filter criteria. As expected, this event will occur repeatedly when large scans are executed.
A 10046 trace is a good source for seeing this event recorded. We provide a small section of a large table scan, operating in parallel, as follows:
WAIT #46969064935512: nam='cell smart table scan' ela= 794053 cellhash#=88802347 p2=0 p3=0 obj#=133235 tim=1371608209311673
WAIT #46969064935512: nam='cell smart table scan' ela= 151 cellhash#=88802347 p2=0 p3=0 obj#=133235 tim=1371608209317400
WAIT #46969064935512: nam='PX Deq: Execution Msg' ela= 80 sleeptime/senderid=268566527 passes=1 p3=7325501352 obj#=133235 tim=1371608209321134
WAIT #46969064935512: nam='cell smart table scan' ela= 116 cellhash#=88802347 p2=0 p3=0 obj#=133235 tim=1371608209321618
WAIT #46969064935512: nam='cell smart table scan' ela= 39049 cellhash#=88802347 p2=0 p3=0 obj#=133235 tim=1371608209360713
WAIT #46969064935512: nam='PX Deq: Execution Msg' ela= 138 sleeptime/senderid=268566527 passes=1 p3=7325501352 obj#=133235 tim=1371608209361123
WAIT #46969064935512: nam='cell smart table scan' ela= 126 cellhash#=398250101 p2=0 p3=0 obj#=133235 tim=1371608209361740
WAIT #46969064935512: nam='cell smart table scan' ela= 39318 cellhash#=398250101 p2=0 p3=0 obj#=133235 tim=1371608209401129
WAIT #46969064935512: nam='PX Deq: Execution Msg' ela= 84 sleeptime/senderid=268566527 passes=1 p3=7325501352 obj#=133235 tim=1371608209401312
WAIT #46969064935512: nam='cell smart table scan' ela= 100 cellhash#=398250101 p2=0 p3=0 obj#=133235 tim=1371608209401875
WAIT #46969064935512: nam='cell smart table scan' ela= 44604 cellhash#=398250101 p2=0 p3=0 obj#=133235 tim=1371608209446549
WAIT #46969064935512: nam='PX Deq: Execution Msg' ela= 76 sleeptime/senderid=268566527 passes=1 p3=7325501352 obj#=133235 tim=1371608209446719
WAIT #46969064935512: nam='cell smart table scan' ela= 120 cellhash#=2520626383 p2=0 p3=0 obj#=133235 tim=1371608209447214
WAIT #46969064935512: nam='cell smart table scan' ela= 223022 cellhash#=2520626383 p2=0 p3=0 obj#=133235 tim=1371608209670288
WAIT #46969064935512: nam='PX Deq: Execution Msg' ela= 82 sleeptime/senderid=268566527 passes=1 p3=7325501352 obj#=133235 tim=1371608209670463
WAIT #46969064935512: nam='cell smart table scan' ela= 103 cellhash#=2520626383 p2=0 p3=0 obj#=133235 tim=1371608209670967
WAIT #46969064935512: nam='cell smart table scan' ela= 5520 cellhash#=2520626383 p2=0 p3=0 obj#=133235 tim=1371608209676590
WAIT #46969064935512: nam='PX Deq: Execution Msg' ela= 90 sleeptime/senderid=268566527 passes=1 p3=7325501352 obj#=133235 tim=1371608209676760
WAIT #46969064935512: nam='cell smart table scan' ela= 161 cellhash#=398250101 p2=0 p3=0 obj#=133235 tim=1371608209677348
WAIT #46969064935512: nam='cell smart table scan' ela= 43339 cellhash#=398250101 p2=0 p3=0 obj#=133235 tim=1371608209720745
WAIT #46969064935512: nam='PX Deq: Execution Msg' ela= 113 sleeptime/senderid=268566527 passes=1 p3=7325501352 obj#=133235 tim=1371608209720974
WAIT #46969064935512: nam='cell smart table scan' ela= 132 cellhash#=2520626383 p2=0 p3=0 obj#=133235 tim=1371608209721594
WAIT #46969064935512: nam='cell smart table scan' ela= 9462 cellhash#=2520626383 p2=0 p3=0 obj#=133235 tim=1371608209731125
WAIT #46969064935512: nam='PX Deq: Execution Msg' ela= 102 sleeptime/senderid=268566527 passes=1 p3=7325501352 obj#=133235 tim=1371608209731327
WAIT #46969064935512: nam='cell smart table scan' ela= 84 cellhash#=2520626383 p2=0 p3=0 obj#=133235 tim=1371608209732040
WAIT #46969064935512: nam='cell smart table scan' ela= 19 cellhash#=88802347 p2=0 p3=0 obj#=133235 tim=1371608209732081
Notice this section also includes a number of 'PX Deq: Execution Msg' events, proof of the parallel processing utilized during the query execution.
Parameters supplied by this event provide only the object id (obj#) and the cell hash number (cellhash#). Parameters P2 and P3 are unused. In contrast, the direct path-read event provides the object_id, the file number, the offset into the file (first dba), and the number of contiguous blocks read (block cnt), shown as follows:
WAIT #47901347109608: nam='direct path read' ela= 355651 file number=1 first dba=1474969 block cnt=3 obj#=243 tim=1371659720952304
WAIT #47901347109608: nam='direct path read' ela= 59 file number=1 first dba=1474980 block cnt=4 obj#=243 tim=1371659720952562
WAIT #47901347109608: nam='direct path read' ela= 16812 file number=1 first dba=1474984 block cnt=4 obj#=243 tim=1371659720969412
Note that on Exadata systems implementing direct-path reads (when the query or statement does not qualify for Smart Scan execution), there is no cell information provided, so it’s not known which cell received and processed which request.
In the same manner as the cell smart table scan, the cell smart index scan records time against fast full-index scans that are offloaded to the storage cells, the only real difference being the object type being scanned.
On one of the most active systems, we have a total of 1184 cell smart table scans recorded in DBA_HIST_ACTIVE_SESS_HISTORY, while during that same time period only 8 cell smart index scans were reported. This may be due to the propensity of Oracle 11.2 to perform direct-path reads on serial table scans. There is a hidden parameter, _serial_direct_read, normally set to FALSE, that can be set to TRUE to favor this activity. Setting this event may create performance issues in heavily used production systems, so it’s best to test this on a nonproduction system, using an environment as close to production as possible. Note that the parameter only specifies serial direct reads, not the object those reads operate upon, so indexes can also utilize this serial-read mechanism. Because indexes tend to be smaller than the tables they index, it’s a good possibility their smaller size precludes use of the cell smart index scan event. Also, only fast full-index scans are eligible for cell smart index scans; index range scans and index full scans do not qualify.
In the same manner as the cell smart table scan event, the cell smart index scan event doesn’t provide much information; only the cell hash number (cellhash#) and the object_id (obj#) are found in a 10046 trace file, with parameters P2 and P3 again unused.
WAIT #46969064936345: nam='cell smart index scan' ela= 178 cellhash#=2520626383 p2=0 p3=0 obj#=448299 tim=1371608209741332
WAIT #46969064936345: nam='cell smart index scan' ela= 462 cellhash#=2520626383 p2=0 p3=0 obj#=448299 tim=1371608209752118
Cell Single-Block Physical Read
This event is the Exadata equivalent of the db file sequential read event on non-Exadata systems. These are usually indicative of index access operations, including index block reads and table block reads by rowids provided from the index lookups, but other operations can also use this event when accessing single blocks.
Operations that generate the cell single block physical read event can be many, as shown by the following output from DBA_HIST_ACTIVE_SESS_HISTORY:
The most common event in the preceding list is index access, accounting for 11 of the listed entries. Add to that the total occurrences of these events, and it’s even more obvious that the most likely operation to mark time against this event is index access.
This event provides more information than the other events discussed thus far, reporting the cell hash number (cellhash#), the disk hash number (diskhash#), the total bytes passed during the operation (bytes), and the object_id (obj#). With this event, you know not only which object was accessed but from which disk, which cell, and how many bytes were read. The following is a small part of the output from grep on a 10046 trace file that shows these reported values:
WAIT #47185678513912: nam='cell single block physical read' ela= 13725 cellhash#=398250101 diskhash#=2110579711 bytes=8192 obj#=134196 tim=1364280947867845
WAIT #47185663242504: nam='cell single block physical read' ela= 562 cellhash#=398250101 diskhash#=736840779 bytes=8192 obj#=21 tim=1364280948818774
WAIT #47185660188840: nam='cell single block physical read' ela= 516 cellhash#=398250101 diskhash#=592573387 bytes=8192 obj#=69 tim=1364280948820595
WAIT #47185681467920: nam='cell single block physical read' ela= 518 cellhash#=398250101 diskhash#=323658058 bytes=8192 obj#=62 tim=1364280948828550
WAIT #47185664116080: nam='cell single block physical read' ela= 593 cellhash#=2520626383 diskhash#=1392289187 bytes=8192 obj#=61 tim=1364280948829493
WAIT #47185671820512: nam='cell single block physical read' ela= 520 cellhash#=2520626383 diskhash#=1878859599 bytes=8192 obj#=54 tim=1364280948832544
WAIT #47185672026856: nam='cell single block physical read' ela= 536 cellhash#=88802347 diskhash#=1487722174 bytes=8192 obj#=37 tim=1364280948855726
WAIT #47185676137528: nam='cell single block physical read' ela= 505 cellhash#=398250101 diskhash#=949169843 bytes=8192 obj#=75 tim=1364280948856712
WAIT #47185663821624: nam='cell single block physical read' ela= 504 cellhash#=2520626383 diskhash#=372821781 bytes=8192 obj#=106 tim=1364280948857502
WAIT #47185663821624: nam='cell single block physical read' ela= 510 cellhash#=88802347 diskhash#=2410699693 bytes=8192 obj#=104 tim=1364280948858089
WAIT #47185671360600: nam='cell single block physical read' ela= 484 cellhash#=398250101 diskhash#=4004546112 bytes=8192 obj#=108 tim=1364280948859019
WAIT #47185671360600: nam='cell single block physical read' ela= 508 cellhash#=2520626383 diskhash#=3373534499 bytes=8192 obj#=108 tim=1364280948859781
WAIT #47185663242504: nam='cell single block physical read' ela= 499 cellhash#=398250101 diskhash#=736840779 bytes=8192 obj#=21 tim=1364280948860521
WAIT #47185671976024: nam='cell single block physical read' ela= 519 cellhash#=88802347 diskhash#=2410699693 bytes=8192 obj#=4 tim=1364280948873162
WAIT #47185678747440: nam='cell single block physical read' ela= 505 cellhash#=88802347 diskhash#=3487034449 bytes=8192 obj#=427 tim=1364280948876978
WAIT #47185678747440: nam='cell single block physical read' ela= 504 cellhash#=88802347 diskhash#=351342063 bytes=8192 obj#=425 tim=1364280948877576
This event was renamed for Exadata systems, as the non-Exadata event is named db file scattered read , which is descriptive but not entirely accurate, as the blocks read may not be scattered but contiguous. Oracle 11.2 still reports multiblock reads using the db file scattered read event on non-Exadata systems, regardless of whether they are reading contiguous or scattered blocks. For Exadata systems, this event records time for contiguous multiblock reads.
A partial list of the operations that trigger this wait event follows:
The output of processing another 10046 trace file with grep follows, showing how this event is reported:
WAIT #47901352100880: nam='cell multiblock physical read' ela= 640 cellhash#=398250101 diskhash#=4004546112 bytes=65536 obj#=631 tim=1371659723908619
WAIT #47901352100880: nam='cell multiblock physical read' ela= 844 cellhash#=398250101 diskhash#=4004546112 bytes=65536 obj#=631 tim=1371659723909603
WAIT #47901352100880: nam='cell multiblock physical read' ela= 632 cellhash#=398250101 diskhash#=4004546112 bytes=65536 obj#=631 tim=1371659723910387
WAIT #47901352100880: nam='cell multiblock physical read' ela= 620 cellhash#=398250101 diskhash#=4004546112 bytes=65536 obj#=631 tim=1371659723911140
WAIT #47901352100880: nam='cell multiblock physical read' ela= 639 cellhash#=398250101 diskhash#=4004546112 bytes=65536 obj#=631 tim=1371659723911925
WAIT #47901352100880: nam='cell multiblock physical read' ela= 653 cellhash#=398250101 diskhash#=2110579711 bytes=65536 obj#=631 tim=1371659723912743
WAIT #47901352100880: nam='cell multiblock physical read' ela= 960 cellhash#=398250101 diskhash#=2110579711 bytes=65536 obj#=631 tim=1371659723913842
WAIT #47901352100880: nam='cell multiblock physical read' ela= 841 cellhash#=398250101 diskhash#=2110579711 bytes=65536 obj#=631 tim=1371659723914879
WAIT #47901352100880: nam='cell multiblock physical read' ela= 630 cellhash#=398250101 diskhash#=2110579711 bytes=65536 obj#=631 tim=1371659723915692
WAIT #47901352100880: nam='cell multiblock physical read' ela= 726 cellhash#=398250101 diskhash#=2110579711 bytes=65536 obj#=631 tim=1371659723916608
WAIT #47901352100880: nam='cell multiblock physical read' ela= 820 cellhash#=398250101 diskhash#=2110579711 bytes=65536 obj#=631 tim=1371659723917621
WAIT #47901352100880: nam='cell multiblock physical read' ela= 687 cellhash#=398250101 diskhash#=2110579711 bytes=65536 obj#=631 tim=1371659723918461
WAIT #47901352100880: nam='cell multiblock physical read' ela= 757 cellhash#=398250101 diskhash#=2110579711 bytes=65536 obj#=631 tim=1371659723919396
WAIT #47901352100880: nam='cell multiblock physical read' ela= 13061 cellhash#=398250101 diskhash#=323658058 bytes=1048576 obj#=631 tim=1371659723933281
WAIT #47901352100880: nam='cell multiblock physical read' ela= 11539 cellhash#=2520626383 diskhash#=1346328363 bytes=1048576 obj#=631 tim=1371659723946098
WAIT #47901352100880: nam='cell multiblock physical read' ela= 14240 cellhash#=88802347 diskhash#=493540951 bytes=1048576 obj#=631 tim=1371659723964141
WAIT #47901352100880: nam='cell multiblock physical read' ela= 7785 cellhash#=88802347 diskhash#=4062498200 bytes=163840 obj#=631 tim=1371659723973491
WAIT #47901352100880: nam='cell multiblock physical read' ela= 597 cellhash#=398250101 diskhash#=949169843 bytes=32768 obj#=997 tim=1371659723976483
This event reports the same information as the cell single-block physical read event, that is, the cell hash (cellhash#), disk hash (diskhash#), total bytes passed (bytes), and the object_id (obj#).
Cell List of Blocks Physically Read
Originally called db file parallel read, on Exadata systems it was renamed to more accurately reflect the operations that trigger it. However, on non-Exadata systems, the originally named event is the one to which time is recorded. Interestingly, this event has nothing to do with parallel data manipulation language (DML) operations.
This event records time for multi-block reads when the blocks are not contiguous. This makes it easier to distinguish from contiguous multiblock reads. Again, a number of plan operations can trigger this event, as the following list shows:
The four most “popular” plan operations on one of our databases are TABLE ACCESS BY INDEX ROWID , INDEX RANGE SCAN , INDEX UNIQUE SCAN , and TABLE ACCESS STORAGE FULL , in order of frequency.
Looking again at a 10046 trace file, it can be seen how this event is reported in the following output from grep:
WAIT #47901352100880: nam='cell list of blocks physical read' ela= 537 cellhash#=398250101 diskhash#=2110579711 blocks=3 obj#=997 tim=1371659723985684
WAIT #47901352100880: nam='cell list of blocks physical read' ela= 574 cellhash#=398250101 diskhash#=2110579711 blocks=4 obj#=997 tim=1371659723988550
WAIT #47901352100880: nam='cell list of blocks physical read' ela= 1723 cellhash#=398250101 diskhash#=323658058 blocks=46 obj#=997 tim=1371659723998837
WAIT #47901352100880: nam='cell list of blocks physical read' ela= 1208 cellhash#=2520626383 diskhash#=1346328363 blocks=36 obj#=997 tim=1371659724003584
WAIT #47901352100880: nam='cell list of blocks physical read' ela= 1567 cellhash#=2520626383 diskhash#=4245410826 blocks=47 obj#=997 tim=1371659724010794
WAIT #47901352100880: nam='cell list of blocks physical read' ela= 632 cellhash#=398250101 diskhash#=4080537574 blocks=4 obj#=997 tim=1371659724013129
WAIT #47901344458904: nam='cell list of blocks physical read' ela= 33814 cellhash#=398250101 diskhash#=949169843 blocks=27 obj#=447547 tim=1371659793327488
WAIT #47901354882040: nam='cell list of blocks physical read' ela= 702 cellhash#=398250101 diskhash#=949169843 blocks=7 obj#=4 tim=1371659807119269
WAIT #47901354882040: nam='cell list of blocks physical read' ela= 715 cellhash#=398250101 diskhash#=323658058 blocks=8 obj#=4 tim=1371659807144991
WAIT #47901354882040: nam='cell list of blocks physical read' ela= 40454 cellhash#=398250101 diskhash#=4080537574 blocks=61 obj#=4 tim=1371659807259159
WAIT #47901354882040: nam='cell list of blocks physical read' ela= 1135 cellhash#=398250101 diskhash#=4080537574 blocks=17 obj#=4 tim=1371659807265443
WAIT #47901354882040: nam='cell list of blocks physical read' ela= 805 cellhash#=398250101 diskhash#=4080537574 blocks=12 obj#=4 tim=1371659807266892
WAIT #47901354882040: nam='cell list of blocks physical read' ela= 198850 cellhash#=88802347 diskhash#=2443481431 blocks=57 obj#=4 tim=1371659807516838
WAIT #47901354882040: nam='cell list of blocks physical read' ela= 1987 cellhash#=88802347 diskhash#=987865679 blocks=31 obj#=4 tim=1371659807569906
WAIT #47901354882040: nam='cell list of blocks physical read' ela= 1733 cellhash#=88802347 diskhash#=987865679 blocks=37 obj#=4 tim=1371659807574314
WAIT #47901354882040: nam='cell list of blocks physical read' ela= 2216 cellhash#=88802347 diskhash#=4062498200 blocks=69 obj#=4 tim=1371659807579817
WAIT #47901354882040: nam='cell list of blocks physical read' ela= 34220 cellhash#=88802347 diskhash#=4062498200 blocks=54 obj#=4 tim=1371659807615518
WAIT #47901354882040: nam='cell list of blocks physical read' ela= 36753 cellhash#=398250101 diskhash#=949169843 blocks=126 obj#=4 tim=1371659807657273
WAIT #47901354882040: nam='cell list of blocks physical read' ela= 2424 cellhash#=398250101 diskhash#=4004546112 blocks=89 obj#=4 tim=1371659807700181
WAIT #47901354882040: nam='cell list of blocks physical read' ela= 2656 cellhash#=88802347 diskhash#=987865679 blocks=95 obj#=4 tim=1371659807707733
WAIT #47901354882040: nam='cell list of blocks physical read' ela= 1534 cellhash#=2520626383 diskhash#=3381263565 blocks=15 obj#=4 tim=1371659807714012
WAIT #47901354882040: nam='cell list of blocks physical read' ela= 1958 cellhash#=2520626383 diskhash#=3381263565 blocks=51 obj#=4 tim=1371659807719799
As with the other cell block-read events, this, too, reports the cell hash (cellhash#), disk hash (diskhash#), and the object_id of the scanned object (obj#). It differs by reporting the total number of noncontiguous blocks read (blocks).
When data files are created or extended, data blocks must be formatted. On non-Exadata systems, this task falls to the database server, and the data file init write event collects time when this occurs. Exadata does things differently by having the storage cells perform this task, and as a result, the cell smart file creation wait event records time when data blocks are formatted. Note that a file does not have to be added to a tablespace for this wait to occur; the event actually records time against data block formatting.
It may seem strange to have this wait event in the User I/O category, but many times, data blocks are formatted due to DML operations, such as autoextending a data file or deleting data. Other operations that can trigger this event include updates, nonunique index creations, direct-path loads, and inserts using the /*+ append */ hint.
How can updates and deletes trigger the cell smart file creation wait event? As noted previously, the wait event actually records time for data block-formatting operations, and deletes can cause data blocks to be reformatted for reuse when those blocks no longer contain data. Updates can also trigger this event, as an update may trigger a data file extension or use of a new data block, if row chaining or migration occurs.
There is only one parameter returned when this event is triggered and that is the cell hash (cellhash#); all other parameters that could be passed are unused.
This is a wait event that we feel is incorrectly categorized as User I/O, because it doesn’t record time against disk I/O operations; rather, it records time reading from V$CELL_THREAD_HISTORY, V$CELL_REQUEST_TOTALS, and other X$ tables and V$ views referencing cell activities, as well as recursive queries against the AUD$ table.
Generating a 10046 trace file and using grep to find this event produces the following abbreviated output:
WAIT #47971177959976: nam='cell statistics gather' ela= 235 cellhash#=0 p2=0 p3=0 obj#=384 tim=1372568877816026
WAIT #47971177959976: nam='cell statistics gather' ela= 253 cellhash#=0 p2=0 p3=0 obj#=384 tim=1372568877836094
WAIT #47971177959976: nam='cell statistics gather' ela= 256 cellhash#=0 p2=0 p3=0 obj#=384 tim=1372568877855930
WAIT #47971177959976: nam='cell statistics gather' ela= 280 cellhash#=0 p2=0 p3=0 obj#=384 tim=1372568877882687
WAIT #47971177959976: nam='cell statistics gather' ela= 276 cellhash#=0 p2=0 p3=0 obj#=384 tim=1372568877902691
WAIT #47971177959976: nam='cell statistics gather' ela= 256 cellhash#=0 p2=0 p3=0 obj#=384 tim=1372568877921221
WAIT #47971177959976: nam='cell statistics gather' ela= 318 cellhash#=0 p2=0 p3=0 obj#=384 tim=1372568877940424
These waits are against the AUD$ table (object_id and data_object_id 384 in our database). The query executed in this trace file was not against AUD$ but against V$CELL and V$CELL_THREAD_HISTORY. The formatted output from tkprof from the trace file generated for the V$CELL_THREAD_HISTORY query follows:
SQL ID: fqv4mmcqsysbp Plan Hash: 3553215696
select *
From
v$cell_thread_history
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 4 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2775 0.13 0.15 0 0 0 41609
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2777 0.13 0.15 0 4 0 41609
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 401
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
41609 41609 41609 FIXED TABLE FULL X$KCFISOSSN (cr=0 pr=0 pw=0 time=116083 us cost=0
size=676 card=1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2775 0.00 0.00
cell statistics gather 66 0.00 0.02
SQL*Net message from client 2775 2.63 12.53
row cache lock 9 0.00 0.00
library cache lock 2 0.00 0.00
library cache pin 2 0.00 0.00
********************************************************************************
There are five cell-specific events in this wait class, and three of these events rarely accumulate any appreciable time. The two backup events do accrue time during backup and recovery operations and are interesting not only for what operations have time recorded against them but also for the operations that you think would accrue time but do not. We list the non-backup events in Table 7-2.
Table 7-2. Cell Startup and Shutdown Wait Events
Exadata has optimized RMAN (Recovery Manager) incremental backups by allowing offloading to the storage tier. As such, this event accrues wait time for Incremental Level 1 backups, where the processing has been offloaded to the storage tier. With Exadata, changes are tracked at the block level, allowing for faster backups of blocks changed since the last Incremental Level 0 backup. The storage tier is especially suited for this type of processing, thus the need for this new wait event.
Not every incremental backup triggers this wait event. Incremental Level 0 backups do not, as they are, essentially, full backups and don’t rely on block change tracking. They are the starting point for any recovery operation utilizing incremental backups, which is why the most recent Incremental Level 0 backup supersedes any prior Incremental Level 0 backup when performing a restore and recovery absent a RECOVER ... UNTIL ... clause or a SET UNTIL TIME statement.
This event populates the cell hash (cellhash#) and nothing else. Normally, Oracle will reset or clear any parameters that are not passed by the current wait event, but, occasionally, values populated by prior wait events get through. The obj# parameter is one that could be populated in a 10046 trace file that records this event. In such cases, a non-negative obj# is meaningless. Oracle will populate the obj# with a -1, to show it was cleared from a prior event.
Cell Smart Restore from Backup
This event logs wait time spent doing a restore with RMAN. Just like the prior event, Exadata offloads this processing to the storage cells, which, again, are best suited for such operations, having direct access to the storage.
This event actually relates to the cell smart file creation event; both events record time for formatting of empty data blocks and initialization operations related to file creation.
The only parameter populated by this event is, again, the cell hash (cellhash#).
The five wait events in these classes are rarely used in a properly operating Exadata system. We haven’t found them on the systems we manage, and it’s likely you won’t either. There is one exception to this, the cell flash unkeep event, which will be covered in its own section. The remainder of these events are uninformative with respect to the P1, P2, and P3 values provided in the V$SESSION_WAIT view. Table 7-3 lists the events you’re not likely to see and a basic description of the parameters supplied to Oracle.
Table 7-3. Rarely Reported Cell Wait Events
This event logs time waiting while Oracle flushes blocks out of the Smart Flash Cache, but it doesn’t get triggered for objects using the Smart Flash Cache in the default FIFO manner. This event tracks wait time for objects intended to be kept in the Smart Flash Cache (using the STORAGE(FLASH_CACHE KEEP) parameter). Operations that trigger this event would be table drops and truncates. An additional event associated with this wait is enq: RO fast object reuse, as the operation that triggers this event also triggers the buffer cache cleanout. Truncating or dropping a table also requires removal of any cached data from that object, and, on Exadata, there are two separate caches to be maintained.
The only parameter supplied by this event is the cell hash (cellhash#), to identify which cell was accessed for the truncated or dropped operation. No other parameters in the WAIT event in a 10046 trace, or in the V$SESSION_WAIT view, are populated.
Things to Know
The wait interface, which has already been expanded over the last few Oracle releases, experiences another expansion with Exadata: the addition of cell-specific events, which, in some cases, replace existing events found on non-Exadata systems. New events have also been added to monitor new mechanisms specific to Exadata.
The cell smart table scan event records time against Smart Scans, making this event an important one with respect to Exadata or systems using Exadata storage. This event usually replaces the direct path read event, although you can still find direct-path read waits on an Exadata system when queries do not qualify for a Smart Scan.
The cell smart index scan event reports time waited for index fast full scans; no other index access path triggers this wait.
The cell single block physical read event is the Exadata equivalent of the db file sequential read event on non-Exadata systems. It is most often associated with index access operations and table access by index rowid operations, but any single-block access operation can trigger this wait. It is, in our opinion, more accurately named than its non-Exadata equivalent, as the blocks read are single blocks and not necessarily sequential.
The cell multiblock physical read event collects time for sequential multiblock reads and is the replacement for the db file scattered read event. Again, we feel the Exadata event is better named, as it indicates that sequential collections of data blocks are read. The db file scattered read event records time against both sequential and nonsequential reads in non-Exadata systems, so its name is somewhat confusing.
Exadata provides a new event, cell list of blocks read, to record time against nonsequential, or scattered, block reads. This event can also replace the db file scattered read event, because the old event, as mentioned previously, records time against all multiblock reads, sequential or not.
Data block preparation is offloaded to the storage cells in Exadata and, because of this, a new event, named cell smart file creation, has been added. The name is somewhat of a misnomer, as it doesn’t necessarily refer to file creation (although that is one operation that can trigger this wait), but usually collects time against data block formatting/reformatting, to prepare for new data. Operations that trigger this event include deletes, updates, database file creation, and file autoextension.
The cell statistics gather event is one that we feel is miscategorized, as it has nothing to do with disk I/O operations. Recording time against access of various V$ and X$ tables/views, it may be more accurately considered a System I/O or other wait-class event.
RMAN also has waits specific to Exadata, as some of the RMAN operations are offloaded to the storage cells. The cell smart incremental backup event records wait time experienced by Incremental Level 1 backups. Because an Incremental Level 0 backup is actually a full backup and starting point for the next series of Level 1 incremental backups, it is not a backup that triggers this event.
The cell smart restore from backup event collects wait times experienced by RMAN when performing a database restore, as operations specific to this wait are offloaded to the storage cells. Because the storage cells are the only servers in an Exadata system having direct access to the storage, it makes sense to offload these two RMAN operations.
Exadata has, as one of the performance enhancements, the Smart Flash Cache. When tables/indexes are created with the STORAGE(FLASH_CACHE KEEP) directive, the cell smart flash unkeep event can be triggered when these objects are truncated or dropped. These operations also trigger buffer cache cleanup, so trace files reporting this event usually report the enq: RO fast object reuse wait as well.