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:
- 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
- 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.
Martins Blog 