Re: LIOs on INSERT?
- From: Patty.Charlebois@xxxxxxxxxxxxxx
- To: breitliw@xxxxxxxxxxxxx
- Date: Thu, 10 Feb 2005 13:55:21 -0500
I am using ASSM for the indexes, not the tables (we are working our way=20=
towards full ASSM)=2E
How did you conclude that Oracle was chasing down the freelist from the=20=
sql trace? What do I look for and what did you do about it?
Patty
Wolfgang Breitling <breitliw@centrexcc=2Ecom>=20=
2005/02/10 11:06 AM
To
Patty=2ECharlebois@greenshield=2Eca
cc
ryan_gaffuri@comcast=2Enet, oracle-l@freelists=2Eorg,=20=
oracle-l-bounce@freelists=2Eorg
Subject
Re: LIOs on INSERT?
Has that table been heavily deleted from prior to the insert so that now=20=
almost=20=
all blocks are on the free list - I just realize that I assume you are not=20=
using ASSM?
I did run into a similar scenario with an insert with lots of LIO and I=20=
had the=20=
same question as you=2E Looking at a detailed sql trace (10046 level 8) I=20=
eventually concluded that is was because Oracle was chasing down the=20=
freelist=20=
for the next block to insert into=2E
The other possibility, If you ARE using ASSM (and are on 9=2E2=2E0=2E5) is
that=20=
you=20=
are running into the insert - delete - insert bug of 9=2E2=2E0=2E5=2E
Quoting Patty=2ECharlebois@greenshield=2Eca:
> There are no triggers and no foreign keys on this table=3D2E
>=20=
>=20=
>=20=
>=20=
> ryan_gaffuri@comcast=3D2Enet=3D20=3D
> Sent by: oracle-l-bounce@freelists=3D2Eorg
> 2005/02/10 10:20 AM
> Please respond to
> ryan_gaffuri@comcast=3D2Enet
>=20=
>=20=
> To
> Patty=3D2ECharlebois@greenshield=3D2Eca, oracle-l@freelists=3D2Eorg
> cc
>=20=
> Subject
> Re: LIOs on INSERT?
>=20=
>=20=
>=20=
>=20=
>=20=
>=20=
> check your triggers and constraints=3D2E see what is being fired when you
> do=3D20=3D
> the insert=3D2E foreign key means a lookup in a parent table=3D2E trigger=20=
could
> be=3D20=3D
> selecting from somewhere=3D2E=3D20=3D
> -------------- Original message --------------=3D20=3D
>=20=
>=20=
> > I am trying to tune a custom app running on Oracle
> > 9=3D3D2E2=3D3D2E0=3D3D2E5=20=
for
> AIX=3D20=3D
> 5L=3D3D20=3D3D=3D20=3D
> > (64bit) and found the following SQL in my statspack
> snapshot=3D3D2E=3D3D20=3D3D=3D20=3D
> >=3D20=3D
> >=3D20=3D
> > 1) Why there are so many LIOs on an INSERT statement?=3D20=3D
> > 2) What can I do to reduce the LIOs?=3D20=3D
> >=3D20=3D
> > TIA=3D3D2E=3D3D2E=3D3D2E=3D3D2E Patty=3D20=3D
> >=3D20=3D
> > INSERT INTO PROD=3D3D2ECLAIM_RELATED_RULE (=20=
CLRR_CLAIM_RULE_ID,=3D3D20=3D3D=3D20=3D
> > CF_CLAIM_FORM_ID,=3D3D20=3D3D=3D20=3D
> > CLAIM_FORM_REVISION_NO, CL_CLAIM_ID, CLAIM_REVISION_NO,=3D20=3D
> CD_DETAIL_ID,=3D3D20=3D3D=3D20=3D
>=20=
>=20=
> --
> http://www=3D2Efreelists=3D2Eorg/webpage/oracle-l
>=20=
>=20=
>=20=
>=20=
>=20=
>=20=
>=20=
>=20=
> ----------------------------------------------------------
>=20=
> This e-mail is confidential, intended solely for the use of the
> recipient(s) to whom it was addressed=3D2E If you have received it
> in error, please do not copy or distribute this e-mail=3D2E We ask
> that you notify us immediately by replying to the sender and
> then delete this e-mail=3D2E E-mail sent or received over the
> internet may not be secure=3D2E You should use caution when sending
> e-mail messages containing private and confidential information
> or consider other secure means to send the information=3D2E If you
> have any questions regarding the authenticity or security of
> e-mail you have received from Green Shield Canada please do not
> hesitate to contact us at 1-800-265-5615=3D2E
>=20=
> Please visit us at our website: http://www=3D2Egreenshield=3D2Eca
>=20=
>=20=
> ----------------------------------------------------------
>=20=
> Ce message de courriel est confidentiel et s=3D92adresse uniquement
> =3DE0 la personne ou =3DE0 l=3D92organisme indiqu=3DE9(e)=3D2E Si vous
> l=3D92a=
vez
> re=3DE7u
> par erreur, veuillez ne pas copier ni distribuer ce message de
> courriel=3D2E Nous vous demandons de nous aviser imm=3DE9diatement en
> r=3DE9pondant =3DE0 l=3D92exp=3DE9diteur, puis en supprimant ce message=3D2E
> Le=
s
> messages envoy=3DE9s ou re=3DE7us par courriel pourraient ne pas =3DEAtre
> s=3DE9curis=3DE9s=3D2E Vous devriez =3DEAtre prudent lorsque vous envoyez des
> messages de courriel contenant des renseignements confidentiels
> et priv=3DE9s ou songer =3DE0 prendre d=3D92autres moyens s=3DE9curis=3DE9s
> pou=
r
> envoyer les renseignements=3D2E Si vous avez des questions concernant
> l=3D92authenticit=3DE9 ou la s=3DE9curit=3DE9 d=3D92un courriel que vous
> avez=20=
re=3DE7u
> de
> Green Shield Canada, n=3D92h=3DE9sitez pas =3DE0 communiquer avec nous au
> 1-800-265-5615=3D2E
>=20=
> Visitez notre site Web: http://www=3D2Egreenshield=3D2Eca
>=20=
> --
> http://www=2Efreelists=2Eorg/webpage/oracle-l
>=20=
--=20=
regards
Wolfgang Breitling
Oracle 7,8,8i,9i OCP DBA
Centrex Consulting Corporation
www=2Ecentrexcc=2Ecom
----------------------------------------------------------
This e-mail is confidential, intended solely for the use of the
recipient(s) to whom it was addressed=2E If you have received it
in error, please do not copy or distribute this e-mail=2E We ask
that you notify us immediately by replying to the sender and
then delete this e-mail=2E E-mail sent or received over the
internet may not be secure=2E You should use caution when sending
e-mail messages containing private and confidential information
or consider other secure means to send the information=2E If you
have any questions regarding the authenticity or security of
e-mail you have received from Green Shield Canada please do not
hesitate to contact us at 1-800-265-5615=2E
Please visit us at our website: http://www=2Egreenshield=2Eca
----------------------------------------------------------
Ce message de courriel est confidentiel et s=92adresse uniquement
=E0 la personne ou =E0 l=92organisme indiqu=E9(e)=2E Si vous l=92avez re=E7u
par erreur, veuillez ne pas copier ni distribuer ce message de
courriel=2E Nous vous demandons de nous aviser imm=E9diatement en
r=E9pondant =E0 l=92exp=E9diteur, puis en supprimant ce message=2E Les
messages envoy=E9s ou re=E7us par courriel pourraient ne pas =EAtre
s=E9curis=E9s=2E Vous devriez =EAtre prudent lorsque vous envoyez des
messages de courriel contenant des renseignements confidentiels
et priv=E9s ou songer =E0 prendre d=92autres moyens s=E9curis=E9s pour
envoyer les renseignements=2E Si vous avez des questions concernant
l=92authenticit=E9 ou la s=E9curit=E9 d=92un courriel que vous avez re=E7u de
Green Shield Canada, n=92h=E9sitez pas =E0 communiquer avec nous au
1-800-265-5615=2E
Visitez notre site Web: http://www=2Egreenshield=2Eca
--
//www.freelists.org/webpage/oracle-l
Other related posts:
