- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: Potential memory bottleneck with Oracle 9.2.0?
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
тАО07-21-2004 09:26 AM
тАО07-21-2004 09:26 AM
Are we memory bottlenecked at the 150+ DB connection level? Are DB connections ("ps -ef | grep LOCAL"), a good measure of oracle activity?
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-21-2004 02:46 PM
тАО07-21-2004 02:46 PM
Re: Potential memory bottleneck with Oracle 9.2.0?
Memory management is one of the areas which has been given significant attention in Oracle9i. Oracle9i introduces a dynamic memory management feature which allows for dynamically resizing the buffer cache and shared pool. It also includes a buffer cache size advice mechanism that predicts the performance of running with different sizes for the buffer cache. Oracle9i also provides transparent management of working memory for SQL execution by self-tuning the initialization runtime parameters that control allocation of private memory. This feature helps low-end users to reduce the time and effort required to tune memory parameters for their data warehouse and reporting applications, while high-end users are able to avoid memory tuning for individual work loads.
One of the most common problems when running large numbers of concurrent users on UNIX machines is lack of memory. With Oracle9i, the dynamic SGA infrastructure will allow for the sizing of the Buffer Cache, Shared Pool and the Large Pool without having to shutdown the database.
In this new model, a new unit of allocation is created called the 'Granule'. A granule is a unit of contiguous virtual memory allocation. The size of a granule depends on the estimated total SGA size, whose calculation is based on the value of the parameter SGA_MAX_SIZE. This would be 4MB if the SGA size is less than 128MB, otherwise it will be 16MB.
The Buffer Cache, Shared Pool, Large Pool and Java Pool are allowed to grow and
shrink based on granule boundaries. SGA memory will be tracked in granules by SGA components. To monitor the creation, the V$BUFFER_POOL view can used.
The following are the Size parameters which define the sizes of the caches for buffers for the primary block size:
DB_CACHE_SIZE
DB_KEEP_CACHE_SIZE
DB_RECYCLE_CACHE_SIZE
In Oracle 9i it is possible to use the "Dynamic Buffer Cache Advisory feature" which enables and disables statistic gathering for predicting the behaviour of the buffer cache with different cache sizes.
Most UNIX systems today use a combination of paging and swapping to manage memory. Generally, you will see the following behavior:
System lightly used: no paging or swapping occurs. System under a medium load: paging occurs as RAM memory runs low System under a very heavy load: paging stops and swapping begins.
Make sure that the machine is not swapping at all and at worst paging lightly. This indicates a system with a healthy amount of memory available. To analyze paging and swapping, refer to the document.
Attached is document that puts together few aspects of Memory usage, IO and CPU and Oracle parameters inrelation to the above.
I hope this helps to some extent.
Indira A
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-21-2004 03:43 PM
тАО07-21-2004 03:43 PM
Re: Potential memory bottleneck with Oracle 9.2.0?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-21-2004 05:09 PM
тАО07-21-2004 05:09 PM
Re: Potential memory bottleneck with Oracle 9.2.0?
Me too would recommend to setup STATSPACK and run it on each system for 15 mins intervals during the problem period.
Statspack will help you identify the top sql, the big wait events, contention points, bad performance metric.
You can then compare the values from each. There could be application "problems" using one of the instances.
regards
Yogeeraj
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-21-2004 05:39 PM
тАО07-21-2004 05:39 PM
SolutionAs the other posters have pointed out, though, you have a lot of options to address this problem.
Some options:
- Get more RAM
- Resize the SGA's of the instances to something smaller. Under 9i this can be done dynamically as mentioned)
- connection pooling. It sounds like it is connections that are causing the problem... connection pooling might keep the memory load low enough to keep you happy.
- check disk i/o. (sar -d 5 5) or top (not as reliable, but easy) to look for i/o %
Questions: Are you running 64-bit or 32-bit... I think you will hit a 4GB limit if you are 32 bit that you wouldn't see unless the instances we active.
Steve
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-22-2004 07:00 AM
тАО07-22-2004 07:00 AM
Re: Potential memory bottleneck with Oracle 9.2.0?
Thanks,
Brian
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-22-2004 07:17 AM
тАО07-22-2004 07:17 AM
Re: Potential memory bottleneck with Oracle 9.2.0?
Indira - your comments were great, with a lot of good tips which I will make available to the DBA. Your note towards the bottom of the response on Unix behavior ("medium load: paging occurs as RAM runs low", etc), seems a little out of sync with Steve's comment which is definitive ("you are memory bottlenecked"). During the busy period, we have both page outs (15+/sec) and a swapout rate of 1/sec.
Ericfjchen and Yogeeraj - more good tuning stuff and will pass along to DBA.
Steve - appreciate the short and definitive answer ("memory bottlenecked") and the options, including the reference to the good comments from the DBA's, above. Relative to your comments/questions: disk busy as measured by "sar" and PerfView is low, in the 5-10% range; we are running 64-bit. Do you see a conflict with your answer and Indira's? (From his reference point, I think we have a "medium" load with paging, low memory and low level swapping/deactivations). I think we have a light CPU load (5-10%), light I/O load (max 5-10% LUN busy) and a heavy memory load, particularly at 150+ DB connections.
Thanks again, Tom
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-22-2004 11:46 AM
тАО07-22-2004 11:46 AM
Re: Potential memory bottleneck with Oracle 9.2.0?
You may want to suggest to your DBA to consider using MTS (multi-threaded server) to allow he instances to scale and better use memory...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-29-2004 05:20 AM
тАО07-29-2004 05:20 AM
Re: Potential memory bottleneck with Oracle 9.2.0?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-29-2004 05:33 AM
тАО07-29-2004 05:33 AM
Re: Potential memory bottleneck with Oracle 9.2.0?
You might need to collect data on your system to get a better handle on your kernel and tuning situation. I'm attaching a script set that does that.
Also, here is a doc, written by an HP performance guru who picked up a software case I had with HP a ways back.
http://www1.itrc.hp.com/service/cki/search.do?category=c0&docType=Security&docType=Patch&docType=EngineerNotes&docType=BugReports&docType=Hardware&docType=ReferenceMaterials&docType=ThirdParty&searchString=UPERFKBAN00000726&search.y=8&search.x=28&mode=id&admit=-682735245+1091122386701+28353475&searchCrit=allwords
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