How to get an execution plan and an execution plan

How to get an execution plan and an execution plan

How to get an execution plan from oracle 1. Get a common execution plan. The effect is similar to executing set autot on exp first and then executing SQL. Explain an plan for your_ SQL; select * from table (dbms_xplan.display); 2. obtain the execution plan with outline information, which is useful when optimizing with sqlprofile, or use this execution plan to learn more about the hint explan plan for your_ SQL in oracle; select * from table (dbms_xplan.display (null, null, 'advanced-projection ') 3. the actual execution plan shows the actual Starts (execution times) | E-Rows (Estimated number of returned Rows) | A-Rows (actual number of returned Rows) alter session set STATISTICS_LEVEL = ALL; execute your_ SQL; SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CUR SOR (NULL, NULL, 'allstats last'). In this case, you can write a script getplan. SQL to obtain the execution plan, which is very convenient to use. -- Getplan. sqlset feedback off timing off ver offpro 'General, outline, starts' proacc type prompt 'enter value for plan type: 'default' general 'select * from table (DBMS_XPLAN.DISPLAY_CURSOR (NULL, NULL, 'allstats last') where '& type' = 'starts'; select * from table (dbms_xplan.display) where' & type' = 'General '; select * from table (dbms_xplan.display (null, null, 'advanced-projection ') where' & type '= 'outlin E '; set feedback on timing on ver onundef type test: SQL> select * from; id name ---------- 1 a1 2 a2 3 a3 4 a4 5 a5SQL> select * from B; ID NAME ---------- -------- 1 b1 2 b2 -- Execution Plan 1: SQL> explain plan for select. *, (select name from B where B. id =. id) from a; Explained. elapsed: 00:00:00. 04SQL> @ getplan 'General, outline, starts 'enter value for plan type: ---- Enter general or press Enter PLAN_TABLE _ OUTPUT partition Plan hash value: 3653839899 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | -------------------------------------------------------------------------- | 0 | select statement | 5 | 100 | 3 (0) | 00:00:01 | * 1 | TABLE ACC Ess full | B | 1 | 20 | 3 (0) | 00:00:01 | 2 | TABLE ACCESS FULL | A | 5 | 100 | 3 (0) | 00:00:01 | identified by operation id: ----------------------------------------------------------------- 1-filter ("B ". "ID" =: B1) Note ------dynamic sampling used for this statement -- Execution plan 2: outline execution plan SQL> explain plan for select. *, (Select name from B where B. id =. id) from a; Explained. elapsed: 00:00:00. 01SQL> @ getplan 'General, outline, starts' Enter value for plan type: outline -- Enter outlinePLAN_TABLE_OUTPUT partition Plan hash value: 3653839899 Rows | Id | Operation | Name | Rows | Tes | Cost (% CPU) | Time | ------------------------------------------------------------------------ | 0 | select statement | 5 | 100 | 3 (0) | 00:00:01 | * 1 | table access full | B | 1 | 20 | 3 (0) | 00:00:01 | 2 | table access full | A | 5 | 100 | 3 (0) | 00:00:01 | invalid Query Block Name/Object Alias (identified by operation id ): Starting 1-SEL $2/B @ SEL $2 2-SEL $1/A @ SEL $1 Outline Data -------------/* + BEGIN_OUTLINE_DATA FULL (@ "SEL $2" "B "@" SEL $2 ") FULL (@ "SEL $1" "A" @ "SEL $1") OUTLINE_LEAF (@ "SEL $1") OUTLINE_LEAF (@ "SEL $2 ") ALL_ROWS OPTIMIZER_FEATURES_ENABLE ('10. 2.0.4 ') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */Predicate Information (identified by operation id ):----- -------------------------------------------- 1-filter ("B ". "ID" =: B1) Note ------dynamic sampling used for this statement -- Execution Plan 3: real execution plan SQL> set serveroutput offSQL> ALTER SESSION SET STATISTICS_LEVEL = ALL; Session altered. elapsed: 00:00:00. 00SQL> select. *, (select name from B where B. id =. id) from a; id name (SELECTNAM ---------- 1 a1 b1 2 a2 b2 3 a3 4 a4 5 a55 rows select Ed. elapsed: 00:00:00. 03SQL> @ getplan 'General, outline, starts' Enter value for plan type: starts -- Enter startsPLAN_TABLE_OUTPUT 1_ SQL _id 8rv825dykpx1m, child number 0 --------------------------------- select. *, (select name from B where B. id =. id) from aPlan hash value: 3653839899 ------------------------------ ------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Time | Buffers | tables | * 1 | table access full | B | 5 | 1 | 2 | 00:00:00. 01 | 35 | 2 | table access full | A | 1 | 5 | 5 | 00:00:00. 01 | 8 | --------------------------------------------------------------------------- --------- Predicate Information (identified by operation id): --------------------------------------------------- 1-filter ("B ". "ID" =: B1) Note ------dynamic sampling used for this statement -- Note: The 3rd kinds of execution plans cannot be obtained multiple times, but can only be executed once, otherwise, you will not be able to obtain the following information again: SQL> @ getplan 'General, outline, starts 'enter value for plan type: startsPLAN_TABLE_OUTPUT tables --------------------------------------------------------------------------- ------------------ SQL _ID dvp8nn63wuhs8, child number 0 explain select * from table (dbms_xplan.display (null, null, 'advanced-projection ') where 'starts' = 'outline' Plan hash value: 3440229843 bytes | Id | Operation | Name | Starts | A-Rows | A-Time | --------------------------------------------- -------------------------------------- | * 1 | FILTER | 1 | 0 | 00:00:00. 01 | 2 | collection iterator pickler fetch | DISPLAY | 0 | 0 | 00:00:00. 01 | -- unable to obtain the actual execution plan for the second time (identified by operation id): --------------------------------------------------- 1-filter (null is not null) Note ------rule B Ased optimizer used (consider using cbo) -- set serveroutput off should be disabled for 3rd kinds of execution plans; otherwise, the execution plan cannot be obtained. The test is as follows: This is the same SESSION as the test above, so no alter session set STATISTICS_LEVEL = ALL; is executed. SQL> set serveroutput onSQL> select. *, (select name from B where B. id =. id) from a; id name (SELECTNAM ---------- 1 a1 b1 2 a2 b2 3 a3 4 a4 5 a55 rows selected. elapsed: 00:00:00. 03SQL> @ getplan 'General, outline, starts 'enter value for plan type: startsPLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------- SQL _ID 9babjv8yq8ru3, child number 0 BEGIN DBMS_OUTPUT.GET_LINES (: LINES,: NUMLINES); END; -- the actual execution plan cannot be obtained either. NOTE: cannot fetch plan for SQL _ID: 9babjv8yq8ru3, CHILD_NUMBER: 0 Please verify value of SQL _ID and CHILD_NUMBER; it coshould also be that the plan is no longer in cursor cache (check v $ SQL _plan)

How to Use sqlplus or pl/SQL developer to obtain the execution plan

In PL/SQL DEVELOPER, press F5
Set autot trace in sqlplus
Then execute your SQL statement !!

How to obtain the execution plan of oracle 10g statements?

In PLSQL, select the statement to run and press F5 to view the execution plan.