Constraints, Inserts and bind

Everyone knows that bind variables are an important aid to achieving scalability  in OLTP systems. Here’s an example to show how important they are with insert statements – particularly if you believe in using constraints to enforce data correctness.

rem
rem     Script:         constraints.sql
rem     Author:         Jonathan Lewis
rem     Dated:          June 2006
rem
rem     Last tested
rem             12.1.0.2  [Dec 2017]
rem             10.2.0.1
rem              9.2.0.6
rem

create table t1 (
        n1      number          check (n1 > 0),
        v1      varchar2(10)    check (v1 = upper(v1))
)
;       

begin
	for i in 1..1000 loop
		execute immediate
			'insert into t1 values(' || i || ',''XXX'')';
	end loop;
end;
/

The pl/sql loop is going to produce 1,000 statements like: insert into t1 values(1,’XXX’);

When this has run, take a look in v$sql for statement looking like: select condition from cdef$ where rowid=:1; you’ll find that it has been run 2,000 times. That’s twice for each statement you executed – once for each constraint.

select condition
from
 cdef$ where rowid=:1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse     2000      0.01       0.00          0          0          0           0
Execute   2000      0.00       0.00          0          0          0           0
Fetch     2000      0.00       0.00          0       4000          0        2000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     6000      0.01       0.00          0       4000          0        2000

Oracle doesn’t keep long columns cached in the dictionary cache (rowcache) so every time it optimises a new statement that uses a check constraint (and the condition column is a long column) it has to re-read the constraint definition from the database – just as it does with the view definition when you optimise a statement that uses a view.

So if you have lots of constraints in place, and do lots of separate insert statements – make sure you use bind variables.

Update Dec 2017:

Following an incoming comment I rediscovered this observation about the “unexpected” impact of constraints on inserts that I hadn’t thought about for more than 10 years; so I re-ran the test to check if it were still true – it is.

Update 2 [same day]

After tweeting a link to this note, Mohamed Houri followed up with a reference to a similar problem that appears with column groups in 12cR1:

“A similar parsing issue occurred in 12cR1 due to bug 20907061 (HIGH # OF EXECUTIONS FOR RECURSIVE CALL ON COL$) linked with column group extension having default value > 32 bytes”

47r1y8yn34jmj  select default$ from col$ where rowid=:1

A quick search on MoS (formerly Metalink) suggests that there’s also a patch for this issue needed on 11.2.0.4, and my search also found: Bug 26818146 : HIGHER EXECUTIONS FOR RECURSIVE CALL ON COL$ EVEN AFTER PATCHING 20907061.

At the same time the search reminded me that “as of SCN” queries parse on each call – which means they could see the same issue even if the text of the query doesn’t change! (See Bug 23189809 : AS OF SCN IS SLOWER AFTER UPGRADE TO 12C) – which is marked as not a bug, but references base bug 20907061.