Performance Issue need urgent help
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Performance Issue need urgent help

  1. #1
    Join Date
    Sep 2000
    Posts
    305

    Performance Issue need urgent help

    Hi Friends

    My problem is not yet solved in the previous thread and that thread had became too long so I am starting a new thread as I mentioned in the problem that the cpu is 100% utilized during the peak hours I am pasting here the statistics

    procs memory page disk faults cpu
    r b w swap free si so pi po fr de sr f0 s0 s1 s6 in sy cs us sy id
    6 0 0 2937880 33120 0 0 4 54 54 3480 0 0 9 7 0 892 5067 722 96 4 0
    5 0 0 2939408 33424 0 0 0 33 43 31536 2 0 5 4 0 675 2865 376 95 5 0
    11 0 0 2930072 31344 0 0 11 115 814 23000 476 0 10 8 0 1000 6078 897 93 7 0
    9 0 0 2934024 33192 0 0 14 22 24 13600 0 0 15 13 0 1095 8035 1120 94 6 0
    5 0 0 2935808 33304 0 0 6 1 1 8040 0 0 7 5 0 906 5483 770 95 5 0
    4 0 0 2942016 36464 0 0 1 0 0 4760 0 0 6 4 0 856 5244 730 95 5 0


    If you see the first column i.e r(procs) it is more then the number of cpu i.e greater then 2 (no. of cpu is 2) I am not able to find out why it happens suddenly it was working fine without any problem but this problem I am facing for last four five days can anyone give me some idea do I need to change the size of SGA right now it is 600 MB total memory is 2 GB.

    thanks and wating for your suggestions.
    Shailendra

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Can you please check /var/adm/messages..is there something wrong with your system ?

    regards
    Hrishy

  3. #3
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    Have you checked with the developers of your application that uses the db? Any issues there?

    So also, check your v$session...
    Code:
    SELECT  rpad(USERNAME,15), TO_CHAR(LOGON_TIME, 'DD-MON-YYYY'), rpad(OSUSER,20),
    rpad(module,15), RPAD(MACHINE,20),  status, COUNT(*) FROM V$SESSION
    GROUP BY USERNAME, TO_CHAR(LOGON_TIME, 'DD-MON-YYYY'), OSUSER, module, MACHINE, status
    order by 6
    HTH.

  4. #4
    Join Date
    Sep 2000
    Posts
    305
    Hrishy there is nothing in the /var/adm/messages I have checked also I have checked the query which ggnanaraj had given it is showing 5 rows and the total number of sessions is 20 anything else you wanted to know from myside?

    Shailendra

  5. #5
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    vmstat analysis show the following:
    - paging exists
    - user utilization of cpu is almost 100%

    Post the result of the following query:
    Code:
    select * from v$waitstat;
    BTW, has there been any...
    1. any change in the application using the database
    2. any change in OS made after which this occurs (patches, etc.)
    3. any change made in the db level

    HTHu.

  6. #6
    Join Date
    Sep 2000
    Posts
    305
    here is the output of V$WAITSTAT

    Class Count Time
    data block 3459 0
    sort block 0 0
    save undo block 0 0
    segment header 1 0
    save undo header 0 0
    free list 0 0
    extent map 0 0
    bitmap block 0 0
    bitmap index block 0 0
    unused 0 0
    system undo header 0 0
    system undo block 0 0
    undo header 48 0
    undo block 0 0


    Shailendra

  7. #7
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    If u really need help you must be courteous enough to answer the questions asked and not ignore it. That will enable us to get a solution to your problem. I hope u understand.

    Here are some more...

    1. How long has the database been up? (from v$session).
    2. Of the 4 disks (f0, s0, s1, s6), f0 & s6 seem to be relatively free from access? Any reason why?


    Check Article 62161.1 on metalink for the waits on data blocks.

    HTH.

  8. #8
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    What is the version of oracle are you running..can you get the list of sql that consumes so much CPU..what changed in the last few dyas..this problem is getting difficult to investigate..you should involve your sysadmin to..

    regards
    Hrishy

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