How to get Full Plan in Oracle?

All we need is an easy explanation of the problem, so here it is.

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 [email protected] 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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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:

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags:

Leave a Reply