Re: Same query with different response time

I attached here three different sqls one update, one insert and one select and sql monitor for each of them on Encrypted vs unencrypted tablespace. This time i'm not seeing any difference in the number of read requests , rather it seems in all of the cases it's reading more bytes in the case of encrypted tablespace as compared to non encrypted tablespace.

So does it mean that , as in case of encrypted objects the size will be increased a bit for the objects , so this amount of degradation in performance is expected and we should move ahead with this? Also I think you also pointed towards 20-30% degradation. And here just to note we are manually testing a few queries , as we don't have capability to run/test full application suite on a similar volume database same as production.

*"Since data blocks are encrypted on disc and decrypted for memory then keeping the working data sets cached becomes more important - my tests suggested that for operations that were almost pure encryption/decryption and very little else (e.g. direct path tablescans to filter and aggregated blocks; insert into into encrypted t/s select from non-encrypted data), there was (as you have seen) a significant performance impact (20% - 30%)."*

Jonathan, Does your above point means that , pure encryption/decryption will have ~20-30% of degradation (mostly in a non exa database), however in an exadata environment , queries doing direct read/cell offload may suffer lesser? Won't the exadata cell disks have to decrypt the required data and passon to buffer cache in the same fashion?

On Sat, Jul 17, 2021 at 9:04 AM Lok P <loknath.73_at_gmail.com> wrote:

> Jonathan, We have created two different tablespaces(with and without
> encryption) and are trying to test out the queries but yet to see any
> significant difference in execution time/resource consumption in them. So i
> was curious to know, as we were seeing the difference in run time here
> mainly because of the avg read per request , sometimes they were
> 128KB/request whereas in other times 1MB/request for similar full segment
> scan. So I wanted to understand if it's possible that oracle can opt for
> ~128KB/request for the same segment full scan at different times , because
> of some other factor apart from encryption, say because of different load
> in the database/storage server etc?
>
> 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





text/plain attachment: compare_tde_vs_nontde.txt