根据linux的oracle进程号查询正在执行的sql
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
获取耗时的前10条sql
select *
from (select v.sql_id,
v.child_number,
v.sql_fulltext,
v.elapsed_time,
v.cpu_time,
v.disk_reads,
rank() over(order by v.elapsed_time desc) elapsed_rank
from v$sql v) a
where elapsed_rank <= 10
ORDER BY cpu_time desc;
sql执行计划
EXPLAIN PLAN FOR select questionid,mark from zeanswerdetail where username=:1 and paperid =:2 and questionid=:3 and answerid=:4 ;
select * from table(DBMS_XPLAN.DISPLAY);