Performance / Explain plan for delete statements

Question

How to check the Performance plan for delete statements since the Explain Plan is not showing the cost /Cardinality (showing only for select statements)

Or is there any other way to check the performance for delete statements in Oracle?

Delete using 'Where exists'check

DECLARE FTCode VARCHAR (20) := ‘XI-9873’; BEGIN DELETE FROM table1 t1 WHERE EXISTS (select 1 from t1 Where t1.ftcode = FTCode) ;

DELETE FROM table2 t2    
WHERE EXISTS (select 1 from t2 Where t2.ftcodet2 = FTCode) ;

END;

Delete using Count(*)

DECLARE FTCode VARCHAR (20) := ‘XI-9873’; FT_COUNT NUMBER := 0; BEGIN SELECT COUNT (*) INTO FT_COUNT FROM table1 t1 WHERE t1.ftcode = FTCode;

IF FT_COUNT > 0
THEN
    DELETE FROM table1 t1
    WHERE t1.ftcode = FTCode;  
END IF;

FT_COUNT := 0;

SELECT COUNT (*)
  INTO FT_COUNT
  FROM table2 t2
 WHERE t2. ftcodet2 = FTCode;

IF FT_COUNT > 0
THEN
    DELETE FROM table2 t2
    WHERE t2. ftcodet2 = FTCode;
END IF;

END;

Welcome to Stack Overflow. Please take the [tour] to learn how Stack Overflow works and read [ask] on how to improve the quality of your question. Then see [Tips for asking a good Structured Query Language (SQL) question](//meta.stackoverflow.com/questions/271055). Please show your attempts you have tried and the problems/error messages you get from your attempts. — Progman, Apr 07 '23 at 10:36
Post the command you used to generate the execution plan, and the execution plan output. Nobody can answer generic questions like this without seeing what you're seeing. — Paul W, Apr 07 '23 at 13:13
you already posted a question yesterday and were told you don't need to do all these checks to delete a row from the table. There is no execution plan for pl/sql blocks. And you can certainly see an execution plan for a single delete statement. — OldProgrammer, Apr 07 '23 at 15:21
Wanted to check the plan for both type of statements though I informed on the single delete. — Aruna Gopalakrishnan, Apr 07 '23 at 15:52

score 0 · Answer 1 · answered Apr 07 '23 at 16:00

You can't generate a plan from a PL/SQL block. You can only do so for SQL. So, take each of these DELETE statements and pull them out, replacing any PL/SQL variables with bind variable placeholders:

EXPLAIN PLAN FOR
DELETE FROM table1 t1
  WHERE t1.ftcode = :FTCode
/
SELECT * FROM TABLE(dbms_xplan.display())
/

EXPLAIN PLAN FOR
DELETE FROM table2 t2
    WHERE t2. ftcodet2 = :FTCode;
/
SELECT * FROM TABLE(dbms_xplan.display())
/

Note the : symbol. That will make it a bind variable (though you aren't actually binding a value yet). That's enough to get an execution plan.

Now be advised that with heavy data skew and histograms in place with bind peeking enabled, Oracle could generate more than one child cursor due to the bind variable value itself, which could lead to a different plan depending on what value you bind. So sometimes you might want to try a literal:

EXPLAIN PLAN FOR
DELETE FROM table1 t1
  WHERE t1.ftcode = 1234
/
SELECT * FROM TABLE(dbms_xplan.display())
/

To see if it makes a difference in the plan. Even that is not guaranteed to be what your code will do, because a literal may result in a plan different than a bind variable would. The only way to be absolutely sure what plan is being used is to actually observe it upon (or after) being executed. That would require SELECT ANY DICTIONARY privs and some knowledge of how to navigate v$session, v$active_session_history, v$sql and v$sql_plan, which I will leave off any further discussion of to not prolong this post.

One more important point: when you look at a plan, learn to read it and understand what every step is doing and the number of executions of each step and row count coming from each step. Do NOT look at the COST. That is a wasteful piece of information that Oracle should never have exposed. By definition Oracle will always choose the lowest cost plan, by its calculation. If it has made a mistake, then it has calculated the cost incorrectly, and the number is bogus. Therefore, for performance tuning there is no point at all in giving that number the slightest bit of attention. You have to understand how to read plans and understand how Oracle does table access, joins, sorts, etc.. in addition to knowing your data relationships, cardinality and volume in order to know whether it has chosen the right plan.

Performance / Explain plan for delete statements

Delete using 'Where exists'check

Delete using Count(*)

1 Answers1