developers

    Forum Stats

  • 3,873,729 Users
  • 2,266,635 Discussions
  • 7,911,624 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: 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.

Answers

  • 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
    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: 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 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

developers