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.