Re: Insert statement has high LIO
Date: Fri, 17 Jun 2022 06:27:17 -0700
Message-ID: <5d321dd1-6acd-34dd-6eaf-d62fd0258fb4_at_gmail.com>
Can you run the SQL statement from SQL*Plus after enabling SQL trace
level 12?
On 6/17/2022 5:53 AM, Andy Sayer wrote:
> Let’s rule out the obvious suspects first:
>
> Have you got any triggers?
> Have you got many indexes?
> Any indexes that look suspiciously large?
>
> I am trusting the rows per execution of 1, but this does tend to
> happen when array binds are used (which should increase rows per
> execution).
>
> Thanks,
> Andy
>
> On Fri, 17 Jun 2022 at 13:14, Krishnaprasad Yadav
> <chrishna0007_at_gmail.com> wrote:
>
> Hi Experts ,
>
> Below is insert statement has high LIO
>
> INSERT INTO "A1" ( "MOD", "k1", "K2", "VALUE1", "DES", "STATUS",
> "MID", "MDATE", "AUT", "ADATE", "ACTIVE_FLAG")
> VALUES (:B11, :B10, :B9, :B8, :B7, :B6, :B5, :B4, :B3, :B2, :B1)
>
> *STATS : *
> *=======*
> Stat Name Statement Per Execution % Snap
> ---------------------------------------- ---------- --------------
> -------
> Elapsed Time (ms) 6.7031E+07 612.0
> 5.3
> CPU Time (ms) 6.6711E+07 609.0
> 6.0
> Executions 109,536 N/A
> N/A
> *Buffer Gets 2.6882E+09
> 24,541.9 3.4*
> Disk Reads 952,253 8.7
> 0.0
> Parse Calls 109,535 1.0
> 0.1
> Rows 109,393 1.0
> N/A
> User I/O Wait Time (ms) 428,156 N/A
> N/A
> Cluster Wait Time (ms) 6,212 N/A
> N/A
> Application Wait Time (ms) 0 N/A
> N/A
> Concurrency Wait Time (ms) 18 N/A
> N/A
> Invalidations 0 N/A
> N/A
> Version Count 95 N/A
> N/A
> Sharable Mem(KB) 2,629 N/A
> N/A
>
>
> Currently it is being called from remote database via dblink and
> on local server for single execution buffer *gets 24.5K *
> *
> *
> *Request to show some path since its a plan insert it should not
> cause huge buffer get *
> *
> *
> *Regards,*
> *Krishna *
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Jun 17 2022 - 15:27:17 CEST