EDUCBA

Home Data Science Data Science Tutorials DBMS Tutorial dbms_xplan.display_cursor

dbms_xplan.display_cursor

Updated April 1, 2023

dbms_xplan.display_cursor

Introduction to dbms_xplan.display_cursor

The dbms_xplan.display_cursor is the function which was used in oracle, it was introduced in the oracle 10g version. This function is used to display the actual plan which was used by the query, it is not displaying the plantable for the execution plan. This function is collecting the information from V$SQL, V$SQL_PLAN_STATISTICS and V$SQL_PLAN views, to collect the information from the display cursor we need access to V$SQL, V$SQL_PLAN_STATISTICS and V$SQL_PLAN views, without access to that view we cannot collect the plan of the query. The dbms_xplan.display_cursor function is accepting the three-parameter i.e. sql_id, child_number and format.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

dbms_xplan.display_cursor Overviews

This function is displaying the execution plan of the query which was already executed and its result is in cursor cache.

To use dbms_xplan.display_cursor function on a query we are using below parameter, these all three parameters are optional.

  • Sql_id
  • Child_number
  • Format

1. Sql_id

  • This is existing in the cache of cursor. This parameter is available in V$SQL_AREA and V$SQL views.
  • This parameter is also available in V$SESSION view by using column name as PREV_SQL_ID.
  • If suppose we are omitting the session, it will be displaying the last cursor executed session.

2. Child_number

  • This parameter is specified by the SQL_ID parameter from the function of dbms_xplan.display_cursor.
  • If we are not specifying child_number then all SQL_ID from the cursor cache will be displayed.

3. Format

  • This option is required to set the STATISTICS_LEVEL parameter as ALL.
  • To display the available display format settings for this function, have RUNSTATS_TOT and RUNSTATS_LAST to displaying the total statistics of runtime.

The format parameter will be accepting the four levels to control the query plan.

Those four parameters are as follows:

  • Basic
  • Typical
  • Serial
  • All

The basic level is displaying minimum information from the plan like operation name, id and other options. Typical is default level of format parameter in dbms_xplan.display_cursor function. Typical level is displaying relevant information from the plan i.e. operation name, option, id and cost of optimizer. Parallel and pruning information using this level will have displayed only when it was applicable. The serial level works the same as typical, it is not excepting the parallel information, even if our plan was executing in a parallel fashion. All level is the user’s maximum level. This level contains all information of the typical level as well as it contains the information of alias, projection and remote SQL. The keyword of the format is separated by using space or comma.

How to Use?

We can use dbms_xplan.display_cursor function to display the actual plan of the query by using cursor cache.

Below syntax shows that how to use dbms_xplan.display_cursor function in oracle as follows:

Syntax of dbms_xplan.display_cursor function:

Select * from table_name where condition;
Set parameter value;
Select * from table (function_name (dbms_xplan.display_cursor) (format));

In the above syntax, select is the command which was used to select the data from the specified table. Table name is showing that we are selecting, inserting, updating and deleting data from the specified table. Where the condition is showing that select the data from the specified column. Using dbms_xplan.display_cursor function we are also setting the session level parameter to retrieve the explain plan of query. We are also using the display format of dbms_xplan.display_cursor function output. We can use either all, typical, basic and serial format to display the plan of the executed query.

We are also using the following formats which were supported in the old version as follows:

  • RUNSTATS_LAST
  • RUNSTATS_TOT

RUNSTATS_TOT format is the same as IOSTATS which display the statistics of IO of all execution for the specified cursor. RUNSTATS_LAST format is the same as IOSTATS_LAST which was displaying the runtime statistics of the executed cursor. Basically display cursor function is used to format and display the execution plan context from the loaded cursor.

We can also use the function of table to display cursor execution plan for the loaded cursor which was stored in the cache of cursor. We need to provide the reference of a child function to the function of the table, which includes the statement SQL ID and also we can use the child number as an optional parameter. We can also use the GATHER_PLAN_STATISTICS hint which was allowing the extra collection of metrics at the time of execution of query. It was showing the estimated number of rows from the optimizer.

Using dbms_xplan.display_cursor

To call the dbms_xplan.display_cursor function we need select and read privileges on V$SQL, V$SQL_PLAN_STATISTICS and V$SQL_PLAN views. Without select and read privileges we cannot check the explain plan of last executed query. As we know that dbms_xplan.display_cursor function will take three parameter i.e. sql_id, cursor_child_no and format. We can check the elapsed time and actual number of rows for each step in plan by using gather_plan_statistics SQL statement.

We can check an actual number of rows from each step by following the two steps as follows:

  • Add the hint name as GATHER_PLAN_STATISTICS to statement of SQL.
  • Set the format parameter as allstats_last.

Using the above two-step we can check actual number of rows of each step. At the time of using ALLSTATS as last for the parameter of format, then the estimated number of bytes and cost will not display as default. We can easily use that column which was displayed by adding the format parameter predicates

Examples of dbms_xplan.display_cursor

Given below are the examples of dbms_xplan.display_cursor function:

Example #1

dbms_xplan.display_cursor function using format as advanced.

Code:

Select * from emp_test where id = 1;
SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR (format => 'ADVANCED') );

Output:

dbms_xplan.display_cursor 1

format as advanced

dbms_xplan.display_cursor 3

Example #2

dbms_xplan.display_cursor function using format as basic.

Code:

Select * from emp_test where id = 2;
SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR (format => 'basic') );

Output:

format as basic

dbms_xplan.display_cursor 5

Example #3

dbms_xplan.display_cursor function using format as serial.

Code:

Select * from emp_test where id = 2;
SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR (format => 'serial') );

Output:

format as serial

dbms_xplan.display_cursor 7

Example #4

dbms_xplan.display_cursor function using format as all.

Code:

Select * from emp_test where id = 2;
SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR (format => 'all') );

Output:

using format as all

dbms_xplan.display_cursor 9

dbms_xplan.display_cursor 10

Conclusion

The dbms_xplan.display_cursor function is used to display the actual plan which was used by the query, it is not displaying the plan of table execution query. Basic, typical, serial and all formats available in dbms_xplan.display_cursor function. To use dbms_xplan.display_cursor function on a query we are using sql_id, child_number and format parameter.

Recommended Articles

We hope that this EDUCBA information on “dbms_xplan.display_cursor” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. DBMS Locks
  2. DBMS 3 tier Architecture
  3. DBMS Components
  4. DBMS Transaction Processing