Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

Donald K. Burleson

Oracle Tips

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

 

 

 

 

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 
 
email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.