Oracle 状态及连接信息SQL

根据当前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;