Re: primary keys and dictionary overhead

From: David Fitzjarrell <>
Date: Wed, 19 Oct 2011 13:53:22 -0700 (PDT)
Message-ID: <>

Apparently a few vendors did that 20+ years ago and claimed it was for performance reasons.  Never mind that their code was suspiciously absent of bind variables forcing a hard parse for every run of a query where only the string literal value was changed.  Never mind that their idea of referential integrity was 'enforced' in the application code and not the database.  Never mind that they also enforced uniqueness in the same way.  Which explains why migrating from one of these types of applications to one which uses database constraints requires far more data cleanup than should be necessary.  
It's funny sometimes what vendors do in the name of  'performance'. David Fitzjarrell

From: "Powell, Mark" <> To: ORACLE-L <> Sent: Wednesday, October 19, 2011 1:31 PM Subject: RE: primary keys and dictionary overhead

No unique indexes?  How can you have an OLTP system that performs without unique indexes?  

-----Original Message-----

From: [] On Behalf Of Dba DBA Sent: Wednesday, October 19, 2011 4:09 PM To: ORACLE-L
Subject: primary keys and dictionary overhead

I am working on a nearly 20 year old legacy system. The OLTP peaks at about 100,000 executions/minute and we expect this to continue to increase. When the system first came out, they had to make sure there were no constraints, unique indexes, triggers, synonyms or anything to make sure the system would perform. I can understand that 20 years ago. We just started adding some unique constraints. I wanted to make the  unique constraints into primary keys. One of the guys is worried about dictionary contention from the primary keys. I have worked on some pretty large OLTPs and never saw any issues with this. has anyone seen this? We are on (I forget the PSU level) on HP-UX. If I was going to test this, I'm not really sure what I would look for. I don't have a way to simulate the volume of users and executions we get in prod and then just check the waits. Any suggestions on what to look for? I'll need to show some test cases.



-- Received on Wed Oct 19 2011 - 15:53:22 CDT

Original text of this message