Oracle queries the most resource-consuming SQL statements

 

I. First, run the TOP command to view the spid numbers with more resources.

 

II. Query SQL statements by PID

1. The first method is to query multiple PIDs.

(1) query the current time-consuming session ID, user name, sqlid, etc.In ('123', '123'); replace the ID with one or more IDs.
Select Sid, serial #, machine, username, program, SQL _hash_value, SQL _id,
To_char (logon_time, 'yyyy/MM/DD hh24: MI: ss') as login_time from V $ session
Where paddr in (select ADDR from V $ process where spid in ('20160301', '20160301 '));

(2) If the SQL _id or hash_value in the previous step is not empty, use v $ sqlarea to find the SQL statement currently in use.
Select SQL _text
From v $ sqltext_with_newlines
Where hash_value = & hash_value
Order by piece;

2. Method 2: one step in place, but only one PID can be queried at a time

Select SQL _text
From V $ sqltext
Where (A. hash_value, A. Address) In
( Select Decode (SQL _hash_value, 0 , Prev_hash_value, SQL _hash_value ),
Decode (SQL _hash_value, 0 , Prev_ SQL _addr, SQL _address)
From V $ session B
Where B. paddr = ( Select ADDR From V $ Process
C Where C. spid =   ' & Pid ' ))
Order   By Piece ASC