Loki!
By the way, some of the initialization parameters that might be useful for
seeing (in plans) and affecting whether (in execution) decryption takes place
in the storage cells are found in the very fine manuals. A non-comprehensive
list is CELL_OFFLOAD_PROCESSING, CELL_OFFLOAD_PLAN_DISPLAY, and
CELL_OFFLOAD_DECRYPTION.
Whether and how much of an encrypted object is cached decrypted presumably also
affects the overhead on the compute nodes, regardless of whether that
decryption is taking place in the storage cells and regardless where the
caching is taking place, including inmemory cache representation, especially of
predicates but also if it happens that all the columns you reference are
inmemory.
I have done very little experimentation with this, but the possible
optimizations for particular situations beggar the imagination. One thing I do
imagine is that if you have some priority queries involving encrypted storage,
using some of the Oracle widgets effectively can probably make those queries
not experience any detectable decryption overhead after warm-up. That
assessment does not apply to scan everything of objects too big to cache in any
way.
Storage Indexes may also be useful.
Good luck.
mwf
From: Jeff Smith [mailto:jeff.d.smith@xxxxxxxxxx] ;
Sent: Thursday, July 22, 2021 8:27 AM
To: loknath.73@xxxxxxxxx; Mark W. Farnham
Cc: Jonathan Lewis; Oracle L
Subject: RE: [External] : Re: Same query with different response time
OT: I always read your emails as being from ‘Loki’
Also I’m enjoying the free education on these posts lately. The L has been hot!
Jeff
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> On Behalf
Of Lok P
Sent: Wednesday, July 21, 2021 4:42 PM
To: Mark W. Farnham <mwf@xxxxxxxx>
Cc: Jonathan Lewis <jlewisoracle@xxxxxxxxx>; Oracle L <oracle-l@xxxxxxxxxxxxx>
Subject: [External] : Re: Same query with different response time
So in buffer cache data is stored in unencrypted format, which means if any
query is just reading recent blocks(may be table or index blocks) i.e. from
buffer cache it should not see any degradation as it's all in decrypted format
already and it should be true in most OLTP database scenarios. But the only
case when data is written to OR read from disk/cell disk has to be encrypted
and decrypted and thus should see additional CPU cycles spent on that. And also
with write back flash cache , all the DMLs(Insert/update) will have to first be
written to the flash cache, so I hope they will have to go through the
encryption cycle because flash is a persistent storage and not like buffer
cache. So we can assume that in cases where we are reading from disk/ or
writing to disk , we have to experience those 20-30% performance degradation.
So it's basically dependent on the type of application , if it's doing most
read/writes to disk then it may see 20-30% degradation across , whereas if it's
doing most of those from buffer cache , it may not see that amount of impact.
Is my understanding correct here?
As I had attached sql monitors for read and write queries, I am still trying to
understand the cause of degradation in those. And that seems to be happening
both with IO and CPU times. And also the read bytes seems to be higher in case
of encrypted tablespace causing this. And initially the suspicion that
first_rows along with encryption may be causing the difference in number read
requests and thus increase in run time , seems not the case.
On Wed, Jul 21, 2021 at 7:10 PM Mark W. Farnham <mwf@xxxxxxxx> wrote:
just this part: “Won't the exadata cell disks have to decrypt the required data
and passon to buffer cache in the same fashion?”
If the decryption is done in the Exadata cell and the unencrypted data is
passed to the compute machine, the compute machine will not see this extra
usage except possibly as an increase in the latency and rate of data delivered.
That would be only the time to decrypt, but typically the cpu in data cells is
in rich supply, so the time element of the extra cpu probably involves near
zero wait and near zero concurrency issues and it is not going to be
utilization in the upper half. I have never tried to measure any difference in
the data delivery rate to the upper half since that has never been a
significant piece of the response time or data processing rate in something I
have been asked to improve.
If the decryption is done in the compute machine, you see the extra cpu
utilization on cpu that are serving all the compute machine requirements, so it
is much more likely that 20-30% is significant.
I am not confident in which cases Oracle can (or in fact does) the decryption
in the data cells. Someone may know whether it is sometimes, always, or never
for a particular release from testing and someone may know whether this is
documented for what is intended and possible. Your mileage may vary.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Lok P
Sent: Tuesday, July 20, 2021 3:35 PM
To: Jonathan Lewis
Cc: Oracle L
Subject: 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@xxxxxxxxx> 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@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
