Re: Insert statement has high LIO

You didn't say which version of Oracle was reporting these stats, and whether the version of the source database was the same. Would I be right in thinking the stats from from an AWRSQRPT report for the sql_id?

It's possible that the "1 row per execution" is misleading; that would be a little surprising, but it's possible that the code to handle the rowcount has an error when the data is incoming from a remote site. You could create a simple model to test this, or you could find the source code to see if it thought it was doing single row or array inserts, or you could enable tracing at level 4 for the SQL_ID for a limited number of executions to see if the incoming bind data was in arrays or single values.

A possibility that seems likely, though, given the very large number of gets per execution/row is that Oracle is having a problem handling the free space search for every single row. If this were a local sessions it would be easy to check the session stats after a few inserts, but since it's an incoming remote session it might be hard to track the relevant session, but you could check the Instance Activity stats for the snapshot you've reported and see if any of the statistics about ASSM activity are around the same size as the buffer gets for this query. Check all the stats starting with 'ASSM', and I think either the 'ASSM gsp%' or the 'ASSM cbk%' may show some interesting results relating to examination and rejection.

Regards
Jonathan Lewis

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