|
What is Really Going On?
We are taught, from the beginning, that we want to separate our
tablespaces onto different mount points or drives to minimize I/O level
contention. There are devices that minimize this physical level
contention; but most of my smaller clients don't have these devices, so we
still have this concern. How do we see what is really at the file level?
Given a smaller system, where I can fit the majority of the database into
memory; does the I/O matter?
Theory
Ideally all the data we want to work with are in memory and no I/O is
needed. In reality, you usually can't count on this being the case. So,
our goal is to try to minimize the disk physical movement for any given
data request. For example, if the index and the data are on the same disk,
there is movement needed for the index and then the same disk must move
for the data. If the next read wants the next record, then we must move
back for the index and back again for the data. We have made the read for
the data and the read for the index get in each other's way.
The theory says that all objects that might be used in the same
transaction should be on different mount points. So we have the following
minimum mount points:
§
System tablespace
§
Data tablespace
§
Index tablespace
§
Rollback segments
§
Archive logs
§
Temporary tablespace
These six mount points would give us our basic good system setup. Let's
put this theory to the test.
Test It
There are two very useful dynamic system views,
v$datafile and
v$tempfile, that will allow us
to test this theory. Previous to 8.1 you won't find
v$tempfile. These both have the
same layout, so I will just work with
v$datafile for the testing. The
fields we are interested in first are the number of physical reads/writes
and the number of blocks read and written.
This view gives the statistics since instance startup so we have created a
table to isolate and compare the results of
v$datafile for the current
activity:
create table phy_io tablespace data01
storage
(initial 64k next 64k pctincrease 0) pctfree 0 pctused 80 unrecoverable
as select file#,phyrds,phywrts,phyblkrd,phyblkwrt from v$filestat;
So let's see what we have right now in a newly started instance - the only
activity has been to create this table:
SQL> select c.file_name,a.file#,a.phyrds-b.phyrds
phyrds,a.phywrts-
b.phywrts phywrts
FILE_NAME (IO)
FILE#
PHYRDS
PHYWRTS PHYBLKRD
PHYBLKWRT
---------------- ---------- ---------- ----------
---------- ----------
SYSTEM01.DBF
1
29
26
47
26
DATA0101.DBF
3
1
1
1
1
The I/O against DATA0101.DBF is me accessing the
phy_io table. If we check
memory we can see the current blocks:
SQL> select b.file_name,a.file#,a.cnt from
2
(select file#,count(1) cnt from v$bh group by file#) a
3
,dba_data_files b
4
where a.file#=b.file_id;
FILE_NAME (IN MEMORY)
FILE#
CNT
---------------------- ---------- ----------
SYSTEM01.DBF
1
569
RBS01.DBF
2
11
DATA0101.DBF
3
2
Okay, so we see how the system starts. Now, if we access some data, what
happens?
SQL> select dsn,subst_id from iuc4.ds_admin_tab where
dsn=523;
DSN SUBST_ID
---------- -----------
523 101316-69-2
Checking our I/O now we see there were four physical reads to get this
data in and we have four additional blocks allocated in memory:
FILE_NAME (IO)
FILE#
PHYRDS
PHYWRTS PHYBLKRD
PHYBLKWRT
---------------- ---------- ---------- ----------
---------- ----------
SYSTEM01.DBF
1
59
52
92
52
DATA0101.DBF
3
5
1
5
1
FILE_NAME (IN MEMORY)
FILE#
CNT
---------------------- ---------- ----------
SYSTEM01.DBF
1
587
RBS01.DBF
2
11
DATA0101.DBF
3
6
These four new blocks in memory are the data and index:
SQL> select b.owner,b.object_name,a.cnt from
2
(select objd,count(1) cnt from v$bh group by objd) a
3
,dba_objects
b
4
where b.data_object_id = a.objd and b.owner = 'IUC4';
OWNER
OBJECT_NAME
CNT
--------------- ------------------------- ----------
IUC4
DS_ADMIN_TAB
2
IUC4
DS_ADMIN_IDX1
2
To bring this data in, we performed four separate read actions and we see
that we needed to take two separate objects, table and index, from the
same file so we were contending with ourselves. We also see that there was
additional I/O against the system tablespace to get the object definition.
In addition, there is I/O going on against the system tablespace for
timing, so you will see that number rise even when there is no activity.
What Happens When We Update?
SQL> update iuc4.ds_admin_tab set subst_id =
'101316-69-2' where dsn=523;
1 row updated.
I also committed!
FILE_NAME (IO)
FILE#
PHYRDS
PHYWRTS PHYBLKRD
PHYBLKWRT
---------------- ---------- ---------- ----------
---------- ----------
SYSTEM01.DBF
1
70
78
118
78
DATA0101.DBF
3
5
1
5
1
FILE_NAME (IN MEMORY)
FILE#
CNT
---------------------- ---------- ----------
SYSTEM01.DBF
1
591
RBS01.DBF
2
11
DATA0101.DBF
3
6
Nothing happened! We see there is no physical change. I expected I/O at
least to the rollback file. If I rollback instead of commit, there is
still no I/O count change. So we are seeing that the system really wants
to work just in memory whenever it can.
Now let's force the issue with a checkpoint:
SQL> alter system checkpoint;
System altered.
FILE_NAME (IO)
FILE#
PHYRDS
PHYWRTS PHYBLKRD
PHYBLKWRT
---------------- ---------- ---------- ----------
---------- ----------
SYSTEM01.DBF
1
71
103
119
103
RBS01.DBF
2
1
12
1
12
DATA0101.DBF
3
6
4
6
4
FILE_NAME(IN MEMORY)
FILE#
CNT
---------------------- ---------- ----------
SYSTEM01.DBF
1 591
RBS01.DBF
2
11
DATA0101.DBF
3
6
Here we see the write contention. We performed a write against all three
files. If they are on the same mount point, then it happened serially
--they wrote single threaded.
What Else?
The v$filestat
also will tell us the time spent performing reads and writes, in
hundredths of a second, if
timed_statistics is set to true. When I look at a system that has been
up for a longer time, I see that the average time to write a block of data
is about ten times longer than the average time to read a block.
So What?
Take a look at your v$filestat
and v$tempstat views. Mine have
shown me that even though Oracle works in memory as much as possible, I
still need to be very aware of I/O-level contention. I also see that
wherever I can, I will try to minimize the number of write actions
performed.
Watch yours for a while to see what is really going on.
|
