Estimating Oracle Small and Large Block Read/Write Sizes

Estimating Oracle Small and “Large” Block Read/Write Sizes

EMC FAST Cache, VFCache, and EFDs have a sweet-tooth for small-block random reads showing the best performance boost for applications that can be characterizes as such. Application with a high percentage of large-block sequential read activity will also gain from these technologies, but less so particularly if the block sizes generated are blocked from their use. For example, FAST Cache on the VNX/CX works on 64KB chunks, but any IO call, read or write, exceeding 128KB is not a candidate IO call to be promoted to FAST Cache. VFCache works on 64KB chunks also with IO calls, read or write, exceeding 128KB not considered for placement in the host-based VFCache PCIe card. Orginally for VFCache the IO boundary was 64KB, EMC has raised this to 128KB. Update: EMC now calls VFCache XtremCache. The default boundary is still 128KB, but adjustable upward to 256KB.

Estimating Using Oracle AWR or Statspack Metrics

As of the 10gR2 release of the Oracle database AWR or Statspack reports supply us metrics that allow us to estimate percentages and sizes of small block and large block reads and writes.

Some metrics, full Oracle definitions are supplied at the end of this discussion, see below:

1. Physical read total multi block requests – prtmbr below -- two database blocks or more – available with 10gR2 or higher versions of the database
2. Physical read total IO requests -- prtior below – available with 10gR2 or higher versions of the database
3. Physical read total bytes – prtb below – available with 10gR2 or higher versions of the database

Physical read total multi block requests is a subset of physical read total IO requests, see the Oracle 11gR2 definitions below.  Note that Oracle considers multi-block requests as two or more database blocks.

These statistics are supplied for writes also.

Calculating the estimates:

Number small block reads = prtior- prtmbr = number of single block reads

% small block read = (prtior- prtmbr)/ prtior  = %sbr

% “large” block read = 1-((prtior- prtmbr)/ prtior) =%lbr 

‘Large’ is in quotes since Oracle defines their metrics for large as two or more database blocks, e.g. with an 8KB database block size this could be as little as 16KB.  

Number of bytes read small block = (prtior-prtmbr)*default_db_block_size = #sbrbytes

By definition, subtracting the multi-block reads (two or more blocks) from total reads gives the single-block reads. A small block read is the db_block_size by definition as long as non-default block size tablespaces are not used; estimates will be imprecise if the some tablespaces use a non-default block size.

Number of bytes read “large” block = prtb - #sbrbytes = #lbrbytes

Average Sizes:

Average KB Size small block read = (#sbrbytes/(prtior – prtmbr))/1024

Average Size small block read should equal default db_block_size, if non-default block size tablespaces are not used.

Average KB Size “large” block read = (#lbrbytes/prtmbr)/1024

Below are summary statistics with some commentary for two examples from production databases. 

What we would like to see is evidence of small block reads and writes as indicators of the usefulness of introducing EFDs, VFCache or FAST Cache.

The first is an OLTP-type database that is a “mixed” database with reporting also occurring. Knowing this we would expect significant small block activity. The second is an ETL-type database used for staging and pre-aggregating data for loading into a data warehouse. Knowing this we would expect significant large block activity. Both databases have a default database block size of 8KB.

Using the methods described above for each observation in the sample and then calculating the maximums and averages for the estimates we have the following.

	% small block read	% “large” block read	Size small block read KB	Size “large” block read KB	% small block write	% “large” block write	Size small block write KB	Size “large” block write KB
Maximums	1.00	0.53	8	351.30	0.84	0.70	8	446.04
Averages	0.89	0.11	8	128.25	0.56	0.44	8	50.30

Table 1: OLTP-Type Database with Some Reporting: Sample 94 Hourly AWR Reports

Table 1 shows some estimates for an OLTP-type database that is a “mixed” database in so far as reporting is also done against it.  Average small size block reads and writes are what we would expect from a database with db_block_size of 8K. At least once the percentage of large block read hit 53% and large block write hit 70%. But on average 89% of the time reads where small block, i.e. single block 8KB reads, and on average 56% of the time the writes where small block writes, single block 8KB also. On average even when the block reads and writes where "large" (two-blocks or larger) they are relatively small at 128.25KB and 50.3KB, particularly in comparison to the next sample.

	% small block read	% “large” block read	Size small block read KB	Size “large” block read KB	% small block write	% “large” block write	Size small block write KB	Size “large” block write KB
Maximums	0.99	0.96	8	3808.50	0.99	0.98	8	3976.13
Averages	0.72	0.28	8	1063.76	0.64	0.36	8	716.65

Table 2: ETL-Type Database: Sample 47 Hourly AWR Reports

Table 2 shows some estimates for an ETL-type database used for staging and pre-aggregating before loading into a data warehouse. Average small read and write size block is what we would expect from a database with db_block_size of 8K. Looking at the maximums we see at least once almost all activity was either small or large block reads and writes. But the averages tell us that a high percentage of reads and writes where large block at 28% and 36% respectively with average large block read sizes at 1063.76KB and average large block write sizes of 716.65KB. But even with these large-block findings there are significant number of small block reads and writes occurring at 72% and 64% respectively.

11gR2 Oracle definitions quoted from the Oracle Database Reference Guide:

Physical read total bytes: Total size in bytes of disk reads by all database instance activity including application reads, backup and recovery, and other utilities. The difference between this value and "physical read bytes" gives the total read size in bytes by non-application workload.

Physical read total IO requests: Number of read requests which read one or more database blocks for all instance activity including application, backup and recovery, and other utilities. The difference between this value and "physical read total multi block requests" gives the total number of single block read requests.

Physical read total multi block requests: Total number of Oracle instance read requests which read in two or more database blocks per request for all instance activity including application, backup and recovery, and other utilities.

Physical write total bytes: Total size in bytes of all disk writes for the database instance including application activity, backup and recovery, and other utilities. The difference between this value and "physical write bytes" gives the total write size in bytes by non-application workload.

Physical write total IO requests:  Number of write requests which wrote one or more database blocks from all instance activity including application activity, backup and recovery, and other utilities. The difference between this stat and "physical write total multi block requests" gives the number of single block write requests.

Physical write total multi block requests: Total number of Oracle instance write requests which wrote two or more blocks per request to the disk for all instance activity including application activity, recovery and backup, and other utilities.

     

           

Message was edited by: WBGaynor