Re: Insert statement has high LIO

From: Krishnaprasad Yadav <chrishna0007_at_gmail.com>
Date: Tue, 21 Jun 2022 10:01:52 +0530
Message-ID: <CAO8FHeV-X6442cRW4iNp82aXr+KZFh4tgbgtkzacjbowY4ZGpA_at_mail.gmail.com>

Dear Gurus ,

Thanks for your response

Oracle version is 11203 , we tried to trace the insert statement using below command :

alter system set events 'sql_trace [sql: sql_id=<sqlid>]'; alter system set events 'sql_trace [sql: sql_id=<sqlid>] off';

below is tkprof output :

SQL ID: 029ryxsfphw44 Plan Hash: 0

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) call count cpu elapsed disk query current  rows
------- ------ -------- ---------- ---------- ---------- ----------

---

Parse 0 0.00 0.00 0 0 0

    0
Execute 10 7.35 7.39 0 59462 43

   10
Fetch 0 0.00 0.00 0 0 0

    0
------- ------ -------- ---------- ---------- ---------- ----------

---

total 10 7.35 7.39 0 59462 43

   10

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 130

also along with this other sql are also getting captured which are more of select statement .

Since its 11203 i am not sure how i can validate issue ASSM activity as these session are generated from DBLINK and doing insert in my DB .

Regards,
Krishna

On Sat, 18 Jun 2022 at 11:48, Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

> 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

Received on Tue Jun 21 2022 - 06:31:52 CEST