primary keys and dictionary overhead

From: Dba DBA <oracledbaquestions_at_gmail.com>
Date: Wed, 19 Oct 2011 16:08:38 -0400
Message-ID: <CAE-dsOK5niSakTbjmWN-tYZcG0n6+rfbgLnK=JfsoiQ7fqFg4w_at_mail.gmail.com>



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 10.2.0.5 (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.
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Oct 19 2011 - 15:08:38 CDT

Original text of this message