Query execution is slow in PL/SQL anonymous block

Question

The query runs slow in Oracle PL/SQL. It takes about 8 minutes in PL/SQL whereas it takes only 4 seconds when run in SQL Editor or SQL Plus.

Please let me know what is causing this. Is it possible that a different execution plan is picked up by SQL and PL/SQL ?

----SQL Editor query---takes 4 seconds---400 row count--
SELECT count(*) FROM
(
SELECT col1, col2
FROM
my_tab1 t1, my_tab2 t2
WHERE
t1.pk_col1=t2.pk_col1
and t1.created_date < t2.created_date
)


--PL/SQL Code--takes about 8 minutes---400 row rount--
DECLARE 
v_cnt   PLS_INTEGER:=0;
BEGIN
SELECT count(*) INTO v_cnt 
FROM
(
SELECT col1, col2
FROM
my_tab1 t1, my_tab2 t2
WHERE
t1.pk_col1=t2.pk_col1
and t1.created_date < t2.created_date
)

END;
/

Answer 1

The easiest way to capture the execution plan within an anonymous block is to call dbms_xplan.display_cursor in a loop and print each line of output:

declare
    v_cnt pls_integer;
begin
    execute immediate 'alter session set statistics_level = ALL';

    select count(*) into v_cnt 
    from
    (
        select col1, col2
        from   my_tab1 t1, my_tab2 t2
        where  t1.pk_col1 = t2.pk_col1
        and    t1.created_date < t2.created_date
    );

    for r in (
        select p.plan_table_output
        from   table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST -OUTLINE +NOTE +PREDICATE +IOSTATS +REPORT')) p
    )
    loop
        dbms_output.put_line(r.plan_table_output);
    end loop;
end;

You can make the same call from a SQL*Plus command prompt immediately after executing a SQL statement, but you first have to disable dbms_output, as otherwise the SQL statement is not the 'last' statement you made. (You can also specify the sql_id if you know it.) For more details see the dbms_xplan documentation.

set serverout off

alter session set statistics_level = ALL;

select count(*)
from
(
    select col1, col2
    from   my_tab1 t1, my_tab2 t2
    where  t1.pk_col1 = t2.pk_col1
    and    t1.created_date < t2.created_date
);

select p.plan_table_output
from   table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST +OUTLINE +ADAPTIVE +PARTITION +NOTE')) p;

For a handy script to call this in one line, see www.williamrobertson.net/documents/xplanx.html. Then it's just

select count(*)
from
(
    select col1, col2
    from   my_tab1 t1, my_tab2 t2
    where  t1.pk_col1 = t2.pk_col1
    and    t1.created_date < t2.created_date
)

@xplanx