Operating System - HP-UX
1748202 Members
3039 Online
108759 Solutions
New Discussion юеВ

Re: Poor Oracle Performance

 
Ralph_27
Occasional Contributor

Poor Oracle Performance

We're experiencing poor performance on our Oracle system and could really use a hand nailing down the problem.

Essentially the database users are complaining about really long load times. The machine is a 16 cpu 9000/800/V2600 with 16Gb of RAM, with all oracle data residing on EMC SAN connected drives. OS is HP-UX 11.00 and Oracle is version 8, and estimated size is about 1Tb.

Sar data is showing very poor service times, averaging about 20-30ms across most SAN connected devices.

Things we've checked:
1) The Symmetrix.. it looks healthy. The disks on the backend aren't even close to being fully utilized.
2) Powerpath and the HBAS. EMCGrab was run and came back clean.
3) GlancePlus shows a number of disks being over utilized.
4) CPU's aren't being maxed out
5) No system swapping at all.

My guess is that either kernel isn't properly configured or the filesystems aren't mounted properly and we're experiencing poor caching on the host side.

I've attached our kernel parameters. Please be aware that we've already reduced dbc_max_pct and performance was twice as bad. Filesystems are mounted using the following options: vxfs rw,suid,largefiles,delaylog,datainlog

Any suggestions?




13 REPLIES 13
Prashant Zanwar_4
Respected Contributor

Re: Poor Oracle Performance

What I understand from your problem is IO is quite high and it must be wait IO which will cause such a problem.
Your choice is to drill on

how the VG's are structured? Like not on one controller all the load is coming...hope this is not the case..if so you have to change here.

Check also configuration on EMC side..many times the internal array config can cause problem like this if it is shared especially and the number of servers are talking to set of disks at one place.

Check in glance the queue length. Also disk utilisation..which all disks are hitting highly..how are they arranged...

Hope you have all latest patches installed on the system..

Hope this helps
Thanks
Prashant
"Intellect distinguishes between the possible and the impossible; reason distinguishes between the sensible and the senseless. Even the possible can be senseless."
RAC_1
Honored Contributor

Re: Poor Oracle Performance

The buffer cache seems OK. anyway, oracle does it's own buffering.

1. Which FS are hit most?? (glance -i)
2. Is the data distributed across the different FSs that oracle uses??
3. Have you configured the alternate paths if any??
4. What the SGA configured for oracle??
5. What does oracle statpack says??

Anil
There is no substitute to HARDWORK
A. Clay Stephenson
Acclaimed Contributor

Re: Poor Oracle Performance

You didn't list the tunable that I always examine first when Oracle is mentioned -- timeslice. Make sure that it is not set to 1; it should be very near 10.

You should also be aware that almost all Oracle performance problems have to do with
the SQL itself and often little to do with OS Tuning.

Some Glance output would prove useful in spotting bottlenecks.
If it ain't broke, I can fix that.
Jean-Luc Oudart
Honored Contributor

Re: Poor Oracle Performance

Hi Ralph,

IS the problem recent ?
Have any baseline configuration (OS + Oracle) and reports (sar,... + statspack) from when the system was performing within SLA ?

EMS storage : RAID 5, RAID1 RAID 10 ?
do you use lvm stripping ?

Could you also provide a statspack report ?
(what is the exact version for oracle ?
sqlplus / slect * from v$version)

Regards,
Jean-Luc
fiat lux
Ralph_27
Occasional Contributor

Re: Poor Oracle Performance

Thanks for the quick responses. Here are the answers to your questions:

1) FS arrangement: 1 FS per RAID 10 LUN, per volume group. We've already identified the hardest hit filesystems and the dbas are planning to reorg things if possible.
2) Controllers: LUNS are shared to both HBAS and balanced with powerpath.
3) Queue length: Queue length in glance has 1 device with a queue of 2..it's the archive log filesystem. Everything else is zeros.
4) Oracle: I'm not an Oracle DBA so I dont have access to SQLplus.. is there a way to determine SGA, statspack, version, etc from the root account?
5) Timeslice: Set to 10.
6) Pre Problem statistics: None available
7) Glance output: I'm new to glance.. please provide a command you'd like to see run.

I've attached the current sar -A output.
Rick Garland
Honored Contributor

Re: Poor Oracle Performance

Appears that most, if not all, settings on OS are OK. (Still depends on other variables, how big is DB, how many users, etc). Do have the database checked into more throughly. How how the SQL searches run? Are you doing full table scans on lookups?

To check some of the Oracle config settings as root you can look at the initora file. (I typically see this file named init.ora)

I find these files in $ORACLE_HOME/product/...

Prashant Zanwar_4
Respected Contributor

Re: Poor Oracle Performance

Yes how big is the database.
Also please ask DBA's to see sql performance. If sql's are poorly designed..it is highly possible that the performance will fall.
Still again your choice to look at sar, glance..

sar -M -q
also You can use gpm a GUI tool of glance which is easy to operate.
or
glance -u to look at disk hits and qlen

Hope this takes you to analysis of your problem. I am not much familier with oracle..You can check like below:

su - oracle
echo $pfile
pg $pfile

this contains many parameters or locations where you can find files.

Thanks
Prashant
"Intellect distinguishes between the possible and the impossible; reason distinguishes between the sensible and the senseless. Even the possible can be senseless."
doug mielke
Respected Contributor

Re: Poor Oracle Performance

don't forget to look at index changes. If you have tables that have grown much over time (many extents) and indexes that have been recently reorg. or otherwise modified, result will be full table scans ==> over loaded disks. ( however, I'd think w i/o % would be hi too, maxing sar cpu %)

An explain plan on a heavily used oracle job could give a hint.
Mark Greene_1
Honored Contributor

Re: Poor Oracle Performance

You can check out the HBAs by runnnig "fcmsutil /dev/td0" to verify the topology setting, then run "fcmsutil /dev/td0 stat" to see if there are any errors. If there are more than a few, reset the stats and run periodically to see what sort of error rate you are incurring, if any.

If the preformance degredation was relatively sudden, take a look at recent changes that might have contributed to this. You likely candidates are changes to indices or logging within Oracle.

mark
the future will be a lot like now, only later