Re: Same query with different response time

Thank You Mark. Actually I had attached the sql monitors for three queries and I am not seeing major deviation in resource consumption of index access in those paths. But I did pick up a few indexes in the database and tried to see the clustering factor and leaf block count history from WRI$_optstat_ind_history , to see if post TSE there is a major increase in those but didn't find any such difference. And in these sql monitors it's the last execution path which shows an increase in read bytes in the line where it's doing "table access storage full first rows' '. But again i was trying to see if the number of blocks has been increased from WRI$_optstat_tab_history post TSE , but unfortunately it seems we don't gather stats frequently in this table , so not getting entry in this awr view for this table.

 Regards
Lok

On Mon, Jun 28, 2021 at 9:03 AM Mark W. Farnham <mwf_at_rsiz.com> wrote:

> Are any indexes used in your plans and if so, did the cluster factors
> change?
>
>
>
> IF you have block visits minimized via an index prior to TSE but after TSE
> it has to skip around a lot more, that **could** explain changes in CPU
> and IO with no change in plan.
>
>
>
> Just a thing to check, probably not it but worth a glance.
>
>
>
> mwf
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Lok P
> *Sent:* Sunday, June 27, 2021 10:19 PM
> *To:* Pap
> *Cc:* Oracle L
> *Subject:* Re: Same query with different response time
>
>
>
> No other change happened. It was only tablespace encryption which has been
> done on top of existing data.
>
>
>
> I am also struggling to understand why the increase in IO is noted in SQL
> monitor.
>
>
>
> Regards
>
> Lok
>
> On Sun, 27 Jun 2021, 8:22 am Pap, <oracle.developer35_at_gmail.com> wrote:
>
> I would expect a little more CPU requirement for any encryption/decryption
> activity. But in your SQL monitor it's also showing difference in IO. Not
> sure if it can only attributed to tablespace encryption. Any other change
> happened?
>
>
>
>
>
>
>
> On Sat, 26 Jun 2021, 7:49 pm Lok P, <loknath.73_at_gmail.com> wrote:
>
> Hello Listers, We have seen TSE (tablespace encryption) implementation in
> 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-l