Oracle Database Discussions

Hi,

Please help me in finding out the most cpu consuming process...

I am using 10.2.0 in solaris 10 machine....

So I am using 'prstat' command...

SQL>!prstat

From RSS column ...we can find out the pid of the most cpu consuming process....

PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP
1113 oracle 105M 35M sleep 59 0 0:00:18 7.6% java/14
739 oracle 110M 45M sleep 59 0 0:00:16 3.3% Xorg/1
1198 oracle 419M 342M sleep 59 0 0:00:05 1.0% oracle/1
1067 oracle 80M 22M sleep 59 0 0:00:01 0.4% nautilus/4
1088 oracle 83M 16M sleep 59 0 0:00:05 0.3% gnome-terminal/2
924 oracle 62M 5628K sleep 59 0 0:00:12 0.2% vmware-user/1
1416 oracle 419M 346M sleep 59 0 0:00:00 0.2% oracle/1 1194 oracle 420M 348M sleep 59 0 0:00:04 0.2% oracle/1
1414 oracle 5196K 2780K cpu0 59 0 0:00:00 0.1% prstat/1


My question is that, after finding out the pid of the most cpu consuming process, how will we find out the sql statement correspoding to that pid.

using these views (V$process.pid and spid and V$session.sql_hash_value and finaly use v$sql.sql_text) we can find which query consuming more process...but how we will figure it out ......

Please help