Tips from the trenches by Rohit Gupta
Being a DBA, most of us have been regularly talking about and dealing
with Logical I/O and Physical I/O as part of performance tuning discussions
and issues. I have asked questions like - What actually is a logical I/O?
What is a physical I/O? Which is better? etc. - to many experienced
DBAs and practitioners. This paper is a collection of the answers I got, my
research and my own experience on these topics over last few years.
What is a Physical I/O??
Whenever you execute a query, Oracle has to go and fetch data to
give you the result of the query execution. Here, data means the actual data
in data blocks. Whenever a new data block is requested, it has to be fetched
from the physical datafiles residing on the physical disks. This fetching of
data blocks from the physical disk involves an I/O operation known as
physical I/O. By virtue of this physical I/O, now the block has been fetched
and read into the memory area called buffer cache. This is a default action.
We know that a data block might be requested multiple times by multiple
queries. So what happens when the same data block is requested again by the
some other user?
What is a Logical I/O??
Once a physical I/O has taken place and the block has been read into the
memory, the next request for the same data block won't require the block to
be fetched from the disk and hence avoiding a physical I/O. So now to return
the results for the select query requesting the same data block, the block
will be fetched from the memory and is called a Logical I/O.
Whenever the quantum of Logical I/O is calculated, two kinds of reads are
considered - Consistent reads and Current reads. Jointly, these 2 statistics
are known as Logical I/O performed by Oracle. Let us look at these
separately to better understand the concept of Logical I/O.
Consistent reads
It is a well known fact that whenever a change is induced in a data
block, the old data/entry is written to the UNDO/ROLLBACK segments. From the
fundamentals of UNDO, we also know that this is to provide a read consistent
view of the data block to other users trying to read the same data block.
Consistent reads mean reading the block in a consistent mode w.r.t a
?point in time?. Here the phrase ?point in time? means the time when the
query/statement began. A consistent read might or might not involve any UNDO
data. UNDO data will be applied when it is necessary to roll back a data
block to the required ?point in time? when the SQL statement was fired. If
on reading the buffer cache, it is found that the data block is already in
the required state, no UNDO data is required because the block is already
consistent.
I have referred to the phrase 'state of data block? several times. Let me
pause here and explain what I actually mean. The 'state of data block? is
basically determined by the SCN. When the SQL statement is issued the SCN
number is noted. Then Oracle scans all data blocks (needed for the
transaction) and checks which blocks have SCN higher than the locked SCN.
For such data blocks, UNDO data is used to roll back to the locked SCN and
hence provide a consistent read.
Consistent reads and array size
Consistent reads could also depend on and vary with the array size
setting of SQLPLUS. The default value is 15. Array size is the number of
rows fetched in a single read. The value of array size is an indicator of
the number of network round trips made to fetch the required data from
Oracle. A careful adjustment of array size value can improve performance by
reducing the network round trips. A higher array size might be good for
performance of queries (by reducing the network round trips and also the
consistent reads) but too high value also uses more memory. However, array
size is not a setting restricted to SQLPLUS; it can be set in many other
applications requesting data from oracle database.
Now since we know the dependency on array size, let us extend our
discussion to understand a rule for estimating the amount of consistent
gets.
Say our table spans across ?N? number of data blocks (space usage can be
calculated using show_space). Now in an ideal condition, the numbers of
consistent gets to do a full table scan of this table should be about ?N?
i.e every block read at least once. But here comes into picture the array
size and number of rows in the table. Suppose the array size is ?A? and the
number of rows in the table ?R?. Hence, the rule of thumb to estimate the
consistent gets is R/A+N. This means that the block needs to be visited in
consistent mode R/A times plus once for each block i.e N. The expression R/A
means the number of trips needed to fetch all the rows because in 1 trip,
only ?A? rows (array size) can be fetched.
Current reads
Current reads mean getting the data block(s) as it exists (i.e the ?as of
now? copy). Unlike consistent gets, there is no concept of ?point in time?
in case of current reads. Current read gets the data block in the current
shape. The difference between current and consistent gets can be best
understood by considering a DML statement as explained below.
In case of DMLs - There is at least one consistent read performed by
Oracle (this is called the read phase of the DML statement) to check which
rows need to be modified (to ensure that only that data is modified which
was there when the DML was fired). This means that Oracle will try and get
the state of data blocks as they were when the DML statement was fired and
then perform the DML action on that data. And then there are subsequent
current reads to actually get the current state of all those rows and modify
them as per the consistent reads (point in time data) which happened
earlier. More the number of rows to be altered more are the current reads.
In case of Select statements - There could be any number of current reads
and consistent gets. This will depend on the number of blocks that have been
modified and how many of them need to have UNDO data applied to create a
consistent copy and provide the result of the Select statement. There will
be some consistent reads performed while reading the segment header and if
there are no modifications to any of the requested blocks, rest would be
current reads.
What they actually mean in terms of performance??
As part of this whole discussion, it is also important to know what
logical I/O and physical I/O mean in terms of performance. A physical I/O is
not good for performance of queries - well, that's quite obvious. Whenever a
physical I/O takes place, Oracle tries to read the data block from the disk
which means that number of factors like disk speed etc. get involved and
play a role in deciding the response time etc. Oracle has now to depend on
the external hardware resource like hard disks, network etc. to get you the
results. The goal hence is to avoid physical I/O as far as possible. But at
the same time, it won't be apt to generically comment that logical reads are
always good. Though desired in many cases, excess of everything is bad and
logical I/O is no exception to this rule.
A Logical I/O is considered better for performance (when compared to
Physical I/O) because the reads happen from the memory as the data block is
pre-fetched from the disk. So now Oracle doesn't need to go to disk to fetch
blocks for your query results. But it is important to note that excess of
Logical reads (I/O) is also not good or recommended. First reason is that a
logical read might have resulted in a physical read to fetch the data block
into the buffer cache. Second, every time a block is read from the cache, a
lock or latch is acquired on the cache and hence higher logical reads could
mean that there are high chances of buffer cache contention.
Place holders for these statistics
Having talked about I/O, let us see where in the database these stats are
available and how to interpret them.
AUTOTRACE
AUTOTRACE is a utility used to get the execution path and execution
statistics of a SQL statement. Here are some important stats worth knowing:
- DB Blocks Reads - Current Reads, i.e the state of the block as it
exists
- Consistent Reads - blocks read from the buffer cache in a consistent
state
- Logical Reads - DB Blocks Reads + Consistent Reads
- Physical Reads - blocks read from the disk
TKPROF
TKPROF stats are considered more accurate as compared to AUTOTRACE as it
the part of the actual output of the transaction/query where as AUTOTRACE
provides more of the speculative or estimated statistics. Here are the stats
worth some interest relevant to our current discussion:
- DISK - number of physical reads
- QUERY - number of buffers read in consistent mode
- CURRENT - number of buffers read in current mode
Generating and reading AUTOTRACE and TKPROF reports is all together a
different topic and hence we won't invest time and words in discussing them
here as part of this paper.
V$SYSSTAT
Use the following query (or similar) to get the information from this
dictionary view:
select name, value from v$sysstat where name in
('consistent gets','db block gets','physical reads');
*******************************************************************************************
and include the URL for the page.
