Re: What this is?

When a statement fails because due to a constraint problem, the error messages reports the name and owner of the constraint. Since these are a waste of space in the dictionary cache (and should never be needed ;) they have to be looked up every time.

Typical cause is code like:

loop

    insert into table value (......)
exception

    when duplicate key then

        update table where ...
end loop

As a general rule, updating and finding
no rows is a lot cheaper than inserting
and failing unless the number of duplicates is known to be a very small fraction of the data.

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated Sept 19th  

: Subject: What this is?
: Date: Fri, 12 Nov 2004 14:46:03 -0600
: From: "Stephen Lee" <Stephen.Lee_at_DTAG.Com>
:
: select c.name, u.name
: from con$ c, cdef$ cd, user$ u=20
: where c.con# =3D cd.con# and cd.enabled =3D :1 and c.owner# =3D u.user#;
:
: After some concern about a database running slower than desired and
: slower than normal, the statement that shows up as being the biggest for
: the number executions and buffer gets (but NOT buffer gets per
: execution) is above. I know that a large table is getting data
: inserted, and there are two unique constraints, one primary key, three
: foreign keys, and a few not null constraints on the table. The table
: has 321 million rows, and sum(bytes) from dba_segments comes back with
: 44,669,337,600.
:

--
http://www.freelists.org/webpage/oracle-l