Known Performance Issues When Using TDE and Indexes on the Encrypted Columns
Last updated on JULY 05, 2017
Applies to:Advanced Networking Option - Version 10.2.0 to 11.1
Information in this document applies to any platform.
This document describes some particular performance problems that can happen with encrypted columns, due to two optimizer bugs we were able to identify and fix.
To understand and identify the issue please consider the following background information on TDE.
With Transparent Data Encryption (TDE) the server creates a key for each table that uses encrypted columns. The table key is stored encrypted with the database master key in the data dictionary. This master key is stored in a PKCS12 wallet, outside the database or, from 11g onwards, optionally into a Hardware Security Module (HSM). For the database to use TDE, a wallet must exist either as file ewallet.p12 or inside the HSM. The procedure below uses a basic generated key. The master encryption key could also be from a PKI pair.
Beware that because of this there are a number of normal restrictions when using indexes on encrypted columns, eg bitmap indexes are not supported. Please check for the complete details on these restrictions.:
<Note.454980.1> Ext/Rev Best Practices for having indexes on encrypted columns using TDE in 10gR2
The conclusion is that, in order to use an index including at least one encrypted column, the predicate should be an equality predicate and the index access should be INDEX UNIQUE SCAN. Other forms of predicate would result in FULL TABLE SCAN, because the index cannot be used with INDEX RANGE SCANs.
The above also means that when resolving queries that involve (indexed) columns that are encrypted, to be of any use when comparing the predicate conditions, they must first be decrypted, the goal of the optimizer is to do as little of these operations as possible. The following scenario's describe problems where the optimizer fails to resolve some queries involving encrypted columns into an optimal execution plan.
Scenario 1 : Encrypted columns on joined tables.
For example doing a join between 2 tables on encrypted columns, the values must be first decrypted in order to apply the join predicate supposing the following scenario:
AND B.COL2=<some value>;
with table A having COL1 encrypted and with an index on it (for the sake of the demonstration, I assume a low selectivity of the index and that the values returned by the second predicate are reasonably low in number). The driving table would be table B, since it has a predicate for COL2. Then, the values in COL1 from table B would be used to probe the values in table A using COL1, using, hopefully, an INDEX UNIQUE SCAN.
However, when the COL1 columns are both encrypted, the situation gets fairly complicated, even in theory. The value B.COL1 should be decrypted using the B key, then re-encrypted using the A key, then the returned value should be used to probe COL1 in table A. The alternative would be to do a full table scan on A, then filter out the values using the B.COL1 values. This is a very unfortunate situation, as for every value in COL1 in B a full table scan would be executed on table A.
The problem is the optimizer did not rewrite decrypt(column)=constant to column =encrypt(constant) in all cases such as a correlated subquery. This situation was actually met in a bug support filed with development:
<Bug:7147087> AFTER ENABLING TDE, EXECUTION PLAN CHANGES FOR THE WORSE
and it can be recognized from the following symptoms:
1. both tables participating in a join have encrypted columns.
2. there is at least a join condition with encrypted columns at both ends.
3. the second table has an index on the join column(s).
4. the INTERNAL_FUNCTION is applied to the encrypted columns in the join in the second table and the execution plan that used to be an INDEX UNIQUE SCAN on the unenecrypted columns turns into an INDEX RANGE SCAN or FULL TABLE SCAN.
Scenario 2: Pushed Predicates
The second known TDE performance bug is the one when the queries are using pushed predicates on encrypted columns inside explicit or implicit views and the encrypted column values are decrypted to filter out the values instead of encrypting the pushed predicates. This situation is met when:
1. external predicates are pushed into views
2. the execution plan presents predicate of the form INTERNAL_FUNCTION(column) = <pushed value>;
This problem is caused by:
<BUG:6469667> - SELECT FROM A VIEW ON ENCRYPED COLUMNS DOES NOT MAKE USE OF INDEXES
Scenario 3: Indexed Column with Column Level VPD and TDE.
If a query would have a predicate that should normally lead to Index Usage, the Column Level VPD would disable the index usage and lead to a full table scan plan.
<Note:602280.1> Column Level VPD on Indexed Columns Can Lead to Slow Execution Plan (Full Table Scan Instead of Index Access)
Having TDE on the same column disables the possibility to use the workaround, because function based indexes cannot be built on TDE encrypted columns.
Scenario 4: Execution plan performs a sort at the end, despite using an index that has the same columns in the same order as the ORDER BY clause
order by emp.company, emp.encrypted_column, emp.employee
encrypted_column being a column that is encrypted.
Consider as well that emp has an index, empindex on the following columns: emp.company, emp.encrypted_column, emp.employee
Consequently, the execution plan for the non-encrypted version of the query would be:
270318 TABLE ACCESS BY INDEX ROWID EMP (cr=168117 pr=28921 pw=0 time=34868251 us cost=141749 size=267319777 card=270293)
270318 INDEX RANGE SCAN EMPINDEX (cr=19399 pr=1105 pw=0 time=2346991 us cost=1528 size=0 card=270293)(object id 310095)
Apparently, the SORT operation that should have been triggered by the ORDER BY clause of the query does not exist anymore. The cause for this is the fact that the EMPINDEX index is already providing the data in the order requested by the query, consequently the sort operation is eliminated by the optimizer. This is an optimizer feature which is applied when generating the execution plan.
The same statement, when the ENCRYPTED_COLUMN is encrypted, becomes:
270895 SORT ORDER BY (cr=273577 pr=42165 pw=41686 time=30863823 us cost=318716 size=267489885 card=270465)
270895 TABLE ACCESS BY INDEX ROWID EMP (cr=273577 pr=479 pw=0 time=3867006 us cost=271496 size=267489885 card=270465)
270895 INDEX RANGE SCAN EMPINDEX (cr=2685 pr=479 pw=0 time=1089685 us cost=2669 size=0 card=270465)(object id 305665)
As seen, the SORT that would have been expected because of the ORDER BY clause is now executed. The optimizer can no longer eliminate the sort by accessing the EMPINDEX index in the ORDER BY requested order. This is because now the index data includes encrypted data and the ORDER BY order no longer corresponds to the EMPINDEX order. The optimizer feature can no longer be applied and the SORT ORDER BY operation becomes mandatory.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms