Re: Same query with different response time

From: Lok P <loknath.73_at_gmail.com>
Date: Thu, 1 Jul 2021 00:55:26 +0530
Message-ID: <CAKna9VZBNXxjZuEesL+r4nkTZtK5KnWmQA5yqCx56rc_=nvb7A_at_mail.gmail.com>



Thanks a lot Jonathan. Planning to test it again by creating two tablespaces side by side and running the queries again on exactly matching objects. Btw, out of curiosity , as a few teammates highlighted that we will see degradation in index range scan performance(not equal operators but mainly range/between operators) post tablespace encryption because the actual values would be encrypted ones. Is that true and we should be really concerned about the index range scan performance ?

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

> Forget the application. Test the hypothesis that FIRST_ROWS and TSE do not
> behave well together
>
> Do you have a test database (on the same platform).
>
> Create two tablespaces, one with TSE
>
> Copy the same 8GB of data into each tablespace, index as appropriate
>
> Run the tablescan queries you reported on each table - once with ALL_ROWS,
> once with whatever FIRST_ROWS_N setting you have (I hope it's not just
> first_rows which was deprecated several versions ago). Examine the session
> stats and wait events for each execution individually - if necessary start
> a new session for each query
>
> Can you see anything in the stats that tells you that first_rows_n uses a
> significantly different mechanism from all_rows, and that it uses more CPU
> as a side effect. Is this happening in both the TSE and non-TSE
> tablespaces; conversely is it the change from non-TSE to TSE that makes a
> difference while first_rows and all_rows (on any one tablespace) act the
> same way.
>
> If you can conclude that the combination of first_rows_n and TSE introduce
> a change in mechanism with significant performance side effects you can go
> to your management with the results and ask them to choose between TSE and
> FIRST_ROWS, and you can go to Oracle Corp. and tell them that the
> combination introduces a side effect.
>
> Regards
> Jonathan Lewis
>
>
>
>
>
> On Mon, 28 Jun 2021 at 17:13, Lok P <loknath.73_at_gmail.com> wrote:
>
>> 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
Received on Wed Jun 30 2021 - 21:25:26 CEST

Original text of this message