Thank You Sayan.
Added only the non-zero stats fetched for one of the application sessions
from v$sesstats view in below location. Want to understand , if there is
anything pointing to encryption being the cause ?
GitHub - oraclelearner/sessionstats
<https://github.com/oraclelearner/sessionstats>
Regards
Lok
On Thu, Nov 4, 2021 at 6:37 AM Sayan Malakshinov <xt.and.r@xxxxxxxxx> wrote:
Hi Lok,
Just use gist.github.com or pastebin.com
On Thu, Nov 4, 2021 at 12:21 AM Lok P <loknath.73@xxxxxxxxx> wrote:
Trying to post the v$sesstats details for the captured session but it's
getting bounced back. Tried copy-paste and even attached the sheet, it's
not going through. Any other way to share those info.
On Thu, Nov 4, 2021 at 2:38 AM Lok P <loknath.73@xxxxxxxxx> 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
--
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org
