Actual Explain Plan (Execute Plan) in Oracle

In order to find the actual explain plan used to execute your query, otherwise known as the execute plan as opposed to the explain plan, you need to use dbms_xplan.display_cursor.

First execute the query you are interested in, and optionally include the gather_plan_statistics hint:

select /*+gather_plan_statistics */* 
from foo 
where bar = 5;

Then execute the following to hopefully read the execute plan:

select * from table(
    dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST')
);

However, this doesn't always work. It seems like when you pass in null for the first parameter (the sql_id), it will return the most recent query executed by the system, which may not necessarily be your query.

A better approach is to add some comment in your SQL, such as your username, and then go find the sql_id from v$sql:

select /*matthewmoisen*/ * from foo where bar = 5;

Now query v$sql to get the sql_id and optionally the child_number:

select sql_id, child_number, sql_text
from v$sql
where 1=1
    and sql_text like '%matthewmoisen%'
    and sql_text NOT LIKE '%v$sql%'
;

This might return something like:

gqtyhg12xw36c   0        select /*matthewmoisen*/ ...

Now we can run dbms_xplan.display_cursor with the sql_id and optionally the child_number:

select *
from table(
    dbms_xplan.display_cursor(
        'gqtyhg12xw36c',
        null, -- Optionally 0 for the child_number
        'ALLSTATS LAST'
    )
);

We can save a step by doing it in one query:

select *
from table(
    dbms_xplan.display_cursor(
        (
            select max(sql_id)
            from v$sql
            where 1=1
                and sql_text like '%matthewmoisen%'
                and sql_text not like '%v$sql%'
        ),
        null,
        'ALLSTATS LAST'
    )
)

Let's wrap this up in a function so we can reuse it:

CREATE OR REPLACE FUNCTION execute_plan(
    p_comment_i VARCHAR2,
    p_format_i VARCHAR2 := 'ALLSTATS LAST'
)
RETURN sys.odcivarchar2list PIPELINED
IS
    l_sql_id VARCHAR2(32);
    l_child_number NUMBER;
BEGIN
    SELECT sql_id, MAX(child_number)
    INTO l_sql_id, l_child_number
    FROM v$sql
    WHERE 1=1
        AND sql_text like '%' || p_comment_i || '%'
        AND sql_text NOT LIKE '%v$sql%'
        AND UPPER(sql_text) NOT LIKE '%EXECUTE_PLAN(''' || p_comment_i || '''%'
    GROUP BY sql_id
    FETCH FIRST 1 ROWS ONLY
    ;

    FOR i IN (
        SELECT plan_table_output line
        FROM TABLE(
            DBMS_XPLAN.DISPLAY_CURSOR(
                l_sql_id,
                l_child_number,
                p_format_i
            )
        )
    ) LOOP
        PIPE ROW (i.line);
    END LOOP;

    RETURN;
END;
/

And we can call it like:

select * from execute_plan('matthewmoisen');

Actual Explain Plan (Execute Plan) in Oracle

Comments

Add Comment