Same query with different response time

  • From: Lok P <loknath.73@xxxxxxxxx>
  • To: Oracle L <oracle-l@xxxxxxxxxxxxx>
  • Date: Sat, 26 Jun 2021 19:48:26 +0530

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 |          |                 |
================================================================================================================================================================



Other related posts: