1828960 Members
2135 Online
109986 Solutions
New Discussion

Oracle 9i and disk I/O

 
SOLVED
Go to solution
Jdamian
Respected Contributor

Oracle 9i and disk I/O

Hi

Last week DBAs upgraded the main database from Oracle 8.1.6 to Oracle 9i.

Now it is working but DBAs complain the disk I/O is slower than before and the process launched is slow (some additional processes post-migration).

I noticed the half physical memory is unused (2.6 GB from 5 GB) as opposite to Oracle 8.1.6 did (95 % of Phy memory was used).
The box is a V2500 class server (8 CPUs and 5 GB memory). Disks are LUNs from an XP512 whose cache memory is 12 GB size. The total amount of disk size is aprox. 850 GB (used by Oracle as raw disks).

I notice that the new SGA size is 800 MB while in Oracle 8.1.6 was 2.6 GB); but DBAs emphatize that the problem resides on disk I/O not in memory usage.

I don't know Oracle but my common sense tells that larger memory is used, fewer physical disk access (recall buffer-cache in HP-UX).

What do you think ? I need your advice

Thanx a lot
9 REPLIES 9
Simon Hargrave
Honored Contributor
Solution

Re: Oracle 9i and disk I/O

I don't know a lot about the differences in performance generally between 8i and 9i, but having an SGA 1/3 of the size surely must be a performance hit??

You mention buffer-cache. This shouldn't really be an issue with oracle since it has its own "cache" within the SGA. If anything I would expect the SGA of 9i to be larger (oracle tends to grow fatter with newer releases).

I'd definately suggest the SGA needs to be increased to give more buffer!

Get the DBA's to compare the cache-hit-ratio from the old database against the new - almost guaranteed that it will be lower in the new instance.

Re: Oracle 9i and disk I/O

Hmmm - so you DBAs have changed something and you haven't, but they think there is something wrong with the OS or hardware?? I think my response would be - 'when you have eliminated all the changes you have made, I will look at the OS and hardware'.

Absolutely they should look at setting the SGA back to 2.6GB!! An 800MB SGA is small even for a small database, never mind a 850GB one.

HTH

Duncan

I am an HPE Employee
Accept or Kudo
Nicolas Dumeige
Esteemed Contributor

Re: Oracle 9i and disk I/O

Hello,

With this kind of matter, evryone should stick to the facts. What does the DBA mean by it's slower? Which kind of test have they done ?
For instance, if you want to prove that the IO subsystem is just a good as it was, use dd to make some test.
If so, that will demonstrate that the Oracle configuration is the issue. Oracle 9 is pretty different from Oracle 8 is many ways, applying the old settings can result in bad performance.

Cheers

Nicolas
All different, all Unix
Jean-Luc Oudart
Honored Contributor

Re: Oracle 9i and disk I/O

Well, I would try to compare with a performance baseline (the "old" ora816) with perfstat reports. Then run the perfstat report on the new database (9i) and compare.

Obviously, you will have to put things back regarding number of users and transactions.

What is the explanation for reducing the SGA from 2.6Gb down to 2.6Gb ?

Regards,
Jean-Luc
fiat lux
Amiel Tutolo
Frequent Advisor

Re: Oracle 9i and disk I/O

I heard these same complaints when our DBA upgraded. We have 2 rp5430 servers with 8gb memory and 875mhz. Apparently there are some internal database settings that can affect performance. If some of the pl/sql code is using specific code for 8 and 9 doesn't react the same way you will have a problem. We had one batch process that went from 5 minutes to 3 hours. Hardware was blamed but I ran this on an A500 server with 8.1.6 and it ran in 15 minutes. You need to get from them exactly what is going on. There are some kernel changes that need to happen from 8 to 9.
Live, love and laugh
Steven E. Protter
Exalted Contributor

Re: Oracle 9i and disk I/O

disk i/o didn't change.

You didn't change that.

These issues probably go to the instance.ora files and settings there.

Oracle tries to buffer disks itself.

The fact that you have physical memory unused gives you the freedom to increased shared memory allocation (ex shmmmax) and tune the init.ora files.

Seems like some dba's didn't do enough QA testing prior to going live.

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
R. Allan Hicks
Trusted Contributor

Re: Oracle 9i and disk I/O

Just a shot in the dark.....

SGA contains the database buffer cache. Cache holds buffers until dbwriter can do her thing. So if the cache is smaller, then you must write to the disk more often. When the cache is full, you must roll something out to disk before you can roll something in.

Basic question: What does the database adivsor in OEM tell you? It will make recommendation on databasebuffer cache.

SGA contains data dictionary cache. If an data dictionary def is not in the cache, you gotta go walk the disk to find it. Library cache and shared pool also reside in SGA.

As far as the problem being in disk I/O, one must ask one's self "What changed?"

The hardware didn't change. The O/S didn't change. The version of Oracle and the size of the SGA did. The performance advisor is your friend. Have a look at it.
"Only he who attempts the absurd is capable of achieving the impossible
Tony Willis_1
Frequent Advisor

Re: Oracle 9i and disk I/O

We went to Oracle 9i over a year ago.
We were able to test for three months prior to going into production.

Our application may be different but here is what we did:

1. Verified Kernel parameters with Oracle
( we had to adjust some size settings)
2. We also discovered that 64 bit Oracle was more processor intesive tha 32 bit 8.1.7
3. We use FS so that the Sys Admin can monitor the disk.
4. We actually increased our SGA by 25%.
( How were you able to use 2.6 before when 1.8 was a threshold ?)
5. It is also very common for the new database to become fragmented because it grew and indexes are different.

It took weeks to smooth everything over and we were the first to do this conversion wothout Oracle on site in 2002.
We have assisted many through forums since.

Here is a major obsevation from your question:
You are the only location that I am aware of that has REDUCED the SGA.
You do know that SGA reduction increases disk usage for any process that exceds the SGA.



The bottom line is that the SGA is causing the increased I/O

Tony
"Not Today,Nice Try, Next Time"
Jdamian
Respected Contributor

Re: Oracle 9i and disk I/O

Thanx everyone.