Monthly Archives: February 2015

Understanding enhancements to block cleanouts in Exadata part 2

In part 1 of the series I tried to explain (probably a bit too verbose when it came to session statistics) what the effect is of delayed block cleanout and buffered I/O. In the final example the “dirty” blocks on disk have been cleaned out in the buffer cache, greatly reducing the amount of work to be done when reading them.

Catching up with now, and direct path reads. You probably noticed that the migration to 11.2 caused your I/O patterns to change. Suitably large segments are now read using direct path read not only during parallel query but also potentially during the serial execution of a query. Since the blocks read during a direct path read do not end up in the buffer cache there is an interesting side effect to block cleanouts. The scenario is the same unrealistic yet reproducible one as the one presented in part 1 of this article.

Enter Direct Path Reads – non Exadata

To be absolutely sure I am getting results without any optimisation offered by Exadata I am running the example on different hardware. I am also using 11.2.0.4 because that’s the version I had on my lab server. The principles here apply between versions unless I am very mistaken.

I am repeating my test under realistic conditions, leaving _serial_direct_read at its default, “auto”. This means that I am almost certainly going to see direct path reads now when scanning my table. As Christian Antognini has pointed out direct path reads have an impact on the amount of work that has to be done. The test commences with an update in session 1 updating the whole table and flushing the blocks in the buffer cache to disk to ensure they have an active transaction in the ITL part of the header. The selects on session 2 show the following behaviour. The first execution takes longer as expected, the second one is faster.

SQL> select /* not_exadata */ count(*) from t1_100k;

  COUNT(*)
----------
    500000

Elapsed: 00:00:20.62

SQL> @scripts/mystats stop r=physical|cleanout|consistent|cell|table
...
------------------------------------------------------------------------------------------
2. Statistics Report
------------------------------------------------------------------------------------------

Type    Statistic Name                                                               Value
------  ----------------------------------------------------------------  ----------------
STAT    active txn count during cleanout                                            83,334
STAT    cell physical IO interconnect bytes                                    747,798,528
STAT    cleanout - number of ktugct calls                                           83,334
STAT    cleanouts and rollbacks - consistent read gets                              83,334
STAT    consistent changes                                                           1,019
STAT    consistent gets                                                            751,365
STAT    consistent gets - examination                                              667,178
STAT    consistent gets direct                                                      83,334
STAT    consistent gets from cache                                                 668,031
STAT    consistent gets from cache (fastpath)                                          850
STAT    data blocks consistent reads - undo records applied                        583,334
STAT    immediate (CR) block cleanout applications                                  83,334
STAT    physical read IO requests                                                    8,631
STAT    physical read bytes                                                    747,798,528
STAT    physical read total IO requests                                              8,631
STAT    physical read total bytes                                              747,798,528
STAT    physical read total multi block requests                                       673
STAT    physical reads                                                              91,284
STAT    physical reads cache                                                         7,950
STAT    physical reads direct                                                       83,334
STAT    table fetch by rowid                                                           170
STAT    table scan blocks gotten                                                    83,334
STAT    table scan rows gotten                                                     500,000
STAT    table scans (direct read)                                                        1
STAT    table scans (short tables)                                                       1


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

==========================================================================================
End of report
==========================================================================================

The second execution shows slightly better performance as seen before in part 1. The relevant statistics are shown here:


SQL> select /* not_exadata */ count(*) from t1_100k;

  COUNT(*)
----------
    500000

Elapsed: 00:00:04.60

SQL> @scripts/mystats stop r=physical|cleanout|consistent|cell|table
...
------------------------------------------------------------------------------------------
2. Statistics Report
------------------------------------------------------------------------------------------

Type    Statistic Name                                                               Value
------  ----------------------------------------------------------------  ----------------
STAT    active txn count during cleanout                                            83,334
STAT    cell physical IO interconnect bytes                                    682,672,128
STAT    cleanout - number of ktugct calls                                           83,334
STAT    cleanouts and rollbacks - consistent read gets                              83,334
STAT    consistent changes                                                             472
STAT    consistent gets                                                            750,250
STAT    consistent gets - examination                                              666,671
STAT    consistent gets direct                                                      83,334
STAT    consistent gets from cache                                                 666,916
STAT    consistent gets from cache (fastpath)                                          245
STAT    data blocks consistent reads - undo records applied                        583,334
STAT    immediate (CR) block cleanout applications                                  83,334
STAT    physical read IO requests                                                      681
STAT    physical read bytes                                                    682,672,128
STAT    physical read total IO requests                                                681
STAT    physical read total bytes                                              682,672,128
STAT    physical read total multi block requests                                       673
STAT    physical reads                                                              83,334
STAT    physical reads direct                                                       83,334
STAT    table fetch by rowid                                                             1
STAT    table scan blocks gotten                                                    83,334
STAT    table scan rows gotten                                                     500,000
STAT    table scans (direct read)                                                        1


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

==========================================================================================
End of report
==========================================================================================

The amount of IO is slightly less. Still all the CR processing is done for every block in the table-each has an active transaction (active txn count during cleanout). As you would expect the buffer cache has no blocks from the segment stored:

SQL> select count(*), inst_id, status from gv$bh 
  2  where objd = (select data_object_id from dba_objects where object_name = 'T1_100K') 
  3  group by inst_id, status;

  COUNT(*)    INST_ID STATUS
---------- ---------- ----------
     86284          2 free
    584983          1 free
         1          1 scur

Subsequent executions all take approximately the same amount of time. Every execution has to perform cleanouts and rollbacks-no difference to before really except that direct path reads are used to read the table. No difference here to the buffered reads, with the exception that there aren’t any blocks from T1_100K in the buffer cache.

Commit

Committing in session 1 does not have much of an effect-the blocks read by the direct path read are going to the PGA instead of the buffer cache. A quick demonstration: the same select has been executed after the transaction in session 1 has committed.

SQL> select /* not_exadata */ count(*) from t1_100k;

  COUNT(*)
----------
    500000

Elapsed: 00:00:04.87

SQL> @scripts/mystats stop r=physical|cleanout|consistent|cell|table
...
------------------------------------------------------------------------------------------
2. Statistics Report
------------------------------------------------------------------------------------------

Type    Statistic Name                                                               Value
------  ----------------------------------------------------------------  ----------------
STAT    cell physical IO interconnect bytes                                    682,672,128
STAT    cleanout - number of ktugct calls                                           83,334
STAT    cleanouts only - consistent read gets                                       83,334
STAT    commit txn count during cleanout                                            83,334
STAT    consistent changes                                                             481
STAT    consistent gets                                                            166,916
STAT    consistent gets - examination                                               83,337
STAT    consistent gets direct                                                      83,334
STAT    consistent gets from cache                                                  83,582
STAT    consistent gets from cache (fastpath)                                          245
STAT    immediate (CR) block cleanout applications                                  83,334
STAT    physical read IO requests                                                      681
STAT    physical read bytes                                                    682,672,128
STAT    physical read total IO requests                                                681
STAT    physical read total bytes                                              682,672,128
STAT    physical read total multi block requests                                       673
STAT    physical reads                                                              83,334
STAT    physical reads direct                                                       83,334
STAT    table fetch by rowid                                                             1
STAT    table scan blocks gotten                                                    83,334
STAT    table scan rows gotten                                                     500,000
STAT    table scans (direct read)                                                        1


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

==========================================================================================
End of report
==========================================================================================

As you can see the work done for the cleanout is repeated very time, despite the fact that active txn count during cleanout is not found in the output. And still, there aren’t really any blocks pertaining to T1_100K in the buffer cache.

SQL> select count(*), inst_id, status from gv$bh where
  2   objd = (select data_object_id from dba_objects where object_name = 'T1_100K')
  3  group by inst_id, status;

  COUNT(*)    INST_ID STATUS
---------- ---------- ----------
     86284          2 free
    584983          1 free
         1          1 scur

OK that should be enough for now-over to the Exadata.

Direct Path Reads – Exadata

The same test again, but this time on an X2-2 quarter rack running database 12.1.0.2 and cellsrv 12.1.2.1.0. After the update of the entire table and the flushing of blocks to disk, here are the statistics for the first and second execution.

SQL> select count(*) from t1_100k;

  COUNT(*)
----------
    500000

Elapsed: 00:00:17.41

SQL> @scripts/mystats stop r=physical|cleanout|consistent|cell|table


------------------------------------------------------------------------------------------
2. Statistics Report
------------------------------------------------------------------------------------------

Type    Statistic Name                                                               Value
------  ----------------------------------------------------------------  ----------------
STAT    active txn count during cleanout                                            83,334
STAT    cell IO uncompressed bytes                                             682,672,128
STAT    cell blocks processed by cache layer                                        83,933
STAT    cell commit cache queries                                                   83,933
STAT    cell flash cache read hits                                                  14,011
STAT    cell num smartio automem buffer allocation attempts                              1
STAT    cell physical IO bytes eligible for predicate offload                  682,672,128
STAT    cell physical IO interconnect bytes                                    804,842,256
STAT    cell physical IO interconnect bytes returned by smart scan             682,904,336
STAT    cell scans                                                                       1
STAT    cleanout - number of ktugct calls                                           83,334
STAT    cleanouts and rollbacks - consistent read gets                              83,334
STAT    consistent changes                                                             796
STAT    consistent gets                                                            917,051
STAT    consistent gets direct                                                      83,334
STAT    consistent gets examination                                                833,339
STAT    consistent gets examination (fastpath)                                      83,336
STAT    consistent gets from cache                                                 833,717
STAT    consistent gets pin                                                            378
STAT    consistent gets pin (fastpath)                                                 377
STAT    data blocks consistent reads - undo records applied                        750,002
STAT    immediate (CR) block cleanout applications                                  83,334
STAT    physical read IO requests                                                   16,147
STAT    physical read bytes                                                    804,610,048
STAT    physical read requests optimized                                            14,011
STAT    physical read total IO requests                                             16,147
STAT    physical read total bytes                                              804,610,048
STAT    physical read total bytes optimized                                    114,778,112
STAT    physical read total multi block requests                                       851
STAT    physical reads                                                              98,219
STAT    physical reads cache                                                        14,885
STAT    physical reads direct                                                       83,334
STAT    table fetch by rowid                                                             1
STAT    table scan blocks gotten                                                    83,334
STAT    table scan disk non-IMC rows gotten                                        500,000
STAT    table scan rows gotten                                                     500,000
STAT    table scans (direct read)                                                        1


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

==========================================================================================
End of report
==========================================================================================


SQL> -- second execution, still no commit in session 1
SQL> select count(*) from t1_100k;

  COUNT(*)
----------
    500000

Elapsed: 00:00:01.31

SQL> @scripts/mystats stop r=physical|cleanout|consistent|cell|table
...
------------------------------------------------------------------------------------------
2. Statistics Report
------------------------------------------------------------------------------------------

Type    Statistic Name                                                               Value
------  ----------------------------------------------------------------  ----------------
STAT    active txn count during cleanout                                            83,334
STAT    cell IO uncompressed bytes                                             682,672,128
STAT    cell blocks processed by cache layer                                        83,949
STAT    cell commit cache queries                                                   83,949
STAT    cell flash cache read hits                                                   1,269
STAT    cell num smartio automem buffer allocation attempts                              1
STAT    cell physical IO bytes eligible for predicate offload                  682,672,128
STAT    cell physical IO interconnect bytes                                    682,907,280
STAT    cell physical IO interconnect bytes returned by smart scan             682,907,280
STAT    cell scans                                                                       1
STAT    cleanout - number of ktugct calls                                           83,334
STAT    cleanouts and rollbacks - consistent read gets                              83,334
STAT    consistent changes                                                             791
STAT    consistent gets                                                            917,053
STAT    consistent gets direct                                                      83,334
STAT    consistent gets examination                                                833,339
STAT    consistent gets examination (fastpath)                                      83,337
STAT    consistent gets from cache                                                 833,719
STAT    consistent gets pin                                                            380
STAT    consistent gets pin (fastpath)                                                 380
STAT    data blocks consistent reads - undo records applied                        750,002
STAT    immediate (CR) block cleanout applications                                  83,334
STAT    physical read IO requests                                                    1,278
STAT    physical read bytes                                                    682,672,128
STAT    physical read requests optimized                                             1,269
STAT    physical read total IO requests                                              1,278
STAT    physical read total bytes                                              682,672,128
STAT    physical read total bytes optimized                                    678,494,208
STAT    physical read total multi block requests                                       885
STAT    physical reads                                                              83,334
STAT    physical reads direct                                                       83,334
STAT    table fetch by rowid                                                             1
STAT    table scan blocks gotten                                                    83,334
STAT    table scan disk non-IMC rows gotten                                        500,000
STAT    table scan rows gotten                                                     500,000
STAT    table scans (direct read)                                                        1


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

==========================================================================================
End of report
==========================================================================================

In the example table scans (direct read) indicates that the segment was read using a direct path read. Since this example was executed on an Exadata this direct path read was performed as a Smart Scan (cell scans = 1). However, the Smart Scan was not very successful: although all 83,334 table blocks were opened by the cache layer (the first one to touch a block on the cell during the Smart Scan) none of them passed the examination of the transaction layer-they have all been discarded. You can see that there was no saving by using the Smart Scan here: cell physical IO bytes eligible for predicate offload is equal to cell physical IO interconnect bytes returned by smart scan.

83,334 blocks were read during the table scan (table scan blocks gotten), all of which were read directly (physical reads direct). All of the blocks read had an active transaction (active txn count during cleanout) and all of these had to be rolled back (cleanouts and rollbacks – consistent read gets).

The same amount of work has to be done for every read.

Commit in session 1

After committing the transaction in session 1 the statistics for the select statement in session 2 are as follows (also note the execution time)

SQL> select count(*) from t1_100k;

  COUNT(*)
----------
    500000

Elapsed: 00:00:00.16

SQL> @scripts/mystats stop r=physical|cleanout|consistent|cell|table

------------------------------------------------------------------------------------------
2. Statistics Report
------------------------------------------------------------------------------------------

Type    Statistic Name                                                               Value
------  ----------------------------------------------------------------  ----------------
STAT    cell IO uncompressed bytes                                             682,672,128
STAT    cell blocks helped by commit cache                                          83,334
STAT    cell blocks processed by cache layer                                        83,334
STAT    cell blocks processed by data layer                                         83,334
STAT    cell blocks processed by txn layer                                          83,334
STAT    cell commit cache queries                                                   83,334
STAT    cell flash cache read hits                                                     663
STAT    cell num smartio automem buffer allocation attempts                              1
STAT    cell physical IO bytes eligible for predicate offload                  682,672,128
STAT    cell physical IO interconnect bytes                                     13,455,400
STAT    cell physical IO interconnect bytes returned by smart scan              13,455,400
STAT    cell scans                                                                       1
STAT    cell transactions found in commit cache                                     83,334
STAT    consistent changes                                                             791
STAT    consistent gets                                                             83,717
STAT    consistent gets direct                                                      83,334
STAT    consistent gets examination                                                      3
STAT    consistent gets examination (fastpath)                                           3
STAT    consistent gets from cache                                                     383
STAT    consistent gets pin                                                            380
STAT    consistent gets pin (fastpath)                                                 380
STAT    physical read IO requests                                                      663
STAT    physical read bytes                                                    682,672,128
STAT    physical read requests optimized                                               663
STAT    physical read total IO requests                                                663
STAT    physical read total bytes                                              682,672,128
STAT    physical read total bytes optimized                                    682,672,128
STAT    physical read total multi block requests                                       654
STAT    physical reads                                                              83,334
STAT    physical reads direct                                                       83,334
STAT    table fetch by rowid                                                             1
STAT    table scan blocks gotten                                                    83,334
STAT    table scan disk non-IMC rows gotten                                        500,000
STAT    table scan rows gotten                                                     500,000
STAT    table scans (direct read)                                                        1
STAT    table scans (short tables)                                                       1


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

==========================================================================================
End of report
==========================================================================================

There are no more active transactions found during the cleanout. The blocks however still have to be cleaned out every time the direct path read is performed. As a matter of design principle Oracle cannot reuse the blocks it has read with a direct path read (or Smart Scan for that matter) they simply are not cached.

But have a look at the execution time: quite a nice performance improvement that is. In fact there are a few things worth mentioning:

  • The segment is successfully scanned via a Smart Scan. How can you tell?
    • cell scans = 1
    • cell physical IO interconnect bytes returned by smart scan is a lot lower than cell physical IO bytes eligible for predicate offload
    • You see cell blocks processed by (cache|transaction|data) layer matching the number of blocks the table is made up of. Previously you only saw blocks processed by the cache layer
  • The commit cache made this happen
    • cell commit cache queries
    • cell blocks helped by commit cache
    • cell transactions found in commit cache

The commit cache “lives” in the cells and probably caches recently committed transactions. If a block is read by the cell and an active transaction is found the cell has a couple of options to avoid a round-trip of the block to the RDBMS layer for consistent read processing:

  1. If possible it uses the minscn optimisation. Oracle tracks the minimum SCN of all active transactions. The cell software can compare the SCN from the ITL in the block with the lowest SCN of all active transactions. If that number is greater than the SCN found in the block the transaction must have already completed and it is safe to read the block. This optimisation is not shown in the blog post, you’d see  cell blocks helped by minscn optimization increase
  2. If the first optimisation does not help the cells make use of the commit cache-visible as cell commit cache queries and if a cache hit has been scored, cell blocks helped by commit cache. While the transaction in session 1 hasn’t completed yet you could only see the commit cache queries. After the commit the queries were successful

If both of them fail the block must be sent to the RDBMS layer for consistent read processing. In that case there is no difference to the treatment of direct path read blocks outside Exadata.

Summary

The commit cache is a great enhancement Exadata offers for databases using the platform. While non-Exadata deployments will have to clean out blocks that haven’t been subject to cleanouts every time the “dirty” block is read via direct path read, this can be avoided on Exadata if the transaction has committed.

Understanding enhancements to block cleanouts in Exadata part 1

Travel time is writing time and I have the perfect setting for a techie post. Actually I got quite excited about the subject causing the article to get a bit longer than initially anticipated. In this part you can read about block cleanouts when using buffered I/O. The next part will show how this works using direct path reads and Smart Scans.

The article ultimately aims at describing the enhancements Exadata brings to the table for direct path reads and delayed block cleanouts. Delayed block cleanouts are described in Jonathan Lewis’s “Oracle Core”, and in one of his blog posts, so here’s just a summary.

The delayed block cleanout

In a nutshell Oracle’s database writer (the process persisting your transactions to disk) is free to write blocks to disk in batches when it has to be done. A commit on its own won’t trigger a write of dirty (or modified) block to disk. If it were to trigger the write, the commit-time would be proportional to the number of blocks affected by the last transaction. The commit command however completes very quickly regardless of how much data has been affected. Unlike a rollback … It is entirely possible that a block modified by a transaction is written to disk before the transaction has been completed. A little later, once the commit has been acknowledged by Oracle there is no process that would read the blocks back into the buffer cache and clear them out-this happens later. It would also be quite inefficient to do so.

Defining “clean”

Now when database writer writes to disk it is possible that the block just written has an active transaction recorded in its header. You can see this by dumping a block with an active transaction – the ITL in the header will reference the XID, UBA and the number of rows affected plus other information. The individual rows that are part of the transaction have their lock byte set as you can see in the row directory. The number in the lb field refers back to an ITL you see further up in the block dump (don’t worry I’ll show you an example shortly).

What happened in Oracle before direct path reads became a lot more common is this:

  • A user starts a transaction, for example by updating a large portion of the table
  • In the meantime the database writer flushes some blocks to disk, including some of the ones affected by the transaction.
  • The user commits a transaction
  • The next user queries the block (let’s assume it is not in the buffer cache)
  • The second session’s foreground process reads the block
    • Realises that it has an active transaction recorded in it
    • Checks if that transaction is still active
    • And clears the block out if not
  • The block with a valid CR image is now in the buffer cache

Huh?

Sounds too abstract-I agree? Let’s have an example.

SQL> select table_name,num_rows,compression,partitioned from tabs
  2  where table_name = 'T1_100k';

TABLE_NAME                       NUM_ROWS COMPRESS PAR
------------------------------ ---------- -------- ---
T1_100K                            500000 DISABLED NO

SQL> update T1_100k set state = 'MODIFIED';

5000000 rows updated.

An easy way to simulate the flushing of blocks to disk is a slightly brute-force approach of flushing the buffer cache. Only recommended in the lab, really. Let’s have a look at what the block looks like:

SQL> select dbms_rowid.rowid_block_number(rowid), dbms_rowid.rowid_relative_fno(rowid)
  2  from t1_100k where rownum < 11;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
------------------------------------ ------------------------------------
                             3940811                                    5
                             3940811                                    5
                             3940811                                    5
                             3940811                                    5
                             3940811                                    5
                             3940811                                    5
                             3940812                                    5
                             3940812                                    5
                             3940812                                    5
                             3940812                                    5

10 rows selected.

Elapsed: 00:00:00.01
SQL> alter system dump datafile 5 block 3940811;

System altered.

Elapsed: 00:00:00.01
SQL> select value from v$diag_info where name like 'Default%';

VALUE
---------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/dbm01/dbm011/trace/dbm011_ora_94351.trc

Elapsed: 00:00:00.02

The block dump for block 3940811 on datafile 5 (users tablespace) is now in that trace file:

Block header dump:  0x017c21cb
 Object id on Block? Y
 seg/obj: 0x12a13  csc: 0x00.2191ff7  itc: 3  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x17c21c8 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.02191d10
0x02   0x0009.005.00002575  0x0003688a.0ef1.1f  ----    6  fsc 0x0000.00000000
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x017c21cb
data_block_dump,data header at 0x7f026361707c
===============
tsiz: 0x1f80
hsiz: 0x1e
pbl: 0x7f026361707c
     76543210
flag=--------
ntab=1
nrow=6
...
0xe:pti[0]      nrow=6  offs=0
0x12:pri[0]     offs=0x1b75
0x14:pri[1]     offs=0x176a
0x16:pri[2]     offs=0x135f
0x18:pri[3]     offs=0xf54
0x1a:pri[4]     offs=0xb49
0x1c:pri[5]     offs=0x73e
block_row_dump:
tab 0, row 0, @0x1b75
tl: 1035 fb: --H-FL-- lb: 0x2  cc: 6
col  0: [ 2]  c1 02
col  1: [999]
 31 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
...

The significant pieces of information here are in the ITL (interested transaction list) and the lock byte set, pointing to the second ITL entry. The absence of flags for ITL #2 indicates the transaction is ongoing (you can see this in v$transaction-this is a lab environment, in real databases you’d see more than 1 active transaction):

SQL> select xidusn, xidslot, xidsqn, status, start_scn from v$transaction;

    XIDUSN    XIDSLOT     XIDSQN STATUS            START_SCN
---------- ---------- ---------- ---------------- ----------
         9          5       9589 ACTIVE             35200741

This is indeed the transaction that is referenced in v$transaction and the ITL 0x02 above (9589 is decimal for 2575 in hexadecimal notation, 9 and 5 don’t need converting to decimal numbers). If you are curious now about some more internals then you might find this post by Arup Nanda interesting.

Now what happens if another session queries the table? Since the query in session 1 has not yet committed the changes in it must not be seen by any other session (that would be a dirty read otherwise. We do not use dirty reads in Oracle RDBMS). So to give the user a consistent view of the data the block must be rolled back. Before the test I made sure there wasn’t a block in the buffer cache for the object in question:

SQL> select count(*), inst_id, status from gv$bh where objd =
  2   (select data_object_id from dba_objects where object_name = 'T1_100K')
  3  group by inst_id, status;

  COUNT(*)    INST_ID STATUS
---------- ---------- ----------
    524327          1 free

Let’s test:

SQL> @mystats start

SQL> select /* test004 */ count(*) from t1_100k;

  COUNT(*)
----------
    500000

Elapsed: 00:00:13.96

@mystats stop t=1

==========================================================================================
MyStats report : 22-FEB-2015 08:42:09
==========================================================================================


------------------------------------------------------------------------------------------
1. Summary Timings
------------------------------------------------------------------------------------------

Type    Statistic Name                                                               Value
------  ----------------------------------------------------------------  ----------------
TIMER   snapshot interval (seconds)                                                  35.57
TIMER   CPU time used (seconds)                                                       3.21


------------------------------------------------------------------------------------------
2. Statistics Report
------------------------------------------------------------------------------------------

Type    Statistic Name                                                               Value
------  ----------------------------------------------------------------  ----------------
STAT    CPU used by this session                                                       327
STAT    CPU used when call started                                                     327
STAT    CR blocks created                                                           83,334
STAT    DB time                                                                      1,401
STAT    Requests to/from client                                                         16
STAT    SQL*Net roundtrips to/from client                                               16
STAT    active txn count during cleanout                                            83,334
STAT    buffer is not pinned count                                                       2
STAT    bytes received via SQL*Net from client                                      13,279
STAT    bytes sent via SQL*Net to client                                             5,106
STAT    calls to get snapshot scn: kcmgss                                              758
STAT    calls to kcmgas                                                             83,332
STAT    calls to kcmgcs                                                                 30
STAT    cell flash cache read hits                                                   8,045
STAT    cell physical IO interconnect bytes                                    748,576,768
STAT    change write time                                                               48
STAT    cleanout - number of ktugct calls                                           83,334
STAT    cleanouts and rollbacks - consistent read gets                              83,334
STAT    consistent changes                                                         591,717
STAT    consistent gets                                                            757,980
STAT    consistent gets examination                                                674,254
STAT    consistent gets examination (fastpath)                                      83,337
STAT    consistent gets from cache                                                 757,980
STAT    consistent gets pin                                                         83,726
STAT    consistent gets pin (fastpath)                                              83,055
STAT    data blocks consistent reads - undo records applied                        590,917
STAT    db block changes                                                            84,134
STAT    db block gets                                                                2,301
STAT    db block gets from cache                                                     2,301
STAT    enqueue releases                                                                 4
STAT    enqueue requests                                                                 4
STAT    execute count                                                                   13
STAT    file io wait time                                                       11,403,214
STAT    free buffer requested                                                      174,766
STAT    gc local grants                                                            174,713
STAT    global enqueue gets sync                                                        43
STAT    global enqueue releases                                                         41
STAT    heap block compress                                                          4,549
STAT    hot buffers moved to head of LRU                                                 4
STAT    immediate (CR) block cleanout applications                                  83,334
STAT    index fetch by key                                                               1
STAT    lob writes                                                                     375
STAT    lob writes unaligned                                                           375
STAT    logical read bytes from cache                                        6,228,221,952
STAT    messages sent                                                                    9
STAT    non-idle wait count                                                         17,446
STAT    non-idle wait time                                                           1,141
STAT    opened cursors cumulative                                                       13
STAT    parse count (hard)                                                               1
STAT    parse count (total)                                                             13
STAT    physical read IO requests                                                    8,715
STAT    physical read bytes                                                    748,576,768
STAT    physical read requests optimized                                             8,045
STAT    physical read total IO requests                                              8,715
STAT    physical read total bytes                                              748,576,768
STAT    physical read total bytes optimized                                     65,904,640
STAT    physical read total multi block requests                                       654
STAT    physical reads                                                              91,379
STAT    physical reads cache                                                        91,379
STAT    physical reads cache prefetch                                               82,664
STAT    recursive calls                                                              1,985
STAT    recursive cpu usage                                                              6
STAT    redo entries                                                                84,005
STAT    redo entries for lost write detection                                          671
STAT    redo size                                                                9,016,740
STAT    redo size for lost write detection                                       3,016,296
STAT    redo subscn max counts                                                      83,334
STAT    rows fetched via callback                                                        1
STAT    session cursor cache count                                                       4
STAT    session cursor cache hits                                                        9
STAT    session logical reads                                                      760,281
STAT    session pga memory                                                         -65,536
STAT    session uga memory                                                          65,488
STAT    session uga memory max                                                     148,312
STAT    shared hash latch upgrades - no wait                                         8,218
STAT    table fetch by rowid                                                             1
STAT    table scan blocks gotten                                                    83,334
STAT    table scan disk non-IMC rows gotten                                        500,000
STAT    table scan rows gotten                                                     500,000
STAT    table scans (short tables)                                                       1
STAT    temp space allocated (bytes)                                             2,097,152
STAT    user I/O wait time                                                           1,141
STAT    user calls                                                                      22
STAT    workarea executions - optimal                                                    1
STAT    workarea memory allocated                                                      -43


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

==========================================================================================
End of report
==========================================================================================

I am using Adrian Billington’s mystats script here which I can only recommend to you-it’s very decent (and that’s an understatement). In the example above it took two snapshots and calculated the change of the session counters in v$mystat during the query execution. Have a look at the cleanout% statistics here! These are the block cleanouts. As this generates redo you can see that recorded here too. This is a tricky interview question: can a select generate redo? Yes sure can! There is also a fair amount of physical I/O going on. After all the buffer cache was empty.

The second execution of the query completes a lot faster due to the absence of physical I/O. Nevertheless, the session has to clean out all these blocks and perform consistent read processing again (remember the transaction is still uncommitted as seen in active txn%):

SQL> select count(*) from t1_100k;

  COUNT(*)
----------
    500000

Elapsed: 00:00:01.76

SQL> @mystats stop t=1

==========================================================================================
MyStats report : 22-FEB-2015 08:42:59
==========================================================================================


------------------------------------------------------------------------------------------
1. Summary Timings
------------------------------------------------------------------------------------------

Type    Statistic Name                                                               Value
------  ----------------------------------------------------------------  ----------------
TIMER   snapshot interval (seconds)                                                   8.55
TIMER   CPU time used (seconds)                                                       1.75


------------------------------------------------------------------------------------------
2. Statistics Report
------------------------------------------------------------------------------------------

Type    Statistic Name                                                               Value
------  ----------------------------------------------------------------  ----------------
STAT    CPU used by this session                                                       181
STAT    CPU used when call started                                                     181
STAT    CR blocks created                                                           83,334
STAT    DB time                                                                        183
STAT    Requests to/from client                                                         16
STAT    SQL*Net roundtrips to/from client                                               16
STAT    active txn count during cleanout                                            83,334
STAT    buffer is not pinned count                                                       2
STAT    bytes received via SQL*Net from client                                      13,280
STAT    bytes sent via SQL*Net to client                                             5,177
STAT    calls to get snapshot scn: kcmgss                                              757
STAT    calls to kcmgas                                                             83,331
STAT    calls to kcmgcs                                                                 30
STAT    change write time                                                               28
STAT    cleanout - number of ktugct calls                                           83,334
STAT    cleanouts and rollbacks - consistent read gets                              83,334
STAT    consistent changes                                                         591,708
STAT    consistent gets                                                            757,980
STAT    consistent gets examination                                                674,254
STAT    consistent gets examination (fastpath)                                      83,337
STAT    consistent gets from cache                                                 757,980
STAT    consistent gets pin                                                         83,726
STAT    consistent gets pin (fastpath)                                              83,726
STAT    cursor authentications                                                           1
STAT    data blocks consistent reads - undo records applied                        590,917
STAT    db block changes                                                            84,125
STAT    db block gets                                                                2,895
STAT    db block gets from cache                                                     2,895
STAT    execute count                                                                   13
STAT    free buffer requested                                                       83,382
STAT    global enqueue gets sync                                                        38
STAT    global enqueue releases                                                         36
STAT    heap block compress                                                          4,549
STAT    immediate (CR) block cleanout applications                                  83,334
STAT    index fetch by key                                                               1
STAT    lob writes                                                                     375
STAT    lob writes unaligned                                                           375
STAT    logical read bytes from cache                                        6,233,088,000
STAT    messages sent                                                                    7
STAT    non-idle wait count                                                             16
STAT    opened cursors cumulative                                                       13
STAT    parse count (total)                                                             13
STAT    recursive calls                                                              1,982
STAT    recursive cpu usage                                                              6
STAT    redo entries                                                                83,334
STAT    redo size                                                                6,000,400
STAT    rows fetched via callback                                                        1
STAT    session cursor cache hits                                                       12
STAT    session logical reads                                                      760,875
STAT    session pga memory                                                      -1,769,472
STAT    session uga memory                                                      -1,858,472
STAT    shared hash latch upgrades - no wait                                           524
STAT    table fetch by rowid                                                             1
STAT    table scan blocks gotten                                                    83,334
STAT    table scan disk non-IMC rows gotten                                        500,000
STAT    table scan rows gotten                                                     500,000
STAT    table scans (short tables)                                                       1
STAT    user calls                                                                      22
STAT    workarea executions - optimal                                                    1
STAT    workarea memory allocated                                                      -83


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

==========================================================================================
End of report
==========================================================================================

I should have mentioned that these 2 examples were executed on an X2-2 quarter rack. To prevent Exadata from applying some clever optimisations I specifically disabled direct path reads and turned off Exadata features. Here is the view from the buffer cache again:

SQL> select count(*), inst_id, status from gv$bh where objd =
  2   (select data_object_id from dba_objects where object_name = 'T1_100K')
  3  group by inst_id, status;

  COUNT(*)    INST_ID STATUS
---------- ---------- ----------
    351704          1 free
         1          1 scur
     83334          1 xcur
     83334          1 cr

4 rows selected.

For each subsequent execution of the select statement consistent read processing has to be conducted. The number of CR blocks in the buffer cache will increase. For the third execution I can see the following:

SQL> r
  1  select count(*), inst_id, status from gv$bh where objd =
  2   (select data_object_id from dba_objects where object_name = 'T1_100K')
  3* group by inst_id, status

  COUNT(*)    INST_ID STATUS
---------- ---------- ----------
    267526          1 free
         1          1 scur
     83334          1 xcur
    166668          1 cr

4 rows selected.

Things improve when the transaction commits.

The impact of a commit

What about a commit now? Committing in session 1 and running the query again in session 2 shows a very good response time. Please remember that I forced buffered reads. The next part deals with this case when using direct path reads instead (those are not buffered). The segment size would have qualified for direct path reads otherwise.

SQL> select /* test004 */ count(*) from t1_100k;

  COUNT(*)
----------
    500000

Elapsed: 00:00:00.18

==========================================================================================
MyStats report : 22-FEB-2015 09:40:08
==========================================================================================


------------------------------------------------------------------------------------------
1. Summary Timings
------------------------------------------------------------------------------------------

Type    Statistic Name                                                               Value
------  ----------------------------------------------------------------  ----------------
TIMER   snapshot interval (seconds)                                                  19.73
TIMER   CPU time used (seconds)                                                       0.18


------------------------------------------------------------------------------------------
2. Statistics Report
------------------------------------------------------------------------------------------

Type    Statistic Name                                                               Value
------  ----------------------------------------------------------------  ----------------
STAT    CPU used by this session                                                        25
STAT    CPU used when call started                                                      25
STAT    DB time                                                                         25
STAT    Heatmap SegLevel - Full Table Scan                                               1
STAT    Requests to/from client                                                         16
STAT    SQL*Net roundtrips to/from client                                               16
STAT    buffer is not pinned count                                                       2
STAT    bytes received via SQL*Net from client                                      13,280
STAT    bytes sent via SQL*Net to client                                             5,107
STAT    calls to get snapshot scn: kcmgss                                              757
STAT    calls to kcmgcs                                                                 30
STAT    consistent changes                                                             800
STAT    consistent gets                                                             83,729
STAT    consistent gets examination                                                      3
STAT    consistent gets examination (fastpath)                                           3
STAT    consistent gets from cache                                                  83,729
STAT    consistent gets pin                                                         83,726
STAT    consistent gets pin (fastpath)                                              83,726
STAT    db block changes                                                               800
STAT    db block gets                                                                2,301
STAT    db block gets from cache                                                     2,301
STAT    enqueue releases                                                                 3
STAT    enqueue requests                                                                 3
STAT    execute count                                                                   13
STAT    free buffer requested                                                           53
STAT    global enqueue gets sync                                                        39
STAT    global enqueue releases                                                         37
STAT    index fetch by key                                                               1
STAT    lob writes                                                                     375
STAT    lob writes unaligned                                                           375
STAT    logical read bytes from cache                                          704,757,760
STAT    no work - consistent read gets                                              83,334
STAT    non-idle wait count                                                             16
STAT    opened cursors cumulative                                                       13
STAT    parse count (total)                                                             13
STAT    recursive calls                                                              1,984
STAT    recursive cpu usage                                                              7
STAT    rows fetched via callback                                                        1
STAT    session cursor cache count                                                       4
STAT    session cursor cache hits                                                       10
STAT    session logical reads                                                       86,030
STAT    session pga memory                                                          65,536
STAT    session pga memory max                                                      65,536
STAT    session uga memory                                                          65,488
STAT    session uga memory max                                                     148,312
STAT    shared hash latch upgrades - no wait                                           525
STAT    table fetch by rowid                                                             1
STAT    table scan blocks gotten                                                    83,334
STAT    table scan disk non-IMC rows gotten                                        500,000
STAT    table scan rows gotten                                                     500,000
STAT    table scans (short tables)                                                       1
STAT    temp space allocated (bytes)                                             2,097,152
STAT    user calls                                                                      22
STAT    workarea executions - optimal                                                    1
STAT    workarea memory allocated                                                      -40


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

==========================================================================================
End of report
==========================================================================================

Access to blocks in the buffer cache is quick! There was no need to clean out any blocks anymore. The fastest way to do something is indeed not having to do it at all. Mind you, the block on disk still has the active transaction recorded!

SQL> alter system dump datafile 5 block 3940811;

System altered.

Elapsed: 00:00:00.01
SQL> select value from v$diag_info where name like 'Default%';

VALUE
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/dbm01/dbm011/trace/dbm011_ora_100297.trc

SQL> exit

$ vi /u01/app/oracle/diag/rdbms/dbm01/dbm011/trace/dbm011_ora_100297.trc

Block header dump:  0x017c21cb
 Object id on Block? Y
 seg/obj: 0x12a13  csc: 0x00.2317620  itc: 3  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x17c21c8 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.02191d10
0x02   0x0009.005.00002575  0x0003688a.0ef1.1f  --U-    6  fsc 0x0000.0232cfca
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x017c21cb
data_block_dump,data header at 0x7fc9f3cb407c
...

$ grep lb -B1 /u01/app/oracle/diag/rdbms/dbm01/dbm011/trace/dbm011_ora_100297.trc
tab 0, row 0, @0x1b75
tl: 1035 fb: --H-FL-- lb: 0x2  cc: 6
--
tab 0, row 1, @0x176a
tl: 1035 fb: --H-FL-- lb: 0x2  cc: 6
--
tab 0, row 2, @0x135f
tl: 1035 fb: --H-FL-- lb: 0x2  cc: 6
--
tab 0, row 3, @0xf54
tl: 1035 fb: --H-FL-- lb: 0x2  cc: 6
--
tab 0, row 4, @0xb49
tl: 1035 fb: --H-FL-- lb: 0x2  cc: 6
--
tab 0, row 5, @0x73e
tl: 1035 fb: --H-FL-- lb: 0x2  cc: 6
--

The transaction recorded in the ITL slot 2 is gone though:

SQL> select xidusn, xidslot, xidsqn, status, start_scn from v$transaction
  2  where xidusn = 9 and xidslot = 5 and xidsqn = 9589;

no rows selected

Did you notice the Flag in the second ITL entry? That wasn’t there before. It indicates a fast commit according to Core Oracle by Jonathan Lewis.

Summary

Block cleanouts are simple with buffered I/O. Dirty blocks can be rolled back and kept in the buffer cache and can be accessed without having to undergo block cleanout.

What happens in ASM if usable_file_mb is negative and you lose a failgroup

Having read the excellent post “Demystifying ASM REQUIRED_MIRROR_FREE_MB and USABLE_FILE_MB” again by Harald von Breederode I wanted to see what happens if you create a setup where your usable_file_mb is negative and you actually have to rebalance after a fatal failgroup error. I am using 12.1.0.2.0 on Oracle Linux 6.6/UEK3 in a KVM in case anyone is interested. I/O times aren’t stellar on that environment. It’s Oracle Restart, not clustered ASM.

Note: this post is only applicable if you are using ASM for data protection, e.g. normal or high redundancy. External redundancy is a different thing: if you lose a failgroup in a disk group with external redundancy defined then you are toast. The disk group will dismount on all ASM instances. No disk group = nothing to write to = crash of dependent databases.

Setup

Here is my setup. I created the VM with 2 volume groups, one for the operating system, and another one for Oracle. After the installation of the database and Grid Infrastructure I have a disk group named DATA comprised of 4 disks, each in their own failgroup (which is both the default and intentional):

ASMCMD> lsdsk -k
Total_MB  Free_MB  OS_MB  Name       Failgroup  Failgroup_Type  Library  Label  UDID  Product  Redund   Path
    5119     4131   5119  DATA_0000  DATA_0000  REGULAR         System                         UNKNOWN  /dev/vdc1
    5119     4127   5119  DATA_0001  DATA_0001  REGULAR         System                         UNKNOWN  /dev/vdd1
    5119     4128   5119  DATA_0002  DATA_0002  REGULAR         System                         UNKNOWN  /dev/vde1
    5119     4139   5119  DATA_0003  DATA_0003  REGULAR         System                         UNKNOWN  /dev/vdf1
ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  NORMAL  N         512   4096  1048576     20476    16525             5119            5703              0             N  DATA/
ASMCMD>

My database uses the datafiles as shown here:

SQL> select name, bytes from v$datafile;

NAME                                                              BYTES
------------------------------------------------------------ ----------
+DATA/NCDB/DATAFILE/system.258.871538097                      817889280
+DATA/NCDB/DATAFILE/sysaux.257.871538033                      629145600
+DATA/NCDB/DATAFILE/undotbs1.260.871538187                     62914560
+DATA/NCDB/DATAFILE/users.259.871538183                         5242880

ASMCMD must take the file information from v$asm_file-the “space” column seems to take the normal redundancy into account:


SQL> select block_size, blocks, bytes, space, redundancy from v$asm_file where file_number = 259 and incarnation = 871538183;

BLOCK_SIZE     BLOCKS      BYTES      SPACE REDUND
---------- ---------- ---------- ---------- ------
      8192        641    5251072   12582912 MIRROR

As others have explained already the “space” column also takes into account ASM metadata that is of course mirrored too. So to me those numbers make sense. If you look up to the asmcmd output you will notice that the useable_file_mb is 5703MB.

This is where it goes wrong

Let’s cause trouble for the sake of this blog post and to trigger failure. The following is STRONGLY discouraged in your environment! I have put this here so you can see the effect of “negative free space” without having to endure this yourself.

I am deliberately oversubscribing space in the data disk group, which is definitely Not A Good Thing(tm). Please monitor usable_file_mb for each disk group carefully and add storage when needed (you might be unpleasantly surprised how long it can take to add storage so request it early). You will see why it shouldn’t be negative-ever-shortly:

SQL> create tablespace iamtoolargetofit datafile size 6g;

Tablespace created.

[oracle@server1 ~]$ asmcmd lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  NORMAL  N         512   4096  1048576     20476     4212             5119            -453              0             N  DATA/

The new tablespace is bigger than usable_file_mb and turned that number into the negative. Now if I were to lose a failgroup (the term “disk” is a bit misleading here) I’d be in Big Trouble. Since this is my controlled lab environment and I wanted to see what happens I carried on.

On the host I use virsh detach-disk to remove an arbitrary of my ASM disks. Note from further up the repair timer was 0 for all disks. In other words, there won’t be a grace period to allow for transient failures, the disk will be dropped straight away.

# virsh detach-disk --domain server_12c_asm --target /var/lib/.../server1_asm_data02.qcow2 --current
Disk detached successfully

This removed disk /dev/vdd on the guest. This has not gone unnoticed. From the ASM alert.log:

NOTE: Standard client NCDB:NCDB:ASM registered, osid 7732, mbr 0x1, asmb 7728 (reg:3524888596)
2015-02-15 06:01:56.858000 -05:00
NOTE: client NCDB:NCDB:ASM mounted group 1 (DATA)
2015-02-15 08:23:29.188000 -05:00
NOTE: process _user9772_+asm (9772) initiating offline of disk 1.3916023153 (DATA_0001) with mask 0x7e in group 1 (DATA) with client assisting
NOTE: checking PST: grp = 1
GMON checking disk modes for group 1 at 10 for pid 23, osid 9772
NOTE: checking PST for grp 1 done.
NOTE: initiating PST update: grp 1 (DATA), dsk = 1/0xe969c571, mask = 0x6a, op = clear
GMON updating disk modes for group 1 at 11 for pid 23, osid 9772
WARNING: Write Failed. group:1 disk:1 AU:1 offset:1044480 size:4096
path:/dev/vdd1
 incarnation:0xe969c571 synchronous result:'I/O error'
 subsys:System krq:0x7fb991c5e9e8 bufp:0x7fb991c5f000 osderr1:0x0 osderr2:0x0
 IO elapsed time: 0 usec Time waited on I/O: 0 usec
WARNING: found another non-responsive disk 1.3916023153 (DATA_0001) that will be offlined
NOTE: group DATA: updated PST location: disk 0000 (PST copy 0)
NOTE: group DATA: updated PST location: disk 0002 (PST copy 1)
NOTE: group DATA: updated PST location: disk 0003 (PST copy 2)
NOTE: PST update grp = 1 completed successfully

...

NOTE: process _b000_+asm (11355) initiating offline of disk 1.3916023153 (DATA_0001) with mask 0x7e in group 1 (DATA) without client assisting
NOTE: sending set offline flag message (1976690176) to 1 disk(s) in group 1
Errors in file /u01/app/oracle/diag/asm/+asm/+ASM/trace/+ASM_gmon_5836.trc:
ORA-27072: File I/O error
Linux-x86_64 Error: 5: Input/output error
Additional information: 4
Additional information: 4088
Additional information: 4294967295
Errors in file /u01/app/oracle/diag/asm/+asm/+ASM/trace/+ASM_gmon_5836.trc:
ORA-27072: File I/O error
Linux-x86_64 Error: 5: Input/output error
Additional information: 4
Additional information: 4088
Additional information: 4294967295
Errors in file /u01/app/oracle/diag/asm/+asm/+ASM/trace/+ASM_gmon_5836.trc:
ORA-27072: File I/O error
Linux-x86_64 Error: 5: Input/output error
Additional information: 4
Additional information: 4088
Additional information: 4294967295
Errors in file /u01/app/oracle/diag/asm/+asm/+ASM/trace/+ASM_gmon_5836.trc:

...


WARNING: Disk 1 (DATA_0001) in group 1 mode 0x15 is now being offlined
NOTE: initiating PST update: grp 1 (DATA), dsk = 1/0xe969c571, mask = 0x6a, op = clear
GMON updating disk modes for group 1 at 12 for pid 25, osid 11355

...

SUCCESS: PST-initiated drop disk in group 1(3645453725))
2015-02-15 08:24:04.910000 -05:00
NOTE: Attempting voting file refresh on diskgroup DATA
NOTE: starting rebalance of group 1/0xd949359d (DATA) at power 1
Starting background process ARB0
ARB0 started with pid=26, OS id=11364
NOTE: assigning ARB0 to group 1/0xd949359d (DATA) with 1 parallel I/O
NOTE: F1X0 on disk 3 (fmt 2) relocated at fcn 0.26087: AU 0 -> AU 4059
NOTE: 02/15/15 08:24:04 DATA.F1X0 copy 2 relocating from 1:10 to 2:10 at FCN 0.26087
NOTE: 02/15/15 08:24:04 DATA.F1X0 copy 3 relocating from 2:10 to 3:4059 at FCN 0.26087
NOTE: F1B1 fcn on disk 0 synced at fcn 0.26087
NOTE: F1B1 fcn on disk 2 synced at fcn 0.26087
2015-02-15 08:24:28.853000 -05:00
NOTE: restored redundancy of control and online logs in DATA
2015-02-15 08:24:33.219000 -05:00

...

And that went on for a little while. As with any rebalance you can see the progress in v$asm_operation:

SQL> r
  1* select * from v$asm_operation

GROUP_NUMBER OPERA PASS      STAT      POWER     ACTUAL      SOFAR   EST_WORK   EST_RATE EST_MINUTES ERROR_CODE                                       CON_ID
------------ ----- --------- ---- ---------- ---------- ---------- ---------- ---------- ----------- -------------------------------------------- ----------
           1 REBAL RESYNC    DONE         11         11          0          0          0           0                                                       0
           1 REBAL REBALANCE RUN          11         11        559       4529      12417           0                                                       0
           1 REBAL COMPACT   WAIT         11         11          0          0          0           0                                                       0

You can see detailed information about the disk dropping in asmcmd too:

ASMCMD> lsdsk -kpt
Total_MB  Free_MB  OS_MB  Name                Failgroup  Failgroup_Type  Library  Label  UDID  Product  Redund   Group_Num  Disk_Num      Incarn  Mount_Stat  Header_Stat  Mode_Stat  State    Create_Date  Mount_Date  Repair_Timer  Path
    5119     3237      0  _DROPPED_0001_DATA  DATA_0001  REGULAR         System                         UNKNOWN          1         1  3916023153  MISSING     UNKNOWN      OFFLINE    FORCING  15-FEB-15    15-FEB-15   0
    5119      323   5119  DATA_0000           DATA_0000  REGULAR         System                         UNKNOWN          1         0  3916023154  CACHED      MEMBER       ONLINE     NORMAL   15-FEB-15    15-FEB-15   0             /dev/vdc1
    5119      324   5119  DATA_0002           DATA_0002  REGULAR         System                         UNKNOWN          1         2  3916023152  CACHED      MEMBER       ONLINE     NORMAL   15-FEB-15    15-FEB-15   0             /dev/vde1
    5119      328   5119  DATA_0003           DATA_0003  REGULAR         System                         UNKNOWN          1         3  3916023156  CACHED      MEMBER       ONLINE     NORMAL   15-FEB-15    15-FEB-15   0             /dev/vdf1
ASMCMD>

Missing, forcing, offline all don’t sound too good… What is worse was this:

ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  NORMAL  Y         512   4096  1048576     15357        0             5119           -2559              1             N  DATA/

And then the inevitable happened:

2015-02-15 08:31:55.491000 -05:00
ERROR: ORA-15041 thrown in ARB0 for group number 1
Errors in file /u01/app/oracle/diag/asm/+asm/+ASM/trace/+ASM_arb0_11547.trc:
ORA-15041: diskgroup "DATA" space exhausted
WARNING: Resync encountered ORA-15041; continuing rebalance
NOTE: stopping process ARB0
NOTE: requesting all-instance membership refresh for group=1
WARNING: rebalance not completed for group 1/0xd949359d (DATA)
GMON updating for reconfiguration, group 1 at 20 for pid 25, osid 11773
NOTE: cache closing disk 1 of grp 1: (not open) _DROPPED_0001_DATA

ORA-15041 is the dreaded error: space exhausted. This was to be expected, but I had to see it with my own eyes.

The ASM rebalance is not complete:

SQL> r
  1* select * from v$asm_operation

GROUP_NUMBER OPERA PASS      STAT      POWER     ACTUAL      SOFAR   EST_WORK   EST_RATE EST_MINUTES ERROR_CODE                                       CON_ID
------------ ----- --------- ---- ---------- ---------- ---------- ---------- ---------- ----------- -------------------------------------------- ----------
           1 REBAL RESYNC    DONE         11                                                                                                               0
           1 REBAL REBALANCE ERRS         11                                                         ORA-15041                                             0
           1 REBAL COMPACT   WAIT         11                                                                                                               0

Interestingly the database remained up-but I can’t do anything with it as these examples demonstrate:

SQL> alter tablespace IAMTOOLARGETOFIT add datafile size 100m;
alter tablespace IAMTOOLARGETOFIT add datafile size 100m
*
ERROR at line 1:
ORA-01119: error in creating database file '+DATA'
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-15041: diskgroup "DATA" space exhausted



SQL> create table t as select * from dba_objects;
create table t as select * from dba_objects
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace USERS

Errors in file /u01/app/oracle/diag/rdbms/ncdb/NCDB/trace/NCDB_arc3_12194.trc:
ORA-19504: failed to create file "+DATA"
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-15041: diskgroup "DATA" space exhausted
ARC3: Error 19504 Creating archive log file to '+DATA'
2015-02-15 08:47:31.410000 -05:00
Unable to create archive log file '+DATA'

You are now in a bit of a situation … So be careful when you are on negative values for usable_file_mb-a loss of only one failgroup does not cause the disk group to be dismounted in ASM normal redundancy, but you are likewise ending up in a very undesirable place.