0

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
0
3

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.