Quick Oracle Fault Locating

First, use the operating system command to find the process ID with high CPU utilization

1. View All SQL statements currently executed by the database and the corresponding process ID

 
Column Machine format A16;
Column program format A16;
SelectP. spid, S. Status, S. Machine, P. Program, Q. SQL _text
FromV $ session S, V $ sqlarea Q, V $ PROCESS p
WhereS. SQL _address = Q. Address and S. SQL _hash_value = Q. hash_value
And S. paddr = P. ADDR
Order5;
 

 

2. view the table currently locked and the corresponding process ID ---------

Column object_name format A20;
Column program format A30;
Column username format A18;
Column Machine format A16;
SelectO. object_name, P. spid, L. session_id, S. Serial #, S. program,
S. username, S. Machine, S. seconds_in_wait
FromV $ locked_object L, all_objects o, V $ session S, V $ PROCESS p
WhereO. object_id = L. object_id and S. Sid = L. session_id
And S. paddr = P. ADDR and S. Status ='Active' 
Order1DESC;
 

View SQL statements that have been operated for a long time

Column target_desc format A15; column Message format A50; column opname format A12; select Sid, opname, target_desc, SOFAR, totalwork, trunc (SOFAR/totalwork * 100,2) | '%' As perwork, to_char (start_time, 'dd hh24: MI: ss') starttime, elapsed_seconds, messagefrom v $ session_longops order by 8
 

 

----- Top event ------
Select Sid, state, wait_class, event from V $ session_wait;

----- Historical analysis ---------- v $ active_session_history, dba_hist_active_sess_history
Select to_char (sample_time, 'ddhh24mi '), count (*) from
Dba_hist_active_sess_history
Where sample_time> sysdate-1
Group by to_char (sample_time, 'ddhh24mi ')
Order by 1

Common SQL statements in different time periods

 Set Pagesize 0 
Column SQL _id format A14;
Column SQL _text format A50;
 Select Distinct D. SQL _id, Q. SQL _text
 From Dba_hist_active_sess_history D, V $ sqlarea Q
 Where Sample_time between to_date ( '  02131558  ' , '  Mmddhh24mi  ' ) And to_date ( '  02131559  ' ,'  Mmddhh24mi  ' )
And D. SQL _id = Q. SQL _id
Intersect
 Select Distinct D. SQL _id, Q. SQL _text
 From Dba_hist_active_sess_history D, V $ sqlarea Q
 Where Sample_time between to_date ( '  02131551  ' , '  Mmddhh24mi  ' ) And to_date ( '  02131552  ' , '  Mmddhh24mi  ' )
And D. SQL _id = Q. SQL _id