转 v$session_longops视图

转http://www.dbdream.com.cn/2013/10/14/vsession_longops%E8%A7%86%E5%9B%BE/

 

有的时间，我们想监控 运行超过4个小时的SQL

如果是10g 版本的库，可以用  v$session_longops ，如果11g的库，推荐使用  v$sql_monitor 监视正在运行的SQL语句的统计信息，具体介绍见下文

另外使用  v$sql_monitor 也可以用来调优使用，比如查找长时间运行的SQL.具体介绍见下文

：

1.有的时候不准确 ，我看到 session wait event 一直在变化 ，也是 active 的 ，运行 时间也是48小时 以上 。但是 在  v$session_longops 显示为totalwork =0 , 无法观察 。是一条3000w 记录的update. 所以只能看v$transaction.

 

2.

对大部分DBA来说，V$SESSION_LONGOPS视图都不会陌生，以前在面试的时候，也有一些企业会问到如何查询数据库中运行时间比较长的SQL，就可以通过这个视图来查看。

V$SESSION_LONGOPS视图不但可以监控运行式时间比较长的SQL，也会记录RMAN备份、EXP/EXPDP、收集统计信息、排序等操作，基本数据库所有运行时间超过6秒的SQL都会记录在这个视图中，也有的DBA会定期检查这个视图来寻找可优化的SQL。

下面是这个视图的结构：

01 SQL> desc v$session_longops
02  名称                                      是否为空? 类型                 注释
03  ----------------------------------------- -------- ------------------    ------------------
04  SID                                                NUMBER                和V$SESSION中的SID一样
05  SERIAL#                                            NUMBER                和V$SESSION中的SERIAL#一样
06  OPNAME                                             VARCHAR2(64)          操作的名称，如全表扫描
07  TARGET                                             VARCHAR2(64)          被操作的对象名，如表名
08  TARGET_DESC                                        VARCHAR2(32)          TARGET的描述
09  SOFAR                                              NUMBER                以完成的数量，如扫描多少数据块
10  TOTALWORK                                          NUMBER                一共需要完成的数量
11  UNITS                                              VARCHAR2(32)          计量单位
12  START_TIME                                         DATE                  开始时间
13  LAST_UPDATE_TIME                                   DATE                  最后一次调用set_session_longops的时间
14  TIMESTAMP                                          DATE                  特定操作的时间戳
15  TIME_REMAINING                                     NUMBER                预计剩余时间，单位秒
16  ELAPSED_SECONDS                                    NUMBER                开始操作到最后更新的时间
17  CONTEXT                                            NUMBER               
18  MESSAGE                                            VARCHAR2(512)         对操作的描述
19  USERNAME                                           VARCHAR2(30)          操作用户的名字
20  SQL_ADDRESS                                        RAW(4)                用于关联V$SQL等视图
21  SQL_HASH_VALUE                                     NUMBER                用于关联V$SQL等视图
22  SQL_ID                                             VARCHAR2(13)          用于关联V$SQL等视图
23  SQL_PLAN_HASH_VALUE                                NUMBER                用于关联V$SQL等视图
24  SQL_EXEC_START                                     DATE                  SQL开始运行的时间
25  SQL_EXEC_ID                                        NUMBER                SQL执行的标识符
26  SQL_PLAN_LINE_ID                                   NUMBER                SQL执行计划相关
27  SQL_PLAN_OPERATION                                 VARCHAR2(30)          SQL执行计划相关
28  SQL_PLAN_OPTIONS                                   VARCHAR2(30)          SQL执行计划相关
29  QCSID                                              NUMBER                并行查询

下面简单做几个超过6秒的操作，来查看下这个视图。

先测试下insert操作。

01 SQL> create table longops_test as select * from dba_objects;
02 表已创建。
03 SQL> insert into longops_test select * from longops_test;
04 已创建65525行。
05 SQL> /
06 已创建131050行。
07 SQL> /
08 已创建262100行。
09 SQL> /
10 已创建524200行。
11 SQL> /
12 已创建1048400行。
13 SQL> commit;
14 提交完成。

现在INSERT操作已经超过6秒，查询下V$SESSION_LONGOPS视图。

1 SID SERIAL#  OPNAME    TARGET           SOFAR TOTALWORK UNITS  MESSAGE         SQL_PLAN_OPERATION SQL_PLAN_OPTIONS
2 --- ------- --------- ---------------- ----- --------  -----  --------------- ------------------- ---------------
3 194 12       Table Scan SYS.LONGOPS_TEST    14895 14895   Blocks Table Scan:     TABLE ACCESS        FULL
4                                                               SYS.LONGOPS_TEST:
5                                                               14895 out of
6                                                               14895 Blocks done

这是已经运行结束的INSERT操作，可见SOFAR和TOTALWORK的值是一样的，在看下没运行完的SQL。

1 SQL> create table longops_ctas as select * from longops_test;

在SQL执行6秒后(还没运行完)时，看下V$SESSION_LONGOPS视图的状态。

01 SID SERIAL#  OPNAME    TARGET           SOFAR TOTALWORK UNITS  MESSAGE         SQL_PLAN_OPERATION SQL_PLAN_OPTIONS
02 --- ------- --------- ---------------- ----- --------  -----  --------------- ------------------- ---------------
03 194 12       Table Scan SYS.LONGOPS_TEST    14895 14895   Blocks Table Scan:     TABLE ACCESS        FULL
04                                                               SYS.LONGOPS_TEST:
05                                                               14895 out of
06                                                               14895 Blocks done
07 194 12  Table Scan SYS.LONGOPS_TEST 13275 29785     Blocks Table Scan:     TABLE ACCESS        FULL
08                                                               SYS.LONGOPS_TEST:
09                                                               13275 out of
10                                                               29785 Blocks done

可见，当前扫描了13275个数据块，总共需要扫描29785个数据块。RMAN备份和收集统计信息也同样会被这个视图记录。

01 OPNAME                                 SOFAR  TOTALWORK MESSAGE
02 --------------------------------- ---------- ---------- -------------------------------------------
03 Gather Table Partition Statistics          1          1 Gather Table Partition Statistics: Table
04                                                         WR H$_OSSTAT : 1 out of 1 Partitions done
05 RMAN: aggregate input               1181953 1181953 RMAN: aggregate input: backup 33: 1181953
06                                                         out of 1181953 Blocks done 
07 RMAN: aggregate input               1181953 1181953 RMAN: aggregate input: backup 33: 1181953
08                                                         out of 1181953 Blocks done 
09 RMAN: aggregate output                359461      359461 RMAN: aggregate output: backup 33: 359461
10                                                          out of 359461 Blocks done 
11 RMAN: full datafile backup          1181280    1181280 RMAN: full datafile backup: Set Count 18:
12                                                         1181280 out of 1181280 Blocks done

下面是ORACLE官方文档对V$SESSION_LONGOPS视图的介绍：

01 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 release.
02  
03 To monitor query execution progress, you must be using the cost-based optimizer and you must:
04  
05 •Set the TIMED_STATISTICS or SQL_TRACE parameters to true
06  
07 •Gather statistics for your objects with the DBMS_STATS package
08  
09 You can add information to this view about application-specific long-running operations by using the DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS procedure.
10 Column              Datatype   Description
11 ------------------- ---------- ---------------------------------------------------------------------------------
12 SID                 NUMBER     Identifier of the session processing the long-running operation. If multiple sessi
13                                ons are cooperating in the long-running operation, then SID corresponds to the mai
14                                n or master session.
15 SERIAL#             NUMBER     Serial number of the session processing the long-running operation. If multiple se
16                                ssions are cooperating in the long-running operation, then SERIAL# corresponds to
17                                the main or master session. SERIAL# is used to uniquely identify a session's objec
18                                ts. Guarantees that session-level commands are applied to the correct session obje
19                                cts if the session ends and another session begins with the same session ID.
20 OPNAME             VARCHAR2(64) Brief description of the operation
21 TARGET             VARCHAR2(64) Object on which the operation is carried out
22 TARGET_DESC        VARCHAR2(32) Description of the target
23 SOFAR              NUMBER       Units of work done so far
24 TOTALWORK          NUMBER       Total units of work
25 UNITS              VARCHAR2(32) Units of measurement
26 START_TIME         DATE         Starting time of the operation
27 LAST_UPDATE_TIME   DATE         Time when statistics were last updated for the operation
28 TIMESTAMP          DATE         Timestamp specific to the operation
29 TIME_REMAINING     NUMBER       Estimate (in seconds) of time remaining for the operation to complete
30 ELAPSED_SECONDS    NUMBER       Number of elapsed seconds from the start of the operations
31 CONTEXT            NUMBER       Context
32 MESSAGE           VARCHAR2(512) Statistics summary message
33 USERNAME           VARCHAR2(30) User ID of the user performing the operation
34 SQL_ADDRESS        RAW(4 | 8)   Used with the value of the SQL_HASH_VALUE column to identify the SQL statement as
35                                 sociated with the operation
36 SQL_HASH_VALUE     NUMBER       Used with the value of the SQL_ADDRESS column to identify the SQL statement assoc
37                                 iated with the operation
38 SQL_ID             VARCHAR2(13) SQL identifier of the SQL statement associated with the long operation, if any
39 SQL_PLAN_HASH_VALUE NUMBER      SQL plan hash value; NULL if SQL_ID is NULL
40 SQL_EXEC_START     DATE         Time when the execution of the SQL started; NULL if SQL_ID is NULL
41 SQL_EXEC_ID         NUMBER      SQL execution identifier (see V$SQL_MONITOR)
42 SQL_PLAN_LINE_ID    NUMBER      SQL plan line ID corresponding to the long operation; NULL if the long operation
43                                 is not associated with a line of the execution plan
44 SQL_PLAN_OPERATION VARCHAR2(30) Plan operation name; NULL if SQL_PLAN_LINE_ID is NULL
45 SQL_PLAN_OPTIONS   VARCHAR2(30) Plan operation options; NULL if SQL_PLAN_LINE_ID is NULL
46 QCSID               NUMBER      Session identifier of the parallel coordinator

 

 

########sample   V$SQL_MONITOR

https://www.cnblogs.com/andy6/p/7507195.html

https://docs.oracle.com/cd/B28359_01/server.111/b28320/dynviews_3048.htm#REFRN30479

https://oracle-base.com/articles/11g/real-time-sql-monitoring-11gr1

 

 

具体监控语句如下：

###for   V$SESSION_LONGOPS

select * from ( \
select \
lops.SQL_ID||' elapsed time '||lops.ELAPSED_SECONDS \
from V$SESSION_LONGOPS lops \
where lops.SQL_ID is not null and username <> 'SYS' \
and ELAPSED_SECONDS >= 60*60*4 and time_remaining>0 \
group by lops.SQL_ID,USERNAME, ELAPSED_SECONDS \
order by ELAPSED_SECONDS desc \
) where rownum <= 5

 

##for   gv$sql_monito

select '- sql_id= ->',sql_id,'- username= ->',username, \
'- sql_exec_start= ->',to_char(sql_exec_start,'yyyy-mm-dd hh24:mi:ss') sql_exec_start, \
'- LAST_REFRESH_TIME= ->',to_char(LAST_REFRESH_TIME,'yyyy-mm-dd hh24:mi:ss') LAST_REFRESH_TIME, \
'- FIRST_REFRESH_TIME= ->',to_char(FIRST_REFRESH_TIME,'yyyy-mm-dd hh24:mi:ss') FIRST_REFRESH_TIME, \
'- STATUS= ->',STATUS,'- sid= ->',SID,'- PROGRAM= ->',PROGRAM,'- USERNAME= ->',USERNAME \
,'- Duration_seconds= ->',(LAST_REFRESH_TIME-FIRST_REFRESH_TIME)* 24 * 60 * 60 as Duration_seconds \
from gv$sql_monitor \
where username not in ('SYS','SYSTEM') \
and status='EXECUTING' \
and (LAST_REFRESH_TIME-FIRST_REFRESH_TIME)* 24 * 60 * 60 > 7200

 

 

11g中引入了新的动态性能视图V$SQL_MONITOR，该视图用以显示Oracle监视的SQL语句信息。SQL监视会对那些并行执行或者消耗5秒以上cpu时间或I/O时间的SQL语句自动启动，同时在V$SQL_MONITOR视图中产生一条记录。当SQL语句正在执行，V$SQL_MONITOR视图中的统计信息将被实时刷新，频率为每秒1次。SQL语句执行完成后，监视信息将不会被立即删除，Oracle会保证相关记录保存一分钟(由参数_sqlmon_recycle_time所控制，默认为60s)，最终这些记录都会被删除并被重用。这一新的SQL性能监视特性仅在CONTROL_MANAGEMENT_PACK_ACCESS为DIAGNOSTIC+TUNING和STATISTICS_LEVEL为ALL|TYPICAL时被启用。

v$sql_monitor视图包含当前正在运行的SQL语句，以及最近运行的SQL语句。

使用v$sql_monitor视图中所监控的SQL语句时需在满足以下条件：
1） 自动监控任何并行语句
2） 如果一个SQL语句消耗了超过5秒的CPU或I/O时间，则会自动监控
3） 监控任何在语句级启用监控的SQL语句（使用monitor或no_monitor）

提示：结合v$sql_monitor视图与v$sql_plan_monitor视图可以进一步查询SQL的执行计划等信息。联合一些其他视图，如v$active_session_history、v$session、v$session_longops、v$sql、v$sql_plan等，可以获得关于SQL的更多信息。

注意：为了进行SQL监控，初始化参数STATISTICS_LEVEL必须设置为TYPICAL或ALL，CONTROL_MANAGEMENT_PACK_ACCESS必须设置为DIAGNOSTIC+TUNING。

SQL> show parameter STATISTICS_LEVE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL

SQL> show parameter CONTROL_MANAGEMENT_PACK_ACCESS
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access string DIAGNOSTIC+TUNING

1、 如查看数据库中消耗CPU资源排名前10位的查询：

select * from (
select sql_id,username,to_char(sql_exec_start,'yyyy-mm-dd hh24:mi:ss') sql_exec_start,
sql_exec_id,sum(buffer_gets) buffer_gets,
sum(disk_reads) disk_reads,round(sum(cpu_time/1000000),1) cpu_secs
from v$sql_monitor
where username not in ('SYS','SYSTEM')
group by sql_id,username,sql_exec_start,sql_exec_id
order by 7 desc)
where rownum<=10;


SQL_ID USERNAME SQL_EXEC_START SQL_EXEC_ID BUFFER_GETS DISK_READS CPU_SECS
------------- ------------------------------ ------------------- ----------- ----------- ---------- ----------
8p8ann3c68vxc SCOTT 2016-11-24 09:50:20 16777216 0 0 37.1


2、监控指定的SQL语句：
2.1、 Hint提示监控指定的SQL语句
select /*+ monitor */e.empno,e.ename,e.sal,d.dname from scott.emp e,scott.dept d where e.deptno=d.deptno;

2.2、v$sql_monitor视图中查看被监控的SQL语句消耗的资源信息：

select sql_id,username,to_char(sql_exec_start,'yyyy-mm-dd hh24:mi:ss') sql_exec_start,
sql_exec_id,sum(buffer_gets) buffer_gets,
sum(disk_reads)disk_reads,round(sum(cpu_time/1000000),1) cpu_secs
from v$sql_monitor
where sql_text like '%monitor%'
group by sql_id,username,sql_exec_start,sql_exec_id;

2.3、 然后可以使用DBMS_SQLTUNE包来查看SQL语句生成的监控信息（如执行计划等）：
SQL> 
set long 10000000
set longchunksize 10000000
set linesize 200
select dbms_sqltune.report_sql_monitor from dual;

 

posted @ 2017-12-17 18:13  feiyun8616  阅读(573)  评论(0)  编辑  收藏  举报