To find the most resource SQL, we can first use TOP and other tools, find the best resources (remember the process number), for example, the operating system process number is 1217, then according to this process number (v $ process.spid ) Find the process address (V $ process.addr) in V $ Process, and then find the corresponding SID (V $ session.sid) in V $ session, then find the corresponding Hash Alue according to this SID (V $ SESSION. SQL_HASH_VALUE), then find the corresponding SQL statement (SQL_Text) in the view of V $ SQLText, $ SQL, V $ SQLAREA, according to this Hash Alue.
E.g:
Oracle @ CS_DB02: / Arch1 / Lunar / Tools> Whoistopsql.sh 6799connected.
Sid Serial # username OSUSER Machine Program Process To_Char (Logon_Time, -------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------ ------------------------------------ -------------------------------------------------------------------------------------------------------------------------------------------------------- ------------ ------------ ------------------- 483 1224 Portal WWW16 BJ_WAP03 JDBC Thin CLIENT 2004/09/22 02:21:03
SQL_Text ------------------------------------------------- --------------- SELECT * from spinfo where spid =: 1
Oracle @ cs_db02: / arch1 / lunar / Tools>
