Re: Same query with different response time

Thank You Jonathan.

It is actually a third party database in which the default optimizer mode has been set as 'first_rows'. This application is mostly relying on indexed queries, but yes there are queries doing full scan too. I am not sure if the 128KB request per table scan and no benefit out of cell offload/storage indexes is just because of FIRST_ROWS optimizer mode. I am thinking if i should test it using the FULL_ROWS hint, but then the plan will change and that won't be an apple to apple comparison, correct me if wrong. I can fetch the details from v$sesstat for these query runs, but yes I may not be able to get the details of the before TSE version of the query.

 But yes here the real concern for us is if the TSE is going to add such an overhead and it's expected or we are missing something.

Regards
Lok

On Mon, Jun 28, 2021 at 5:37 PM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> There are four points to investigate in your Monitor repors:
>
> a) Why are your "TABLE ACCESS STORAGE FULL"s qualified by "FIRST ROWS" --
> this feature may have some bearing on the preformance.
> b) Why are your tablescans (before tse) doing an average of about 128KB
> per read request while the index fast full scan is going for 1MB ? Is this
> related to FIRST ROWS
> c) One (and only one) of your tablescans (after tse) using 1MB read
> requests - what's different about that one?
> d) The buffer gets value is consistent with the "read bytes" in all cases,
> which suggests you're not getting any benefit from the storage cells (cell
> offload/storage indexes).
>
> 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
> Jonathan Lewis
>
>
>
> 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 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