Without select and read privileges we cannot check the explain plan of last executed query. 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. It was showing the estimated number of rows from the optimizer. We can also use the GATHER_PLAN_STATISTICS hint which was allowing the extra collection of metrics at the time of execution of query. 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 function of table to display cursor execution plan for the loaded cursor which was stored in the cache of cursor. Basically display cursor function is used to format and display the execution plan context from the loaded cursor. RUNSTATS_LAST format is the same as IOSTATS_LAST which was displaying the runtime statistics of the executed cursor. RUNSTATS_TOT format is the same as IOSTATS which display the statistics of IO of all execution for the specified cursor.
We are also using the following formats which were supported in the old version as follows:
#XPLAN LOGIN SERIAL#
We can use either all, typical, basic and serial format to display the plan of the executed query. We are also using the display format of dbms_xplan.display_cursor function output. Using dbms_xplan.display_cursor function we are also setting the session level parameter to retrieve the explain plan of query. Where the condition is showing that select the data from the specified column.
Table name is showing that we are selecting, inserting, updating and deleting data from the specified table. In the above syntax, select is the command which was used to select the data from the specified table. Select * from table (function_name (dbms_xplan.display_cursor) (format)) Select * from table_name where condition Syntax of dbms_xplan.display_cursor function:
#XPLAN LOGIN HOW TO#
We can use dbms_xplan.display_cursor function to display the actual plan of the query by using cursor cache.īelow syntax shows that how to use dbms_xplan.display_cursor function in oracle as follows:
The keyword of the format is separated by using space or comma. This level contains all information of the typical level as well as it contains the information of alias, projection and remote SQL. 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. Parallel and pruning information using this level will have displayed only when it was applicable. operation name, option, id and cost of optimizer. Typical level is displaying relevant information from the plan i.e. Typical is default level of format parameter in dbms_xplan.display_cursor function. The basic level is displaying minimum information from the plan like operation name, id and other options. The format parameter will be accepting the four levels to control the query plan.
0 kommentar(er)
