用v$session_longops监视大型操作
来源:互联网 发布:淘宝客拍a发b 编辑:程序博客网 时间:2024/05/26 19:17
用v$session_longops监视大型操作
作者:Lunar
我们可以使用v$session_longops视图用于监视任何运行时间很长的操作(DDL和DML)。,使用它之前必须满足两个条件:
1.
设置TIMED_STATISTICS
或者SQL_TRACE
,
2.
因为这个功能只能应用于基于成本的优化器,所以必须存在统计数据
与之相同的功能也可以使用Oracle 9i提供的DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS
来完成。
Column Datatype Description
SID
NUMBER
Session identifier
SERIAL#
NUMBER
Session serial number
OPNAME
VARCHAR2(64)
Brief description of the operation
TARGET
VARCHAR2(64)
The object on which the operation is carried out
TARGET_DESC
VARCHAR2(32)
Description of the target
SOFAR
NUMBER
The units of work done so far
TOTALWORK
NUMBER
The total units of work
UNITS
VARCHAR2(32)
The units of measurement
START_TIME
DATE
The starting time of operation
LAST_UPDATE_TIME
DATE
Time when statistics last updated
TIME_REMAINING
NUMBER
Estimate (in seconds) of time remaining for the operation to complete
ELAPSED_SECONDS
NUMBER
The number of elapsed seconds from the start of operations
CONTEXT
NUMBER
Context
MESSAGE
VARCHAR2(512)
Statistics summary message
USERNAME
VARCHAR2(30)
User ID of the user performing the operation
SQL_ADDRESS
RAW(4)
Used with the value of the SQL_HASH_VALUE column to identify the SQL statement associated with the operation
SQL_HASH_VALUE
NUMER
Used with the value of the SQL_ADDRESS column to identify the SQL statement associated with the operation
QCSID
NUMBER
Session identifier of the parallel coordinator
下面的脚本将显示一个状态信息,说明了运行时间很长的DDL操作已经使用的时间。
Select sid, message
From v$session_longops
Where sid = &sid
order by start_time;
实例1——监视创建大索引(DDL)
Microsoft Windows 2000 [Version 5.00.2195]
(C) 版权所有 1985-2000 Microsoft Corp.
C:/>sqlplus lunar/lunar@lunar
SQL*Plus: Release 9.2.0.1.0 - Production on 星期二 6月 15 16:32:25 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
连接到:
Oracle9i
With the OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> select distinct sid from v$mystat;
SID
----------
9
SQL> set time on
17:16:51 SQL> create index idx_t on t(OBJECT_ID);
索引已创建。
已用时间: 00: 03: 08.00
17:20:19 SQL>
在另一个session中:
SQL> conn lunar/lunar@lunar
已连接。
SQL> analyze table t ESTIMATE STATISTICS sample 1 PERCENT;
表已分析。
SQL> set linesize 1000
SQL> column message format a70
SQL> Select sid, message
2 From v$session_longops
3 Where sid = 9
4 order by start_time
5 /
SID MESSAGE
---------- --------------------------------------------------------
9 Table Scan: LUNAR.T: 19537 out of 19537 Blocks done
9 SQL Execution: : 1881 out of 1881 units done
9 Table Scan: LUNAR.T: 39173 out of 39173 Blocks done
9 Sort/Merge: : 9976 out of 9976 Blocks done
9 Sort Output: : 9482 out of 9482 Blocks done
已用时间: 00: 00: 00.00
SQL>
实例2——监视全表扫描
SELECT sid, serial#, opname,
TO_CHAR(start_time,'HH24:MI:SS') AS start_time,
(sofar/totalwork)*100 AS PERCENT_COMPLETE,
ELAPSED_SECONDS
FROM v$session_longops;
SQL> SHOW USER
USER 为"LUNAR"
SQL>
SQL> select avg_space from user_tables
2 where table_name='T';
AVG_SPACE
----------
867 说明有统计数据
SQL> show parameter timed_stat
NAME TYPE VALUE
------------------------------------ ----------- --------------
timed_statistics boolean TRUE
SQL> select * from t;
已选择3209728行。
已用时间: 00: 05: 38.06
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1881 Card=1605276 By
tes=120395700)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=1881 Card=1605276 Bytes=1
20395700)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
250492 consistent gets
39165 physical reads
0 redo size
187470467 bytes sent via SQL*Net to client
2354294 bytes received via SQL*Net from client
213983 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3209728 rows processed
SQL>
Microsoft Windows 2000 [Version 5.00.2195]
(C) 版权所有 1985-2000 Microsoft Corp.
C:/>sqlplus "/@lunar as sysdba"
SQL*Plus: Release 9.2.0.1.0 - Production on 星期二 6月 15 17:57:04 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
连接到:
Oracle9i
With the OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> set linesize 1000
SQL> column message format a70
SQL> Select sid, message
2 From v$session_longops
3 Where sid = 9
4 order by start_time
5 /
SID MESSAGE
---------- ----------------------------------------------------------------------
9 Table Scan: LUNAR.T: 19537 out of 19537 Blocks done
9 SQL Execution: : 1881 out of 1881 units done
9 Table Scan: LUNAR.T: 39173 out of 39173 Blocks done
9 Sort/Merge: : 9976 out of 9976 Blocks done
9 Sort Output: : 9482 out of 9482 Blocks done
9 Table Scan: LUNAR.T: 39173 out of 39173 Blocks done
9 SQL Execution: : 1881 out of 1881 units done
9 Table Scan: LUNAR.T: 39173 out of 39173 Blocks done
9 SQL Execution: : 1881 out of 1881 units done
已选择9行。
SQL> /
SID SERIAL# OPNAME START_TIME PERCENT_COMPLETE
---------- ---------- -------------------------- ---------- ----------------
10 86 Import Schema Statistics 17:03:44 100
10 86 Delete Schema Statistics 17:04:02 100
9 167 Table Scan 17:05:21 100
9 167 SQL Execution 17:05:21 100
9 167 Table Scan 17:17:11 100
9 167 Sort/Merge 17:18:10 100
9 167 Sort Output 17:18:56 100
9 167 Table Scan 17:28:19 100
9 167 SQL Execution 17:28:19 100
9 167 Table Scan 17:28:52 100
9 167 SQL Execution 17:28:52 100
9 182 Table Scan 17:59:14 100
9 182 Table Scan 18:22:02 100
9 182 Table Scan 18:24:17 2.70339264289179
14 rows selected
SQL> /
SID SERIAL# OPNAME START_TIME PERCENT_COMPLETE
---------- ---------- -------------------------- ---------- ----------------
10 86 Import Schema Statistics 17:03:44 100
10 86 Delete Schema Statistics 17:04:02 100
9 167 Table Scan 17:05:21 100
9 167 SQL Execution 17:05:21 100
9 167 Table Scan 17:17:11 100
9 167 Sort/Merge 17:18:10 100
9 167 Sort Output 17:18:56 100
9 167 Table Scan 17:28:19 100
9 167 SQL Execution 17:28:19 100
9 167 Table Scan 17:28:52 100
9 167 SQL Execution 17:28:52 100
9 182 Table Scan 17:59:14 100
9 182 Table Scan 18:22:02 100
9 182 Table Scan 18:24:17 44.0354325683506
14 rows selected
SQL> /
SID SERIAL# OPNAME START_TIME PERCENT_COMPLETE
---------- ---------- -------------------------- ---------- ----------------
10 86 Import Schema Statistics 17:03:44 100
10 86 Delete Schema Statistics 17:04:02 100
9 167 Table Scan 17:05:21 100
9 167 SQL Execution 17:05:21 100
9 167 Table Scan 17:17:11 100
9 167 Sort/Merge 17:18:10 100
9 167 Sort Output 17:18:56 100
9 167 Table Scan 17:28:19 100
9 167 SQL Execution 17:28:19 100
9 167 Table Scan 17:28:52 100
9 167 SQL Execution 17:28:52 100
9 182 Table Scan 17:59:14 100
9 182 Table Scan 18:22:02 100
9 182 Table Scan 18:24:17 70.1860975672019
14 rows selected
SQL> SELECT sid, serial#, opname,
2 TO_CHAR(start_time,'HH24:MI:SS') AS start_time,
3 (sofar/totalwork)*100 AS PERCENT_COMPLETE,
4 ELAPSED_SECONDS
5 FROM v$session_longops;
SID SERIAL# OPNAME START_TIME PERCENT_COMPLETE ELAPSED_SECONDS
---- ---------- ---------------------------- ---------- ---------------- ---------------
10 86 Import Schema Statistics 17:03:44 100 0
10 86 Delete Schema Statistics 17:04:02 100 1
9 167 Table Scan 17:05:21 100 266
9 167 SQL Execution 17:05:21 100 266
9 167 Table Scan 17:17:11 100 59
9 167 Sort/Merge 17:18:10 100 46
9 167 Sort Output 17:18:56 100 82
9 167 Table Scan 17:28:19 100 15
9 167 SQL Execution 17:28:19 100 15
9 167 Table Scan 17:28:52 100 19
9 167 SQL Execution 17:28:52 100 19
9 182 Table Scan 17:59:14 100 336
9 182 Table Scan 18:22:02 100 18
9 182 Table Scan 18:24:17 100 357
14 rows selected
SQL>
- 用v$session_longops监视大型操作
- V$SESSION_LONGOPS
- V$SESSION_LONGOPS
- V$SESSION_LONGOPS
- V$SESSION_LONGOPS
- v$session_longops
- V$SESSION_LONGOPS
- v$session_longops
- v$session_longops
- V$SESSION_LONGOPS
- v$session_longops视图
- V$session_longops解释
- v$session_longops简介
- Oracle技巧:用v$session_longops跟踪DDL语句
- Oracle v$session_longops 视图说明
- Oracle v$session_longops 视图说明
- 对v$session_longops 的理解
- 动态性能视图 V$SESSION_LONGOPS
- 热备期间数据库crash(9201)
- 不对称加密和解密,用C#实现
- 编写高性能J2ME游戏的秘密!!
- 使用创建数据文件的方法修复数据库(9201)
- SORT_AREA_SIZE和SORT_AREA_RETAINED_SIZE的区别
- 用v$session_longops监视大型操作
- 讨论一下DB_FILE_MULTIBLOCK_READ_COUNT参数和区间尺寸的设置问题
- 文件下载时出现提示框不直接打开
- 在UserControl中调用所在页面的ViewState
- 在asp.net中计算叶面执行所耗时间
- 在域控制器中使用默认ASPNET 帐户时ASP.NET 不能正常运行
- 委托(Delegate)
- 如何设置服务器的镜像磁盘
- 无法创建Web项目"WebApplication1