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
SELECT
PBR.RTID, PBR.BI1, PBR.BI2, PBR.RTI2, PBRT.DSCR
FROM PBRT, PBR
WHERE PBR.ROLE_TYPE_ID_1 = 999 AND PBR.EDT <= SYSDATE AND PBR.RTID =
PBRT.RTID
AND ( PBR.RL_DT >=
(SELECT SUC_STRT_TIM FROM PPSC WHERE SID = 111 AND
NVL (UPPER (STS), 'F') = 'F')
AND PBR.RL_DT <
(SELECT CUR_STRT_TIM FROM PPSC WHERE SID = 111 AND
NVL (UPPER (STS), 'F') = 'F'))
************ Before TSE *******************
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
SQL ID : abhz9mnd107sb
SQL Execution ID : 16777216
Execution Started : 06/22/2021 13:01:07
First Refresh Time : 06/22/2021 13:01:07
Last Refresh Time : 06/22/2021 13:02:16
Duration : 69s
Fetch Calls : 1
Global Stats
============================================================================
| Elapsed | Cpu | IO | Cluster | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
============================================================================
| 72 | 18 | 54 | 0.00 | 1 | 1M | 58255 | 8GB |
============================================================================
SQL Plan Monitoring Details (Plan Hash Value=4240615525)
======================================================================================================================================================================================================
| Id | Operation | Name |
Rows | Cost | Time | Start | Execs | Rows | Read | Read |
Activity | Activity Detail |
| | | |
(Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (%)
| (# samples) |
======================================================================================================================================================================================================
| 0 | SELECT STATEMENT | |
| | | | 1 | | | |
| |
| 1 | NESTED LOOPS | |
3 | 13 | | | 1 | | | |
| |
| 2 | NESTED LOOPS | |
3 | 13 | | | 1 | | | |
| |
| 3 | TABLE ACCESS STORAGE FULL FIRST ROWS | PBR |
3 | 11 | 69 | +1 | 1 | 0 | 58254 | 8GB | 100.00
| Cpu (15) |
| | | |
| | | | | | | |
| cell multiblock physical read (49) |
| | | |
| | | | | | | |
| cell single block physical read (5) |
| 4 | TABLE ACCESS BY INDEX ROWID | PPSC |
1 | 1 | 1 | +2 | 1 | 0 | 1 | 8192 |
| |
| 5 | INDEX UNIQUE SCAN | PPSC_UQ |
1 | 1 | 1 | +2 | 1 | 1 | | |
| |
| 6 | TABLE ACCESS BY INDEX ROWID | PPSC |
1 | 1 | | | | | | |
| |
| 7 | INDEX UNIQUE SCAN | PPSC_UQ |
1 | 1 | | | | | | |
| |
| X | INDEX UNIQUE SCAN | PBRT |
1 | 1 | | | | | | |
| |
| 9 | TABLE ACCESS BY INDEX ROWID | PBRT |
1 | 1 | | | | | | |
| |
======================================================================================================================================================================================================
**************** After TSE*********************
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
SQL ID : abhz9mnd107sb
SQL Execution ID : 16777216
Execution Started : 06/23/2021 06:21:19
First Refresh Time : 06/23/2021 06:21:19
Last Refresh Time : 06/23/2021 06:22:58
Duration : 99s
Fetch Calls : 1
Global Stats
============================================================================
| Elapsed | Cpu | IO | Cluster | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
============================================================================
| 102 | 35 | 67 | 0.00 | 1 | 1M | 61252 | 7GB |
============================================================================
SQL Plan Monitoring Details (Plan Hash Value=4240615525)
==================================================================================================================================================================================
| Id | Operation | Name |
Rows | Cost | Time | Start | Execs | Rows | Read | Read |
Activity | Activity Detail |
| | | |
(Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (%)
| (# samples) |
==================================================================================================================================================================================
| 0 | SELECT STATEMENT | |
| | | | 1 | | | |
| |
| 1 | NESTED LOOPS | |
2 | 9 | | | 1 | | | |
| |
| 2 | NESTED LOOPS | |
2 | 9 | | | 1 | | | |
| |
| 3 | TABLE ACCESS STORAGE FULL FIRST ROWS | PBR |
2 | X | 99 | +1 | 1 | 0 | 61252 | 7GB |
| |
| 4 | TABLE ACCESS BY INDEX ROWID | PPSC |
1 | 1 | 1 | +2 | 1 | 0 | | |
| |
| 5 | INDEX UNIQUE SCAN | PPSC_UQ |
1 | 1 | 1 | +2 | 1 | 1 | | |
| |
| 6 | TABLE ACCESS BY INDEX ROWID | PPSC |
1 | 1 | | | | | | |
| |
| 7 | INDEX UNIQUE SCAN | PPSC_UQ |
1 | 1 | | | | | | |
| |
| X | INDEX UNIQUE SCAN | PBRT |
1 | 1 | | | | | | |
| |
| 9 | TABLE ACCESS BY INDEX ROWID | PBRT |
1 | 1 | | | | | | |
| |
==================================================================================================================================================================================
*************************************************************************************************************************************
SQL Text
------------------------------
select count(0) from PBR
********************** Before TSE********************
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
SQL ID : 5yrgp0w3hpwx6
SQL Execution ID : 16777216
Execution Started : 06/22/2021 16:02:48
First Refresh Time : 06/22/2021 16:02:48
Last Refresh Time : 06/22/2021 16:03:11
Duration : 23s
Fetch Calls : 1
Global Stats
===========================================================================
| Elapsed | Cpu | IO | Cluster | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
===========================================================================
| 23 | 5.88 | 16 | 1.12 | 1 | 243K | 2036 | 2GB |
===========================================================================
SQL Plan Monitoring Details (Plan Hash Value=872042381)
==========================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time
| Start | Execs | Rows | Read | Read | Activity | Activity Detail |
| | | | (Estim) | |
Active(s) | Active | | (Actual) | Reqs | Bytes | (%) | (# samples)
|
==========================================================================================================================================================
| 0 | SELECT STATEMENT | | | |
22 | +2 | 1 | 1 | | | | |
| 1 | SORT AGGREGATE | | 1 | |
22 | +2 | 1 | 1 | | | | |
| 2 | INDEX STORAGE FAST FULL SCAN | PR_IDX | 70M | 66698 |
23 | +1 | 1 | 70M | 2036 | 2GB | | |
==========================================================================================================================================================
************************** After TSE *************************
SQL Text
------------------------------
select count(0) from PBR
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 2
SQL ID : 5yrgp0w3hpwx6
SQL Execution ID : 33554432
Execution Started : 06/23/2021 08:35:32
First Refresh Time : 06/23/2021 08:35:32
Last Refresh Time : 06/23/2021 08:36:03
Duration : 31s
Fetch Calls : 1
Global Stats
===========================================================================
| Elapsed | Cpu | IO | Cluster | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
===========================================================================
| 31 | 13 | 18 | 0.86 | 1 | 243K | 2036 | 2GB |
===========================================================================
SQL Plan Monitoring Details (Plan Hash Value=872042381)
==========================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time
| Start | Execs | Rows | Read | Read | Activity | Activity Detail |
| | | | (Estim) | |
Active(s) | Active | | (Actual) | Reqs | Bytes | (%) | (# samples)
|
==========================================================================================================================================================
| 0 | SELECT STATEMENT | | | |
30 | +2 | 1 | 1 | | | | |
| 1 | SORT AGGREGATE | | 1 | |
30 | +2 | 1 | 1 | | | | |
| 2 | INDEX STORAGE FAST FULL SCAN | PR_IDX | 70M | 64691 |
31 | +1 | 1 | 70M | 2036 | 2GB | | |
==========================================================================================================================================================
*************************************************************************************************************************************
SQL Text
------------------------------
select * from PBR where RL_DT > sysdate - 200
********************* Before TSE*********************
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
SQL ID : 18chn67bbs705
SQL Execution ID : 16777216
Execution Started : 06/22/2021 15:57:35
First Refresh Time : 06/22/2021 15:57:35
Last Refresh Time : 06/22/2021 15:58:45
Duration : 70s
Fetch Calls : 1
Global Stats
============================================================================
| Elapsed | Cpu | IO | Cluster | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
============================================================================
| 73 | 17 | 57 | 0.00 | 1 | 1M | 59159 | 8GB |
============================================================================
SQL Plan Monitoring Details (Plan Hash Value=3601134248)
==================================================================================================================================================================
| Id | Operation | Name | Rows | Cost |
Time | Start | Execs | Rows | Read | Read | Activity | Activity
Detail |
| | | | (Estim) | |
Active(s) | Active | | (Actual) | Reqs | Bytes | (%) | (#
samples) |
==================================================================================================================================================================
| 0 | SELECT STATEMENT | | | |
69 | +2 | 1 | 451 | | | |
|
| 1 | TABLE ACCESS STORAGE FULL FIRST ROWS | PBR | 1 | 41659 |
71 | +0 | 1 | 451 | 59159 | 8GB | |
|
==================================================================================================================================================================
SQL Text
------------------------------
select * from PBR where RL_DT > sysdate - 200
********************* After TSE*********************
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
SQL ID : 18chn67bbs705
SQL Execution ID : 16777216
Execution Started : 06/23/2021 04:54:35
First Refresh Time : 06/23/2021 04:54:35
Last Refresh Time : 06/23/2021 04:56:10
Duration : 95s
Fetch Calls : 1
Global Stats
===========================================================================
| Elapsed | Cpu | IO | Cluster | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
===========================================================================
| 96 | 34 | 62 | 0.00 | 1 | 1M | 9587 | 9GB |
===========================================================================
SQL Plan Monitoring Details (Plan Hash Value=3601134248)
================================================================================================================================================================
| Id | Operation | Name | Rows | Cost |
Time | Start | Execs | Rows | Read | Read | Activity | Activity Detail
|
| | | | (Estim) | |
Active(s) | Active | | (Actual) | Reqs | Bytes | (%) | (# samples)
|
================================================================================================================================================================
| 0 | SELECT STATEMENT | | | |
94 | +2 | 1 | 451 | | | | |
| 1 | TABLE ACCESS STORAGE FULL FIRST ROWS | PBR | 2 | 54 |
95 | +1 | 1 | 451 | 9587 | 9GB | | |
================================================================================================================================================================
