Two Methods for viewing execution plans: Explain plan and autotrace

Both explain Plan and autotrace can view the execution plan. It is worth mentioning that the former is only the optimizer that reads the statistical information of the data dictionary to make an 'optimum 'access path judgment,The statement is not actually executed.The latter executes the SQL statement and prints the number of access records, execution plans, and statistics.

The following is a description of the experiment results. Note:Compare the time consumption between the two:

 

<P> SQL> connect/As sysdbaconnected. </P> <p> SQL> set linesize 300; SQL> set timing on; SQL> set pagesize; SQL> explain Plan for select count (*) from dba_objects, dba_objects; </P> <p> explained. </P> <p> elapsed: 00:00:00. 03 <<consumes only 0.03 seconds> SQL> select * from table (dbms_xplan.display ); plan hash value: 2343274122 </P> <p> bytes | ID | operation | Name | rows | bytes | cost (% CPU) | time | percent | 0 | SELECT statement | 1 | 7187 K (5) | 23:57:25 | 1 | sort aggregate | 1 | 2 | merge join Cartesian | 2325m | 7187 K (5) | 23:57:25 | 3 | View | dba_objects | 48221 | 149 (5) | 00:00:02 | 4 | Union-all | * 5 | filter | * 6 | hash join | 51192 | 4099k | 148 (5) | 00:00:02 | 7 | table access full | user $ | 59 | 177 | 2 (0) | 00:00:01 | * 8 | table access full | OBJ $ | 51192 | 3949k | 145 (5) | 00:00:02 | * 9 | table access by index rowid | ind $ | 1 | 8 | 2 (0) | 00:00:01 | * 10 | index unique scan | I _ind1 | 1 | 1 (0) | 00:00:01 | 11 | nested loops | 1 | 16 | 1 (0) | 00:00:01 | 12 | index full scan | I _link1 | 1 | 13 | 0 (0) | 00:00:01 | 13 | Table Access Cluster | user $ | 1 | 3 | 1 (0) | 00:00:01 | * 14 | index unique scan | I _user # | 1 | 0 (0) | 00:00:01 | 15 | buffer sort | 48221 | 7187 K (5) | 23:57:25 | 16 | View | dba_objects | 48221 | 149 (5) | 00:00:02 | 17 | Union-all | * 18 | filter | * 19 | hash join | 51192 | 4099k | 148 (5) | 00:00:02 | 20 | table access full | user $ | 59 | 177 | 2 (0) | 00:00:01 | * 21 | table access full | OBJ $ | 51192 | 3949k | 145 (5) | 00:00:02 | * 22 | table access by index rowid | ind $ | 1 | 8 | 2 (0) | 00:00:01 | * 23 | index unique scan | I _ind1 | 1 | 1 (0) | 00:00:01 | 24 | nested loops | 1 | 16 | 1 (0) | 00:00:01 | 25 | index full scan | I _link1 | 1 | 13 | 0 (0) | 00:00:01 | 26 | Table Access Cluster | user $ | 1 | 3 | 1 (0) | 00:00:01 | * 27 | index unique scan | I _user # | 1 | 0 (0) | 00:00:01 | reset </P> <p> predicate information (identified by Operation ID): ------------------------------------------------- </P> <p> 5-filter ("O ". "Type #" <> 1 and "O ". "Type #" <> 10 or "O ". "Type #" = 1 and (select 1 from "sys ". "IND $" "I" where "I ". "OBJ #" =: B1 and ("I ". "Type #" = 1 or "I ". "Type #" = 2 or "I ". "Type #" = 3 or "I ". "Type #" = 4 or "I ". "Type #" = 6 or "I ". "Type #" = 7 or "I ". "Type #" = 9) = 1) 6-access ("O ". "Owner #" = "U ". "user #") 8-filter (bitand ("O ". "Flags", 128) = 0 and "O ". "name" <> '_ next_object' and "O ". "name" <> '_ default_auditing_options _' and "O ". "linkname" is null) 9-filter ("I ". "Type #" = 1 or "I ". "Type #" = 2 or "I ". "Type #" = 3 or "I ". "Type #" = 4 or "I ". "Type #" = 6 or "I ". "Type #" = 7 or "I ". "Type #" = 9) 10-access ("I ". "OBJ #" =: B1) 14-access ("L ". "Owner #" = "U ". "user #") 18-filter ("O ". "Type #" <> 1 and "O ". "Type #" <> 10 or "O ". "Type #" = 1 and (select 1 from "sys ". "IND $" "I" where "I ". "OBJ #" =: B1 and ("I ". "Type #" = 1 or "I ". "Type #" = 2 or "I ". "Type #" = 3 or "I ". "Type #" = 4 or "I ". "Type #" = 6 or "I ". "Type #" = 7 or "I ". "Type #" = 9) = 1) 19-access ("O ". "Owner #" = "U ". "user #") 21-filter (bitand ("O ". "Flags", 128) = 0 and "O ". "name" <> '_ next_object' and "O ". "name" <> '_ default_auditing_options _' and "O ". "linkname" is null) 22-filter ("I ". "Type #" = 1 or "I ". "Type #" = 2 or "I ". "Type #" = 3 or "I ". "Type #" = 4 or "I ". "Type #" = 6 or "I ". "Type #" = 7 or "I ". "Type #" = 9) 23-access ("I ". "OBJ #" =: B1) 27-access ("L ". "Owner #" = "U ". "user #") </P> <p> 58 rows selected. </P> <p> elapsed: 00:00:00. 04 </P>

 

<P> SQL> set autotrace; usage: set autot [race] {off | on | trace [only]} [exp [lain] [stat [istics] SQL> set autotrace on; SQL> select count (*) from dba_objects, dba_objects; 2508707569 <<count () </P> <p> elapsed: 00:01:41. 97 <<consumed 1 minute and 41.97 seconds </P> <p> execution plan ------------------------------------------------------ plan hash value: 2343274122 </P> <p> bytes | ID | operation | Name | rows | bytes | cost (% CPU) | time | percent | 0 | SELECT statement | 1 | 7187 K (5) | 23:57:25 | 1 | sort aggregate | 1 | 2 | merge join Cartesian | 2325m | 7187 K (5) | 23:57:25 | 3 | View | dba_objects | 48221 | 149 (5) | 00:00:02 | 4 | Union-all | * 5 | filter | * 6 | hash join | 51192 | 4099k | 148 (5) | 00:00:02 | 7 | table access full | user $ | 59 | 177 | 2 (0) | 00:00:01 | * 8 | table access full | OBJ $ | 51192 | 3949k | 145 (5) | 00:00:02 | * 9 | table access by index rowid | ind $ | 1 | 8 | 2 (0) | 00:00:01 | * 10 | index unique scan | I _ind1 | 1 | 1 (0) | 00:00:01 | 11 | nested loops | 1 | 16 | 1 (0) | 00:00:01 | 12 | index full scan | I _link1 | 1 | 13 | 0 (0) | 00:00:01 | 13 | Table Access Cluster | user $ | 1 | 3 | 1 (0) | 00:00:01 | * 14 | index unique scan | I _user # | 1 | 0 (0) | 00:00:01 | 15 | buffer sort | 48221 | 7187 K (5) | 23:57:25 | 16 | View | dba_objects | 48221 | 149 (5) | 00:00:02 | 17 | Union-all | * 18 | filter | * 19 | hash join | 51192 | 4099k | 148 (5) | 00:00:02 | 20 | table access full | user $ | 59 | 177 | 2 (0) | 00:00:01 | * 21 | table access full | OBJ $ | 51192 | 3949k | 145 (5) | 00:00:02 | * 22 | table access by index rowid | ind $ | 1 | 8 | 2 (0) | 00:00:01 | * 23 | index unique scan | I _ind1 | 1 | 1 (0) | 00:00:01 | 24 | nested loops | 1 | 16 | 1 (0) | 00:00:01 | 25 | index full scan | I _link1 | 1 | 13 | 0 (0) | 00:00:01 | 26 | Table Access Cluster | user $ | 1 | 3 | 1 (0) | 00:00:01 | * 27 | index unique scan | I _user # | 1 | 0 (0) | 00:00:01 | reset </P> <p> predicate information (identified by Operation ID): ------------------------------------------------- </P> <p> 5-filter ("O ". "Type #" <> 1 and "O ". "Type #" <> 10 or "O ". "Type #" = 1 and (select 1 from "sys ". "IND $" "I" where "I ". "OBJ #" =: B1 and ("I ". "Type #" = 1 or "I ". "Type #" = 2 or "I ". "Type #" = 3 or "I ". "Type #" = 4 or "I ". "Type #" = 6 or "I ". "Type #" = 7 or "I ". "Type #" = 9) = 1) 6-access ("O ". "Owner #" = "U ". "user #") 8-filter (bitand ("O ". "Flags", 128) = 0 and "O ". "name" <> '_ next_object' and "O ". "name" <> '_ default_auditing_options _' and "O ". "linkname" is null) 9-filter ("I ". "Type #" = 1 or "I ". "Type #" = 2 or "I ". "Type #" = 3 or "I ". "Type #" = 4 or "I ". "Type #" = 6 or "I ". "Type #" = 7 or "I ". "Type #" = 9) 10-access ("I ". "OBJ #" =: B1) 14-access ("L ". "Owner #" = "U ". "user #") 18-filter ("O ". "Type #" <> 1 and "O ". "Type #" <> 10 or "O ". "Type #" = 1 and (select 1 from "sys ". "IND $" "I" where "I ". "OBJ #" =: B1 and ("I ". "Type #" = 1 or "I ". "Type #" = 2 or "I ". "Type #" = 3 or "I ". "Type #" = 4 or "I ". "Type #" = 6 or "I ". "Type #" = 7 or "I ". "Type #" = 9) = 1) 19-access ("O ". "Owner #" = "U ". "user #") 21-filter (bitand ("O ". "Flags", 128) = 0 and "O ". "name" <> '_ next_object' and "O ". "name" <> '_ default_auditing_options _' and "O ". "linkname" is null) 22-filter ("I ". "Type #" = 1 or "I ". "Type #" = 2 or "I ". "Type #" = 3 or "I ". "Type #" = 4 or "I ". "Type #" = 6 or "I ". "Type #" = 7 or "I ". "Type #" = 9) 23-access ("I ". "OBJ #" =: B1) 27-access ("L ". "Owner #" = "U ". "user #") </P> <p> Statistics limit 15 recursive call0 db block gets 10208 consistent gets 0 physical reads 0 redo size 519 bytes sent via SQL * Net to client 492 bytes encoded ed via SQL * net From Client 2 SQL * Net roundtrips to/from client 1 sorts (memory) 0 sorts (Disk) 1 rows processed <1 message is returned. result set </P>

Therefore, the explain Plan often does not reflect the actual execution plan, especially in the application of variable binding. The F5 of PLSQL/developer can quickly view the execution plan, which is actually the explain Plan method.

If you want to know the real execution plan of the SQL statement, you may use the following methods:

O set auotrace

O 10046

O dbms_xplan.display_coursor or dbms_xplan.display_awr

O query v $ SQL _plan

Two Methods for viewing execution plans: Explain plan and autotrace