CPU and Memory Consumption by Oracle

Hi,

I am running SAP R/3 on Oracle 816 EE / WinNT 4.0 EE.

RAM = 2 GB
Processors 4 x 733 XEON


My SGA is as below :

SHARED_POOL_SIZE = 381 MB
BUFFER CACHE = 703 MB (90000 x 8192)
JAVA POOL = 19 MB

All this comes out to 1103 MB.

These days when I see the Task Manager I have observed following :


CPU Utilization Above 40% and sometimes it reaches 70% also.
Memory allocated to Oracle.exe 1.35 GB.

My peformance is also getting very slow. Some is due to expensive queries, but how to attack this thins.

Help me !!!!

This could be because your sql statements are not shared or they might be getting aged out or there might be lot of dynamic sql statements for which oracle is performing lot of re-parsing which is cosnuming lot of CPU.
Trying set setting CURSOR_SHARING=FORCE,which will oracle will share sql statements which are identical except for their where clause.

you can also find the amount of reparsing by querying v$sqlarea which will show the sql statements along with the parsing count,execution count.

You can also query v$sysstat to find the amount of hard parse i.e oracle will rebuild the parse tree and execution plan because the object referenced in the sql statements have under going a massive change in the structure or no of rows,when new statistics are available.

hope this helps
regards
anandkl

Quote:

---

Originally posted by vijay8282

Hi,

I am running SAP R/3 on Oracle 816 EE / WinNT 4.0 EE.

RAM = 2 GB
Processors 4 x 733 XEON


My SGA is as below :

SHARED_POOL_SIZE = 381 MB
BUFFER CACHE = 703 MB (90000 x 8192)
JAVA POOL = 19 MB

All this comes out to 1103 MB.

These days when I see the Task Manager I have observed following :


CPU Utilization Above 40% and sometimes it reaches 70% also.
Memory allocated to Oracle.exe 1.35 GB.

My peformance is also getting very slow. Some is due to expensive queries, but how to attack this thins.

Help me !!!!

---

Hello;

1) How many USers access the SAP-System?

2) Orcla.exe is SGA + User-Session-mem because The session are handled as threads.
so there are 700M left.
128M for OS, i think you have place for 256M to give it to shared pool without having contention.

3) SAP-SQL-Statements are very large, probably increasing your SHARED_POOL_SIZE would help. But as mentioned look in the statistics.

Orca

Hi Vijay,

If it is due to queries, then set the following 2 parameters to a high value and both should be same.

SORT_AREA_SIZE and SORT_RETAINED_AREA_SIZE.

Both values should be same.

If there are any structural changes to objects in the schema, provide the new statistics by analyzing the objects.

The lower performance may be due to more I/O contentions.

-ShanDJ

Hi,

First of all both SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE need not be the same.

The following metrics will be helpful to you.

1. No. of instances running in the server and the amount of SGA allocated for it.

2. Total no. of users connected to the Oracle instance.

3. Value for SORT_AREA_SIZE.

4. If any other applications are running in the same server where the database server resides then
how many applications are running?
how much amount of space does these applications occupy?
how many users work with these applications.

5. Hit ratio for SGA.

6. How efficiently does Oracle reuses the same shared sql areas?

Thanks.

Vijay.

Hi,

SAP is having 300 Users. DB Size is 170 GB.

SORT_AREA_SIZE = 2097152
SORT_AREA_RETAINED_SIZE = 0
No Of Instances = 1
Max Users connected to Oracle = 70

Any other applications = No

Lib Cache Hit Ratio = 96%
Row Cache HR = 92%

Statistoice Update = Every Saturday

What I understand is the following :

- I need to Increase SORT_AREA_SIZE and
SORT_AREA_RETAINED_SIZE

And Observe , right ?

Vijay

Hi,

Try it out and let us know if it increases your DB performance.

Thanks,

Quote:

---

Originally posted by shandj

SORT_AREA_SIZE and SORT_RETAINED_AREA_SIZE.

Both values should be same.

---

It is not a bad rule, but it is not a must. I do not have same values for SORT_AREA_SIZE and SORT_RETAINED_AREA_SIZE in my databases.

Quote:

---

Originally posted by shandj
Hi Vijay,

If it is due to queries, then set the following 2 parameters to a high value and both should be same.

SORT_AREA_SIZE and SORT_RETAINED_AREA_SIZE.

Both values should be same.

If there are any structural changes to objects in the schema, provide the new statistics by analyzing the objects.

The lower performance may be due to more I/O contentions.

-ShanDJ

---

Hello;
Please before increasing SORT_AREA_SIZE inspect the sort_vals in V$sysstsat : How many sorts on disk and how many in memory.
SORT_AREA_SIZE is a very expensive Parameter.

Orca

Hi,

Sorts Memory - 5314099
Sorts disk - 1350


So, sorting is taking place in Memory only..

So i guess this value is ok.

can u also tell me , when i check for wait stats, wait for Undo Header is cming as 350 ms. This is with respect to Rollback segs, right, so can this be an issue also.

Now what.

Vijay

Yes that could be the reason. Increase the number of rollback segments.

since you are saying that Oracle.exe is taking 1.35 GB, I believe you Oracle is doing paging and swapping.

Do you have the means to find this at the OS Level ?

All the Parameters and Values dont look that bad to me,
are there any other application on that NT Server that are fighting for Memory ?

With a 300+ userbase, your DB must be a MTS configuration.
It that the case ?

Quote:

---

Originally posted by patnams


With a 300+ userbase, your DB must be a MTS configuration.
It that the case ?

---

well well it is NOT a must

Hi,

But I already have rollback segments at 40.

Nop other application is running on Prod Server only One instance of Oracle and SAP Central Instance.

No it is not MTS config, an all 300 users doesn't interact directly with Oracle. There is only One user SAPR3 which have multiple sessions to interact with Oracle.

When I say MTS is a must, I meant at 300 Users you'd require a very large shared pool and processing capacity. Sorry about that...

It does not matter whether you have 1 user or 300 users. When refering to performance and # of users it invariably means # of sessions.

At 300 Sessions and with your Server Configuration, I'm sure there should be a significant difference when you switch to MTS.

Also 40 Rollback Segments ? Assuming 4 Trans per RBS, do you really have 160 Transaction at any point of time. Anyway RBS might not be an issue in this case.

Again, I'd go for an MTS if my userbase exceeds say 40 on a reasonably good server.