Key Concepts - Troubleshooting Oracle Performance - page 38

SQL statements processing a lot of data : Whenever a lot of data is processed, the hard
parse time is usually several orders of magnitude less than the execution time. In that
kind of situation, using bind variables isn't only irrelevant for the whole response time,
it also increases the risk that the query optimizer will generate very inefficient execution
plans. Therefore, bind variables shouldn't be used. Typically, such SQL statements are
used for batch jobs, for reporting purposes, or, in data warehousing environments,
by OLAP and BI tools.
Reading and Writing Blocks
To read and write blocks belonging to data files, the database engine takes advantage of several types of disk I/O
operations (see Figure 2-4 ):
Logical reads : A server process performs a logical read when it accesses a block that is
either in the buffer cache or in the private memory of the process. Note that logical reads
are used for both reading and writing data to a block.
Buffer cache reads : A server process performs a buffer cache read when it needs a block that
isn't in the buffer cache yet. Consequently, it opens the data file, reads the block, and stores
it in the buffer cache.
DBWR writes : In general, server processes don't write data into a data file, they modify only
the blocks that are stored in the buffer cache. Then, the database writer process (which is a
background process) is responsible for storing the modified blocks (also called dirty blocks )
in the data files.
Direct reads : In some particular situations (described in Chapter 13 and 15), a server
process is able to directly read blocks from a data file. When it uses this method, the blocks,
instead of being loaded in the buffer cache, are directly transferred to the private memory
of the process.
Direct writes : In some particular situations (described in Chapter 15), a server process is
able to directly write blocks into a data file.
In case it isn't important to distinguish between disk I/O operations involving the buffer cache and those that
don't, the following terms are used:
Physical reads include buffer cache reads and direct reads.
Physical writes include direct writes and DBWR writes.