how to obtain execution plan from a PLSQL-CodePudding

I have a code snippet like :

declare
    cursor c is select ....
begin
    for i in c loop
        update table t1 where ti.c1 = i.column ...
    end loop;
end;

I am wondering how to obtain an execution plan from the update statement since there is involved a condition between a column from a table and a column from cursor.

Can anyone give me a hint, please?

Thank you.

CodePudding user response：

The simplest way might be to capture the plan while it's executing, by noting the sql_id and sql_child_number from v$session, and running

select plan_table_output
from   table(dbms_xplan.display_cursor(sql_id, sql_child_number));

Alternatively, you can embed the call in the PL/SQL for testing purposes and have it output the plan using dbms_output.put_line. (The plan will be the same for every loop iteration, unless you want to check the actual row counts etc, so I have added an exit; statement to make it stop after one execution.)

declare
    cursor c is select * from departments;
begin
    for r in c loop
        update employees e set e.email = e.email
        where  e.department_id = r.department_id;
        
        for p in (
            select p.plan_table_output
            from   table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')) p
        )
        loop
            dbms_output.put_line(p.plan_table_output);
        end loop;
        
        rollback;
        exit;
    end loop;
end;

Which in my 19c demo HR schema gives:

SQL_ID  b5xbwbu4kd3r4, child number 1
-------------------------------------
UPDATE EMPLOYEES E SET E.EMAIL = E.EMAIL WHERE E.DEPARTMENT_ID = :B1
 
Plan hash value: 4075606039
 
-------------------------------------------------------------------------------------------------
| Id  | Operation         | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT  |                   |      1 |        |      0 |00:00:00.01 |       4 |
|   1 |  UPDATE           | EMPLOYEES         |      1 |        |      0 |00:00:00.01 |       4 |
|*  2 |   INDEX RANGE SCAN| EMP_DEPARTMENT_IX |      1 |      1 |      1 |00:00:00.01 |       1 |
-------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("E"."DEPARTMENT_ID"=:B1)

To get the actual row counts as displayed above you would need to

alter session set statistics_level = all;

or else include the /* gather_plan_statistics */ hint in the update.

CodePudding user response：

That's just an ordinary UPDATE statement. As it is in a loop, it executes row-by-row (returned from the cursor) (it might, though, update multiple rows at once, depending on its (UPDATE's) WHERE clause).

Therefore, you'd just "extract" it from the loop and explain it, alone, e.g. if it is

cursor c is
  select id, job from emp_2;
begin
  for i in c loop
    update emp e set e.job = i.job where e.id = i.id;
  end loop;
end;

Suppose one of rows returned by cursor has id = 7369, job = CLERK; then

explain plan for update emp e set e.job = 'CLERK' where e.id = 7369;

select * from plan_table;