oracle高资源消耗sql,oracle 耗资源比较大的SQL

时间：

select sql_id,sid,serial#,username,target ,sql_plan_operation,sofar blocks_read,totalwork total_blocks,round(time_remaining/60) minutes from v$session_longops

where sofar<>totalwork

and username='XXX'

sofar : Units of work done so far for the operation specified in the OPNAME column

这个值会一直增加,就是截止到目前所统计出的工作总量，而这里用了where sofar<>totalwork的含义代表就是此事务还没结束，因为结束了sofar会等于totalwork.

Totalwork:Total units of work for the operation specified in the OPNAME column

预估出的总数据块读取量，不会改变

Time_remaining:Estimate (in seconds) of time remaining for the operation to complete

V$SESSION_LONGOPS displays the status of various operations that run for longer than 6 seconds (in absolute time). These operations currently include many backup and recovery functions, statistics gathering, and query execution, and more operations are added for every Oracle release.

官方文档并未对v$session_longops原理做详细的介绍，想要通过v$session_longops监控一个操作，需要满足以下条件：

1 执行操作超过6秒

2 执行操作访问数据块必须大于10000

3 必须设置了TIMED_STATISTICS或者打开了SQL_TRACE

4 执行操作中的对应必须通过DBMS_STATS或ANALYZE进行了分析

在以上基础上再关联v$sql.

IO

select sql_id,disk_reads from

(select sql_id,buffer_gets,disk_reads,sorts,cpu_time/1000000 cpu,rows_processed,elapsed_time from v$sqlstats

order by disk_reads desc

--- 具体要依据什么信息排序可适当选择,而此视图里的多项值是累积的