Considerable overhead when optimizer_capture_sql_plan_baselines is set to TRUE (Doc ID 1304775.1)

Last updated on SEPTEMBER 21, 2015

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.2 to 11.2.0.2 [Release 11.2]
Information in this document applies to any platform.
RDBMS 11.2


Symptoms

You run something like following:

declare
cursor cur1 is select c1 from t1;
begin
for i in 1..10000 loop
for j in cur1 loop
if j.c1 = 1 then
update t1 set c2=c2*1*1 where c1=1;
end if;
end loop;
commit;
end loop;
end;
/


Note we are sitting in a loop and calling an update statement.

If optimizer_capture_sql_plan_baselines is set to FALSE the execution time is fast:

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 9 0.04 0.04 0 2 0 0
Execute 30006 18.49 18.77 1 70030 40014 10000
Fetch 10008 4.43 4.57 0 70024 0 10004
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 40023 22.96 23.39 1 140056 40014 20004


..but turn it on and the performance drops:

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 60198 15.84 16.07 0 2 1 0
Execute 90254 740.40 771.79 12065 390402 305280 40004
Fetch 60534 12.30 13.06 118 111889 0 40340
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 210986 768.54 800.93 12183 502293 305281 80344

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms