查询IO相关SQL
来源:互联网 发布:成本会计 知乎 编辑:程序博客网 时间:2024/06/05 16:18
--查询当前执行IO最多的sql,并按大到小来排序
select parsing_schema_name, cast(buffer_gets / decode(executions, 0, 10000, executions) as int) 平均io,
executions 执行次数,
buffer_gets 总逻辑io,
disk_reads 硬盘读取,
sql_text sql语句
from v$sqlarea
where parsing_schema_name = 'AML2010' --属主
order by buffer_gets desc;
--查找前十条性能差的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;
--查看占io较大的正在运行的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,
st.p1,
st.p2,
st.p3,
st.state,
st.seconds_in_wait,
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 b.spid,
c.piece,
a.username,
a.machine,
a.program,
a.sid,
a.serial#,
a.status,
c.piece,
c.sql_text,
d.sql_fulltext
from v$session a, v$process b, v$sqltext c, v$sqlarea d
where b.addr = a.paddr
and a.sql_address = c.address(+)
and c.sql_id = d.sql_id
and a.sql_id = d.sql_id
and b.spid = '10438'
order by c.piece
--查询走哪些SQL走了全表扫描
select distinct a.sql_text, a.sql_id, b.options, a.last_load_time, b.operation
from v$sql a, v$sql_plan b
where a.sql_id = b.sql_id
and a.last_load_time like '2015-04-15%'
and b.options = 'FULL'
and b.operation = 'TABLE ACCESS'
0 0
- 查询IO相关SQL
- IO相关SQL
- SQL SERVER查询IO
- SQL高级查询相关
- 相关子查询 - SQL
- 查询机相关.sql
- sql时间查询相关
- 查询SQL相关信息
- SQL相关查询
- SQL入门:相关子查询
- SQL相关路径查询脚本
- SQL 相关子查询例子
- Oracle 日期相关查询SQL
- SQL基础--子查询相关
- SQL 查询日期相关记录
- Oracle 日期相关查询SQL
- sql查询相关技术-内连接查询
- SQL查询相关技术-4.SQL操作
- Windows远程访问Ubuntu
- virtualbox中虚拟机和主机之间剪贴板共享
- Symmetric Tree
- java多线程实现生产者与消费者---经典问题
- 二叉树题目小结
- 查询IO相关SQL
- 基于Selenium技术的Web自动化测试框架
- Model、Bean 打印各字段toString方法
- hexo搭建Gitcafe博客(专栏)
- 单击和双击共存
- 常见的几种RuntimeException
- Python Decorator
- linux下字符串函数总结
- Android自定义控件实战——滚动选择器PickerView