Performance tuning issue

[rana786]

Hi all,
I have installed RHLE4 update 5 version (32-bit) in a DELL (PE2950)server with 8GB physical memory. Oracle 10g application is installed in this server with 3TB data.
The problem is, users are facing tremendous performance problem during fetching data from the database. System is using his 97% memory. Only 16MB memory is free. How can I solve this problem. Its taking long time to fetch data from database. Please help.

Rgds,
Rana

[Stuart Browne]

What's using the memory? Is it being used up as Cache/Buffers, or is it being used by Oracle as a shared-memory pool?

'free' shows the contents of /proc/meminfo formatted in a nice manner.

'ps -elf | sort -nr -k10 | head' will show the biggest memory hogs.

'ipcs -a' will show you the shared memory pools in use, and how large they are.

I know the Oracle documentation has tuning tips for Linux boxes running their databases. Have you followed these advisaries?

[rana786]

Hi,

It seems most of the memory is occupied by the oracle. SGA is using 2GB and PGA 800MB. Please tell me how can I see the system buffer cache in linux? For Oracle I have changed the parameter of â kernel.shmmax = 3.42GBâ but it seems to me useless. In addition I can not increase it more than that. I have attached some command outputs please verify. BTW,

1. can it be the reason of the limitation of 32-bit OS?
2. Can it be solved by increasing physical memory?
3. For 3TB database how much buffer cache or Physical memory is needed and can a 32-bit Operating system handle this properly?

Best regards,
Rana

[rana786]

Hi,

It seems most of the memory is occupied by the oracle. SGA is using 2GB and PGA 800MB. Please tell me how can I see the system buffer cache in linux? For Oracle I have changed the parameter of â kernel.shmmax = 3.42GBâ but it seems to me useless. In addition I can not increase it more than that. I have attached some command outputs please verify. BTW,

1. can it be the reason of the limitation of 32-bit OS?
2. Can it be solved by increasing physical memory?
3. For 3TB database how much buffer cache or Physical memory is needed and can a 32-bit Operating system handle this properly?

Best regards,
Rana

[TwoProc]

Rana, re: 32 bit. I think you'd be better off with a 64 bit DB. But, you seem a bit misdirected... that is, are you trying to reduce the amount of memory that Oracle is using? Is this because you are in swap? You mention an upgrade; does this mean that the system ran fine before the upgrade?

Re: what do you need to run a 3TB database. The size of the database is not that important, as much as the amount of activity, and what type. Are you doing tons of full table scans? Are you processor bound? How many processors? Are you memory bound in the OS (going into swap)? Are you memory bound in the Oracle SGA (hit cache ratios seem low)?

Give us more information to help you.

Since you've given only generic information, then I can give generic information back.

Yes, 8G is kind of small, and 64 bit is the way to go, and Raid 0/1 is faster than Raid 5, especially for the redo logs, archive logs, temp table spaces, and undo areas - which should all be separate, and then separate from the data areas.

But in general, all one can say is you need more. Specifics about what your problems are would be required to tell you more about solutions.

[dirk dierickx]

not much info here, just the fact that allmost all your memory is in use, which is good, as long as the system doesn't go on a swapping spree.
did you check other performance details, disk io? perhaps there is a lot, causing the delays, then you need more performant disks or change your raid config.
perhaps it is oracle related, and the structure of the database is really bad (ofcourse your dba will deny this ;) ).

[rana786]

Hi John,

I am memory bound in oracle SGA. The cache hit ratio is 10%. I am failed to increase it. Please help

Rgds,
Rana

[TwoProc]

Rana,
If your cache hit ratio is 10% then you really have issues (I've NEVER seen a database with that low a hit ratio).

The first thing that you can check now is your database's statistics. Have your DBA's check to see that your statistics are current and correct. Incorrect database statistics on heavily used tables will make even my well tuned and fast running databases down to their knees with just the right set of 20 tables or so without correct statistics analyzed on them. Get with the DBA team and make sure that this is done first.


Second suggestion, run a statspack report (look in $ORACLE_HOME/rdbms/admin for all the sp* packages, and get supplemental information from most any Oracle site on how to use it). This report will identify the biggest resource hogs along multiple criteria . Get the development teams involved in getting these items tuned! Especially the items that using lots of I/O.


You need to do is get to a 64 bit database so that you can really use a larger buffer cache to get some I/O off of disk. You need to convert.

Once you've made the upgrade to 64bit, then the fast answer is to pile up a lot of ram in the box, and if you've got the $$$, go ahead and max out the ram. I don't know what the max memory that RH4 will address, but I have 12G in mine with no problem. Call RH and find out what the max memory is, and put it in the server. Increase your block_buffer cache until you start landing in the upper 90% ranges. My guess is you won't even hit it until you start doing the tuning suggestion, but if fixing the box quickly is the priority and money isn't (and hey, Intel ram *is* cheap), go ahead and do this too.

Of course, if the whole problem was JUST your statistics not being up to date, you might be fine, and all of the other steps may be unnecessary.

[Stuart Browne]

From what I read of the memory details you've given us, you've heavily limited the memory Oracle can use. The system is using nearly all of your memory as IO cache, instead of Oracle using it to cache it's own IO/Query results.

You've read through this I take it?

http://www.puschitz.com/TuningLinuxForOracle.shtml

[rana786]

Thanks.