top查看占用cpu高的进程spid
根据sid查spid
例如job:
select * from dba_jobs_running
select c.spid from gv$process c where c.addr=(select b.paddr from gv$session b where b.sid=1489);
根据spid查sql
select sql_text,sql_fulltext
from gv$sql
where (address, hash_value) in
(select /*+unnest*/
DECODE(sql_hash_value, 0, prev_sql_addr, sql_address),
DECODE(sql_hash_value, 0, prev_hash_value, sql_hash_value)
from gv$session
where sid =
(select sid
from gv$session
where paddr =
(select ADDR from gv$process where SPID = '17614')));
根据sid查看sql
查正在执行的sql：
select sql_text from gv$sql where (hash_value,address)=(select sql_hash_value,sql_address from gv$session where sid=1489);
查已经执行过的sql：
select sql_text from gv$sqlarea a,gv$session b where a.SQL_ID=b.PREV_SQL_ID and b.SID=1489;
查看正在执行的SQL:
SELECT d.spid,a.sid,b.event,b.status,C.SQL_TEXT,B.USERNAME,B.OSUSER,B.MACHINE,B.PROGRAM
FROM gV$SESS_IO A,gV$SESSION B,gV$SQL C,gV$PROCESS D
WHERE A.SID=B.SID AND B.SQL_HASH_VALUE=C.HASH_VALUE
AND B.PADDR=D.ADDR and b.status='ACTIVE' order by sql_text;
相关参考：https://www.modb.pro/db/1775051312207564800
