Important Statistics & Wait Events on Exadata

With this posting, I’d like to highlight the most important Statistics & Wait Events on Exadata that a DBA needs to be familiar with in my view. We start with Statistics, retrievable from v$sysstat, for example. For Exadata, we have introduced 43 new Statistics, that start all with ‘cell%':

SQL> select name from v$statname where name like 'cell%';

NAME
----------------------------------------------------------------
cell physical IO interconnect bytes
cell physical IO bytes saved during optimized file creation
cell physical IO bytes saved during optimized RMAN file restore
cell physical IO bytes eligible for predicate offload
cell physical IO bytes saved by storage index
cell smart IO session cache lookups
cell smart IO session cache hits
cell smart IO session cache soft misses
cell smart IO session cache hard misses
cell smart IO session cache hwm
cell num smart IO sessions in rdbms block IO due to user
cell num smart IO sessions in rdbms block IO due to no cell mem
cell num smart IO sessions in rdbms block IO due to big payload
cell num smart IO sessions using passthru mode due to user
cell num smart IO sessions using passthru mode due to cellsrv
cell num smart IO sessions using passthru mode due to timezone
cell num smart file creation sessions using rdbms block IO mode
cell physical IO interconnect bytes returned by smart scan
cell num fast response sessions
cell num fast response sessions continuing to smart scan
cell smart IO allocated memory bytes
cell smart IO memory bytes hwm
cell num active smart IO sessions
cell smart IO sessions hwm
cell scans
cell blocks processed by cache layer
cell blocks processed by txn layer
cell blocks processed by data layer
cell blocks processed by index layer
cell commit cache queries
cell transactions found in commit cache
cell blocks helped by commit cache
cell blocks helped by minscn optimization
cell simulated physical IO bytes eligible for predicate offload
cell simulated physical IO bytes returned by predicate offload
cell CUs sent uncompressed
cell CUs sent compressed
cell CUs sent head piece
cell CUs processed for uncompressed
cell CUs processed for compressed
cell IO uncompressed bytes
cell index scans
cell flash cache read hits

43 rows selected.

The in my opinion most important Statistics are marked in red. Now to their meaning:

SQL> connect adam/adam
Connected.
SQL> select * from sales;
* Output suppressed, because I don't want millions of rows in the posting*
select name,value/1024/1024 as mb from v$statname 
natural join v$mystat where name in
(
'physical read total bytes',
'cell physical IO interconnect bytes',
'cell physical IO interconnect bytes returned by smart scan'
);

NAME                                                                     MB
---------------------------------------------------------------- ----------
physical read total bytes                                          428.4375
cell physical IO interconnect bytes                                428.4375
cell physical IO interconnect bytes returned by smart scan                0

The demo table sales is 428 MB in size, large enough to cause a Serial Direct Read and make Smart Scans possible. You see the Statistic ‘cell physical IO interconnect bytes’ – in this case equal to ‘physical read total bytes’, because the whole amount of data scanned on the storage layer was sent over the Storage Interconnect (do not confuse this with a RAC Interconnect!) to the Database Layer. Consequently, this was no Smart Scan, because we did no column projection nor filtering. The red Statistic above counts the bytes sent between the Storage Layer and the Database Layer, and v$mystat displays that value only for my session. Let’s do a Smart Scan:

SQL> select * from sales where channel_id=2;
* Output suppressed*
select name,value/1024/1024 as mb from v$statname 
natural join v$mystat where name in
(
'physical read total bytes',
'cell physical IO interconnect bytes',
'cell physical IO interconnect bytes returned by smart scan'
);
NAME                                                                     MB
---------------------------------------------------------------- ----------
physical read total bytes                                        855.851563
cell physical IO interconnect bytes                              506.039314
cell physical IO interconnect bytes returned by smart scan       77.6018143

The values in v$mystat are cumulative for the whole session. You see the value of ‘physical read total bytes’ has doubled, because we did again a Full Table Scan on the Storage Layer, but ‘cell physical IO interconnect bytes’ did not double, because the filtering by the WHERE clause was done on the Storage Layer. We needed only to submit about 77 MB to the Database Layer instead, which is also reflected by ‘cell physical IO interconnect bytes returned by smart scan’. So this Statistic shows the data volume that was sent to the Database Layer on behalf of a Smart Scan – which is supposed to be significantly smaller than ‘physical read total bytes’ for a Database that does many Full Table Scans resp. Index Fast Full Scans on large Segments.

Storage Indexes have the ability to speed up Full Table Scan dramatically:

SQL> select * from sales where id=4711;
PRODUCT                   CHANNEL_ID    CUST_ID AMOUNT_SOLD TIME_ID           ID
------------------------- ---------- ---------- ----------- --------- ----------
Oracle Enterprise Edition          3        998        5000 01-NOV-10       4711
SQL> select name,value/1024/1024 as mb from v$statname 
natural join v$mystat where name in
(
'physical read total bytes',
'cell physical IO interconnect bytes',
'cell physical IO interconnect bytes returned by smart scan'
);
NAME                                                                     MB
---------------------------------------------------------------- ----------
physical read total bytes                                        1283.26563
cell physical IO interconnect bytes                              506.041885
cell physical IO interconnect bytes returned by smart scan       77.6043854

Although the Statistic ‘physical read total bytes’ again got increased by 428 MB, we see only a very slight increase in ‘cell physical IO interconnect bytes’ and ‘cell physical IO interconnect bytes returned by smart scan’, because the Data Volume (containing just one row of interest) sent to the Database Layer was very small. Also, the Smart Scan operation was done very fast, because a Storage Index told the Cells where the ID searched after could not possibly be:

SQL> select name,value/1024/1024 as mb from v$statname 
natural join v$mystat where name='cell physical IO bytes saved by storage index';

NAME                                                                     MB
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index                    419.742188

The red Statistic shows the Data Volume, the Cells did not need to scan through, because they knew the Data cannot be there. In this case, the vast majority of the table was not scanned (although ‘physical read total bytes’ counts it as if the Cells scanned the whole table).

The next Statistic is very important for an OLTP Database running on Exadata:

SQL> select name,value  from v$sysstat where name in
('physical read total IO requests','cell flash cache read hits'); 

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical read total IO requests                                      142476
cell flash cache read hits                                            32897

If a large amount of ‘physical read total IO requests’ was satisfied by the blue Statistics value, we can conclude that we have the basis for a high I/O per second (IOPS) rate, because on Flash Storage, we do not experience the latency time related to the positioning of the Read/Write Head as on Spinning Drives. The blue Statistic counts the number of read requests, resolved from Flash Cache.

Wait Events

We introduced 17 new Wait Events for Exadata. Like the statistics, they are visible also on non Exadata Platforms but have no value there. They can be seen in this view:

SQL> select name from v$event_name where name like 'cell%';

NAME
----------------------------------------------------------------
cell smart table scan
cell smart index scan
cell statistics gather
cell smart incremental backup
cell smart file creation
cell smart restore from backup
cell single block physical read
cell multiblock physical read
cell list of blocks physical read
cell manager opening cell
cell manager closing cell
cell manager discovering disks
cell worker idle
cell smart flash unkeep
cell worker online completion
cell worker retry
cell manager cancel work request

17 rows selected.

Again, I have highlighted the in my view most important ones. I’d like to give a brief explanation of them:

‘cell smart table scan':

Wait Event that occurs during a Smart Scan on a Table.

‘cell smart index scan':

Wait Event that occurs during a Smart Scan on an Index (on behalf of a Direct Index Fast Full Scan)

We expect both to be among the Top Wait Events on a Data Warehouse Database, running on Exadata. There is nothing wrong with that; something has to be the Top Wait Event :-)

‘cell multiblock physical read':

This Wait Event occurs most likely because of a conventional Full Table Scan resp. a conventional Index Fast Full Scan (both not offloaded). Typically because the Table  resp. the Index was small. It would be surprising to see this as a major Wait Event on a Data Warehouse Database running on Eaxadata, but it will show up regularly on OLTP running on Exadata

‘cell single block physical read':

The most likely reason for this is a conventional (not Offloaded) Index Access. Again, we expect to see this Wait Event high on OLTP but low on Data Warehouse Databases, running on Exadata

About these ads

,

  1. #1 by Kellyn Potvin on July 6, 2011 - 18:22

    Excellent as always, Uwe!

  2. #2 by Uwe Hesse on July 7, 2011 - 08:59

    Thank you, Kellyn, very much appreciated :-)

  3. #3 by Ravi Swaminathan on January 17, 2012 - 04:18

    Hi Tanel,

    We have a 2 node rac on 11.2.0.2 on Exadata 2-2 quater RAC.

    1 select b.name , a.value_MB
    2 from (select name ,statistic# from v$statname
    3 where name in (
    4 ‘cell physical IO interconnect bytes’,
    5 ‘physical read total bytes’,
    6 ‘physical write total bytes’
    7 )) b,
    8 ( select statistic#, sum(value/1024/1024) Value_MB
    9 from v$sesstat
    10 group by statistic#) a
    11* where b.statistic#=a.statistic#
    18:08:29 SQL> /

    NAME VALUE_MB
    ———————————————————————- ——————
    cell physical IO interconnect bytes 7,667,378.09
    physical write total bytes 3,343,361.75
    physical read total bytes 953,338.61

    What would cause “cell physical IO interconnect bytes” > “physical read total bytes” + “physical write total bytes” ?

    From you post I take it that “cell physical IO interconnect bytes” should always be less than the total physical IO. Did I miss something ?

  4. #4 by Uwe Hesse on January 17, 2012 - 10:26

    I am not Tanel but Uwe :-)
    However, let me guess: You use HCC compression, right? The decompression can be offloaded to the Cells. So they read less than they send over the Storage Interconnect in that case.

    That is indeed a good thing, though, because the decompression is a CPU intensive task that might be a burden for the database nodes – that are less in number than the storage nodes.

  5. #5 by Ravi Swaminathan on January 17, 2012 - 13:13

    SQL> select compression,count(*) from dba_tables group by compression;

    COMPRESSION COUNT(*)
    ———————— ———-
    95
    DISABLED 17349
    ENABLED 2

    SQL> select owner,table_name,COMPRESSION,COMPRESS_FOR from dba_tables where COMPRESSION=’ENABLED';

    OWNER TABLE_NAME COMPRESSION COMPRESS_FOR
    ——————————– ——————————– ———————— ————————————
    SYSMAN MGMT_METRICS ENABLED OLTP
    SYSMAN MGMT_POLICIES ENABLED OLTP

    SQL> select count(*) from v$segment_statistics where (owner,OBJECT_NAME) in (select owner,table_name from dba_tables where compression=’ENABLED’);

    COUNT(*)
    ———-
    0

    The compression does not seem to be the culprit in this case.

  6. #6 by Uwe Hesse on January 18, 2012 - 10:10

    Beats me. Maybe it is a Bug – I have seen Bugs reported about high numbers of ‘cell physical IO interconnect bytes’ statistic even on non-Exadata systems.

  7. #7 by Tanel Poder on March 5, 2012 - 16:40

    Just noticed this. It’s likely due to ASM mirroring – as the “physical write” metric is counted above ASM layer in the stack, but then ASM will double (or triple) these writes due to mirroring – and the “cell physical IO interconnect bytes” metric measures the real traffic (all 2x or 3x writes) across interconnect. I’ve explained this in the book too…

  8. #8 by Uwe Hesse on March 6, 2012 - 13:44

    Thank you, Tanel, for the explanation! It is indeed in your book in Chapter 12 Monitoring Exadata Performance. I was not completely wrong with the HCC guess, though, as this could have been another reason for the phenomenon – you list that also there :-)

  9. #9 by linamos on August 23, 2012 - 06:05

    Uwe,
    Good job! I enjoy reading your blog.
    2 dump questions:
    1 is it “cell physical IO interconnect bytes” in v$systat = “IO_INTERCONNECT_BYTES” in v$sql?
    2 what are the relationship between those stats in v$systat and those “IO_CELLxxxx” columns in v$sql?
    Thanks,
    Amos

  10. #10 by Gary on September 2, 2012 - 01:40

    Hi UWE
    I have OLTP compression enabled on the table

    CREATE BITMAP INDEX
    I1 ON
    T1(
    col 1Asc
    )
    NOLOGGING TABLESPACE ts_index01

    taking very long time.

    The wait event is : cell single block physical read.

    Any ideas what I can do?

    Thank u

  11. #11 by Dontcheff on September 4, 2012 - 11:13

    I find this post extremely interesting and useful for all DBAs. Well-done Uwe!
    Best,
    Julian

  1. Alane
  2. Exadata « Oracle Scratchpad
  3. Real-Time Wait Events and Statistics related to Exadata « bdt's oracle blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,802 other followers

%d bloggers like this: