oracle 耗资源比较大的SQL-ORACLE

goodmm

　　时间：
　　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 sofartotalwork
　　and username='XXX'
　　sofar : Units of work done so far for the operation specified in the OPNAME column
　　这个值会一直增加,就是截止到目前所统计出的工作总量，而这里用了where sofartotalwork的含义代表就是此事务还没结束，因为结束了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>　　官方文档并未对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
　　--- 具体要依据什么信息排序可适当选择,而此视图里的多项值是累积的