Forum Stats

  • 3,768,181 Users
  • 2,252,755 Discussions
  • 7,874,485 Comments

Discussions

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

Kinjan Bhavsar
Kinjan Bhavsar Member Posts: 78 Red 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.

Answers

  • User_H3J7U
    User_H3J7U Member Posts: 640 Silver Trophy

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

  • Kinjan Bhavsar
    Kinjan Bhavsar Member Posts: 78 Red 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: 640 Silver Trophy
    create or replace procedure proc1 as
    begin
     for c in (select /*+ qb_name(query001)*/ * from dual) loop
       null;
     end loop;
    end;
    /
    
    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;
    
    OPERATION        OPTIONS      OBJECT_NAME  
    ---------------- ------------ -----------
    SELECT STATEMENT                                  
     TABLE ACCESS    STORAGE FULL DUAL
    


  • Kinjan Bhavsar
    Kinjan Bhavsar Member Posts: 78 Red 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: 9,786 Gold Crown

    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 https://jonathanlewis.wordpress.com/2014/05/22/sql_trace/ 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.


    Regards

    Jonathan Lewis