Poor Oracle Performance

[Ralph_27]

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?

[Prashant Zanwar_4]

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

[RAC_1]

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

[A. Clay Stephenson]

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.

[Jean-Luc Oudart]

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

[Ralph_27]

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]

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]

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

[doug mielke]

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]

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

[Yogeeraj_1]

hi,

at the Oracle Database level, you SHOULD run statspack report to be able to know what is really going on.

If you want to connect as the oracle user, it will be quite easy since you said you have the root account password. do " su - "

then you can check if statspack is already installed by:
1. login to oracle
sqlplus /nolog
connect / as sysdba

2. check if the statspack tables are installed. Typically, table names start with "STAT":
select object_name from dba_objects
where object_name like 'STATS%';

3. If installed then run:
exec statspack.snap

exec statspack.snap

4. Generate the report by running:
@?/rdbms/admin/spreport
- choose the snapshot numbers (begin_snap and end)
- save the report file to disk.

5. Analyze reports for low load period v/s high load period.


Good luck.
If you need any further guidances, please let us know

regards
Yogeeraj

[Hein van den Heuvel]

>>> 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?

There is your biggest problem.
You can NOT solve this in isolation.
This is NOT going to be a system problem.
Sure, optimal system config will 'ease the pain', but the pain is defined by Oracle.

Your co-operation with the DBA should be such that SQLplus access itself becomes irrelevant.

The really bad IO times are for very low access counts. Ignore those.

Your IO load is reasonably balanced.
The top 10% of the disk, handle 30% of the IO.
The bottom 30% of thes disk, handle 10% of the IO.
That's much better than the 20 - 80 we see all too often.
Still, it can be improved upon. Check out the SAME recommendations (Stripe and mirror everything). Bunch up a large number of LUNs into a single VG, then hand them out at striped LVs (extent based, 1MB? 4MB?). Or just have the storage do the striping.


You have CPU time to burn, but a touch heavy on the overall IO. Any memory to spare? Just give it to Oracle for the SGA and reduce the read IO!

When looking at write IOs (in the Oracle stats), pay close attenion to the REDO LOG, it often defines the user perceived speed. General writes in Oracle are NOT waited for, so matter much less.

I'll attach a small perl script to summarize the sar output into a single line per timestamp. It may help interpretting the data, and can server as a starting point for further formatting/counting:

C:\Temp>perl sar.pl < sar.txt | more

Time usr sys blk rw rw dsk

00:00:01 0 0 0 0 0
00:05:03 8 5 42023 629 105 c47t3d2
00:10:02 7 3 8930 262 64 c12t5d0
00:15:01 2 3 4637 176 150 c12t5d0
00:20:01 7 5 22787 518 83 c47t0d7
00:25:00 6 5 23287 605 62 c12t5d0
00:30:01 4 3 14759 447 59 c12t5d0
00:35:02 27 10 55627 1318 115 c47t0d7

[Mel_12]

I'd say why do you want to do DBA job? Is it because of lack of one? If the production system is of any value, then get the DBA involved. How would you feel having a DBA do System Admin job?

As a DBA and ex system admin, the best place to look for the cause of the bottleneck is the Oracle wait events. Start with:
select * from v$waitstat. This gives you overall health of the database at a glance.

Later, run this one to begin to isolate them.
select sid, event, p1text, p1, p2text, p2, wait_time, state
FROM v$session_wait
ORDER BY wait_time.

Be sure to exlude such events as (rdbms,SQL*,Pipe) These are simply users having idle sessions and are not relevant. You will be able to find the sessions waiting for a particular event and then wear you DBA hat to trouble shoot it.

Hope this helps.
Good luck
Mel

While running the

[Fred Ruffet]

I imagine your problems are not only related to this point, but it may help.

Note that FS for Oracle data files may be mounted with these options :
nodatainlog,mincache=direct,convosync=direct
These options bypass the OS cache (Oracle has its own). It increases perfs and makes OS buffer cache not to grow too much.

Regards,

Fred