Tuning Physical IO


You can barely tune you system for best performance logical IO but it doesnt mean your system will not make any physical IO from your Disk Subsystem because you data is on the disk systems and of course you cannot buy enough memory to store all your data in memory. Now we will going to interested in pyhsical IO performance.

1- When creating a tablespace always be careful about defining “extent management” and “segment space management” parameters. “extent management local” parameter can have two distinct  values. Local: stands for extent management should be in datafile headers which is using bitmap lookup table and far faster than Dictionary: extent management is done in system tablespaces which has lower performance.
tablespaces which has lower performance.
-- creating a tablescpace
--
create tablespace <ts_name>
     datafile '$home/oradata/<SID>/<dbf_name>.dbf'
      size=10M
        extent management local
          segment space management AUTO;

create table <table_name> (<col_name> number) tablespace <ts_name>;

2- Be careful about your RAID levels

RAID      Type of Raid                                Control_File   Database_File     Redo_Log         Archive_Log
----              ------------                                          ------------            -------------              --------                  -----------
0                  Striping                                          Avoid                    OK                        Avoid               Avoid
1                 Shadowing                                     Best                       OK                         Best               Best
1+0   Striping and Shadowing                      OK                           Best                     Avoid              Avoid
3   Striping with static parity                       OK                             OK                        Avoid              Avoid
5   Striping with rotating parity                  OK                            Best(*)                     Avoid           Avoid

(*) if RAID0-1 not available
3- You can examine file statistics
--
-- you can check file statistics
--
select
        t2.name,
        t1.phyrds,
        t1.phywrts,
        t1.readtim,
        t1.writetim
from
        v$filestat t1,
        v$datafile t2
where
        t1.file#=t2.file#
order by phyrds desc, phywrts desc;

--
-- temporary statistics
--
select
       t2.name,
       t1.phyrds,
       t1.phywrts,
       t1.readtim,
       t1.writetim
from
      v$tempstat t1,
      v$tempfile t2
where
      t1.file#=t2.file#
order by phyrds desc, phywrts desc;

4- Consider the following parameters for background processes performance
tuning. More than one dbwr(db writer process can be used)

If not asynchronous IO then
       DBWR_IO_SLAVES = [1...9]
Else
       DISK_ASYNCH_IO = [ON | OF]
End if

--
-- tuning dbwriter process
--
select * from v$parameter where name like '%dbwr_io_slaves%';
select * from v$parameter where name like '%db_writer_processes%';
select * from v$parameter where name like '%disk_asynch_io%';
--alter system set db_writer_processes=[0...9];
alter system set db_writer_processes=2 scope=spfile;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: