Updated March 31, 2023
Introduction to DBMS_XPLAN
The DBMS_XPLAN is defined as a package that can be used for displaying the outputs. It furnishes a simple process to present the output of the ‘EXPLAIN PLAN’ command in respective and preplanned configurations. And also, this package can be used for presenting the plan of statements that have been set aside in an Automatic Workload Repository, or we can say that it can be stored in a SQL tuning set. Additionally, it provides a way to show the SQL execution plan and its execution statistics for the cached cursor in which the information has been stored in a fixed view.
What is DBMS_XPLAN?
The DBMS_XPLAN is the package in which it has been used to display the simple output by using the ‘EXPLAIN PLAN’ command, which has its predefined format. Furthermore, it has an Automated Workload Repository repository, so this package has also been used to show its content. It can also be used to show the execution plan and its runtime, which can be used for statistics in SQL.
We can also say it is a method that can be used for questioning and showing the ‘explain plan output. It can also display the statement set aside in the Automatic Workload Repository, or we can say that from the SQL tuning set. Furthermore, it also sets aside the SQL execution plan and runtime statistics for cached SQL cursors and can plan a baseline of the SQL plan. Finally, it uses a query “Select * From table (dbms_xplan.display)” to show and configure the content of the plan table.
How to Use DBMS_XPLAN?
Let us see the use of DBMS_XPLAN:
1. Overview
The DBMS_XPLAN package has provided four functions. First is the ‘DISPLAY’ function, which has been used to configure and present the content of the plan table, and the second is the ‘DISPLAY_CURSOR’ which has been used to configure and set out the content of an execution plan. The third one is the ‘DISPLAY_AWR’ which can be used for configuration and set out the content of the implementation plan of SQL statement, which we can get in AWR. Finally, the fourth is ‘DISPLAY_SQLSET,’ which has been used to format and show the contents implementing a plan of statements in the SQL tuning set.
2. Security Model
This bundle has been run to call the user; hence, it can not call the package owner that is ‘SYS,’ the table function ‘DISPLAY_CURSOR’ has been beneficial to SELECT the specific view.
- Using the ‘DISPLAY_AWR’ function requires the user to have SELECT the benefit of the DBA_HIST_SQL_PLAN, DBA_HIST_SQLTEXT, and V$DATABASE.
- The ‘DISPLAY_SQLSET’ has been used to call the user who has rights on ALL_SQLSET_STATEMENTS and ALL_SQLSET_PLANS.
- All the advantages have been impulsively taking part in the SELECT catalog.
3. Example
Let us see an example of showing the plan table by using DBMS_XPLAN.DISPLAY.
- First, we must execute an explain plan command over the SELECT statement.
Code:
EXPLAIN PLAN FOR
SELECT * FROM stu s, sec t WHERE s.secno = t.secno AND s.sname='benoit';- And then, it shows a plan using the ‘DBMS_XPLAN.DISPLAY’ function, and we can get the output.
SET LINESIZE 120
SET PAGESIZE 0
SELECT * FROM table(DBMS_XPLAN.DISPLAY);DBMS_XPLAN File
The DBMS_XPLAN has a trace file that can be used for tracing the statements and creating the tkprof of the trace and in which tkprof is a necessary thing to check the performance issues; basically, it can configure the trace file into the readable format for analyzing the performance in which file take part in performance tuning.
A trace file is a raw place for data that an oracle can develop; when any program is running, then it allows to create of the raw text file which has internal activity; some internal activities can be performed internally, that are audit, implement, and bring the total, it also shows the CPU and elapsed time, it can allow the substantial and objective reading, it can able to count the number of rows and to commit and rollback activity is also done at internal which can be traced by trace file.
DBMS_XPLAN Format
Let us see the DBMS_XPLAN format in which it is a parameter that can be used to associate the level of details of the plan that receives four values that are:
- BASIC: This value can show the lowest information in the plan, such as operation ID, operation name, and other options.
- TYPICAL: This is the default value. It can show the applicable information in the plan, such as the operation id name, and show the established and parallel information whenever needed.
- SERIAL: Same as TYPICAL, it cannot display the parallel information, but the plan can implement in parallel.
- ALL: It controls the utmost user level, which can also involve the information at the TYPICAL level.
Some keywords can be attached to the above standard to alter their default behavior that is:
- ROWS: When applicable, then it can display the number of rows.
- BYTES: When applicable, it can show the number of bytes.
- COST: If applicable, it will show the optimizer cost information.
- PARTITION: If applicable, it shows the partition pruning data.
- PARALLEL: If applicable, it shows the dispersed method and table information.
- PREDICATE: If applicable, it can show the established information.
- PROJECTION: If applicable, it will display the projection section.
- ALIAS: If applicable, it will display the alias section.
- REMOTE: If applicable, it can show the information for dispensing the query.
Conclusion
In this article, we conclude that the DBMS_XPLAN is a package that can be used to set out the output of the ‘Explain Plan,’ and Oracle has developed it; we have also seen the using of it, file, and format of the package so this article may be helpful.
Recommended Articles
We hope that this EDUCBA information on “DBMS_XPLAN” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
