Re: Same query with different response time
Date: Mon, 28 Jun 2021 13:06:48 +0100
Message-ID: <CAGtsp8kC3w1fnx4GOsVrPkfNyOM9sG_4c2-84TTDDzoi=drNPw_at_mail.gmail.com>
There are four points to investigate in your Monitor repors:
Since most of your time difference is in the CPU usage you need to find out
what the session is doing by looking at the session activity stats
(v$sesstat) - it strikes me as perfectly feasible that if you are loading
1M blocks into the buffer cache and then decrypting them before examining
their content then the additional CPU spent decrypting them might easily
double the CPU load. But maybe there's a completely different explanation.
Regards
On Sat, 26 Jun 2021 at 15:18, Lok P <loknath.73_at_gmail.com> wrote:
> Hello Listers, We have seen TSE (tablespace encryption) implementation in
Jonathan Lewis
> the past in multiple databases but have not verified any performance aspect
> of this and we have also not got any complaint and now this has been
> mandated by the security team to do it for all the databases. However, we
> recently migrated one of the database to TSE i.e. tablespace encryption(Not
> column level TDE) and it's the lower environment/dev database, and dev team
> sent us few sql monitors noting performance degradation post TSE (some were
> ~100% slower which we were not expecting).
>
> Attached are a few of the sql monitors which we got , stating the
> execution plan is same and volume is same , yet there is significant
> increase in response time. So I am not sure if we can validate from the sql
> monitor report, if the increase in execution time is only because of
> tablespace encryption or anything else. So can you please guide me here,
> how i can validate from this attached sql monitor if the degraded response
> time is because of TSE/tablespace encryption or anything else and how we
> can fix this issue?
>
> It's version 19.9.0.0.0 of Oracle.
>
> Regards
> Lok
>
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Jun 28 2021 - 14:06:48 CEST