1. Database Reference
  2. Dynamic Performance Views
  3. Dynamic Performance (V$) Views: V$SCHEDULER_IN_MEMORY_TRACE to V$ZONEMAP_USAGE_STATS
  4. V$SERVICE_DRAIN_TIMEOUT_ADVICE

10.9 V$SERVICE_DRAIN_TIMEOUT_ADVICE

V$SERVICE_DRAIN_TIMEOUT_ADVICE displays information about connection draining.

This view allows you monitor the performance of the draining process, so you can make informed decisions about tuning the drain timeout configuration.

Column Datatype Description

SERVICE_ID

NUMBER

Service ID

DRAIN_ORIGIN

VARCHAR2(12)

Draining origin

  • DISCONNECT - Sessions are being drained by the ALTER SYSTEM DISCONNECT SESSION statement

  • LOAD BALANCE - Sessions are being drained by Oracle RAC load balancing

  • PDB - Sessions are being drained by a PDB close

  • SERVICE - Sessions are being drained by stopping a service

LAST_DRAIN_TIMEOUT

NUMBER

Number of seconds since the last drain timeout

MARKED_TO_DRAIN

NUMBER

Number of sessions marked to drain

DRAINED

NUMBER

Number of sessions drained (includes session that exited on error)

DRAINED_BY_CLIENT

NUMBER

Number of sessions closed by the client during draining

DRAINED_BY_DATABASE

NUMBER

Number of sessions closed by the database during draining

PLANNED_FAILED_OVER

NUMBER

Number of sessions that failed over during draining

DRAINED_INTERVAL01

NUMBER

Number of sessions drained in time interval 01

(Sessions drained in less than or equal to 1 second)

DRAINED_INTERVAL02

NUMBER

Number of sessions drained in time interval 02

(Sessions drained in less than or equal to 5 seconds)

DRAINED_INTERVAL03

NUMBER

Number of sessions drained in time interval 03

(Sessions drained in less than or equal to 10 seconds)

DRAINED_INTERVAL04

NUMBER

Number of sessions drained in time interval 04

(Sessions drained in less than or equal to 30 seconds)

DRAINED_INTERVAL05

NUMBER

Number of sessions drained in time interval 05

Sessions drained in less than or equal to 60 seconds)

DRAINED_INTERVAL06

NUMBER

Number of sessions drained in time interval 06

(Sessions drained in less than or equal to 120 seconds)

DRAINED_INTERVAL07

NUMBER

Number of sessions drained in time interval 07

(Sessions drained in less than or equal to 180 seconds)

DRAINED_INTERVAL08

NUMBER

Number of sessions drained in time interval 08

(Sessions drained in less than or equal to 300 seconds)

DRAINED_INTERVAL09

NUMBER

Number of sessions drained in time interval 09

(Sessions drained in less than or equal to 600 seconds)

DRAINED_INTERVAL10

NUMBER

Number of sessions drained in time interval 10

(Sessions drained in less than or equal to 1200 seconds)

DRAINED_INTERVAL11

NUMBER

Number of sessions drained in time interval 11

(Sessions drained in less than or equal to 3600 seconds (1 hour))

DRAINED_INTERVAL12

NUMBER

Number of sessions drained in time interval 12

(Sessions drained in less than or equal to 7200 seconds (2 hours))

DRAINED_INTERVAL13

NUMBER

Number of sessions drained in time interval 13

(Sessions drained in less than or equal to 14400 seconds (4 hours))

DRAINED_INTERVAL14

NUMBER

Number of sessions drained in time interval 14

(Sessions drained in greater than 14400 seconds (4 hours))

DRAINED_INTERVAL15

NUMBER

Reserved for internal use

DRAINED_INTERVAL16

NUMBER

Reserved for internal use

TERMINATED_BY_DRAIN_TIMEOUT

NUMBER

Number of sessions terminated at drain timeout

IDLE_IN_DRAIN

NUMBER

Number of sessions idle throughout drain timeout

ESTD_DRAIN_TIMEOUT_ADVICE

VARCHAR2(128)

Estimated drain time advice. Possible values include:

  • Not enough sessions are draining to compute a drain timeout estimation

  • Waiting for 90% of drained sessions in order to compute a drain timeout advice.

  • Estimated drain timeout is <advice_in_seconds> seconds

    Where <advice_in_seconds> is determined by using the number of drained sessions in each time interval

CON_ID

NUMBER

The ID of the container to which the data pertains. Possible values include:

  • 0: This value is used for rows containing data that pertain to the entire CDB. This value is also used for rows in non-CDBs.

  • 1: This value is used for rows containing data that pertain to only the root

  • n: Where n is the applicable container ID for the rows containing data

Note:

This view is available starting with Oracle Database 23ai.