EXPLAIN WORK

Purpose

Use the EXPLAIN WORK statement to obtain an estimate of the work involved in an Oracle Automatic Storage Management (Oracle ASM) disk group rebalance operation.

This statement inserts one row into the V$ASM_ESTIMATE dynamic performance view. The row contains a NUMBER value, EST_WORK, which represents the estimated number of allocation units that must be moved by the rebalance operation.

Prerequisites

To issue an EXPLAIN WORK statement, you must have the privileges necessary to insert rows into the V$ASM_ESTIMATE dynamic performance view.

You must also have the privileges necessary to execute the ALTER DISKGROUP statement for which you are obtaining a work estimate.

To view the work estimate produced by an EXPLAIN WORK statement, you must have the privileges necessary to query the V$ASM_ESTIMATE dynamic performance view.

The EXPLAIN WORK statement is a data manipulation language (DML) statement, rather than a data definition language (DDL) statement. Therefore, Oracle Database does not implicitly commit the changes made by an EXPLAIN WORK statement. If you want to keep the row generated by an EXPLAIN WORK statement in the V$ASM_ESTIMATE view, then you must commit the transaction containing the statement.

Syntax

explain_work::=

Semantics

SET STATEMENT_ID Clause

Specify a value for the STATEMENT_ID column for the row created by this statement in the V$ASM_ESTIMATE view. You can then use this value to identify this row among others in the view. Be sure to specify a STATEMENT_ID value if the view contains rows from many work estimates. If you omit this clause, then the STATEMENT_ID value defaults to null.

FOR statement Clause

Specify the SQL statement for which you want a work estimate. You can specify an ALTER DISKGROUP statement that contains any of the following clauses:

• add_disk_clause (adds one or more disks to a disk group)

• disk_online_clause (brings one or more disks online)

• drop_disk_clause (drops one or more disks from a disk group)

• rebalance_diskgroup_clause (rebalances a disk group)

• replace_disk_clause (replaces one or more disks in a disk group)

• resize_disk_clause (resizes the disks in a disk group)

• undrop_disk_clause (cancels the drop of disks from a disk group)

The EXPLAIN WORK statement estimates the number of allocation units that must be moved during the explicit disk group rebalance that occurs when you issue the rebalance_diskgroup_clause, or the automatic disk group rebalance that occurs when you issue any of the other clauses.

Examples

The following statement estimates the amount of work required to explicitly rebalance disk group dg1. The resulting estimate is inserted into the V$ASM_ESTIMATE view.

EXPLAIN WORK
  FOR ALTER DISKGROUP dg1 REBALANCE;

SELECT est_work FROM V$ASM_ESTIMATE;

EST_WORK
--------
5680

The following statement estimates the amount of work required to automatically rebalance disk group dg2 after dropping a disk. The statement specifies a STATEMENT_ID, which enables you to query the appropriate work estimate in the V$ASM_ESTIMATE view.

EXPLAIN WORK SET STATEMENT_ID='drop d0'
  FOR ALTER DISKGROUP dg2 DROP DISK d0;
Explained.

SELECT est_work FROM V$ASM_ESTIMATE
  WHERE STATEMENT_ID='drop d0';

EST_WORK
--------
426