-
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 !!!!
--------------------------
The Time has come ....
-
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
anandkl
-
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
________________
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.
Say No To Plastics
-
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
--------------------------
The Time has come ....
-
Hi,
Try it out and let us know if it increases your DB performance.
Thanks,
________________
ShanDJ
-
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.
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
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
--------------------------
The Time has come ....
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|