Bind variable in PLSQL block vs bare delete using literal value
Hi,
Can anyone tell me the performance difference in terms of parsed calls and CPU time between the following method of deletion? It is said that using bind variable is better. However I found that using bind variable in PLSQL block does not do me any good because Oracle has to parse PLSQL block as extra for each delete call. If I set cursor_sharing to 'SIMILAR' then with Oracle generated bind variable the (DELETE FROM test WHERE a = 18) uses less time.
CREATE TABLE test (a NUMBER, b VARCHAR2(30), CONSTRAINT testp1 PRIMARY KEY (a));
DELETE FROM test WHERE a = 18
BEGIN EXECUTE IMMEDIATE 'DELETE FROM test WHERE a = :1' USING 17; END;
Can anyone tell me the performance difference in terms of parsed calls and CPU time between the following method of deletion? It is said that using bind variable is better. However I found that using bind variable in PLSQL block does not do me any good because Oracle has to parse PLSQL block as extra for each delete call. If I set cursor_sharing to 'SIMILAR' then with Oracle generated bind variable the (DELETE FROM test WHERE a = 18) uses less time.
CREATE TABLE test (a NUMBER, b VARCHAR2(30), CONSTRAINT testp1 PRIMARY KEY (a));
DELETE FROM test WHERE a = 18
BEGIN EXECUTE IMMEDIATE 'DELETE FROM test WHERE a = :1' USING 17; END;
0