Oracle CPU is too high to handle, this article will describe the Oracle process CPU usage is too high, need to know more than friends can refer to the
1: First use the top command to upload the CPU-intensive SPID number
PID USERNAME THR PRI Nice SIZE RES the state time CPU COMMAND
3575 Oracle 1 0 0K 0K run 748.6H 24.98% Oracle
3571 Oracle 1 0 0K 0K sleep 706.2H 22.84% Oracle
2: Log on to the database using the DBA account and query using the following SQL statement:
SELECT * from v$process WHERE spid=3575 query to SQL related information
3: Use the following SQL query based on the information above:
Select SID, program from V$session S where EXISTS (select 1 from v$process where spid=3575 and ADDR = s.paddr);
You can query the specific client query has been constantly occupying Oracle resources!
Finally, the query to deal with!
4. According to Sid Sqlitpub
Copy Code code as follows:
Select Sql_textitpub
From V$sqltext
where a.hashvalue= (select Sql_hash_value
From V$session b
where b.sid= ' &sid ')
0order by Piece ASC;
Depending on the Lockwait field, you can query for information about the lock that is currently waiting:
Copy Code code as follows:
SELECT * from V$lock where kaddr in (select Lockwait from v$session where sid= $sid);
(Sql_address,sql_hash_value), (Prev_sql_addr,prev_hash_value) based on these two sets of fields, you can query the details of the SQL statement that is being executed or most recently executed by the current session:
SELECT * from v$sqltext where address = &sql_address and Hash_value = &sql_hash_value;
According to the PID check SQL related information:
Copy Code code as follows:
Select id,serial#, Username,osuser,machine,program,process,to_char (Logon_time, ' yyyy/mm/dd hh24:mi:ss ') logon from v$ Session where Paddr in (select addr to v$process where spid in (' &pid '));
Check SQL statements according to PID
Copy Code code as follows:
SELECT A.username,a.machine,a.program,a.sid,a.serial#,a.status,c.piece,c.sql_text from V$session a,v$process b,v$ SQLText c WHERE b.spid= ' &spid ' and b.addr=a.paddr and a.sql_address=c.address (+) Order by c.piece;
Get the SID number of the process:
Copy Code code as follows:
Select id,serial#, Username,osuser,machine,program,process,to_char (Logon_time, ' yyyy/mm/dd hh24:mi:ss ') logon from v$ Session where Paddr in (select addr to v$process where spid in (' &pid '));
Get the SQLTEXT statement of the session:
Copy Code code as follows:
Select Sql_text from V$sqltext_with_newlines where Hash_value into (select Sql_hash_value from V$session where paddr in (sel ECT addr from v$process where spid= ' &pid ') an order by piece;
