Introduction to the Oracle dbms_xplan package

 
1. Usage description the dbms_xplan package is used to display the execution plan. The execution plan is stored in the Schedule, database cache, and AWR. dbms_xplan
The package can extract the execution plan from these locations, format it, and display it to us. Www.2cto.com 2. dbms_xplan: the output of dbms_xplan is the information of the execution plan. Generally, the output is as follows:

 
SQL _ID: Identify the parent cursor child number: SQL _id subcursor number, identify the subcursor SQL text content: shows the execution plan of which SQL statement corresponds to www.2cto.com Part 2:
This SQL statement is split into several steps for execution and provides evaluation and running statistics for each step. Id column: the sequence number of the step. If there is * above, this step filters data based on a filter condition. Opration column: the operations performed, such as index scan, data selection, and table join. Name column: the object to be operated. For example, an operation with the serial number 3 is performed on the T_PK primary key of the table. Rows: number of records returned by this operation. Bytes: the number of records returned by this operation.
Www.2cto.com cost (% cpu): Operation overhead, which is a cumulative value. (We can see that number 4 has the largest operation overhead.) the value in the brackets is the percentage of cpu overhead. For example, the percentage of 0 operations is 97%, and the percentage of 1 Operations is 3%. Time: the time required for the operation. This is also a cumulative value. (Note that this value may be inaccurate because oracle is the value calculated by sampling.) Part 3: Next we will see the following information:

The specific filtering condition of the data filtering step is described here. For example, the filtering condition with the serial number 4 is T1.ID> 6. note that the prefix 4 is filter, which indicates that T1 is scanned in the full table and then filtered according to the condition. The access parameter 3 indicates that the destination access is performed according to the specified condition. Part 4: We may still see the following information:

The field information returned by each step is described. For example, step 4 returns the T1.N column. III. the display function calls the display function to display the execution plan stored in the schedule. The format is as follows: select * from table (dbms_xplan.display (table_name, statement_id, format, filter_preds) table_name: Specifies the name of the schedule, the default value is plan_table. If it is null, the default value is used.
Www.2cto.com statement_id: Specifies the name of the SQL statement. The default value is null, indicating the execution plan of the recently inserted schedule. Format: format of the output content. The basic formats include basic, typical, serial, all, and advanced. basic: displays the most basic information, and advanced displays the most complete information. For details, see:

Filter_preds: Specifies to add a constraint when querying a schedule. For example, statement_id = 'test3'. The default value is null, indicating that the execution plan of the recently inserted schedule is displayed.
Example of www.2cto.com: basic:

Result of advanced: www.2cto.com

 

 

4. display_cursor function this function displays the execution plan stored in the database cache, in the following format: select * from table (dbms_xplan.display_cursor (SQL _id, cursor_child_no, format) SQL _id: specifies the parent cursor of the SQL statement returned to the execution plan. The default value is null, which indicates the execution plan of the last executed SQL statement in the current session.
Cursor_child_no: Specifies the sequence number of the Child cursor under the parent cursor. The default value is 0. If it is set to null, the execution plan of all child game targets is returned. Format: Same as the display function.