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@xxxxxxxxx>
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@xxxxxxxxx> 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@xxxxxxxxx>
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@xxxxxxxxx> 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
