Use Explain Plan in TOAD

[oracle_new]

Hi guys, i realise that we can use explain plan in Toad also, but some of my colleagues say that explain plans should only be used from SQL Plus. Is it that Explain Plan from Toad are not reliable?

[rajavu]

Toad Explain plan information is absolutely reliable .

Check these links in case of any issues

link1
link2

[tyro]

TOAD issues 'explain plan set statement_id = ... into plan_table for 'select ...' in the background when you use the Explain Plan, so there's really no difference whatsoever.

[zargon]

There can be differences, as the PLAN_TABLE definition provided by Quest may be out of date. Yes, Oracle can still use the table, and what plan information it can insert will be current, but you are likely to miss details a current PLAN_TABLE definition would contain, such as predicate and filter information, estimated TEMP space consumption, estimated execution time and the option to gather plan statistics to produce a more detailed report:

SQL> select * from table(dbms_xplan.display_cursor('dmfdcmvwkgfqh',0, 'ALL ALLSTATS'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
--
SQL_ID dmfdcmvwkgfqh, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ distinct owner from v$access

Plan hash value: 174934893

------------------------------------------------------------------------------------------------------------------------------------------------------
---
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | OMem | 1Mem | O/1/M
|
------------------------------------------------------------------------------------------------------------------------------------------------------
---
| 1 | HASH UNIQUE | | 1 | 15 | 1305 | 5 (100)| 00:00:01 | 4 |00:00:00.10 | | |
|
| 2 | NESTED LOOPS | | 1 | 433 | 37671 | 4 (100)| 00:00:01 | 394 |00:00:00.09 | | |
|
| 3 | NESTED LOOPS | | 1 | 433 | 29877 | 3 (100)| 00:00:01 | 394 |00:00:00.06 | | |
|
|* 4 | HASH JOIN | | 1 | 433 | 16021 | 1 (100)| 00:00:01 | 335 |00:00:00.03 | 1236K| 1236K| 1/0
/0|
|* 5 | FIXED TABLE FULL | X$KSUSE | 1 | 170 | 2040 | 0 (0)| | 170 |00:00:00.01 | | |
|
| 6 | FIXED TABLE FULL | X$KGLLK | 1 | 433 | 10825 | 1 (100)| 00:00:01 | 335 |00:00:00.01 | | |
|
|* 7 | FIXED TABLE FIXED INDEX| X$KGLDP (ind:1) | 335 | 1 | 32 | 0 (0)| | 394 |00:00:00.03 | | |
|
|* 8 | FIXED TABLE FIXED INDEX | X$KGLOB (ind:1) | 394 | 1 | 18 | 0 (0)| | 394 |00:00:00.03 | | |
|
------------------------------------------------------------------------------------------------------------------------------------------------------
---

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$5C160134
5 - SEL$5C160134 / S@SEL$3
6 - SEL$5C160134 / L@SEL$3
7 - SEL$5C160134 / D@SEL$3
8 - SEL$5C160134 / O@SEL$3

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("L"."KGLLKUSE"="S"."ADDR")
5 - filter("S"."INST_ID"=USERENV('INSTANCE'))
7 - filter(("L"."KGLLKHDL"="D"."KGLHDADR" AND "L"."KGLNAHSH"="D"."KGLNAHSH"))
8 - filter(("O"."KGLNAHSH"="D"."KGLRFHSH" AND "O"."KGLHDADR"="D"."KGLRFHDL"))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "O"."KGLNAOWN"[VARCHAR2,64]
2 - "O"."KGLNAOWN"[VARCHAR2,64]
3 - "D"."KGLRFHDL"[RAW,8], "D"."KGLRFHSH"[NUMBER,22]
4 - (#keys=1) "L"."KGLNAHSH"[NUMBER,22], "L"."KGLLKHDL"[RAW,8]
5 - "S"."ADDR"[RAW,8], "S"."INST_ID"[NUMBER,22]
6 - "L"."KGLLKUSE"[RAW,8], "L"."KGLLKHDL"[RAW,8], "L"."KGLNAHSH"[NUMBER,22]
7 - "D"."KGLHDADR"[RAW,8], "D"."KGLNAHSH"[NUMBER,22], "D"."KGLRFHDL"[RAW,8], "D"."KGLRFHSH"[NUMBER,22]
8 - "O"."KGLHDADR"[RAW,8], "O"."KGLNAOWN"[VARCHAR2,64], "O"."KGLNAHSH"[NUMBER,22]


48 rows selected.

Remember, too, that EXPLAIN PLAN is an estimated execution path for the query, based upon cursory examination of relevant statistics; the query is not executed so it doesn't go through the same process of parsing and plan generation as an executed query would. Setting autotrace on returns a plan based upon the actual execution of the query, and the plans returned by EXPLAIN PLAN and autotrace can differ.