RE: performance when inserting into child tables
- From: "Mercadante, Thomas F" <thomas.mercadante@xxxxxxxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 21 Apr 2004 14:55:57 -0400
See!! I knew I wasn't going to change anything based on a comment in an
Oracle class.
I'm smarter than the average DBA.
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
From: Jonathan Lewis [mailto:jonathan@xxxxxxxxxxxxxxxxxx]
Sent: Wednesday, April 21, 2004 2:49 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: performance when inserting into child tables
It's released (almost) immediately after it has been
taken, but the request need not be granted immediately.
The request is Share (4) or Sub Share Exclusive (5),
so it has to wait for all current Row Exclusive (3 -
the normal lock for inserts, updates and deletes) to be
released. Moreover, whilst the request is pending,
every other session wishing to do a 'normal' I/U/D
will queue behind it.
It does NOT solve the problem of parent/child tables
with missing indexes on the child - it can reduce the
impact on low-activity systems.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
April 2004 Iceland http://www.index.is/oracleday.php
June 2004 UK - Optimising Oracle Seminar
July 2004 USA West Coast, Optimising Oracle Seminar
August 2004 Charlotte NC, Optimising Oracle Seminar
September 2004 USA East Coast, Optimising Oracle Seminar
September2004 UK - Optimising Oracle Seminar
----- Original Message -----
From: "Mercadante, Thomas F" <thomas.mercadante@xxxxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, April 21, 2004 7:22 PM
Subject: RE: performance when inserting into child tables
Jared,
you said:
"The lack of indexes for the FK's will cause a share lock to be taken
out on the child table when ever the parent is deleted or updated."
The most recent Oracle class I took said that in 9i, this lock on the child
table is taken and released immediately. Supposedly to speed the process up
a bit.
Tom Mercadante
Oracle Certified Professional
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
