- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: Poor Oracle Performance
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-08-2004 03:19 AM
тАО10-08-2004 03:19 AM
Poor Oracle Performance
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-08-2004 03:29 AM
тАО10-08-2004 03:29 AM
Re: Poor Oracle Performance
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-08-2004 03:30 AM
тАО10-08-2004 03:30 AM
Re: Poor Oracle Performance
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-08-2004 03:31 AM
тАО10-08-2004 03:31 AM
Re: Poor Oracle Performance
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-08-2004 03:38 AM
тАО10-08-2004 03:38 AM
Re: Poor Oracle Performance
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-08-2004 04:59 AM
тАО10-08-2004 04:59 AM
Re: Poor Oracle Performance
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-08-2004 05:09 AM
тАО10-08-2004 05:09 AM
Re: Poor Oracle Performance
To check some of the Oracle config settings as root you can look at the initora file. (I typically see this file named init
I find these files in $ORACLE_HOME/product/...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-08-2004 05:29 AM
тАО10-08-2004 05:29 AM
Re: Poor Oracle Performance
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-08-2004 05:42 AM
тАО10-08-2004 05:42 AM
Re: Poor Oracle Performance
An explain plan on a heavily used oracle job could give a hint.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-08-2004 06:05 AM
тАО10-08-2004 06:05 AM
Re: Poor Oracle Performance
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