Re: primary keys and dictionary overhead
- From: David Fitzjarrell <oratune@xxxxxxxxx>
- To: "mark.powell2@xxxxxx" <mark.powell2@xxxxxx>, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 19 Oct 2011 13:53:22 -0700 (PDT)
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" <mark.powell2@xxxxxx>
To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
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: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
