Re: High Administrative and Network waits

As you highlighted, the event "BACKUP:MML write backup piece" time increased along with few network waits, post your 256 encryption and you suspect that may be the cause of the issue. So I believe your backup must not be running the whole day, so just to confirm your theory, you may check the backup timing, if they have increased recently and your application job is running in the same exact backup window.

On Thu, Nov 4, 2021 at 9:14 AM Lok P <loknath.73_at_gmail.com> wrote:

> I have not yet checked these. But yes, this database is hosted on an
> exadata X3 , high capacity full rac machine. I will try to get these
> outputs from the DBA team.
>
> On Thu, Nov 4, 2021 at 5:06 AM Mladen Gogala <gogala.mladen_at_gmail.com>
> wrote:
>
>> Have you tried netstat -s|egrep -i
>> "(fail|error|warn|drop|retrans|drop|collis)"? How about netstat -i? Do
>> you see any errors on the interface? What kind of network is that? 10Gb?
>> 1Gb?
>>
>> On 11/3/21 17:08, Lok P wrote:
>> > It's getting bounced back so resending.
>> >
>> > Its version 19C of oracle. We were suddenly getting complaints from
>> > the team regarding the slowness of few of the application processes.
>> > Those that were running in 2-2.5hrs are now running for ~6hrs. This
>> > process calls many quick queries from Java and we verified most of
>> > them have no change in plan but a bit increase in elapsed time
>> > (which is mainly cpu time) observed. But when looking into the
>> > database we see a spike in Administrative waits (mainly BACKUP:MML
>> > write backup piece) and network wait events(mainly Data Guard Network
>> > buffer stall reap, SQL*Net more data from client followed by SQL*Net
>> > more data to client). And we recently moved from existing AES128 to
>> > AES256 tablespace encryption. Not sure if that is anyway playing any
>> > role here causing these administrative and network waits in turn
>> > impacting the application query/processes.
>> >
>> > However I tried capturing the stats in production from v$sesstat for
>> > two of the processes/sessions while they were already running for
>> > ~3hrs from the logon_time noted in v$session. But I am not able to
>> > understand if that is pointing anything suspicious towards the
>> > encryption being the cause. I don't have results from v$sesstats from
>> > a good time though. Can you guide me here? How these can be logically
>> > related.
>> >
>> > Regards
>> > Lok
>>
>> --
>> Mladen Gogala
>> Database Consultant
>> Tel: (347) 321-1217
>> https://dbwhisperer.wordpress.com
>>
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>

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