Showing results for 
Search instead for 
Did you mean: 

Oracle performance issue.

Oracle performance issue.

I am facing problem with a oracle 9i server runs on hp-ux 11i.System is powered with 2GB system memory and the oracle memory (SGA) sizes 1.3GB. After oracle starts the free memory available on the system is 400MB. Once the users connects to the server the available memory is drastically reduces (availble free memory is not more than 50MB).It seems to me a strange behavior in the system.
Could be this is an oracle problem (According to the DBA they have tune the DB maximum).
Please advice me to monitor and catch the secret in the system.
To get out of a difficulty, one usually must go through it
A. Clay Stephenson
Acclaimed Contributor

Re: Oracle performance issue.

First make sure that the dbc_max_pct is not at the default value of 50%. In your case, you probably want to limit buffer cache to no more than about 200MB. I would set bufpages to 51200 or so and thus turn off dynamic buffer cache.

Next, 2GB is really a tiny amount of memory for Oracle these days. Almost certainly your machine is swapping. Use Glance or vmstat to exemine the page-out rate. If the value is anything over about 10 (and zero would be good) for any length of time then you are swapping way too much. If you can't get more memory then get your DBA's to limit the size of the SGA. You may need a club for this. Any overhead of going to disk more often for Oracle I/O is less costly --- by at least two orders of magnitudes (2 powers of 10) --- than swapping.

If it ain't broke, I can fix that.
Steven E. Protter
Exalted Contributor

Re: Oracle performance issue.

You should collect full performance data on you r machine and deal with any issues that pop up.

I'm attaching a good script for collecting such data based on sar.

Note that shared memory issues often slow oracle down.

shmmax should be set liberaly, but can not exceed 25% of total memory. Total memory is defined as ram plus swap.

swapinfo -tam will get you a figure on total memory.

See attachement.

Steven E Protter
Owner of ISN Corporation
Brian Crabtree
Honored Contributor

Re: Oracle performance issue.

The SGA does not account for process memory. You should check the sort_area_size, or set the pga_aggregate_target to a specific size (ie: 200m) and set WORKAREA_SIZE_POLICY to AUTO, and see if that helps with the problem.


Brian Crabtree
Honored Contributor

Re: Oracle performance issue.

The following is the metalink note that can give more information on the pga_aggregate_target.


Bill Hassell
Honored Contributor

Re: Oracle performance issue.

Your system is badly undersized! 2Gb of RAM for Oeracle is very small, but with an SGA 1.3Gb, you need at LEAST 4Gb of RAM. The system will run with 2Gb but you are probably paging (swapping) and that kills system performance. There is no substitute for RAM when it comes to Oracle and performqance. You need more now and you might as well plan for a lot more in the future, perhaps 6 Gb or 8 GB.

Bill Hassell, sysadmin
Sridhar Bhaskarla
Honored Contributor

Re: Oracle performance issue.


I echo others that you are short on memory. You are left with only 700 MB for buffer cache and system memory. Particularly on 11i, system memory (for kernel's dynamic allocation with most of it going to vx_inode_cache) can be found much higher than previous releases. Look at system memory in glance's memory window. It does not include buffer cache.

Also Add the "KB used" columns in your "swapinfo -tam" corresponding to each "dev" type. You will need to add atleast that much of memory to your system.

You may be disappointed if you fail, but you are doomed if you don't try
Hein van den Heuvel
Honored Contributor

Re: Oracle performance issue.

I fail to understand how some responders here know that this system does not have enough (physical) memory from the minimal information provided.

What is clear, is that the available memory resources are overcommitted. To solve that, one can increase the physical memory as suggested, but I'd like to see more proof that it is not simply a configuration problem.

Specifically, do you really know the SGA needs to be 1.3GB? Maybe this particular application can run happily with 200MB shared pool and 110MB buffer space but someone fat-fingers that to 1100MB buffers? Or they imported an initXXX.ora file from an other server with more memory and failed to reduce that?!

Nuwan, please help us help you better by describing some more. Notably, how many users are connected? 5? 500? You should anticipate 2 - 10mb per user connection to Oracle, on top of the SGA, so your current config can possible only handle 50 or so connections (on a good day). On a bad day, with lots of sorting that may be less still.
Be sure to carefully rad Brian's reply. IMHO that is the most useful one so far.
What are the oracle sort settings / pga target settings, and the likes. What are your dbc_max_pct settings? cooked or raw? How much memory/user do you see (top?)...

Good Luck