Oracle interpretation & amp; Execution Plan

Oracle EXPLAIN & execution plan explain plan statement explain plan is used to display the execution PLAN selected by the optimizer for the SQL statement. It should be noted that when you get the explain Plan output, you actually get the expected execution plan that should be used for SQL statement execution. You have not obtained the actual execution plan and the data source execution statistics related to it. All you get is the estimated value, not the actual value. The estimated information is called the explain Plan output, and the actual information is called the execution plan output to distinguish the actual and expected execution plans. Example of explain plan at www.2cto.com: SQL code SQL> explain Plan for 2 select * from small_customers; explain SQL> select * from table (dbms_xplan.display); PLAN_TABLE_OUTPUT explain plan hash value: 1819012793 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time Stamps | 0 | select statement | 6 | 156 | 3 (0) | 1 | table access full | SMALL_CUSTOMERS | 6 | 156 | 3 (0) | 00:00 notice Note ------dynamic sampling used for this statement 12 rows selected www.2cto.com indent format: SQL code select id, parent_id, Operation from (select level lvl, id, parent_id, lpad ('', level) | operation | ''| options |'' | object_name as operation from plan_table start with id = 0 connect by prior id = parent_id) order by lvl desc, id; the most depressing aspect of explain Plan output is that it may be inconsistent with the plan used for actual statement execution. When using an explain Plan, the following three points may cause inconsistency between the plan output and the actual execution plan: the explain Plan is generated based on the environment when you use it. The explain Plan does not consider the data type of the bound variable (all the bound variables are VARCHAR2) explain plan does not "peat" the value of variable binding. For explain plans, data types are considered the same (all strings ). However, the data type must be considered in the execution plan prepared when the statement is actually executed. ORACLE implicitly converts the string data type to the numeric type to match the data type of the bound variable. How does www.2cto.com learn to read the plan: Learn to identify and split the Parent and Child Groups to master the sequence of Operation execution in the plan. Learn to read the Plan Calculation displayed in the execution order in the form of a statement: SQL code explain plan for select * from test_1; select * from table (dbms_xplan.display); select id, parent_id, operation from (select level lvl, id, parent_id, lpad ('', level) | operation | ''| options |'' | object_name as operation from plan_table start with id = 0 connect by prior id = parent_id) order by lvl desc, id; Execution Plan www.2cto.com when an SQL statement is executed, the actual execution plan of the statement is generated. After the statement is hard parsed, the selected execution plan will be saved to the database cache for future reuse. You can query V $ SQL _PLAN to view the plan operation. SQL code select SQL _id from v $ SQL where SQL _text like '% TEST %'; select * from v $ SQL _plan where SQL _id = '7dfcmhhhxjsp' and child_number = 0; alternatively, use the following statement to replace v $ SQL _plan select * from table (dbms_xplan.display_cursor ('7dfcmhhhxjsp', null, 'allstats la'). You can also use www.2cto.com: SELECT * from test; select xplan. * FROM (select max (SQL _ID) KEEP (DENSE_RANK last order by LAST_ACTIVE_TIME) SQL _ID, MAX (CHILD_NUMBER) KEEP (DENSE_RANK last order by LAST_ACTIVE_TIME) CHILD_NUMBER from v $ SQL where upper (SQL _TEXT) LIKE '% SELECT * from test %' and upper (SQL _TEXT) not like '% from v $ SQL where upper (SQL _TEXT) LIKE %') SQLINFO, TABLE (DBMS_XPLAN.display_cursor (SQLINFO. SQL _ID, SQLINFO. CHILD_NUMBER, 'allstats last') XPLAN;