Oracle中查看慢查询进度的脚本分享
Oracle一个大事务的SQL往往不知道运行到了哪里,可以使用如下SQL查看执行进度。
setlinesize400; setpagesize400; colsql_textformata100; colopnameformata15; SELECTse.sid, opname, TRUNC(sofar/totalwork*100,2)pct_work, elapsed_secondselapsed, ROUND(elapsed_seconds*(totalwork-sofar)/sofar)remain_time, sql_text FROMv$session_longopssl,v$sqlareasa,v$sessionse WHEREsl.sql_hash_value=sa.hash_value ANDsl.sid=se.sid ANDsofar!=totalwork ORDERBYstart_time;
或者
setlinesize400; setpagesize400; setlong4000; colsql_fulltextformata100; colopnameformata15; SELECTse.sid, opname, TRUNC(sofar/totalwork*100,2)pct_work, elapsed_secondselapsed, ROUND(elapsed_seconds*(totalwork-sofar)/sofar)remain_time, sql_fulltext FROMv$session_longopssl,v$sqlareasa,v$sessionse WHEREsl.sql_hash_value=sa.hash_value ANDsl.sid=se.sid ANDsofar!=totalwork ORDERBYstart_time;