Oracle Scratchpad

December 14, 2006

Constraints, Inserts and bind

Filed under: Infrastructure,Performance,Tuning — Jonathan Lewis @ 10:25 pm BST Dec 14,2006

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.

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.

10 Comments »

  1. So does that imply that the parsed version of the SQL statement has keep the information about all the constraints that that statement could possibly trip–if it doesn’t look at cdef$ again each time it’s run?

    For a table with a lot of constraints, couldn’t that get big?

    Comment by Jason Bucata — December 14, 2006 @ 11:03 pm BST Dec 14,2006 | Reply

  2. Jason, Good question.

    create table t1 (n1 number check (n1 = 1), ...
    

    for 255 columns.

    insert /*+ tagged */ into t1 values (1,1,1...)
    

    for 255 columns.
    Track down the SQL statement in query v$db_object_cache to check the shared memory:

    select  sharable_mem, name 
    from    v$db_object_cache 
    where   name like '%tagged%'
    and     name not like '%db_object_cache%'
    ;
    

    A test with 255 constraints in place used 170KB of memory, repeated without constraints the demand was 80KB.

    Worth knowing about the extra memory requirements – on the other hand I’d rather use the memory than allow the data to be wrong.

    Comment by Jonathan Lewis — December 15, 2006 @ 7:37 am BST Dec 15,2006 | Reply

  3. support!

    Comment by globelovers — December 15, 2006 @ 6:00 pm BST Dec 15,2006 | Reply

  4. Jonathan,

    A small confusion in your example, you define column v1 datatype as varchar, but, you say,

    >>Oracle doesn’t keep long columns cached in the dictionary cache (rowcache)

    Is this same with column have other datatype than long?

    Jaffar

    Comment by Syed Jaffar Hussain — December 15, 2006 @ 6:22 pm BST Dec 15,2006 | Reply

  5. Jaffar, the long column in question is the definition column of cdef$. It has nothing to do with the type of the column in the user’s table.

    Comment by Jonathan Lewis — December 15, 2006 @ 6:30 pm BST Dec 15,2006 | Reply

  6. Thanks for clarification. I got it.

    Jaffar

    Comment by Syed Jaffar Hussain — December 15, 2006 @ 7:01 pm BST Dec 15,2006 | Reply

  7. I generally agree that it’s a worthwhile use of memory. My current job has me thinking a lot about the shared pool, since we’ve got a lot of shared pool pressure, so that’s just something I’ve started glomming onto…

    Comment by Jason Bucata — December 18, 2006 @ 3:42 pm BST Dec 18,2006 | Reply

  8. I tried a similar test with SELECT and saw the same thing going on with cdef$. I can understand needing the constraint during an insert, but why does Oracle need it during a query? I’m running 10.2.0.3 on Solaris SPARC.
    declare
            n1 number;
            v1 varchar2(10);
            v_cmd varchar2(256);
    begin
            for i in 1..1000 loop
                v_cmd := ‘select n1, v1 from t1 where n1 = ‘ || i;
                    execute immediate v_cmd into n1, v1;
            end loop;
    end;
    /

    Comment by Robert Winter — June 28, 2007 @ 2:30 am BST Jun 28,2007 | Reply

  9. Robert, since 9i the optimizer has been able to combine existing predicates with constraints to generate new predicates. I suspect you have a constraint on column n1 that is being read each time you optimise a new query. The scale of activity is much less for queries, though, as it only applies to the where clauses, not to every column in the table.

    Comment by Jonathan Lewis — June 28, 2007 @ 10:06 am BST Jun 28,2007 | Reply

  10. [...] As usual my master comes to the rescue with this note – Constraints, Inserts and bind [...]

    Pingback by latch: row cache objects « Oracle — January 25, 2012 @ 10:07 pm BST Jan 25,2012 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,084 other followers