查询oracle比较慢的session和sql

来源：互联网发布：编程培训学费编辑：程序博客网时间：2024/05/28 23:20

--查询最慢的sql

select * from (
select parsing_user_id,executions,sorts
command_type,disk_reads,sql_text from v$sqlarea order by disk_reads desc
) where rownum<10

--查询对应session

select SE.SID,SE.SERIAL#,PR.SPID,
SE.USERNAME,SE.STATUS,SE.TERMINAL,
SE.PROGRAM,SE.MODULE,
SE.SQL_ADDRESS,ST.EVENT,
ST.P1TEXT,SI.PHYSICAL_READS,SI.BLOCK_CHANGES from v$session se,v$session_wait st,
v$sess_io si,v$process pr
where st.SID=se.SID and st.SID=si.SID
AND SE.PADDR=PR.ADDR
AND SE.SID>6
AND ST.WAIT_TIME=0
AND ST.EVENT NOT LIKE '%SQL%'
ORDER BY PHYSICAL_READS DESC;
SELECT sql_address FROM V$SESSION SS,V$SQLTEXT TT
WHERE SS.SQL_HASH_VALUE=TT.HASH_VALUE AND SID=439;

v$sqltext：存储的是完整的SQL,SQL被分割

v$sqlarea：存储的SQL 和一些相关的信息，比如累计的执行次数，逻辑读，物理读等统计信息（统计）

v$sql：内存共享SQL区域中已经解析的SQL语句。（即时）

根据sid查找完整sql语句：

select sql_text from v$sqltext a where a.hash_value = (select sql_hash_value from v$session b where b.sid = '&sid' )
order by piece asc

select a.CPU_TIME,--CPU时间百万分之一（微秒）
       a.OPTIMIZER_MODE,--优化方式
       a.EXECUTIONS,--执行次数
       a.DISK_READS,--读盘次数
       a.SHARABLE_MEM,--占用shared pool的内存多少
       a.BUFFER_GETS,--读取缓冲区的次数
       a.COMMAND_TYPE,--命令类型(3:select,2:insert;6:update;7delete;47:pl/sql程序单元)
       a.SQL_TEXT,--Sql语句
       a.SHARABLE_MEM,
       a.PERSISTENT_MEM,
       a.RUNTIME_MEM,
       a.PARSE_CALLS,
       a.DISK_READS,
       a.DIRECT_WRITES,
       a.CONCURRENCY_WAIT_TIME,
       a.USER_IO_WAIT_TIME
  from SYS.V_$SQLAREA a
WHERE PARSING_SCHEMA_NAME = 'CHEA_FILL'--表空间
order by a.CPU_TIME desc

从V$SQLAREA中查询最占用资源的查询

select b.username username,a.disk_reads reads,
a.executions exec,a.disk_reads/decode(a.executions,0,1,a.executions) rds_exec_ratio,
a.sql_text Statement
from v$sqlarea a,dba_users b
where a.parsing_user_id=b.user_id
and a.disk_reads > 100000
order by a.disk_reads desc;

用buffer_gets列来替换disk_reads列可以得到占用最多内存的sql语句的相关信息。

v$sql：内存共享SQL区域中已经解析的SQL语句。（即时）

列出使用频率最高的5个查询：

select sql_text,executions
from (select sql_text,executions,
   rank() over
    (order by executions desc) exec_rank
   from v$sql)
where exec_rank <=5;

消耗磁盘读取最多的sql top5：
select disk_reads,sql_text
from (select sql_text,disk_reads,
   dense_rank() over
     (order by disk_reads desc) disk_reads_rank
   from v$sql)
where disk_reads_rank <=5;

找出需要大量缓冲读取（逻辑读）操作的查询：

select buffer_gets,sql_text
from (select sql_text,buffer_gets,
   dense_rank() over
     (order by buffer_gets desc) buffer_gets_rank
   from v$sql)
where buffer_gets_rank<=5;

v$sql、v$sqlarea 、v$sqltext
这三个视图都可以用于查询共享池中已经解析过的SQL语句及其相关信息。
V$SQL中列出了共享SQL区中所有语句的信息，它不包含GROUP BY字句，并且为每一条SQL语句中单独存放一条记录；
V$SQLAREA中一条记录显示了一条共享SQL区中的统计信息。它提供了有在内存中、解析过的和准备运行的SQL语句的统计信息；
V$SQLTEXT包含了库缓存中所有共享游标对应的SQL语句。它将SQL语句分片显示。
下面介绍一下常用的V$SQLAREA的结构：
字段 / 数据类型 / 说明
SQL_TEXT VARCHAR2(1000) 游标中SQL语句的前1000个字符。
SHARABLE_MEM NUMBER 被游标占用的共享内存大小。如果存在多个子游标，则包含所有子游标占用的共享内存大小。
PERSISTENT_MEM NUMBER 用于一个打开这条语句的游标的生命过程中的固定内存大小。如果存在多个子游标，则包含所有子游标生命过程中的固定内存大小。
RUNTIME_MEM NUMBER 一个打开这条语句的游标的执行过程中的固定内存大小。如果存在多个子游标，则包含所有子游标执行过程中的固定内存大小。
SORTS NUMBER 所有子游标执行语句所导致的排序次数。
VERSION_COUNT NUMBER 缓存中关联这条语句的子游标数。
LOADED_VERSIONS NUMBER 缓存中载入了这条语句上下文堆（KGL heap 6）的子游标数。
OPEN_VERSIONS NUMBER 打开语句的子游标数。
USERS_OPENING NUMBER 打开这些子游标的用户数。
FETCHES NUMBER SQL语句的fetch数。
EXECUTIONS NUMBER 所有子游标的执行这条语句次数。
USERS_EXECUTING NUMBER 通过子游标执行这条语句的用户数。
LOADS NUMBER 语句被载入和重载入的次数
FIRST_LOAD_TIME VARCHAR2(19) 语句被第一次载入的时间戳。
INVALIDATIONS NUMBER 所以子游标的非法次数。
PARSE_CALLS NUMBER 所有子游标对这条语句的解析调用次数。
DISK_READS NUMBER 所有子游标运行这条语句导致的读磁盘次数。
BUFFER_GETS NUMBER 所有子游标运行这条语句导致的读内存次数。
ROWS_PROCESSED NUMBER 这条语句处理的总记录行数。
COMMAND_TYPE NUMBER Oracle命令类型代号。
OPTIMIZER_MODE VARCHAR2(10) 执行这条的优化器模型。
PARSING_USER_ID NUMBER 第一次解析这条语句的用户的ID。
PARSING_SCHEMA_ID NUMBER 第一次解析这条语句所用的schema的ID。
KEPT_VERSIONS NUMBER 所有被DBMS_SHARED_POOL包标识为保持（Keep）状态的子游标数。
ADDRESS RAW(4 | 8) 指向语句的地址
HASH_VALUE NUMBER 这条语句在library cache中hash值。
MODULE VARCHAR2(64) 在第一次解析这条语句是通过调用DBMS_APPLICATION_INFO.SET_MODULE设置的模块名称。
MODULE_HASH NUMBER 模块的Hash值
ACTION VARCHAR2(64) 在第一次解析这条语句是通过调用DBMS_APPLICATION_INFO.SET_ACTION设置的动作名称。
ACTION_HASH NUMBER 动作的Hash值
SERIALIZABLE_ABORTS NUMBER 所有子游标的事务无法序列化的次数，这会导致ORA-08177错误。
IS_OBSOLETE VARCHAR2(1) 游标是否被废除（Y或N）。当子游标数太多了时可能会发生。
CHILD_LATCH NUMBER 为了包含此游标的子latch数。

查看当前会话所执行的语句以及会话相关信息：
SQL> select a.sid||'.'||a.SERIAL#, a.username, a.TERMINAL, a.program, s.sql_text
2 from v$session a, v$sqlarea s
3 where a.sql_address = s.address(+)
4 and a.sql_hash_value = s.hash_value(+)
5 order by a.username, a.sid;

---整理自网上