折雨的天空

新浪微薄腾讯微薄

最新碎语:最近霉到了住。哎。。。

您的位置:折雨的天空 >其他技术> oracle慢查询可能会用到的SQL

oracle慢查询可能会用到的SQL

原文地址:

https://www.cnblogs.com/adolfmc/p/4796266.html


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

 

引用:http://jenniferok.iteye.com/blog/700985

从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;

---

转载请注明本文标题和链接:《oracle慢查询可能会用到的SQL

分享到:

发表评论

路人甲 表情
看不清楚?点图切换