LIOs on INSERT?

From: <Patty.Charlebois_at_greenshield.ca>
Date: Thu, 10 Feb 2005 10:09:56 -0500
Message-Id: <4D3C00FDAE.0F5C03BATFSEXZXE@greenshield.ca>

I am trying to tune a custom app running on Oracle 9=2E2=2E0=2E5 for AIX 5L=20= (64bit) and found the following SQL in my statspack snapshot=2E=20=

Why there are so many LIOs on an INSERT statement?
What can I do to reduce the LIOs?

TIA=2E=2E=2E=2E Patty

INSERT INTO PROD=2ECLAIM_RELATED_RULE ( CLRR_CLAIM_RULE_ID,=20= CF_CLAIM_FORM_ID,=20=
CLAIM_FORM_REVISION_NO, CL_CLAIM_ID, CLAIM_REVISION_NO, CD_DETAIL_ID,=20= SU_SUBS_ID, PA_DEP_NO, CAT_CATG_TYPE_CD, CAT_CATG_ID,=20= BC_BNFT_CATG_TYPE_CD,=20=
BC_BNFT_CATG_ID, RULE_TYPE_CD, RD_RULE_DEFN_ID, RU_RULE_ID,=20= CT_CLIENT_ID,=20=

  CO_OFFERING_ID, CTRR_CLIENT_RULE_ID, ACCUM_CD, RULE_SCOPE_CD,=20=
  RELATED_TABLE_TYPE_CD, CLRR_RULE_OVERRIDE_IND, OVERRIDE_REASON_CD,=20=
  CLRR_PAYMENT_REDUCTION_AMT, RULE_ADJUDICATION_STATUS_CD,=20=
  CLRR_INTERACTIVE_MSG_SEND_IND, INTERACTIVE_RESPONSE_CD, RESPONSE_CD,=20=
  CLRR_UNITS_CNT, CORRESPONDENCE_TYPE_CD, CLRR_PAYMENT_ACCUM_AMT,=20=

CLRR_UNITS_REDUCTION_CNT, ACCUM_REVERSAL_CD, CLRR_ACCUM_PERIOD,=20= CLRR_ACCUM_UNITS_CNT, CLRR_PAYMENT_ACCUM_SECD_AMT )=20= VALUES
( :B36, :B35, :B34, :B33, :B32, :B31, :B30, :B29, :B28, :B27, :B26, :B25,=20=

:B24, :B23, :B22, :B21, :B20, :B19, :B18, :B17, :B16, :B15, :B14, :B13,=20=
:B12, :B11, :B10, :B9, :B8, :B7, :B6, :B5, :B4, :B3, :B2, :B1 )

call count cpu elapsed disk query current =20= rows

-------      ------          --------     ----------      ----------=20=
----------       ----------  ----------
Parse        0             0=2E00       0=2E00                 0     0  0  0
Execute  44025     13=2E26      45=2E46       4048       7274     809149 44025
Fetch        0              0=2E00       0=2E00                  0      0    0=20=
          0

------- ------ -------- ---------- ---------- ---------- ----------=20=

total 44025 13=2E26 45=2E46 4048 7274 809149 44025

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 21 (PROD) (recursive depth: 1)

Rows Execution Plan

-------  ---------------------------------------------------
      0  INSERT STATEMENT   GOAL: CHOOSE


Elapsed times include waiting on following events:
  Event waited on                             Times   Max=2E Wait  Total=20=

Waited

Waited ----------=20=
db file sequential read 4048 0=2E09 31=2E82 buffer busy waits 218 0=2E04 0=2E60 enqueue 22 0=2E02 0=2E09 latch free 118 0=2E01 0=2E17 log file switch completion 4 0=2E02 0=2E07 log file sync 10 0=2E02 0=2E18 buffer deadlock 3 0=2E00 0=2E00 ********************************************************************************=20= =20=

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

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

Received on Thu Feb 10 2005 - 10:09:34 CST