Oracle databases often encounter high CPU utilization. How can this problem be solved ?, Oracle Utilization

Oracle databases often encounter high CPU utilization. How can this problem be solved ?, Oracle Utilization

Query the SQL statements executed by processes based on the number of processes that occupy the cpu:

SELECT sql_text FROM v$sqltext a WHERE (a.hash_value, a.address) IN (SELECT DECODE(sql_hash_value, 0, prev_hash_value, sql_hash_value), DECODE(sql_hash_value, 0, prev_sql_addr, sql_address) FROM v$session b WHERE b.paddr = (SELECT addr FROM v$process c WHERE c.spid = '&pid')) ORDER BY piece ASC;

For example, query the SQL language corresponding to the 31968 process:

SELECT sql_text FROM v$sqltext a WHERE (a.hash_value, a.address) IN (SELECT DECODE(sql_hash_value, 0, prev_hash_value, sql_hash_value), DECODE(sql_hash_value, 0, prev_sql_addr, sql_address) FROM v$session b WHERE b.paddr = (SELECT addr FROM v$process c WHERE c.spid = '31968')) ORDER BY piece ASC;

Run the corresponding SQL statement using the pid:

select sql_text from v$process pr,v$session ss,v$sqlarea sl where pr.addr=ss.PADDR  and ss.SQL_HASH_VALUE=sl.HASH_VALUE and pr.spid=10840;

View the SQL statement currently being executed:

select a.program, b.spid, c.sql_text,c.SQL_ID from v$session a, v$process b, v$sqlarea c where a.paddr = b.addr and a.sql_hash_value = c.hash_value and a.username is not null;

1. view the process number with high CPU usage

2. check what the process is doing based on the process number.

select sql_text,spid,v$session.program,process from v$sqlarea,v$session,v$process where v$sqlarea.address=v$session.sql_address and v$sqlarea.hash_value=v$session.sql_hash_value and v$session.paddr=v$process.addr and v$process.spid in(PID);

3. check what the database wait events have.

select sid,event,p1,p1text from v$session_wait;

See what processes are waiting for the event

select spid from v$process where addr in(select paddr from v$session where sid in(84,102,101));

The following script can be used to obtain the SQL statement by the SID of the known session.

select sql_text from v$sql_text a where a.hash_value=(  select sql_hash_value   from v$session b   where b.SID=’&sid’) order by piect ASC;

View the current session SQL _id

select sql_id ,username,status,event from v$session;

View SQL statements by SQL _id

select sql_text from v$sql where sql_id='cx7sxk891r782';

ORACLE queries SQL statements with low execution efficiency

-- SQLselect SQL _text from v $ SQL order by cpu_time desc high -- SQL statements with multiple logical reads: select * from (select buffer_gets, SQL _textfrom v $ sqlareawhere buffer_gets> 500000 order by buffer_gets desc) where rownum <= 30; -- SQL statements with multiple executions: select SQL _text, executions from (select SQL _text, executions from v $ sqlarea order by executions desc) where rownum <81; -- SQL statements with multiple hard disk reads: select SQL _text, disk_reads from (select SQL _text, disk_reads from v $ sqlarea order by disk_reads desc) where rownum <21;

Select * from (select SQL _text, SQL _id, cpu_time from v $ SQL order by cpu_time desc) where ownum <= 10 order by rownum asc; select * from (select SQL _text, SQL _id, cpu_time from v $ sqlarea order by cpu_time desc) where rownum <= 10 order by rownum asc; -- these two statements have the same effect, query a query from the v $ SQL view and a query from the v $ sqlarea view. -- List the five most frequently used queries: select SQL _text, executionsfrom (select SQL _text, executions, rank () over (order by executions desc) exec_rank from v $ SQL) where exec_rank <= 5; -- top 5 SQL statements that consume the most disk reads: