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

Hi

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

regards
Hrishy

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.

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

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.

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

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.

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