cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle 9i performance

SOLVED
Go to solution
Shivkumar
Super Advisor

Oracle 9i performance

Hi,

We are running Oracle 9.2.xx RAC with ADR (Advanced Data Replication). We are seeing some disk i/o performance issue.

The Oracle is installed HP's xp series 1024 SAN disk volume.

Can someone suggest how to find out the bottleneck ?

Thanks,
Shiv
16 REPLIES
Bill Hassell
Honored Contributor
Solution

Re: Oracle 9i performance

This is a very simple description of 90%% of performance problems, namely, lots of disk activity and users (and/or DBAs) complaining about performance. Yes, the disk is very likely the bottelneck so you need to reduce the number of disk requests. One way is to reduce the number of users or processes that are accessing the database (probably an unacceptable solution). Another way is to replace all the disks with RAM or solid state disks. But unless you have unlimited amounts of money (a solid state disk replacement for a large XP 1024 would be millions of dollars), that is probably not a solution.

So the first question is how much RAM do you have and what is the size of the HP-UX buffer cache? The (very poor) default for the buffer cache is 50% of RAM. This should be set to a fixed value (in case you add more RAM) by setting bufpages to about 204800 to 409600 (800 to 1600 megs in 4k pages).

The rest of the performance questions must be characterized by your DBAs in terms of SGA usage and index (or partial index or no index) usage. If the database is not monitored, indexes may become severely unbalanced and may be silently bypassed depending on the version of Oracle. If SGA is just a few hundred megs, you'll need the DBAs to look at the performance gains (meaning less disk I/Os) possible with multi-Gb SGAs. Of course, you'll need many Gb of RAM.

Optimizing Oracle will gain the biggest performance improvement. After optimizing RAM, you may find the the number of CPUs is limiting performance, but you'll need to take measurements and correlate them with Oracle stats.


Bill Hassell, sysadmin
Steven E. Protter
Exalted Contributor

Re: Oracle 9i performance

Shalom Shiv,

Free tool:

http://www.hpux.ws/system.perf.sh

Glance Plus will also help.

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
Shahul
Esteemed Contributor

Re: Oracle 9i performance

Hi,

It's a bit tricky to handle. There can be various reasons why an environment is slow. Check for the following,

1. Check your hard disk I/O by glance and perfview. If any of the hard disk is loaded due to high I/O, it slow down the system performance. It may be because poor storage management like improper RAID or mirror config, improper stripe size etc.

2. Check with DBA's if the oracle performance parameteres are tuned to your environment.

3. Check the memeory, files, swap, processes related Unix kernel parameters are tuned correctly.

4. It can also because of lack of memeory, in this case, you may need to increase the physical memory.

Hope this helps,

Best of luck
Shahul
Shivkumar
Super Advisor

Re: Oracle 9i performance

What are parameters to be tuned with regard to Oralce 9.2.x.x RAC ADR on HPUX 11i ?

If someone can list then i can ask my DBA to verify them ?

Regards,
Shiv

Shivkumar
Super Advisor

Re: Oracle 9i performance

Also, Is it okay to suggest to install another network cards on machines running oracle and increase the bandwith of network i/o by combining the speed of 2 or more network cards by trunking technology ?

Regards,
Shiv
Hein van den Heuvel
Honored Contributor

Re: Oracle 9i performance


>> What are parameters to be tuned with regard to Oralce 9.2.x.x RAC ADR on HPUX 11i ?

Don't worry about HPUX untill you have the appropriate understanding of the Oracle performance done.

Your DBA shoudl be able to tell you the critical wait events. The DBA shoudl be able to tell you (with the help of Oracle STATSPACK data ) whether the application has more or less enough SGA, or could use more.

>> Also, Is it okay to suggest to install another network cards on machines running oracle and increase the bandwith of network i/o by combining the speed of 2 or more network cards by trunking technology ?

No, that makes no sense at all untill you have an indication that network performance is at all relevant for the problem on hand.
You indicate/suspect you have a disk access proble. If you had a serious network problem, then you would not have a disk problem (that's the thing with bottlenecks.. you generally have only on at a time. Fix the current one, and you may or might not run into the next bottleneck (Most likely you will :-).

Mind you, with Oracle RAC active, the network may be heavily loaded for locking and cache consistency, but there again your DBA can tell you whether this is the case through statspack. Moreover, if this is the case your first step should probably be to give oracle RAC a dedicated connection for its protocols, rather then increasing generic bandwith through aggregating.

Good luck!
Hein.
Leon Allen
Regular Advisor

Re: Oracle 9i performance

Use glance plus to identify the load distributuion.
Then distribute the load - I have even gone to the extent of putting some file on local SCSI disks. My rational for this was the local disks are on a completely seperate controller (local SCSI cf. SAN HBA). Any 'work' going to the local scsi is a load of the HBA and SAN disks.

The files you put on local SCSI depends on available space, but can be all or some of redo logs, undo tablespace, temp table space..... up to you.

Another thing which may help is turn off logging on your index tablespaces (ie no redo log entries generated)

Cheers!

Leon

Time's fun when your having flys (ancient frog saying)
sysadm_1
Valued Contributor

Re: Oracle 9i performance

HEllo Shiv,

Attaching HP/Oracle documents which explains the kernel parameters and other settings for oracle 9i RAC.

Cheers!!
sysadm
sysadm_1
Valued Contributor

Re: Oracle 9i performance


check this toooo

sysadm
Ben Dehner
Trusted Contributor

Re: Oracle 9i performance

Well, I'll throw in my 2 cents.

In my experience, most Oracle performance problems aren't caused by kernel settings. For the most part, kernel tweaking is a matter of simply giving Oracle the resources it wants. If Oracle doesn't have those resources, its not that it runs slowly, it simply doesn't run at all. It fails with a failure to allocate some IPC resource or fork a process or whatever. Although I have seen I/O get hung for extreme IO loads if the scsi_queue_depth setting was too low on fibre adapters.

Likewise, few performance issues are caused by Oracle parameter settings, for the same reasons. For the things that can be checked, make sure that db_cache_size and shared_pool are high enough. Also make sure that the database stats are analyzed about once a week.

The best place to start looking is within the database; and the best tool to start looking with is statspack. It will identify the top wait events within the database, as well as the SQL statement causing the highest disk I/O. I have seen a lot of performance problems caused by SQL that was not properly optimized for Oracle.
Trust me, I know what I'm doing
Yogeeraj_1
Honored Contributor

Re: Oracle 9i performance

Hi shiv,

also check in your Database control (Enterprise Manager), you have many advisors to help as well as ADDM.

hope this helps too!

kind regards
yogeeraj

No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Shivkumar
Super Advisor

Re: Oracle 9i performance

Can someone explain what is STATSPACK data ?
I don't have access to the database and hence this question. I am contributing by reading and imagination.

Thanks,
Shiv
Alzhy
Honored Contributor

Re: Oracle 9i performance

Shivkumar,

Why do you think or claim that you have a Disk I/O performance issue? I say - first establish that you indeed have an I/O problem. And this is 2 pronged:

(1) DBA approach -- ask your DBA to ascertain if there really is an obvious I/O problem. Ask what volumes/filesystems/files suffer from "slow" performance.

(2) On your end - SysAdmin, check the volume's/filesystem's in question -- check the component disks via "sar -d" if you indeed have I/O issues -- which should manifest via qdepths > 1.0

If (1) or (2) confirms you indeed have an I/O issue, then look at how your XP1024 LUNs are used to build your Oracle storage. It does not matter whether you use RAW or cooked storage -- best pracice for the XP array is to always stripe accross 4 or 8 LUNs with each LUN coming from different ACP and array group and prefereably on each own Fibre Channel Path...

Follow the above recipe and you will alwyas have proof that your I/O configuration is not at fault..

Hakuna Matata.
Yogeeraj_1
Honored Contributor

Re: Oracle 9i performance

hi shiv,

more information on statspack is available at:

http://www.oracle.com/technology/deploy/performance/pdf/statspack.pdf


if you have further queries, please do let us know

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Eric Antunes
Honored Contributor

Re: Oracle 9i performance

Hi Shiv,

Some basic stats you should monitor:

select (bbc.total_waits*100/(cg.value+dbg.value)) "Buff busy ratio ind. <= 0,007"
from v$system_event bbc,
v$sysstat cg, v$sysstat dbg
where bbc.event='buffer busy waits'
and cg.name ='consistent gets'
and dbg.name='db block gets';

select (1-(sum(getmisses)/sum(gets))) * 100 "Hit Ratio > 95%"
from v$rowcache;

select round((congets.value+dbgets.value-physreads.value)*100/
(congets.value+dbgets.value),4) || '%' "Buffer Cache Hit Ratio >= 95%"
from v$sysstat congets, v$sysstat dbgets, v$sysstat physreads
where congets.name='consistent gets'
and dbgets.name='db block gets'
and physreads.name='physical reads';

select round(100 * sum(reloads) / sum(pins), 2) "Reloads Pct < 1%"
from v$librarycache;

Best Regards,

Eric Antunes
Each and every day is a good day to learn.
Michael Kupfer
Occasional Visitor

Re: Oracle 9i performance

Shiv,

I agree with one of the posts above about Solid state disk to an extent. It is a great solution for I/O issues, however you do not need to put your entire dbase on the SSD. So it can be affordable.

I own a company that sells SSD SANS, and the added benefit of putting your most accessed data on them is that they are no longer accessed from the array...making the array work more efficiently as well. We have found that usually 2-5% of your data can cause 80%-90% of your IO problems. If you would like to learn more, please send me an e-mail mike@bdtstorage.com.

MLK