[Oracle] several methods for obtaining execution plans

[Oracle] methods for obtaining execution plans 1. estimate execution Plan-Explain plan Explain Plan uses an SQL statement as the input to obtain the execution plan of this SQL statement and store the execution Plan output to the schedule. First, add the explain plan for statement before the SQL statement you want to execute. Then, store the generated execution plan to the plan table. The statement is as follows: explain plan for SQL statement. Then, query the generated execution plan in the schedule. The statement is as follows: select * from table (dbms_xplan.display). Note: The Explain plan only generates the execution plan and does not actually execute the SQL statement, the execution plan may be inaccurate because: 1) the current environment may be different from the environment when the execution plan is generated; 2) the data type of the variable to be bound is not considered; 3) no variable pegging. 2. query the execution plans cached in the memory (dbms_xplan.display_cursor ), it can be queried in the dynamic performance view. The method is as follows: 1) obtain the SQL statement's cursor is divided into the parent cursor and the Child cursor. The parent cursor is represented by the SQL _id (or combined address and hash_value) field, and the Child cursor is represented by the child_number field. If the SQL statement is running, you can obtain its cursor information from v $ session, such as select status, SQL _id, SQL _child_number from v $ session where status = 'active' and .... if you know that an SQL statement contains certain keywords, you can obtain its cursor information from the v $ SQL view, such as select SQL _id, child_number, SQL _text from v $ SQL where SQL _text like '% keyword %' 2) Get the execution plan in the database cache in order to get the execution plan in the cache library, you can directly query the dynamic performance view v $ SQL _plan and v $ SQL _plan_statistics_all. However, the more convenient method is to use SQL _id and sub-cursor as parameters and execute the following statement: select * from table (dbms_xplan.display_cursor ('SQL _ id', child_number); 3) obtain the previous execution plan: set serveroutput offselect * from table (dbms_xplan.display_cursor (null, null, 'allstats last'); 3. when you query a historical Execution Plan (dbms_xplan.display_awr), AWR regularly saves the execution plan in the dynamic performance View to the dba_hist_ SQL _plan view. If you want to view the historical execution plan, you can query it as follows: select * from table (dbms_xplan.display_awr ('SQL _ id'); 4. when using sqlplus for SQL development, set Autotrace is a function of the sqlplus tool and can only be used in sessions connected through sqlplus, it is very suitable for testing the performance of SQL statements during development. It has the following parameters to choose from: SET AUTOTRACE OFF ---------------- do not display execution plans and statistics, this is the default mode set autotrace on explain ------ only show the optimizer Execution Plan set autotrace on statistics -- only show the statistical information set autotrace on --------------- the execution plan and STATISTICS information are displayed at the same time set autotrace traceonly ------ not true run, only the expected execution plan is displayed, which is the same as the explain plan 5. generate a Trace file to query detailed execution plans (SQL _Trace, 10046) SQL _TRACE can be enabled at the instance level or at the session level as initialization parameters, enabling SQL _TRACE at the instance level will lead to tracking of activities of all processes, including background processes and all user processes. This usually causes serious performance problems, we use SQL _trace to trace the current process. The method is as follows: SQL> alter session set SQL _trace = true ;... SQL statement to be tracked... SQL> alter session set SQL _trace = false; To track other processes, you can use the system package DBMS_SYSTEM provided by Oracle. SET_ SQL _TRACE_IN_SESSION, for example, SQL> exec dbms_system.set_ SQL _trace_in_session (sid, serial #, true) -- start to trace SQL> exec dbms_system.set_ SQL _trace_in_session (sid, serial #, false) -- after the trace file is generated, use tkprof to convert the trace file generated by SQL trace to a readable format. Syntax: tkprof inputfile outputfile10046 is an upgraded version of SQL _TRACE, it is also a tracing session that generates a Trace file, but the content in it is more detailed.