Another thing to look for is the (poor) practice of people leaving their check constraints with
auto-generated names (SYS_C....). When you export/import et al, you can get 'n' copies of the
same constraint.
My favourite example is a vendor that once sent us a seed data export dump file with some 300
copies of the same constraint on SYSTEM.DEF$...(something) because they always unload/reloaded
with a FULL=Y. This was picked up because it took longer to import this (virtually empty) table
then any table in the import!
hth
connor
- Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:
>
>
> 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
>
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
ISBN: 1590592174
web:
http://www.oracledba.co.uk
web:
http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com
Coming Soon! "Oracle Insight - Tales of the OakTable"
"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day"
Win a castle for NYE with your mates and Yahoo! Messenger
http://uk.messenger.yahoo.com
--
http://www.freelists.org/webpage/oracle-l
Received on Sat Nov 13 2004 - 22:05:16 CST
