Re: primary keys and dictionary overhead
- From: "Tim Gorman" <tim@xxxxxxxxx>
- To: oracledbaquestions@xxxxxxxxx, "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 19 Oct 2011 20:25:03 +0000
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 [mailto:oracledbaquestions@xxxxxxxxx]
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 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. 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.--//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
