I am using
oracle R12.1.3 ,
database 11.2.0.3.
Linux Redhat 5
output of top command to check the CPU usage:
$ top -n 1
top - 13:02:12 up 289 days, 19:02, 2 users, load average: 2.06, 2.07, 2.08
Tasks: 751 total, 3 running, 748 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.7%us, 0.1%sy, 0.0%ni, 99.1%id, 0.1%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 32464896k total, 32146980k used, 317916k free, 514852k buffers
Swap: 34812844k total, 140528k used, 34672316k free, 27558564k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
24007 oratest5 25 0 2288m 68m 56m R 98.0 0.2 1515:26 oracle
24009 oratest5 25 0 2282m 49m 44m R 98.0 0.2 1515:26 oracle
29767 oratest5 15 0 13180 1564 756 R 1.9 0.0 0:00.03 top
1 root 15 0 10372 700 588 S 0.0 0.0 0:13.97 init
2 root RT -5 0 0 0 S 0.0 0.0 1:52.29 migration/0
3 root 34 19 0 0 0 S 0.0 0.0 2:11.14 ksoftirqd/0
My requirement:
Finding the query that is causing high CPU consumption to Optimize it.
symptoms:
I have two oracle commands that is eating the CPU. So, my requirement is that
I need to find the query that is causing that high consumption of CPU.
PIDs:
These two processes ID as shown in the output above are as following:
24007
24009
Approach:
So by identifying their PIDs , I can proceed by querying the following:
1. Session ID based on PID.
2. SQL query based on the Session ID.
Here is my query to get the session ID from PID:
sql> select s.sid from v$process p, v$session s
where s.paddr=p.addr and p.spid = 24007 ;
output: 378
The other session ID from the other PID consuming high CPU:
sql>select s.sid from v$process p, v$session s
where s.paddr=p.addr and p.spid =24009
output: 78
Now, getting the actual sql statement from these obtained SID from the queries above:
sql>SELECT SQL_TEXT from V$SQLTEXT_WITH_NEWLINES where HASH_VALUE
= (select sql_hash_value from v$session
where SID = 378) ;
output: null
Getting the actual sql statement from the other SID (78):
sql>SELECT SQL_TEXT from V$SQLTEXT_WITH_NEWLINES where HASH_VALUE
= (select sql_hash_value from v$session
where SID = 78);
output: null
Why is it returning null value?
From the documentation:
V$SQLTEXT_WITH_NEWLINES
This view is identical to the V$SQLTEXT view except that, to improve legibility,
V$SQLTEXT_WITH_NEWLINES does not replace newlines and tabs in the SQL statement with spaces.
v$sqltext
This view contains the text of SQL statements belonging to shared SQL cursors in the SGA.
Am I querying from the wrong views?