Last active
May 30, 2022 14:58
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| **************** Plan on Version 11.2 which is running fast ************** | |
| Execution Plan | |
| ---------------------------------------------------------- | |
| Plan hash value: 280182164 | |
| ---------------------------------------------------------------------------------------------------------------------------------- | |
| | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | | |
| ---------------------------------------------------------------------------------------------------------------------------------- | |
| | 0 | SELECT STATEMENT | | 1 | 508 | 768K (1)| 02:33:38 | | | | |
| | 1 | NESTED LOOPS | | 1 | 508 | 768K (1)| 02:33:38 | | | | |
| | 2 | NESTED LOOPS | | 1365 | 508 | 768K (1)| 02:33:38 | | | | |
| | 3 | NESTED LOOPS | | 455 | 131K| 832 (0)| 00:00:10 | | | | |
| | 4 | PARTITION RANGE ALL | | 3 | 807 | 806 (0)| 00:00:10 | 1 | 268 | | |
| | 5 | TABLE ACCESS BY LOCAL INDEX ROWID| TAB_PART | 3 | 807 | 806 (0)| 00:00:10 | 1 | 268 | | |
| |* 6 | INDEX RANGE SCAN | TAB_PART_IX9 | 3 | | 805 (0)| 00:00:10 | 1 | 268 | | |
| | 7 | SORT UNIQUE | | 154 | 4004 | 22 (0)| 00:00:01 | | | | |
| | 8 | TABLE ACCESS BY INDEX ROWID | TSFS | 154 | 4004 | 22 (0)| 00:00:01 | | | | |
| |* 9 | INDEX RANGE SCAN | TSFS_IX1 | 154 | | 2 (0)| 00:00:01 | | | | |
| | 10 | PARTITION RANGE ALL | | 3 | | 1685 (0)| 00:00:21 | 1 | 538 | | |
| |* 11 | INDEX RANGE SCAN | FEES_IX2 | 3 | | 1685 (0)| 00:00:21 | 1 | 538 | | |
| |* 12 | TABLE ACCESS BY LOCAL INDEX ROWID | FEES | 1 | 213 | 1686 (0)| 00:00:21 | 1 | 1 | | |
| ---------------------------------------------------------------------------------------------------------------------------------- | |
| ***sql monitor | |
| Global Stats | |
| =========================================================================== | |
| | Elapsed | Cpu | IO | Cluster | Fetch | Buffer | Read | Read | | |
| | Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | | |
| =========================================================================== | |
| | 0.02 | 0.01 | 0.01 | 0.00 | 1 | 856 | 13 | 104KB | | |
| =========================================================================== | |
| SQL Plan Monitoring Details (Plan Hash Value=280182164) | |
| ================================================================================================================================================================================ | |
| | 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 | | 1 | 768K | | | 1 | | | | | | | |
| | 2 | NESTED LOOPS | | 1365 | 768K | | | 1 | | | | | | | |
| | 3 | NESTED LOOPS | | 455 | 832 | 1 | +0 | 1 | 0 | | | | | | |
| | 4 | PARTITION RANGE ALL | | 3 | 806 | 1 | +0 | 1 | 1 | | | | | | |
| | 5 | TABLE ACCESS BY LOCAL INDEX ROWID | TAB_PART | 3 | 806 | 1 | +0 | 268 | 1 | 8 | 65536 | | | | |
| | 6 | INDEX RANGE SCAN | TAB_PART_IX9 | 3 | 805 | 1 | +0 | 211 | 1 | 3 | 24576 | | | | |
| | 7 | SORT UNIQUE | | 154 | 22 | | | 1 | | | | | | | |
| | 8 | TABLE ACCESS BY INDEX ROWID | TSFS | 154 | 22 | | | 1 | | | | | | | |
| | 9 | INDEX RANGE SCAN | TSFS_IX1 | 154 | 2 | | | 1 | | 2 | 16384 | | | | |
| | 10 | PARTITION RANGE ALL | | 3 | 1685 | | | | | | | | | | |
| | 11 | INDEX RANGE SCAN | FEES_IX2 | 3 | 1685 | | | | | | | | | | |
| | 12 | TABLE ACCESS BY LOCAL INDEX ROWID | FEES | 1 | 1686 | | | | | | | | | | |
| ================================================================================================================================================================================ | |
| Outline Data | |
| ------------- | |
| /*+ | |
| BEGIN_OUTLINE_DATA | |
| SEMI_TO_INNER(@"SEL$5DA710D3" "TSFS"@"SEL$2") | |
| NLJ_BATCHING(@"SEL$5DA710D3" "FEES"@"SEL$1") | |
| USE_NL(@"SEL$5DA710D3" "FEES"@"SEL$1") | |
| USE_NL(@"SEL$5DA710D3" "TSFS"@"SEL$2") | |
| LEADING(@"SEL$5DA710D3" "TAB_PART"@"SEL$1" "TSFS"@"SEL$2" "FEES"@"SEL$1") | |
| INDEX(@"SEL$5DA710D3" "FEES"@"SEL$1" ("FEES"."SB_ID" "FEES"."O_TYP" | |
| "FEES"."OB_ID")) | |
| INDEX_RS_ASC(@"SEL$5DA710D3" "TSFS"@"SEL$2" ("TSFS"."OS_ID")) | |
| INDEX_RS_ASC(@"SEL$5DA710D3" "TAB_PART"@"SEL$1" ("TAB_PART"."TX_ID")) | |
| OUTLINE(@"SEL$2") | |
| OUTLINE(@"SEL$1") | |
| UNNEST(@"SEL$2") | |
| OUTLINE_LEAF(@"SEL$5DA710D3") | |
| ALL_ROWS | |
| DB_VERSION('11.2.0.4') | |
| OPTIMIZER_FEATURES_ENABLE('11.2.0.4') | |
| IGNORE_OPTIM_EMBEDDED_HINTS | |
| END_OUTLINE_DATA | |
| */ | |
| Predicate Information (identified by operation id): | |
| --------------------------------------------------- | |
| 6 - access("TAB_PART"."TX_ID"=TO_NUMBER(:B2)) | |
| 9 - access("OS_ID"="TAB_PART"."SB_ID") | |
| 11 - access("FEES"."SB_ID"="RS_ID" AND "TAB_PART"."SM_ID"="FEES"."OB_ID") | |
| filter("TAB_PART"."SM_ID"="FEES"."OB_ID") | |
| 12 - filter("FEES"."B_STS"='XXX') | |
| **************** Plan on Version 19C which is running longer ************** | |
| Execution Plan | |
| ---------------------------------------------------------- | |
| Plan hash value: 1498016186 | |
| --------------------------------------------------------------------------------------------------------------------------------------------------- | |
| | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | | |
| --------------------------------------------------------------------------------------------------------------------------------------------------- | |
| | 0 | SELECT STATEMENT | | 7 | 3542 | | 48M (3)| 00:31:51 | | | | |
| |* 1 | HASH JOIN SEMI | | 7 | 3542 | 53M| 48M (3)| 00:31:51 | | | | |
| | 2 | JOIN FILTER CREATE | :BF0000 | 113K| 51M| | 48M (3)| 00:31:49 | | | | |
| |* 3 | HASH JOIN | | 113K| 51M| | 48M (3)| 00:31:49 | | | | |
| | 4 | JOIN FILTER CREATE | :BF0001 | 176 | 46992 | | 807 (0)| 00:00:01 | | | | |
| | 5 | PARTITION RANGE ALL | | 176 | 46992 | | 807 (0)| 00:00:01 | 1 | 266 | | |
| | 6 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| TAB_PART | 176 | 46992 | | 807 (0)| 00:00:01 | 1 | 266 | | |
| |* 7 | INDEX RANGE SCAN | TAB_PART_IX9 | 176 | | | 799 (0)| 00:00:01 | 1 | 266 | | |
| | 8 | JOIN FILTER USE | :BF0001 | 50G| 9994G| | 48M (3)| 00:31:45 | | | | |
| | 9 | PARTITION RANGE ALL | | 50G| 9994G| | 48M (3)| 00:31:45 | 1 | 536 | | |
| |* 10 | TABLE ACCESS STORAGE FULL | FEES | 50G| 9994G| | 48M (3)| 00:31:45 | 1 | 536 | | |
| | 11 | JOIN FILTER USE | :BF0000 | 3359K| 83M| | 39647 (1)| 00:00:02 | | | | |
| |* 12 | TABLE ACCESS STORAGE FULL | TSFS | 3359K| 83M| | 39647 (1)| 00:00:02 | | | | |
| --------------------------------------------------------------------------------------------------------------------------------------------------- | |
| ***sql monitor | |
| Global Stats | |
| ========================================================================================================================================================================================= | |
| | Elapsed | Cpu | IO | Application | Concurrency | Cluster | Other | Fetch | Buffer | Read | Read | Write | Write | Uncompressed | Offload | Offload | Cell | | |
| | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Reqs | Bytes | Bytes | Elig Bytes | Returned Bytes | Offload | | |
| ========================================================================================================================================================================================= | |
| | 156 | 76 | 80 | 0.40 | 0.03 | 0.25 | 0.57 | 1 | 276M | 2M | 2TB | 18081 | 9GB | 8TB | 2TB | 21GB | 397.94% | | |
| ========================================================================================================================================================================================= | |
| SQL Plan Monitoring Details (Plan Hash Value=1498016186) | |
| ================================================================================================================================================================================================================================================ | |
| | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp | Activity | Activity Detail | | |
| | | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | (Max) | (Max) | (%) | (# samples) | | |
| ================================================================================================================================================================================================================================================ | |
| | 0 | SELECT STATEMENT | | | | 141 | +2 | 1 | 0 | | | | | . | . | 0.65 | Cpu (1) | | |
| | 1 | HASH JOIN SEMI | | 7 | 49M | 155 | +2 | 1 | 0 | 18081 | 9GB | 18081 | 9GB | 854MB | 9GB | 31.37 | Cpu (36) | | |
| | | | | | | | | | | | | | | | | | direct path read temp (11) | | |
| | | | | | | | | | | | | | | | | | direct path write temp (1) | | |
| | 2 | JOIN FILTER CREATE | :BF0000 | 113K | 49M | 139 | +2 | 1 | 16M | | | | | . | . | 0.65 | Cpu (1) | | |
| | 3 | HASH JOIN | | 113K | 49M | 139 | +2 | 1 | 16M | | | | | 682KB | . | 1.96 | Cpu (3) | | |
| | 4 | JOIN FILTER CREATE | :BF0001 | 176 | 807 | 1 | +2 | 1 | 1 | | | | | . | . | | | | |
| | 5 | PARTITION RANGE ALL | | 176 | 807 | 1 | +2 | 1 | 1 | | | | | . | . | | | | |
| | 6 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | TAB_PART | 176 | 807 | 1 | +2 | 266 | 1 | 8 | 65536 | | | . | . | | | | |
| | 7 | INDEX RANGE SCAN | TAB_PART_IX9 | 176 | 799 | 3 | +0 | 211 | 1 | 805 | 6MB | | | . | . | 0.65 | KSV master wait (1) | | |
| | 8 | JOIN FILTER USE | :BF0001 | 50G | 49M | 139 | +2 | 1 | 16M | | | | | . | . | | | | |
| | 9 | PARTITION RANGE ALL | | 50G | 49M | 139 | +2 | 1 | 16M | | | | | . | . | | | | |
| | 10 | TABLE ACCESS STORAGE FULL | FEES | 50G | 49M | 139 | +2 | 536 | 16M | 2M | 2TB | | | 7MB | . | 64.05 | enq: KO - fast object checkpoint (3) | | |
| | | | | | | | | | | | | | | | | | Cpu (29) | | |
| | | | | | | | | | | | | | | | | | reliable message (3) | | |
| | | | | | | | | | | | | | | | | | cell smart table scan (63) | | |
| | 11 | JOIN FILTER USE | :BF0000 | 3M | 39647 | 1 | +142 | 1 | 2M | | | | | . | . | | | | |
| | 12 | TABLE ACCESS STORAGE FULL | TSFS | 3M | 39647 | 3 | +140 | 1 | 2M | 1846 | 2GB | | | 7MB | . | 0.65 | Cpu (1) | | |
| ================================================================================================================================================================================================================================================ | |
| Outline Data | |
| ------------- | |
| /*+ | |
| BEGIN_OUTLINE_DATA | |
| PX_JOIN_FILTER(@"SEL$5DA710D3" "TSFS"@"SEL$2") | |
| PX_JOIN_FILTER(@"SEL$5DA710D3" "FEES"@"SEL$1") | |
| USE_HASH(@"SEL$5DA710D3" "TSFS"@"SEL$2") | |
| USE_HASH(@"SEL$5DA710D3" "FEES"@"SEL$1") | |
| LEADING(@"SEL$5DA710D3" "TAB_PART"@"SEL$1" "FEES"@"SEL$1" "TSFS"@"SEL$2") | |
| FULL(@"SEL$5DA710D3" "TSFS"@"SEL$2") | |
| FULL(@"SEL$5DA710D3" "FEES"@"SEL$1") | |
| BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$5DA710D3" "TAB_PART"@"SEL$1") | |
| INDEX_RS_ASC(@"SEL$5DA710D3" "TAB_PART"@"SEL$1" ("TAB_PART"."TX_ID")) | |
| OUTLINE(@"SEL$2") | |
| OUTLINE(@"SEL$1") | |
| UNNEST(@"SEL$2") | |
| OUTLINE_LEAF(@"SEL$5DA710D3") | |
| ALL_ROWS | |
| OPT_PARAM('_fix_control' '27268249:0') | |
| OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false') | |
| OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false') | |
| OPT_PARAM('_px_adaptive_dist_method' 'off') | |
| DB_VERSION('19.1.0') | |
| OPTIMIZER_FEATURES_ENABLE('19.1.0') | |
| IGNORE_OPTIM_EMBEDDED_HINTS | |
| END_OUTLINE_DATA | |
| */ | |
| Predicate Information (identified by operation id): | |
| --------------------------------------------------- | |
| 1 - access("FEES"."SB_ID"="RS_ID" AND "OS_ID"="TAB_PART"."SB_ID") | |
| 3 - access("TAB_PART"."SM_ID"="FEES"."OB_ID") | |
| 7 - access("TAB_PART"."TX_ID"=TO_NUMBER(:B2)) | |
| 10 - storage("FEES"."B_STS"='XXX' AND SYS_OP_BLOOM_FILTER(:BF0001,"FEES"."OB_ID")) | |
| filter("FEES"."B_STS"='XXX' AND SYS_OP_BLOOM_FILTER(:BF0001,"FEES"."OB_ID")) | |
| 12 - storage(SYS_OP_BLOOM_FILTER(:BF0000,"RS_ID","OS_ID")) | |
| filter(SYS_OP_BLOOM_FILTER(:BF0000,"RS_ID","OS_ID")) | |
| ************** Plan on version 19c when force with the 11.2 outline************** | |
| Execution Plan | |
| ---------------------------------------------------------- | |
| Plan hash value: 280182164 | |
| ---------------------------------------------------------------------------------------------------------------------------------- | |
| | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | | |
| ---------------------------------------------------------------------------------------------------------------------------------- | |
| | 0 | SELECT STATEMENT | | 7 | 3542 | 61M (1)| 00:40:06 | | | | |
| | 1 | NESTED LOOPS | | 7 | 3542 | 61M (1)| 00:40:06 | | | | |
| | 2 | NESTED LOOPS | | 110K| 3542 | 61M (1)| 00:40:06 | | | | |
| | 3 | NESTED LOOPS | | 36692 | 10M| 1207 (0)| 00:00:01 | | | | |
| | 4 | PARTITION RANGE ALL | | 176 | 46992 | 807 (0)| 00:00:01 | 1 | 266 | | |
| | 5 | TABLE ACCESS BY LOCAL INDEX ROWID| TAB_PART | 176 | 46992 | 807 (0)| 00:00:01 | 1 | 266 | | |
| |* 6 | INDEX RANGE SCAN | TAB_PART_IX9 | 176 | | 799 (0)| 00:00:01 | 1 | 266 | | |
| | 7 | SORT UNIQUE | | 209 | 5434 | 30 (0)| 00:00:01 | | | | |
| | 8 | TABLE ACCESS BY INDEX ROWID | TSFS | 209 | 5434 | 30 (0)| 00:00:01 | | | | |
| |* 9 | INDEX RANGE SCAN | TSFS_IX1 | 209 | | 2 (0)| 00:00:01 | | | | |
| | 10 | PARTITION RANGE ALL | | 3 | | 1677 (0)| 00:00:01 | 1 | 536 | | |
| |* 11 | INDEX RANGE SCAN | FEES_IX2 | 3 | | 1677 (0)| 00:00:01 | 1 | 536 | | |
| |* 12 | TABLE ACCESS BY LOCAL INDEX ROWID | FEES | 1 | 213 | 1678 (0)| 00:00:01 | 1 | 1 | | |
| ---------------------------------------------------------------------------------------------------------------------------------- | |
| ***sql monitor | |
| Global Stats | |
| ========================================================================================= | |
| | Elapsed | Cpu | IO | Concurrency | Cluster | Fetch | Buffer | Read | Read | | |
| | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | | |
| ========================================================================================= | |
| | 0.01 | 0.01 | 0.00 | 0.00 | 0.00 | 1 | 857 | 1 | 8192 | | |
| ========================================================================================= | |
| SQL Plan Monitoring Details (Plan Hash Value=280182164) | |
| ================================================================================================================================================================================ | |
| | 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 | | | | | | 1 | | | | | | | |
| | 2 | NESTED LOOPS | | 7 | 62M | | | 1 | | | | | | | |
| | 3 | NESTED LOOPS | | 36692 | 1207 | 1 | +0 | 1 | 0 | | | | | | |
| | 4 | PARTITION RANGE ALL | | 176 | 807 | 1 | +0 | 1 | 1 | | | | | | |
| | 5 | TABLE ACCESS BY LOCAL INDEX ROWID | TAB_PART | 176 | 807 | 1 | +0 | 266 | 1 | | | | | | |
| | 6 | INDEX RANGE SCAN | TAB_PART_IX9 | 176 | 799 | 1 | +0 | 211 | 1 | | | | | | |
| | 7 | SORT UNIQUE | | 209 | 30 | | | 1 | | | | | | | |
| | 8 | TABLE ACCESS BY INDEX ROWID | TSFS | 209 | 30 | | | 1 | | | | | | | |
| | 9 | INDEX RANGE SCAN | TSFS_IX1 | 209 | 2 | | | 1 | | 1 | 8192 | | | | |
| | 10 | PARTITION RANGE ALL | | 3 | 1677 | | | | | | | | | | |
| | 11 | INDEX RANGE SCAN | FEES_IX2 | 3 | 1677 | | | | | | | | | | |
| | 12 | TABLE ACCESS BY LOCAL INDEX ROWID | FEES | 1 | 1678 | | | | | | | | | | |
| ================================================================================================================================================================================ | |
| Predicate Information (identified by operation id): | |
| --------------------------------------------------- | |
| 6 - access("TAB_PART"."TX_ID"=TO_NUMBER(:B2)) | |
| 9 - access("OS_ID"="TAB_PART"."SB_ID") | |
| 11 - access("FEES"."SB_ID"="RS_ID" AND "TAB_PART"."SM_ID"="FEES"."OB_ID") | |
| filter("TAB_PART"."SM_ID"="FEES"."OB_ID") | |
| 12 - filter("FEES"."B_STS"='XXX') | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment