Oracle v$sql Tips

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.

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')