Show currently executing SQL

Whenever your database is experiencing a performance problem, it is very useful to have a script that displays all of the currently executing SQL statements. The following script joins the v$sql and v$session tables to show you all of the SQL that is executing at that moment in your database.



set pagesize 24
set newpage 1
set linesize 125

column sql_text format a100
column user_name format a12

select
u.sid,
substr(u.username,1,12) user_name,
s.sql_text
from
v$sql s,
v$session u
where
s.hash_value = u.sql_hash_value
and
sql_text not like '%from v$sql s, v$session u%'
order by
u.sid;


Here is the output from this script. As we see, this is extremely useful because a runaway SQL statement could cause degradation of your whole system. If you identify a runaway query, you can issue the ALTER SYSTEM KILL SESSION 'SID, SER#'; command to kill the query:





SQL> @cur_sql



5 select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t where t.ts#=f.ts# and t.dflextpct!=0 an

d t.bitmapped=0



6 select local_tran_id, global_tran_fmt, global_oracle_id, global_foreign_id, state, status, he

uristic_dflt, session_vector, reco_vector, 3600*24*(sysdate-reco_time), 3600*24*(sysda

te-nvl(heuristic_time,fail_time)), global_commit#, type# from pending_trans$ where session_vector

!= '00000000'



7 BEGIN sys.dbms_ijob.remove(:job); END;

9 READER SELECT TO_CHAR(page_unique_id), page_seq_nbr, book_unique_id, visual_page_nbr,

page_text FROM page WHERE page_unique_id = TO_NUMBER('2380')



14 READER SELECT TO_CHAR(page_unique_id), page_seq_nbr, book_unique_id, visual_page_nbr,

page_text FROM page WHERE page_unique_id = TO_NUMBER('9975')