[GO] Locate SQL that consumes too much Oracle database CPU

today, at dinner, my friend's database has a problem, CPU occupancy is 97%, when I see this problem, I think of the SQL is probably the cause of this problem, because can't help eating, temporarily did not help him to see this problem, this is my after-dinner simulation of the failure, carried out analysis:

1) Check CPU process usage:

See the Oracle process as 6331,6517 and so on these processes consume CPU too high.

2) View related process information:

[Email protected] ~]$ Ps-ef | Grep6331Oracle6331     1  at  -: -?xx: Geneva: toOra_vktm_rhys Oracle6555  6385  0  -: +pts/2    xx:xx:xxGrep6331[[Email protected]-one ~]$ Ps-ef | Grep6517<span style="Color: #ff0000">oracle6517  6513  9  -: -?xx:xx: -Oraclerhys (description= (Local=yes) (Address= (protocol=BEQ)))  </span>oracle6557  6385  0  -: -pts/2    xx:xx:xxGrep6517[[Email protected]-one ~]$

3) View the session information:

Sql>SelectSid,serial#,username,machine,osuser,process fromv$session S2     whereS.paddr= (SelectAddr fromV$process PwhereP.spid='&pid'); Enter value forPid:6517 Old2:whereS.paddr= (SelectAddr fromV$process PwhereP.spid='&pid')  New   2:whereS.paddr= (SelectAddr fromV$process PwhereP.spid='6517') SID serial# USERNAME machine osuser PROCESS---------- ---------- ----------- ---------------- ------------------------------ ------------------------1          +RHYS Oracle-one Oracle6513  

The information of the session is Sid:1 serial#:21 database user is: RHYS, client machine: Oracle-one, operating system User: Oracle Process number: 6513

4) View the SQL that the session is running:

Sql>SelectSql_text fromV$sqltext2   where(Address,hash_value)inch (    SelectSql_address,sql_hash_value fromv$session S3    4        whereS.paddr=5(SelectAddr fromV$process PwhereP.spid='&pid')); Enter value forPid:6517 Old5:      (SelectAddr fromV$process PwhereP.spid='&pid'))  New   5:      (SelectAddr fromV$process PwhereP.spid='6517'))    <span style="Color: #ff0000">Sql_text----------------------------------------------------------------Delete fromAmy_emp</span>

It is known that the current user is in the process of deleting the table, this time through the v$process spid to find the progress number, and then find the v$session addr address, and then find V$sqltext sql_address and Sql_hash_value,

These two fields can be used to locate the unique sql_text, this time through the V$process,v$session,v$sql_text three views combined to find the corresponding SQL;

5) Subsequent processing

If the session is illegal, you can kill it with the following command: Alter SYTEM kill session ' 1,21 ';

Now that we have found the SQL, we can notify the application concerned to confirm whether the data is being manipulated.

Alternatively, we can use the Dbms_system package to track the session in more detail.

An article comparing the old, turn from: http://www.bitscn.com/pdb/oracle/201309/241814.html

[GO] Locate SQL that consumes too much Oracle database CPU