Re: Insert statement has high LIO

Krishna,

If you can follow Tim's advice, I'd be happy to create a nice HTML report for your trace with our Method R Workbench product. Just zip the .trc file and send it to me as an attachment, or if it's too big for that, I can send you a file upload link.

Cary Millsap
Method R Corporation

NEW! Author of *Faster: How to Optimize a System <https://method-r.com/2021/12/23/faster/>*

On Fri, Jun 17, 2022 at 8:27 AM Tim Gorman <tim.evdbt_at_gmail.com> wrote:

> 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-l