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