1. top查看占用CPU比较高的进程ID,并记录下来
例如:
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
31189 nxuser 15 0 12436 11m 256 S 55.4 0.1 35626:21 oracle
32902 oracle 16 0 1304m 1.0g 1.0g S 1 12.5 13:07.28 oracle
5784 nxuser 16 0 2368 1104 780 S 0 0.0 0:00.02 oracle
28779 nxuser 16 0 2364 1140 780 R 0 0.0 0:25.28 top
Pid 31189占用CPU显然是比较高的
Sqlpusl连接登陆ORACLE
2.执行下面的语句,查得相对应的系统进程对应的session id
SQL> select sid from v$session where paddr in (select addr from v$process where spid=&spid);
Enter value for spid: 31189
old1: select sid from v$session where paddr in (select addr from v$process where spid=&spid)
new1: select sid from v$session where paddr in (select addr from v$process where spid=31189)
SID
----------
206
3.根据所得的会话ID查得sql地址和hash值
SQL> select sql_address,sql_hash_value from v$session where sid=206;
SQL_ADDR SQL_HASH_VALUE
-------- --------------
6EC554F43141392848
4.根据sql hash值查得sql语句
SQL> select sql_text from v$sqltext where hash_value=3141392848;
SQL_TEXT
----------------------------------------------------------------
INSERT INTO TEST SELECT * FROM SYS.DBA_OBJECTS
5.若没查得相应的sql地址和hash值,请查询job