根据当前pid线程查询对应执行的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;
查询执行最慢的SQL
select * from (select sa.SQL_TEXT,sa.SQL_FULLTEXT,sa.EXECUTIONS "执行次数",round(sa.ELAPSED_TIME / 1000000, 2) "总执行秒数",round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) "平均执行秒数", sa.COMMAND_TYPE,sa.PARSING_USER_ID "用户ID", u.username "用户名", sa.HASH_VALUE from v$sqlarea sa left join all_users u on sa.PARSING_USER_ID = u.user_id where sa.EXECUTIONS > 0 order by (sa.ELAPSED_TIME / sa.EXECUTIONS) desc) where rownum <= 10;
查询存在全表扫描的语句
select v.sql_ID,V.TIMESTAMP,v.object_owner,s.sql_text,s.sql_fulltext from v$sql_plan v,v$sqlarea s where s.sql_id=v.sql_id and v.operation = 'TABLE ACCESS' and v.OPTIONS = 'FULL';
查看当前会话IP
select sys_context( 'userenv' , 'ip_address' ) from dual;