The disk server may notice that access to a drive is taking place sequen tially, or almost sequentially, and starts to read several pages ahead.

2. The DBMS may notice that a SELECT statement is accessing the pages

of an index or table sequentially, or almost sequentially, and starts to read several pages ahead; this is called dynamic prefetch in DB2 for z/OS.

List Prefetch

In the previous example, this benefit was achieved simply as a result of the table and index rows being in the same sequence. DB2 for z/OS is in fact able to create skip-sequential access even when this isnot the case; to do this, it has to access all the qualifying index rows and sort the pointers into table page sequence before accessing the table rows. Figures 2.6 and 2.7 contrast an access path that does not use list prefetch with one that does, the numbers indicating the sequence of events.

103 109 101 103 109 101 4 6 2 1 3 5

Index

Table

Page

103 109 101 103 109 101 5 6 7 1 2 3

Index

Table

Figure 2.7 DB2 List Prefetch.

Data Block Prefetching

This feature is used by Oracle, again when the table rows being accessed are not in the same sequence as the index rows. In this case, however, as shown in Figure 2.8, the pointers are collected from the index slice and multiple random I/Os are started to read the table rows in parallel. If the table rows represented by steps 4, 5, and 6 reside on three different drives, all three random I/Os will be performed in parallel. As with list prefetch, we could use Figures 2.6 and 2.8 to contrast an access path that does not use data block prefetching with one that does.

Before we leave assisted random reads, it might be worth considering the order in which a result set is obtained. An index could provide the correct sequence automatically, whereas the above facilities could destroy this sequence before the table rows were accessed, thereby requiring a sort.

Comment

Throughout this book, we will refer to three types of read I/O operations: syn- chronous, sequential, and assisted random reads; in order to make the estimation process usable, initially only the first two types will be addressed, but Chapter 15 will discuss assisted random read estimation in some detail.

Note that SQL Server uses the termIndex Read-Ahead and Oracle uses the term Index Skip Scan. The former refers to the reading-ahead of the next leaf pages following leaf page splits, while the latter refers to the reading of several index slices instead of doing a full index scan.

103 109 101 103 109 101 5 6 4 1 2 3

Index

Table

Figure 2.8 Oracle data block prefetching.

Assisted Sequential Reads

When a large table is to be scanned, the optimizer may decide to activate par- allelism; for instance, it may split a cursor into several range-predicate cursors, each of which would scan one slice. When several processors and disk drives are available, the elapsed time will be reduced accordingly. Again we will put this to one side until we come to Chapter 15. Please note that the term assisted sequential reads is again not one that is used by any of the DBMSs.

Synchronous and Asynchronous I/Os

Having discussed these different access techniques, it will be appropriate now to ensure we fully appreciate one final consideration, synchronous and asynchronous I/Os as shown in Figure 2.9.

The term synchronous I/O infers that while the I/O is taking place, the DBMS is not able to continue any further; it is forced to wait until the I/O has completed. With a synchronous read, for example, we have to identify the row required (shown as “C” to represent the first portion of CPU time in the figure), access the page and process the row (shown as the second portion of CPU time), each stage waiting until the previous stage completes.

Asynchronous reads on the other hand are being performed in advance while aprevious set of pages are being processed; there may be a considerable overlap between the processing and I/O time; ideally the asynchronous I/O will complete

Synchronous read Random read Synchronous write Log write Asynchronous read Sequential prefetch Assisted random read Asynchronous write Database writes I/O I/O C C C C C Sync I/O C Sync I/O C I/O I/O

Figure 2.9 Synchronous and asynchronous I/O.

before the pages are actually required for processing. Each group of pages being prefetched and processed in this way is shown in Figure 2.9; note that a syn- chronous read kick-starts the whole prefetch activity before the first group of pages is prefetched to minimize the first wait.

When the DBMS requests a page, the disk system may read the next few pages as well into a disk cache (anticipating that these may soon be requested); this could be the rest of the stripe, the rest of the track, or even several stripes (striping is described shortly). We call this Disk Read Ahead.

Most database writes are performed asynchronously such that they should have little effect on performance. The main impact they do have is to increase the load on the disk environment, which in turn may affect the performance of the read I/Os.