ITPub博客

首页 > 数据库 > 数据库开发技术 > 监控数据库执行超过6s的操作

监控数据库执行超过6s的操作

原创 数据库开发技术 作者:foreverlee 时间:2006-09-18 12:20:34 0 删除 编辑

在日常工作中,经常需要了解一个命令发出去,Oracle执行的进度,这里提供了一个很实用的脚本,专门用来解决这个问题.

监控数据库执行超过超过6s的操作 包括:
1> 监控索引创建过程
2> 监控物化视图刷新
3> 获取当前数据库之行超过6s的SQL

原文发表在:

http://www.itpub.net/633751.html

[@more@]


例子:

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;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/76065/viewspace-865770/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-11-26

  • 博文量
    72
  • 访问量
    1357346