- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- 9i Oracle Performance issues bad Cache Hits
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
тАО08-29-2005 02:39 AM
тАО08-29-2005 02:39 AM
The test of both the upgrade and patches came out fine on the QA systems. On production it seems to have ruptured something. The applications are all running 50% to 400% longer. Needless to say, my life is fun right now since this system handles all of our import retail here at Sears (Kmart).
I have been working with HP on the issues for the last few weeks and they suggested reducing the buffer cache in the server from 2gb to 400mb because the wcache was at <54. The rcache has always been between 70-80 on average.
After completing the change (buffpages=102400) my apps are now running insanely longer. Most are not done from the weekend so I have no idea if they are now 400% or 1000% longer. Cache hits are 36/59 (rcache/wcache). My manager is now stacking empty boxes by my cube ;-)
The SGA in Oracle is defined at 1.3gb and they (DBA) are seeing consistent 91% cache hits. The filesystems for Oracle are NOT raw. So I am assuming that Oracle is buffering and the system is also buffering before there is anything physical happens to disk. Is there any guidance and help available on how large I should set the system buffer cache? The reboot window is only once a week, but they are opening up a reboot window for me today after the weekend jobs finish. Should I just set the buffer cache back to 2gb or is there a better value? Should we look at the size of the SGA?
Oh great, now the cube vultures are showing upтАж
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-29-2005 02:53 AM
тАО08-29-2005 02:53 AM
Re: 9i Oracle Performance issues bad Cache Hits
Set bufpages to 0 and use dynamic cache for this.
How about comparing some other kernel settings between your QA and production. i.e. shared mem, semaphore, etc. There was also a great document out here somewhere that discussed the shared mememory windowing in Oracle.
Also take a look at the processes in glance while running, look at wait states, this may give you an idea. If not io then your problem is somewhere other than the fs cache.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-29-2005 02:56 AM
тАО08-29-2005 02:56 AM
Re: 9i Oracle Performance issues bad Cache Hits
buffer cahe and as result if there is need to buffer more, you just can not do that. You may want to set dynamic buffer cahe (in the range of 400-800mb) and check. Set dbc_max_pct and dbc_min_pct.
But before you do that, if you look at system as a whole, what kind of bottleneck do you see?? CPU-priority run que, global run queue,
MEM-glance -m, vmstat, swapinfo, DISK-is oracle stripped?? Orcle recommands SAME (Stripe all, mirror everything), are disk i/os OK??
Run oracle statspack and check what is happenening?? How are the read and write cahe rates now??
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-29-2005 03:00 AM
тАО08-29-2005 03:00 AM
Re: 9i Oracle Performance issues bad Cache Hits
I would have 1st a few questions :
- OS version
- Database size
- server memory size
- is the storage SAN attached?
Also do you use the new Oracle9i features to manage the PGA and SGA ?
(Dynamic Buffer Cache Advisory feature)
could you run (or get it from the DBA) ?
=> select * from V$PGA_TARGET_ADVICE
Also the cache hit does it not the only one metric.
Would you have a statspack report available ?
OS kmtune report ?
Regards
Jean-Luc
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-29-2005 03:05 AM
тАО08-29-2005 03:05 AM
Re: 9i Oracle Performance issues bad Cache Hits
find attached a document from Oracle Metalink : Note:262946.1 "Performance Issues After Increasing Workload"
Regards
Jean-Luc
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-29-2005 03:08 AM
тАО08-29-2005 03:08 AM
Re: 9i Oracle Performance issues bad Cache Hits
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-29-2005 03:11 AM
тАО08-29-2005 03:11 AM
SolutionThere are two possible areas, SGA and oracle tuning, which you mention. I would install the oracle stats pack if not installed and use it to optimize the database.
Here is an article on the OS aspects, written by one of HP's oracle tuning experts.
http://www1.itrc.hp.com/service/cki/docDisplay.do?docLocale=en_US&admit=-682735245+1125328248032+28353475&docId=200000077186712
Document ID: UPERFKBAN00000726
SEP
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-29-2005 03:21 AM
тАО08-29-2005 03:21 AM
Re: 9i Oracle Performance issues bad Cache Hits
K580
6 x 240mhz
7gb memory
XP512 SAN 2 fiber cards using SecurePath to aggregate over both.
DB is about 120gb in size.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-30-2005 12:45 AM
тАО08-30-2005 12:45 AM
Re: 9i Oracle Performance issues bad Cache Hits
As a DBA that recently upgraded from 8.1.7.4 to 9.2.0.6 and saw his system performance go to hell overnight I'd suggest the following (based on my experience:)
1) Assume that since the DB changed but the OS didn't that the solution lies in the DB. Therefore, don't go changing a bunch of OS parms. Leave that constant.
2) We found that while all tables and indexes appeared to have usable statistics AND EXPLAIN PLANS appeared unchanged, in some cases the perfomance was VERY different. Once we re-computed Oracle statistics on all tables and indexes a large number of these performance problems went away. So that's my best recommendation: Have your DBAs re-compute all statistics ASAP.
3) Oracle's CBO (Cost-Based Optimizer) changes--supposedly for the better--with each release. Once you have fresh statistics there may actually be SQL with changed execution plans that will need to be re-tuned. Again, this is a job for your DBA.
Again, I just went through this (cube vultures and all.) Good luck, hope this helps.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-30-2005 12:53 AM
тАО08-30-2005 12:53 AM
Re: 9i Oracle Performance issues bad Cache Hits
Oracle performance tools like Statspack and particularly SQL trace will allow you know exactly what the problems is and what "parameters" (Oracle, OS, hardware system architecture: more CPU, disks, etc.) to change. This is to warn against guesstimates and changing this or that parameter and hope the system will run better.
Double caching (OS + Oracle) indeed is to be avoided, most certainly so if raw devices are used. With file systems it actually is not that bad as Oracle caches blocks obtained by a full table scan (FTS) only till the next FTS comes along. If the first table needs to be read again you will definitely benefit if it is still cached in the file cache. That said, I agree that using the dynamic file cache with the dbc_ parameters (min=2 max=5, or something of that nature) will suit most systems better that the default settings of then using a fixed cache. Is the system swapping a lot?
Tuning Oracle with BCHR (buffer cache hit ratio) is not very fashionable anymore these days: you might want to consult "Why a 99%+ Database Buffer Cache Hit Ratio is Not Ok" (attached).
You might find the following docs helpful also:
On file cache:
docs.hp.com/en/5580/Misconfigured_Resources.pdf
HP-UX Performance Cookbook
h21007.www2.hp.com/dspp/files/unprotected/ devresource/Docs/TechPapers/UXPerfCookBook.pdf
A paper on the combination HP and SAN
Tuning I/O for Better Performance - Focusing on HP and EMC (http://www.oraperf.com/whitepapers.html)