0

It seems Oracle's execution plan indent each row for just one character. See below image. Is there a setting to increase this value? To me, this is not very readable.

enter image description here

3
  • 1
    You are asking about a tool that displays some data. You want to change the way how the tool displays the data. So you have to tell us which tool you use to display the data. It is not a property of the database you are asking about.
    – miracle173
    Aug 1, 2019 at 5:47
  • I use DataGrip, but the result is exactly the same as sqlplus. I guess they use the same mechanism underneath. Aug 1, 2019 at 5:49
  • Instead of using dbms_xplan package to display the plan, you can write your own (recursive) statement that retrieves the plan from plan_table and uses a different number of spaces to indent. Pretty much the way it was done before Oracle introduced dbms_xplan.display()
    – user1822
    Aug 1, 2019 at 7:34

1 Answer 1

1

You can use PLAN_TABLE for such result as following:

Your statement for generating the execution plan

EXPLAIN PLAN 
SET STATEMENT_ID = 'mystatement1'  -- give your statement a meaningful ID so that we can fetch it from PLAN_TABLE using it
    FOR
SELECT
    *
FROM
    ACTIVE_USERS;

Now, Fetching the execution plan from PLAN_TABLE

SELECT
    ID,
   -- LPAD(' ', LEVEL - 1) -- default format
    LPAD(' ', LEVEL * 2) -- Indentation is done using this
    || OPERATION
    || ' '
    || OPTIONS
    || ' '
    || OBJECT_NAME "Operation",
    OBJECT_NAME   AS "Name"
FROM
    PLAN_TABLE
CONNECT BY PRIOR ID = PARENT_ID
           AND PRIOR STATEMENT_ID = STATEMENT_ID START WITH ID = 0
                AND STATEMENT_ID = 'mystatement2'
ORDER BY
    ID;

Output:

enter image description here

You can add more columns into result according to your need and also add more spaces using LPAD in the above query.

Cheers!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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