How to quickly obtain a real execution plan

Preparations: createtablezbdbaasselect * fromdba_objects; createtablezbdba1asselect * fromdba_objects; createindexzbdba_owneronzbdba (owner); users (user, ZBDBA,

Preparations: create table zbdba as select * from dba_objects; create table zbdba1 as select * from dba_objects; create index zbdba_owner on zbdba (owner); create index zbdba1_owner on zbdba1 (owner ); exec dbms_stats.gather_table_stats (user, ZBDBA,

Preparations:

create table zbdba as select * from dba_objects;create table zbdba1 as select * from dba_objects;create index zbdba_owner on zbdba(owner);create index zbdba1_owner on zbdba1(owner);exec dbms_stats.gather_table_stats(user, 'ZBDBA', method_opt => 'FOR ALL COLUMNS SIZE 1');exec dbms_stats.gather_table_stats(user, 'ZBDBA1', method_opt => 'FOR ALL COLUMNS SIZE 1');

We usually view the execution plan for SQL statements that take a long time to execute:

explain plan for select zbdba.object_name from zbdba,zbdba1 where zbdba.owner=zbdba1.ownerSCOTT@orcl11g>select plan_table_output from table(dbms_xplan.display());PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 1287183320----------------------------------------------------------------------------------------------| Id  | Operation             | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |----------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT      |              |   207M|  7330M|       |  1198  (55)| 00:00:15 ||*  1 |  HASH JOIN            |              |   207M|  7330M|  1272K|  1198  (55)| 00:00:15 ||   2 |   INDEX FAST FULL SCAN| ZBDBA1_OWNER | 72068 |   422K|       |    48   (0)| 00:00:01 ||   3 |   TABLE ACCESS FULL   | ZBDBA        | 72067 |  2181K|       |   288   (1)| 00:00:04 |----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("ZBDBA"."OWNER"="ZBDBA1"."OWNER")15 rows selected.

However, such execution plans are not necessarily accurate. So how can we get an accurate execution plan quickly?

The actual execution plan is the executed SQL

So

Select zbdba. object_name from zbdba, zbdba1 where zbdba. owner = zbdba1.owner; you do not need to wait all the time. Press ctrl + c to interrupt SCOTT @ orcl11g> select SQL _text, SQL _id, hash_value, child_number from v $ SQL where SQL _text like 'select zbdba. object_name from zbdba, zbdba1 where zbdba. owner = zbdba1.owner % '; SQL _TEXT SQL _ID HASH_VALUE CHILD_NUMBER -------------------------- ---------- ------------ select zbdba. object_name from zbdba, zbdba1 where zbdba. owner limit 2043857654 0 = zbdba1.ownerSCOTT @ orcl11g> select * from table (partition ('fr4g7ypwx5krq', 0, 'allstats LAST '); PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------SQL_ID fr4g7ypwx5krq, child number 0 limit select zbdba. object_name from zbdba, zbdba1 wherezbdba. owner = zbdba1.ownerPlan hash value: 1287183320 partitions | Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem | partitions | 0 | select statement |||||| * 1 | hash join | | 207M | 3024K | 1862K | 7066 K (0) | 2 | index fast full scan | zbdbaw.owner | 72068 | 3 | table access full | ZBDBA | 72067 | descripredicate Information (identified by operation id ): ------------------------------------------------- 1-access ("ZBDBA ". "OWNER" = "ZBDBA1 ". "OWNER") Note ------Warning: basic plan statistics not available. these are only collected when: * hint 'gather _ plan_statistics 'is used for the statement or * parameter 'statistics _ level' is set to 'all ', at session or system level27 rows selected.

In this way, the real execution plan is quickly transferred from the memory.