DBA_ADVISOR_SQLPLANS

DBA_ADVISOR_SQLPLANS displays the different SQL execution plans generated as part of an advisor analysis.

Related View

USER_ADVISOR_SQLPLANS displays the different SQL execution plans owned by the current user generated as part of an advisor analysis.

Column Datatype NULL Description
TASK_NAME VARCHAR2(30)   Advisor task name in which the SQL plan was generated (see DBA_ADVISOR_TASKS)
TASK_ID NUMBER(38) NOT NULL Advisor task ID in which the SQL plan was generated (see DBA_ADVISOR_TASKS)
EXECUTION_NAME VARCHAR2(30) NOT NULL Advisor task execution in which the SQL plan was generated (see DBA_ADVISOR_EXECUTIONS)
SQL_ID VARCHAR2(13) NOT NULL Identifier for the relevant SQL statement
OBJECT_ID NUMBER(38) NOT NULL Advisor object ID identifying the relevant SQL statement (see DBA_ADVISOR_OBJECTS)
ATTRIBUTE VARCHAR2(27)   Text string identifying the type of the execution plan. The following values are used by the SQL Tuning Advisor:
  • Original - Original plan of the query

  • Original with adjusted cost - Same as Original but with adjusted cost

  • Using SQL profile - Plan with SQL profile applied

  • Using new indices - Plan with indexes applied

STATEMENT_ID VARCHAR2(30)   Optional statement identifier specified in the EXPLAIN PLAN statement
PLAN_HASH_VALUE NUMBER NOT NULL Numerical representation of the execution plan
PLAN_ID NUMBER NOT NULL Plan identifier
TIMESTAMP DATE   Date and time when the EXPLAIN PLAN statement was issued
REMARKS VARCHAR2(4000)   Place for comments that can be added to the steps of the execution plan
OPERATION VARCHAR2(30)   Name of the operation performed at this step
OPTIONS VARCHAR2(255)   Options used for the operation performed at this step
OBJECT_NODE VARCHAR2(128)   Name of the database link used to reference the object
OBJECT_OWNER VARCHAR2(30)   Owner of the object
OBJECT_NAME VARCHAR2(30)   Name of the object
OBJECT_ALIAS VARCHAR2(65)   Object alias
OBJECT_INSTANCE NUMBER(38)   Numbered position of the object name in the original SQL statement
OBJECT_TYPE VARCHAR2(30)   Descriptive modifier that further describes the type of object
OPTIMIZER VARCHAR2(255)   Current mode of the optimizer
SEARCH_COLUMNS NUMBER   Number of index columns with start and stop keys (that is, the number of columns with matching predicates)
ID NUMBER(38) NOT NULL Identification number for this step in the execution plan
PARENT_ID NUMBER(38)   ID of the next step that operates on the results of this step
DEPTH NUMBER(38)   Depth
POSITION NUMBER(38)   Order of processing for steps with the same parent ID
COST NUMBER(38)   Cost of the current operation estimated by the cost-based optimizer (CBO)
CARDINALITY NUMBER(38)   Number of rows returned by the current operation (estimated by the CBO)
BYTES NUMBER(38)   Number of bytes returned by the current operation
OTHER_TAG VARCHAR2(255)   Describes the function of the SQL text in the OTHER column. Values for OTHER_TAG are:
  • SERIAL - SQL is the text of a locally-executed, serial query plan. Currently, SQL is not loaded in OTHER for this case.

  • SERIAL_FROM_REMOTE - SQL text shown in the OTHER column will be executed at a remote site

  • PARALLEL_COMBINED_WITH_PARENT - Parent of this operation is a DFO that performs both operations in the parallel execution plan

  • PARALLEL_COMBINED_WITH_CHILD - Child of this operation is a DFO that performs both operations in the parallel execution plan.

  • PARALLEL_TO_SERIAL - SQL text shown in the OTHER column is the top-level of the parallel plan.

  • PARALLEL_TO_PARALLEL - SQL text shown in the OTHER column is executed and output in parallel

  • PARALLEL_FROM_SERIAL - Operation consumes data from a serial operation and outputs it in parallel

PARTITION_START VARCHAR2(255)   Start partition of a range of accessed partitions
PARTITION_STOP VARCHAR2(255)   Stop partition of a range of accessed partitions
PARTITION_ID NUMBER(38)   Step that has computed the pair of values of the PARTITION_START and PARTITION_STOP columns
OTHER LONG   Information about parallel execution servers and parallel queries
DISTRIBUTION VARCHAR2(30)   Distribution method
CPU_COST NUMBER(38)   User-defined CPU cost
IO_COST NUMBER(38)   User-defined I/O cost
TEMP_SPACE NUMBER(38)   Temporary space usage of the operation (sort or hash-join) as estimated by the CBO
ACCESS_PREDICATES VARCHAR2(4000)   Predicates used to locate rows in an access structure. For example, start or stop predicates for an index range scan.
FILTER_PREDICATES VARCHAR2(4000)   Predicates used to filter rows before producing them
PROJECTION VARCHAR2(4000)   Expressions produced by the operation
TIME NUMBER(38)   Elapsed time (in seconds) of the operation as estimated by the CBO
QBLOCK_NAME VARCHAR2(30)   Name of the query block
OTHER_XML CLOB   Provides extra information specific to an execution step of the execution plan. The content of this column is structured using XML because it allows multiple pieces of information to be stored, including the following:
  • Name of the schema against which the query was parsed

  • Release number of the Oracle Database that produced the explain plan

  • Hash value associated with the execution plan

  • Name (if any) of the outline or the SQL profile used to build the execution plan

  • Indication of whether or not dynamic sampling was used to produce the plan

  • The outline data, a set of optimizer hints that can be used to regenerate the same plan