RE: primary keys and dictionary overhead
Date: Wed, 19 Oct 2011 16:24:43 -0400
Could you ask him to clarify why he thinks there would be more "dictionary overhead" for a PK than a UK? Yes, there's a certain amount of overhead associated with looking up constraints in the dictionary when executing DML, but, why would it be any worse for PK than UK?
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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 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:24:43 CDT