RE: TDE and execution plans
Date: Tue, 11 Mar 2014 16:06:42 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901DE24E5_at_exmbx05.thus.corp>
Amir,
Unfortunately I don't think it is a question that can be answered sensibly.
The variation in data size, need for extra columns (or not), extra indexes (or not), variations in work done encrypting blocks vs. encrypting columns, and the nature of the work you do makes any prediction impossible. All I recall is that if you were fairly busy in the encrypted area then you could see a few (3 or 4) percent increase in CPU. Unfortunately, I can't even find my notes about how Oracle handled undo and redo and what extra overheads appeared there. I THINK the encrypted columns generated encrypted column values in the undo and redo, but with tablespace encryption the redo and undo vectors were unencrypted - which would be a security hole if my memory is right.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle
From: Hameed, Amir [Amir.Hameed_at_xerox.com] Sent: 11 March 2014 15:42
To: Jonathan Lewis; oracle-l_at_freelists.org Subject: RE: TDE and execution plans
Thank you Jonathan for sharing this valuable information, especially the SALT bit. This may not be a fair question, but generally speaking, what is the ballpark overhead that you have seen when TDE is configured in a column versus tablespace configuration?
Thanks
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis
Sent: Tuesday, March 11, 2014 11:33 AM
To: oracle-l_at_freelists.org
Subject: RE: TDE and execution plans
I'd run up a few samples of the things you want to do, but you've picked a critical point.
If you encrypt a tablespace, blocks are encrypted on disc, but not in memory. (Any attempt to dump an in-memory block in the normal way will result in a line in the trace file saying something like "block not dumped as it is encrypted"). This means that encryption appears to have no impact on indexing strategies.
If you encrypt a column in a table, it is effectively turned into a deterministically random value. and it becomes irrelevant to apply a range-based predicate to the column (a bit like reverse key indexes), although multiple rows with the same column value will end up with the same encrypted value and "column = {constant}" will report an index range scan.
It is important to note two other things as side effects of column encryption. You cannot do referential integrity between encrypted columns. The size of an encrypted value usually be larger than the size of the unencrypted value - the last example I did the encrypted values were rounded up to multiples of 16 bytes, with an extra 16 bytes on top of that if I had a (on by default) salt. If you want to create an index at all, the column has to be encrypted without salt.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle
From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [oracle-l-bounce_at_freelists.org] on behalf of Hameed, Amir [Amir.Hameed_at_xerox.com] Sent: 11 March 2014 15:18
To: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> Subject: TDE and execution plans
Hi,
We are looking into implementing the Oracle eBilling software and will most likely deploy it in a PCI compliant architecture and configuration. As part of securing sensitive data on disk, we may use the TDE feature of Oracle RDBMS (11.2.0.3/11.2.0.4) and it could be at the tablespace level or at the column level. As I understand it, TDE might have an impact on some of the execution plans in that for indexes that contain encrypted columns, the optimizer might not perform range scans and opt for FTS instead. This is primarily driven by the way encrypted columns are sorted and stored in indexes before and after encryption. Does anyone have a first-hand experience with TDE and would like to share their experience in the context of performance overhead and issues?
Any feedback will be appreciated.
Regards,
Amir
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Mar 11 2014 - 17:06:42 CET