DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: CPU and Memory Consumption by Oracle

  1. #1
    Join Date
    May 2001
    Location
    Delhi
    Posts
    340

    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 ....

  2. #2
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    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

  3. #3
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    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

  4. #4
    Join Date
    Feb 2002
    Posts
    70

    Post

    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

  5. #5
    Join Date
    Mar 2002
    Posts
    301
    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

  6. #6
    Join Date
    May 2001
    Location
    Delhi
    Posts
    340
    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 ....

  7. #7
    Join Date
    Feb 2002
    Posts
    70
    Hi,

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

    Thanks,
    ________________
    ShanDJ

  8. #8
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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

  9. #9
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    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

  10. #10
    Join Date
    May 2001
    Location
    Delhi
    Posts
    340
    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
  •  


Click Here to Expand Forum to Full Width