Re: LIOs on INSERT?

Does the table experience heavy deletes prior to the insert? There are two 
scenarios I have run into that can lead to high LIO:
a) You use ASSM and you are on 9.2.0.5
   There is a bug in 9.2.0.5 if you have insert - delete - insert without 
commits in between

b) You are not using ASSM and the delete put a lot of blocks on the freelist. 
On the insert, whenever the current block is "full" (up to 100-pctfree filled) 
Oracle needs to chase the freelist and retrieve the next block off the list and 
needs to maintain the list.

Quoting Patty.Charlebois@xxxxxxxxxxxxxx:

> There are no triggers and no foreign keys on this table=2E
> 
> 
> > I am trying to tune a custom app running on Oracle 9=3D2E2=3D2E0=3D2E5 for
> AIX=20=
> 5L=3D20=3D=20=
> > (64bit) and found the following SQL in my statspack
> snapshot=3D2E=3D20=3D=20=
> >=20=
> >=20=
> > 1) Why there are so many LIOs on an INSERT statement?=20=
> > 2) What can I do to reduce the LIOs?=20=
> >=20=
> > TIA=3D2E=3D2E=3D2E=3D2E Patty=20=
> >=20=
> > INSERT INTO PROD=3D2ECLAIM_RELATED_RULE ( CLRR_CLAIM_RULE_ID,=3D20=3D=20=
> > CF_CLAIM_FORM_ID,=3D20=3D=20=
> > CLAIM_FORM_REVISION_NO, CL_CLAIM_ID, CLAIM_REVISION_NO,=20=
> CD_DETAIL_ID,=3D20=3D=20=
> 

-- 
regards

Wolfgang Breitling
Oracle 7,8,8i,9i OCP DBA
Centrex Consulting Corporation
www.centrexcc.com

--
http://www.freelists.org/webpage/oracle-l

Other related posts: