V $ session

zhaozj2021-02-16  103

Monitor large operations with V $ session_longops

Author: Lunar

We can use the V $ SESSION_LONGOPS view to monitor any operational operations (DDL and DML). Before using it, you must meet two conditions:

1. Set Timed_statistics or SQL_TRACE,

2. Because this feature can only be applied to cost-based optimizer, statistics must be present

The same functionality can also be done using DBMS_Application_info.set_session_longops provided by Oracle 9i.

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 completion

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 color associated with the Operation of THE OFECN

SQL_HASH_VALUE

Numer

Used with the value of the sql_address column to id11 the sql statement associated with the operation

QCSID

Number

Session Identifier of The Parallel Coordinator

The following script will display a status information indicating that the DDL operation has been used for a long run time.

SELECT SID, MESSAGE

From v $ session_longops

WHERE SID = & SID

ORDER by start_time;

Example 1 - Surveillance Creation Large Index (DDL)

Microsoft Windows 2000 [Version 5.00.2195]

(C) All rights reserved 1985-2000 Microsoft Corp.

C: /> SQLPLUS LUNAR / LUNAR @ Lunarsql * Plus: Release 9.2.0.1.0 - Production on Tuesday 15 16:32:25 2004

CopyRight (C) 1982, 2002, Oracle Corporation. All Rights Reserved.

Connected to:

Oracle9i

Enterprise

Edition Release 9.2.0.1.0 - Production

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);

Index has been created.

Time: 00: 03: 08.00

17:20:19 SQL>

In another session:

SQL> Conn Lunar / Lunar @ Lunar

connected.

SQL> Analyze Table TiStimate Statistics Sample 1 Percent;

The table has been analyzed.

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 block done

9 Sort Output:: 9482 out of 9482 block done

Time: 00: 00: 00.00

SQL>

Example 2 - Monitor full mete scan

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 is "Lunar"

SQL>

SQL> SELECT AVG_SPACE from User_Tables

2 where Table_name = 't';

AVG_SPACE

------------

867 Description has statistics

SQL> Show parameter timed_stat

Name Type Value

----------------------------------- --- -----------

TIMED_STATISTICS BOOLEAN TRUESQL> SELECT * FROM T;

3209,728 is selected.

Time: 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 Get

250492 CONSISTENT GETS

39165 Physical READS

0 redo size

187470467 BYTES SENT VIA SQL * NET to Client

2354294 BYtes Received Via SQL * NET from Clom Clism

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) All rights reserved 1985-2000 Microsoft Corp.

C: /> SQLPLUS "/ @ lunar as sysdba"

SQL * Plus: Release 9.2.0.1.0 - Production on Tuesday, Tuesday 15 17:57:04 2004

CopyRight (C) 1982, 2002, Oracle Corporation. All Rights Reserved.

Connected to:

Oracle9i

Enterprise

Edition Release 9.2.0.1.0 - Production

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 block done

9 Sort Output:: 9482 out of 9482 block done

9 Table Scan: Lunar.t: 39173 Out of 39173 Blocks Done9 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 lines have been 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 2.7039264289179

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 1009 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 1009 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 35714 Rows SELECTED

SQL>

转载请注明原文地址:https://www.9cbs.com/read-11209.html

New Post(0)