How to get Full Plan in Oracle?

Question

I'm still a novice in SQL, and I would really appreciate it if you could help me. How do I get SQL Plan for my query? I have used this for getting a short plan:

EXPLAIN PLAN FOR
SELECT *
  FROM custinfo@smartdev2 s_cs
 WHERE s_cs.idcust = (SELECT b_cust_id
                        FROM mlb_test.mt_operation_out
                       WHERE id = 1230);

SELECT plan_table_output
  FROM TABLE (DBMS_XPLAN.display ('plan_table', NULL, 'basic'));

But what do i need to use for me to get entire execution plan?

• Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.3.0.0.0

Answer 1

For starters, the 'basic' format will only display minimal information. Other formats include 'TYPICAL', 'SERIAL', 'ALL', and (undocumented) 'ADVANCED'. From the documentation for DBMS_XPLAN it accepts four values:

BASIC: Displays the minimum information in the plan—the operation ID, the operation name and its option.

TYPICAL: This is the default. Displays the most relevant information in the plan (operation id, name and option, #rows, #bytes and optimizer cost). Pruning, parallel and predicate information are only displayed when applicable. Excludes only PROJECTION, ALIAS and REMOTE SQL information (see below).

SERIAL: Like TYPICAL except that the parallel information is not displayed, even if the plan executes in parallel.

ALL: Maximum user level. Includes information displayed with the TYPICAL level with additional information (PROJECTION, ALIAS and information about REMOTE SQL if the operation is distributed).

SELECT plan_table_output
  FROM TABLE (DBMS_XPLAN.display ('PLAN_TABLE', NULL, 'ALL'));

Check out this white paper from Oracle for additional details:

• The Oracle Optimizer: Explain the Explain Plan