Disclaimer : Apart from the step by step approach nothing here is original thinking.
External influences on disk I/O speed.
1. Stripe size - The stripe size for raw disk partitions will influence I/O
behavior, especially for multi-block read operations (full scans).
2. TCP/IP - TCP IP settings and Oracle TNS settings (e.g. tcp.nodelay) effect I/O timings.
3. RAID level - The RAID used on the disk array can have a profound impact on end-to-end I/O timings. For example, up until 2007, RAID 5 with Oracle was sometimes inappropriate for high-update databases.
4.Disk Controllers - A bottleneck in the disk farm such as a lack of controller resources can precipitate high I/O latency.
5.Storage Array Internals - Many of today's storage arrays have specialized optimization software and on-board RAM caching to improve throughput.
6.SAN/NAS - Dynamically attached storage has special issues
Now for tuning following might be the approaches:
1. Try to use the O/S striping software to distribute database files over as many disks as you can.
2.Filesystemio_options needs to be set with Mount the filesystem with direct IO option.
Mount –F vxfs –o \
"mincache=direct" => bypass buffer cache on read
"convosync=direct" => force direct I/O for DB writers
Now if your filesystem not mounted with this option then filesystemio_options parameter needs to set to setall to use
Parameter: FILESYSTEMIO_OPTIONS OR _FILESYSTEMIO_OPTIONS
IO operations on filesystem files.
This parameter should not normally be set by the user.
The value may be any of the following:
"asynch" - Set by default. This allows asynchronous IO to be used where supported by the OS.
"directIO" - This allows directIO to be used where supported by the OS. Direct IO bypasses any Unix buffer cache.
"setall" - Enables both ASYNC and DIRECT IO.
"none" - This disables ASYNC IO and DIRECT IO so that Oracle uses normal synchronous writes, without any direct io options.
Oracle recommends that you use the value SETALL instead of the value DIRECTIO, because the DIRECTIO value disables asynchronous I/O. The default value for this parameter is ASYNC.
DISK_ASYNCH_IO controls whether I/O to datafiles, control files, and logfiles is asynchronous (that is, whether parallel server processes can overlap I/O requests with CPU processing during table scans). If your platform supports asynchronous I/O to disk, Oracle Corporation recommends that you leave this parameter set to its default value. However, if the asynchronous I/O implementation is not stable, you can set this parameter to false to disable asynchronous I/O. If your platform does not support asynchronous I/O to disk, this parameter has no effect.
If you set DISK_ASYNCH_IO to false, then you should also set DBWR_IO_SLAVES or DB_WRITER_PROCESSES should to a value other than its default of zero in order to simulate asynchronous I/O.
Parameters: DB_WRITER_PROCESSES or DBWR_IO_SLAVES see comments in DISK_ASYNCH_IO
mincache=direct and convosync=direct
Mincache=direct and convosync=direct allow data to be transferred directly from Oracle buffer cache to disk and disk to Oracle buffer cache. This avoids double buffering by bypassing the file system buffer cache and can improve physical read/write performance. But cases where the disk read could have been avoided because a required block was in filesystem buffer cache may be negatively impacted.
Step 2 : If Step 1 results in small increase in performance or if no performance change at all then check ltaches:
Relative amount of time being spent waiting for latches
SELECT event, time_waited,
round(time_waited*100/ SUM (time_waited) OVER(),2) wait_pct
FROM (SELECT event, time_waited
WHERE event NOT IN
'rdbms ipc reply',
'rdbms ipc message',
'PX Idle Wait',
'PL/SQL lock timer',
'WMON goes to sleep',
'virtual circuit status',
'SQL*Net message from client',
'parallel query dequeue wait',
(SELECT NAME, VALUE
WHERE NAME LIKE 'CPU used when call started'))
ORDER BY 2 DESC;
Look at the sleeps in v$latch to determine which latches are likely to be contributing most to this problem:
select name, gets, sleeps,
sleeps*100/sum(sleeps) over() sleep_pct, sleeps*100/gets
from v$latch where gets>0
order by sleeps desc;
From metalink :
How To Identify a Hot Block Within The Database Buffer Cache. [ID 163424.1]
First determine which latch id(ADDR) are interesting by examining the number of sleeps for this latch. The higher the sleep count, the more interesting the latch id(ADDR) is:
SQL> select CHILD# "cCHILD"
, ADDR "sADDR"
, GETS "sGETS"
, MISSES "sMISSES"
, SLEEPS "sSLEEPS"
where name = 'cache buffers chains'
order by 5, 1, 2, 3;
Run the above query a few times to to establish the id(ADDR) that has the most consistent amount of sleeps. Once the id(ADDR) with the highest sleep count is found then this latch address can be used to get more details about the blocks currently in the buffer cache protected by this latch.
The query below should be run just after determining the ADDR with
the highest sleep count.
SQL> column segment_name format a35
select /*+ RULE */
e.owner ||'.'|| e.segment_name segment_name,
x.dbablk - e.block_id + 1 block#,
x.hladdr = 'ADDR' and
e.file_id = x.file# and
x.hladdr = l.addr and
x.dbablk between e.block_id and e.block_id + e.blocks -1
order by x.tch desc ;
In order to reduce contention for this object the following mechanisms can be put in place:
1) Examine the application to see if the execution of certain DML and SELECT statements can be reorganized to eliminate contention on the object.
2) Decrease the buffer cache -although this may only help in a small amount of cases.
3) DBWR throughput may have a factor in this as well.
If using multiple DBWR's then increase the number of DBWR's.
4) Increase the PCTFREE for the table storage parameters via ALTER TABLE or rebuild. This will result in less rows per block.
5) Consider implementing reverse key indexes
(if range scans aren't commonly used against the segment)
Moving OUTLINES from One DB to Another
A user may want to copy OUTLINEs from one database to another, for example,to copy the outlines of an application from a test database to a production database. This can easily be done utilizing Oracle’s export and import routines.
Once the optimization for an application is achieved in a test database, you can move the outlines created and stored for the application in a production database.
Instead of recreating these outlines in the production database using the CREATE OUTLINE ... FOR CATEGORY command for each optimized query of the application, export the outlines for the specified category from the test database, and then import these in the production database.