Forum Stats

  • 3,873,729 Users
  • 2,266,635 Discussions


How to check explain plan of a nested table query in SQL Developer or by some query?

Kinjan Bhavsar
Kinjan Bhavsar Member Posts: 95 Blue Ribbon

Hi All,

I have a PL/SQL block in which I have used TYPE and I am querying a large table and using bulk collect storing the records into TYPE example:- tasks table so TASK_TYPE.

Then, after this, I am using this type in other queries using IN clause as the example shown below

select a1, a2 from table1 where task_id in (select t.task_id from TABLE(TASK_TYPE) t)

So, now in some cases, the query takes time and as it is a nested table, I can't see an explain plan in SQL Developer, it gives me an error regarding nested table. Is there a way I can find the explain plan of a query using nested table type? Is there a way, I can write some code in PL/SQL itself which stored the value in some table, etc?

I tried using a dummy table but that won't give me an exact idea of what exactly is happening in real time.


  • User_H3J7U
    User_H3J7U Member Posts: 1,062 Gold Trophy

    Execute pl/sql and search the real plan in v$sql_plan.

  • Kinjan Bhavsar
    Kinjan Bhavsar Member Posts: 95 Blue Ribbon

    @User_H3J7U Thanks but is it possible for you to share a simple example with some dummy code?

  • User_H3J7U
    User_H3J7U Member Posts: 1,062 Gold Trophy
    create or replace procedure proc1 as
     for c in (select /*+ qb_name(query001)*/ * from dual) loop
     end loop;
    exec proc1
    select lpad(' ', depth*2)||operation operation, options , object_name
    from (
     select max(case when qblock_name='QUERY001' then 1 end) over(partition by address) query001, sp.* from v$sql_plan sp
    ) where query001=1
    order by address, id;
    ---------------- ------------ -----------
    SELECT STATEMENT                                  

  • Kinjan Bhavsar
    Kinjan Bhavsar Member Posts: 95 Blue Ribbon

    @User_H371U Thanks for the quick response.

    I tried and it worked only for the first query in my PLSQL block. When I add multiple queries, it only gives me results for the first query even though both queries has qb_name different.

    Also, are any other details available like cost, IO, joins applied etc which can help me more to understand the issue?

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,116 Blue Diamond

    Can you access the database trace subdirectory - if so you can the SQL trace event for just that procedure (if you know its SQL_ID) every time it runs, and then check the trace file for execution plans and - if you want them - values used for bind variables.

    See for a few comments on the mechanism. You may have to call on your DBA to assist you with the trace files and generating summary (tkprof) files from them.


    Jonathan Lewis