監控資料庫執行超過6s的操作

例子:

session A中執行一個超過6s的查詢
SQL>select l.owner,l.object_name,t.id from large_table l,tiny_table t where t.owner=l.owner;

181507036 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13123 Card=4352863 B
ytes=191525972)

1 0 HASH JOIN (Cost=13123 Card=4352863 Bytes=191525972)
2 1 TABLE ACCESS (FULL) OF 'TINY_TABLE' (Cost=22 Card=50212
Bytes=652756)

3 1 TABLE ACCESS (FULL) OF 'LARGE_TABLE' (Cost=8502 Card=433
5612 Bytes=134403972)





Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
445990 consistent gets
62937 physical reads
0 redo size
SP2-0642: SQL*Plus internal error state 1075, context 1:5:0
Unsafe to proceed
133105814 bytes received via SQL*Net from client
12100471 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
181507036 rows processed


session B中做監控

SQL> @monitor_process.sql

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Table Scan 4606 56009 8.22% select l.owner,l.object_name,t.id from large_
12 Table Scan 4606 56009 8.22% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Table Scan 6020 56009 10.74% select l.owner,l.object_name,t.id from large_
12 Table Scan 6020 56009 10.74% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Table Scan 6849 56009 12.22% select l.owner,l.object_name,t.id from large_
12 Table Scan 6849 56009 12.22% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Table Scan 7510 56009 13.4% select l.owner,l.object_name,t.id from large_
12 Table Scan 7510 56009 13.4% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Table Scan 8281 56009 14.78% select l.owner,l.object_name,t.id from large_
12 Table Scan 8281 56009 14.78% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Table Scan 9090 56009 16.22% select l.owner,l.object_name,t.id from large_
12 Table Scan 9090 56009 16.22% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Table Scan 9762 56009 17.42% select l.owner,l.object_name,t.id from large_
12 Table Scan 9762 56009 17.42% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Table Scan 10277 56009 18.34% select l.owner,l.object_name,t.id from large_
12 Table Scan 10277 56009 18.34% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Table Scan 11311 56009 20.19% select l.owner,l.object_name,t.id from large_
12 Table Scan 11311 56009 20.19% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Table Scan 12621 56009 22.53% select l.owner,l.object_name,t.id from large_
12 Table Scan 12621 56009 22.53% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Table Scan 13267 56009 23.68% select l.owner,l.object_name,t.id from large_
12 Table Scan 13267 56009 23.68% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Table Scan 14537 56009 25.95% select l.owner,l.object_name,t.id from large_
12 Table Scan 14537 56009 25.95% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Table Scan 18256 56009 32.59% select l.owner,l.object_name,t.id from large_
12 Table Scan 18256 56009 32.59% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Table Scan 18741 56009 33.46% select l.owner,l.object_name,t.id from large_
12 Table Scan 18741 56009 33.46% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Table Scan 19146 56009 34.18% select l.owner,l.object_name,t.id from large_
12 Table Scan 19146 56009 34.18% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Table Scan 19537 56009 34.88% select l.owner,l.object_name,t.id from large_
12 Table Scan 19537 56009 34.88% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Table Scan 20964 56009 37.42% select l.owner,l.object_name,t.id from large_
12 Table Scan 20964 56009 37.42% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Table Scan 21972 56009 39.22% select l.owner,l.object_name,t.id from large_
12 Table Scan 21972 56009 39.22% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Table Scan 33109 56009 59.11% select l.owner,l.object_name,t.id from large_
12 Table Scan 33109 56009 59.11% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Table Scan 34110 56009 60.9% select l.owner,l.object_name,t.id from large_
12 Table Scan 34110 56009 60.9% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Table Scan 34940 56009 62.38% select l.owner,l.object_name,t.id from large_
12 Table Scan 34940 56009 62.38% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Table Scan 35521 56009 63.42% select l.owner,l.object_name,t.id from large_
12 Table Scan 35521 56009 63.42% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Table Scan 36169 56009 64.57% select l.owner,l.object_name,t.id from large_
12 Table Scan 36169 56009 64.57% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Table Scan 36631 56009 65.4% select l.owner,l.object_name,t.id from large_
12 Table Scan 36631 56009 65.4% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Table Scan 37486 56009 66.92% select l.owner,l.object_name,t.id from large_
12 Table Scan 37486 56009 66.92% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Table Scan 38067 56009 67.96% select l.owner,l.object_name,t.id from large_
12 Table Scan 38067 56009 67.96% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Table Scan 38518 56009 68.77% select l.owner,l.object_name,t.id from large_
12 Table Scan 38518 56009 68.77% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Table Scan 44374 56009 79.22% select l.owner,l.object_name,t.id from large_
12 Table Scan 44374 56009 79.22% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Table Scan 46272 56009 82.61% select l.owner,l.object_name,t.id from large_
12 Table Scan 46272 56009 82.61% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Table Scan 47538 56009 84.87% select l.owner,l.object_name,t.id from large_
12 Table Scan 47538 56009 84.87% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Table Scan 48258 56009 86.16% select l.owner,l.object_name,t.id from large_
12 Table Scan 48258 56009 86.16% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Table Scan 48837 56009 87.19% select l.owner,l.object_name,t.id from large_
12 Table Scan 48837 56009 87.19% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Table Scan 49338 56009 88.08% select l.owner,l.object_name,t.id from large_
12 Table Scan 49338 56009 88.08% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Table Scan 49847 56009 88.99% select l.owner,l.object_name,t.id from large_
12 Table Scan 49847 56009 88.99% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Table Scan 51552 56009 92.04% select l.owner,l.object_name,t.id from large_
12 Table Scan 51552 56009 92.04% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Table Scan 52146 56009 93.1% select l.owner,l.object_name,t.id from large_
12 Table Scan 52146 56009 93.1% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Table Scan 52851 56009 94.36% select l.owner,l.object_name,t.id from large_
12 Table Scan 52851 56009 94.36% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Table Scan 53442 56009 95.41% select l.owner,l.object_name,t.id from large_
12 Table Scan 53442 56009 95.41% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Table Scan 54030 56009 96.46% select l.owner,l.object_name,t.id from large_
12 Table Scan 54030 56009 96.46% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Table Scan 54894 56009 98% select l.owner,l.object_name,t.id from large_
12 Table Scan 54894 56009 98% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Table Scan 55758 56009 99.55% select l.owner,l.object_name,t.id from large_
12 Table Scan 55758 56009 99.55% t where t.owner=l.owner

SQL> /

no rows selected

SQL> /

no rows selected

SQL> /

no rows selected

SQL> /

no rows selected

SQL> /

no rows selected

SQL> /

no rows selected

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Hash Join 98 13818 .7% select l.owner,l.object_name,t.id from large_
12 Hash Join 98 13818 .7% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Hash Join 112 13818 .81% select l.owner,l.object_name,t.id from large_
12 Hash Join 112 13818 .81% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Hash Join 119 13818 .86% select l.owner,l.object_name,t.id from large_
12 Hash Join 119 13818 .86% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Hash Join 126 13818 .91% select l.owner,l.object_name,t.id from large_
12 Hash Join 126 13818 .91% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Hash Join 154 13818 1.11% select l.owner,l.object_name,t.id from large_
12 Hash Join 154 13818 1.11% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Hash Join 245 13818 1.77% select l.owner,l.object_name,t.id from large_
12 Hash Join 245 13818 1.77% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Hash Join 434 13818 3.14% select l.owner,l.object_name,t.id from large_
12 Hash Join 434 13818 3.14% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Hash Join 1372 13818 9.92% select l.owner,l.object_name,t.id from large_
12 Hash Join 1372 13818 9.92% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Hash Join 1379 13818 9.97% select l.owner,l.object_name,t.id from large_
12 Hash Join 1379 13818 9.97% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Hash Join 1554 13818 11.24% select l.owner,l.object_name,t.id from large_
12 Hash Join 1554 13818 11.24% t where t.owner=l.owner


SQL> l
1 select sql_info.sid,
2 sql_info.opname,
3 sql_info.sofar,
4 sql_info.totalwork,
5 sql_info.perwork,
6 sqltext.sql_text
7 from
8 (select opsinfo.sid,
9 opsinfo.opname,
10 opsinfo.sofar,
11 opsinfo.totalwork,
12 opsinfo.perwork,
13 v.sql_hash_value
14 from
15 (
16 select ops.sid,
17 ops.opname,
18 ops.sofar,
19 ops.totalwork,
20 trunc(ops.sofar/ops.totalwork*100,2)||'%' as perwork
21 from v$session_longops ops
22 where ops.sofar!=ops.totalwork) opsinfo,
23 v$session v
24 where opsinfo.sid = v.sid) sql_info,
25 v$sqltext sqltext
26 where sqltext.hash_value = sql_info.sql_hash_value
27* order by sql_info.sid,sqltext.piece asc
SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Hash Join 2695 13818 19.5% select l.owner,l.object_name,t.id from large_
12 Hash Join 2695 13818 19.5% t where t.owner=l.owner

SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Hash Join 6468 13818 46.8% select l.owner,l.object_name,t.id from large_
12 Hash Join 6468 13818 46.8% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Hash Join 7287 13818 52.73% select l.owner,l.object_name,t.id from large_
12 Hash Join 7287 13818 52.73% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Hash Join 7553 13818 54.66% select l.owner,l.object_name,t.id from large_
12 Hash Join 7553 13818 54.66% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Hash Join 7728 13818 55.92% select l.owner,l.object_name,t.id from large_
12 Hash Join 7728 13818 55.92% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Hash Join 7742 13818 56.02% select l.owner,l.object_name,t.id from large_
12 Hash Join 7742 13818 56.02% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Hash Join 7847 13818 56.78% select l.owner,l.object_name,t.id from large_
12 Hash Join 7847 13818 56.78% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Hash Join 7896 13818 57.14% select l.owner,l.object_name,t.id from large_
12 Hash Join 7896 13818 57.14% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Hash Join 8071 13818 58.4% select l.owner,l.object_name,t.id from large_
12 Hash Join 8071 13818 58.4% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Hash Join 8092 13818 58.56% select l.owner,l.object_name,t.id from large_
12 Hash Join 8092 13818 58.56% t where t.owner=l.owner

SQL>
SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Hash Join 8169 13818 59.11% select l.owner,l.object_name,t.id from large_
12 Hash Join 8169 13818 59.11% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Hash Join 8302 13818 60.08% select l.owner,l.object_name,t.id from large_
12 Hash Join 8302 13818 60.08% t where t.owner=l.owner


SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Hash Join 8778 13818 63.52% select l.owner,l.object_name,t.id from large_
12 Hash Join 8778 13818 63.52% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Hash Join 8855 13818 64.08% select l.owner,l.object_name,t.id from large_
12 Hash Join 8855 13818 64.08% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Hash Join 8911 13818 64.48% select l.owner,l.object_name,t.id from large_
12 Hash Join 8911 13818 64.48% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Hash Join 8918 13818 64.53% select l.owner,l.object_name,t.id from large_
12 Hash Join 8918 13818 64.53% t where t.owner=l.owner

SQL> /

SID OPNAME SOFAR TOTALWORK PERWORK SQL_TEXT
---- ------------------------ ----------- ----------- -------- ---------------------------------------------
12 Hash Join 8918 13818 64.53% select l.owner,l.object_name,t.id from large_
12 Hash Join 8918 13818 64.53% t where t.owner=l.owner
可以看到對於Hash Join Oracle內部執行順序:




借用老和尚的v$session_longops動態效能檢視簡要說明:

v$session_longops動態效能檢視可以檢視執行時間大於6秒的查詢，如果想讓執行時間大於6秒的查詢被資料庫引擎收集，必須滿足以下條件：

1 引數timed_statistics或sql_trace為true
2 涉及的物件（表或索引）必須被分析（analyze或dbms_stats）

在v$session_longops檢視中，sofar欄位表示已經掃描的塊數，totalwork表示總得需要掃描的塊數

指令碼:

set linesize 200
col sid for 999
col opname for a24 trunc
col perwork for a8
col sofar for 9999999999
col totalwork for 9999999999
col sql_text for a45
set lines 131
/*
Author : LiYong
Date : 2006-09-15
Usage : 監控資料庫執行超過超過6s的操作
1> 監控索引建立過程
2> 監控物化檢視重新整理
3> 獲取當前資料庫之行超過6s的SQL
Notes : 以具有DBA角色的資料庫使用者登入執行該指令碼即可


*/
select sql_info.sid,
sql_info.opname,
sql_info.sofar,
sql_info.totalwork,
sql_info.perwork,
sqltext.sql_text
from
(select opsinfo.sid,
opsinfo.opname,
opsinfo.sofar,
opsinfo.totalwork,
opsinfo.perwork,
v.sql_hash_value
from
(
select ops.sid,
ops.opname,
ops.sofar,
ops.totalwork,
trunc(ops.sofar/ops.totalwork*100,2)||'%' as perwork
from v$session_longops ops
where ops.sofar!=ops.totalwork) opsinfo,
v$session v
where opsinfo.sid = v.sid) sql_info,
v$sqltext sqltext
where sqltext.hash_value = sql_info.sql_hash_value
order by sql_info.sid,sqltext.piece asc;