Re: primary keys and dictionary overhead

From: Tim Gorman <>
Date: Wed, 19 Oct 2011 20:25:03 +0000
Message-ID: <W5552016240116681319055903_at_webmail16>

Don't ask us. Prove it to yourself...
SQL trace the transactions involving the tables with the constraints in question, aggregate the trace information using your favorite aggregator (i.e. TKPROF, TRCANLZR, Hotsos/MethodR Profiler, etc) and look at the recursive SQL belonging to SYS. If using TKPROF, be sure not to specify SYS=NO. Also, if using TKPROF, be sure to specify SORT=PRSELA,EXEELA,FCHELA or the equivalent.

If anything belonging to SYS shows up as significant, then there might be "dictionary overhead", but I'd first look more closely at the SYS-generated recursive SQL statements to see what part of the data dictionary is involved. It might have nothing to do with constraints or indexes or whatever it is you're seeking.

I strongly believe that nothing significant will show up. But don't take anyone else's word for it -- prove it for yourself.

Hope this helps...

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

From: Dba DBA [] Sent: Wednesday, October 19, 2011 02:08 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 about100,000 executions/minute and we expect this to continue to increase. Whenthe system first came out, they had to make sure there were no constraints,unique indexes, triggers, synonyms or anything to make sure the system wouldperform. I can understand that 20 years ago. We just started adding someunique constraints. I wanted to make the unique constraints into primarykeys. One of the guys is worried about dictionary contention from theprimary keys. I have worked on some pretty large OLTPs and never saw anyissues 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. Idon't have a way to simulate the volume of users and executions we get inprod 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:25:03 CDT

Original text of this message