Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

The Best Oracle Resource on the Web

Measuring Oracle Segment I/O

by John Weeg

 

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.

 

 

                     
                     

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational