learning v$ view-> v$sql & V$sql_plan

 

V$SQL

A SQL statement can map to multiple cursors, because the objects referred to in the cursor can differ from user to user. If there are multiple cursors (child cursors) present, then V$SQLAREA provides aggregated information for all the cursors.

For looking at individual cursors, V$SQL can be used. This view contains cursor level details for the SQL. It can be used when trying to locate the session or person responsible for parsing the cursor.

The PLAN_HASH_VALUE column contains the numerical representation of the SQL plan for the cursor and can be used for comparing plans. PLAN_HASH_VALUE allows you to easily identify whether or not two plans are the same without comparing the two plans line by line.

 

V$SQL_PLAN

This view provides a way of examining the execution plan for cursors that were executed and are still cached.

Normally, the information in this view is very similar to the output of an EXPLAIN PLAN statement. However, EXPLAIN PLAN shows a theoretical plan that might be used if this statement were to be executed, whereas V$SQL_PLAN contains the actual plan used. The execution plan obtained by the EXPLAIN PLAN statement can be different from the execution plan used to execute the cursor, because the cursor might have been compiled with different values of session parameters (for example, HASH_AREA_SIZE).

Uses for V$SQL_PLAN Data

• Determining the current execution plan
• Identifying the effect of creating an index on a table
• Finding cursors containing a certain access path (for example, full table scan or index range scan)
• Identifying indexes that are, or are not, selected by the optimizer
• Determining whether the optimizer selects the particular execution plan (for example, nested loops join) expected by the developer

This view can also be used as a key mechanism in plan comparison. Plan comparison can be useful when the following types of changes occur:

• Dropping or creating indexes
• Running the ANALYZE statement on the database objects
• Modifying initialization parameter values
• Switching from the rule-based optimizer to the cost-based optimizer
• After upgrading the application or the database to a new release

If previous plans are kept (for example, selected from V$SQL_PLAN and stored in permanent Oracle tables for reference), then it is then possible to identify how changes in the performance of a SQL statement can be correlated with changes in the execution plan for that statement.

Note: Oracle Corporation strongly recommends that you use the DBMS_STATS package rather than ANALYZE to collect optimizer statistics. That package lets you collect statistics in parallel, collect global statistics for partitioned objects, and fine tune your statistics collection in other ways. Further, the cost-based optimizer will eventually use only statistics that have been collected by DBMS_STATS. See Oracle9i Supplied PL/SQL Packages and Types Reference for more information on this package. However, you must use the ANALYZE statement rather than DBMS_STATS for statistics collection not related to the cost-based optimizer, such as: To use the VALIDATE or LIST CHAINED ROWS clauses To collect information on freelist blocks

Useful Columns for V$SQL_PLAN

The view contains almost all PLAN_TABLE columns, in addition to new columns. The columns that are also present in the PLAN_TABLE have the same values:

• ADDRESS: Address of the handle to the parent for this cursor
• HASH_VALUE: Hash value of the parent statement in the library cache

The two columns ADDRESS and HASH_VALUE can be used to join with V$SQLAREA to add the cursor-specific information.

• CHILD_NUMBER: Child cursor number using this execution plan

The columns ADDRESS, HASH_VALUE and CHILD_NUMBER can be used to join with V$SQL to add the child cursor specific information.

• OPERATION: Name of the internal operation performed in this step; for example, TABLE ACCESS
• OPTIONS: A variation on the operation described in the OPERATION column; for example, FULL
• OBJECT_NODE: Name of the database link used to reference the object (a table name or view name); for local queries using parallel execution, this column describes the order in which output from operations is consumed
• OBJECT#: Object number of the table or the index
• OBJECT_OWNER: Name of the user who owns the schema containing the table or index
• OBJECT_NAME: Name of the table or index
• OPTIMIZER: Current mode of the optimizer for the first row in the plan (statement line); for example, CHOOSE. In case the operation is a database access (e.g, TABLE ACCESS), it tells whether the object is analyzed or not
• ID: A number assigned to each step in the execution plan
• PARENT_ID: ID of the next execution step that operates on the output of the current step
• DEPTH: The depth (or level) of the operation in the tree; that is, it is not necessary to do a CONNECT BY to get the level information generally used to indent the rows from the PLAN_TABLE - the root operation (statement) has level 0.
• POSITION: Order of processing for operations that all have the same PARENT_ID
• COST: Cost of the operation as estimated by the optimizer's cost-based approach; for statements that use the rule-based approach, this column is null
• CARDINALITY: The estimate, by the cost-based optimizer, of the number of rows produced by the operation
• BYTES: The estimate, by the cost-based optimizer, of the number of bytes produced by the operation
• OTHER_TAG: Describes the contents of the OTHER column (see Chapter 9, "Using EXPLAIN PLAN" for values)
• PARTITION_START: The start partition of a range of accessed partition
• PARTITION_STOP: The stop partition of a range of accessed partitions
• PARTITION_ID: The step that has computed the pair of values of the PARTITION_START and PARTITION_STOP columns
• OTHER: Other information that is specific to the execution step that a user may find useful (see Chapter 9, "Using EXPLAIN PLAN" for values)
• DISTRIBUTION: For parallel query, stores the method used to distribute rows from producer query servers to consumer query servers.
• CPU_COST: The CPU cost of the operation as estimated by the optimizer's cost-based approach; for statements that use the rule-based approach, this column is null
• IO_COST: The I/O cost of the operation as estimated by the optimizer's cost-based approach; for statements that use the rule-based approach, this column is null
• TEMP_SPACE: Temporary space usage of the operation (sort or hash-join) as estimated by the optimizer's cost-based approach; for statements that use the rule-based approach, this column is null
• ACCESS_PREDICATES: Predicates used to locate rows in an access structure; for example, start or stop predicates for an index range scan
• FILTER_PREDICATES: Predicates used to filter rows before producing them

The DEPTH column replaces the LEVEL pseudo-column produced by the CONNECT BY operator, which sometimes is used in SQL scripts to help indent the PLAN_TABLE data.

Join Columns for V$SQL_PLAN

The columns ADDRESS, HASH_VALUE and CHILD_NUMBER are used to join with V$SQL or V$SQLAREA to fetch the cursor-specific information; for example, BUFFER_GETS, or with V$SQLTEXT to return the full text of the SQL statement.

Table 24-23 lists the join columns for V$SQL_PLAN.

Table 24-23 Join Columns for V$SQL_PLAN

Column	View	Joined Column(s)
ADDRESS, HASH_VALUE	V$SQLAREA	ADDRESS, HASH_VALUE
ADDRESS, HASH_VALUE, CHILD_NUMBER	V$SQL	ADDRESS, HASH_VALUE, CHILD_NUMBER
ADDRESS, HASH_VALUE	V$SQLTEXT	ADDRESS, HASH_VALUE

Determining the Optimizer Plan for a SQL Statement

The following statement shows the EXPLAIN PLAN for a specified SQL statement. Looking at the plan for a SQL statement is one of the first steps in tuning a SQL statement. The SQL statement for which to return the plan is identified by the statement's HASH_VALUE and address.

Example 24-27 shows a query and sample output from V$SQL_PLAN (assumes only one child cursor):

Example 24-27 Querying V$SQL_PLAN

SELECT /* TAG */ count(*) 
  FROM employees e, departments d 
 WHERE e.department_id = d.department_id;

  COUNT(*)
----------
        14

column operation   format a20
column options     format a20
column object_name format a20
column cost        format a20
column cost        format 9999 
SELECT sql_text, address, hash_value 
  FROM v$sql 
 WHERE sql_text like '%TAG%';

SQL_TEXT   ADDRESS  HASH_VALUE
--------  --------  ----------
          82117BEC   171077025


SELECT sql_text, address, hash_value 
  FROM v$sql 
 WHERE sql_text LIKE '%TAG%'


SELECT /* TAG */ count(*) 
  FROM employees e, departments d 
 WHERE e.department_id = d.department_id

SQL_TEXT   ADDRESS  HASH_VALUE
--------  --------  ----------
          82157784  1224822469

SELECT operation, options, object_name, cost 
  FROM v$sql_plan 
 WHERE address = '\ 82157784' 
   AND hash_value = 1224822469;

OPERATION            OPTIONS       OBJECT_NAME        COST
-------------------- ------------- ------------------ ----
SELECT STATEMENT                                         5
  SORT
    AGGREGATE
      HASH JOIN                                          5
      TABLE ACCESS   FULL          DEPARTMENTS           2
      TABLE ACCESS   FULL          EMPLOYEES             2

6 rows selected.

V$SQL_PLAN shows the plan for a cursor, not for a SQL statement. The difference is that a SQL statement can have more than one cursor associated with it, with each cursor further identified by a CHILD_NUMBER. The following are a few examples of how a SQL statement can result in more than one cursor:

• When the same table name resolves to two separate tables:

  User1: SELECT * FROM EMPLOYEES;

  User2: SELECT * FROM EMPLOYEES;

  Where user2 has his own employee table, and user1 uses the table referenced by a public synonym.

• When the environment for user1 differs from user2. For example, if user2 specified the first rows (ALTER SESSION SET OPTIMIZER_GOAL = FIRST_ROWS) in their login script, and user1 did not.

  If the results of querying V$SQL_PLAN for a HASH_VALUE and ADDRESS result in more than one plan appearing, it is because this SQL statement has more than one child cursor. In this case, for each child cursor (identified by CHILD_NUMBER), look at the plan to identify whether they differ significantly.