************** Explain plan from dbms_xplan.display_cursor***************** --------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | 314K(100)| | | 1 | NESTED LOOPS | | 1 | 722 | | 314K (1)| 00:00:13 | | 2 | NESTED LOOPS | | 1 | 714 | | 314K (1)| 00:00:13 | | 3 | NESTED LOOPS OUTER | | 1 | 706 | | 314K (1)| 00:00:13 | | 4 | NESTED LOOPS OUTER | | 1 | 686 | | 314K (1)| 00:00:13 | |* 5 | HASH JOIN OUTER | | 1 | 631 | | 314K (1)| 00:00:13 | | 6 | JOIN FILTER CREATE | :BF0000 | 1 | 624 | | 314K (1)| 00:00:13 | | 7 | NESTED LOOPS | | 1 | 624 | | 314K (1)| 00:00:13 | | 8 | NESTED LOOPS | | 1 | 624 | | 314K (1)| 00:00:13 | | 9 | NESTED LOOPS | | 1 | 563 | | 314K (1)| 00:00:13 | | 10 | NESTED LOOPS | | 1 | 545 | | 314K (1)| 00:00:13 | | 11 | NESTED LOOPS OUTER | | 1 | 433 | | 314K (1)| 00:00:13 | | 12 | NESTED LOOPS | | 1 | 407 | | 314K (1)| 00:00:13 | |* 13 | HASH JOIN | | 1 | 381 | 37M| 314K (1)| 00:00:13 | |* 14 | HASH JOIN | | 105K| 36M| 34M| 30121 (2)| 00:00:02 | | 15 | JOIN FILTER CREATE | :BF0001 | 105K| 36M| | 30121 (2)| 00:00:02 | |* 16 | HASH JOIN | | 105K| 32M| 29M| 27754 (2)| 00:00:02 | | 17 | JOIN FILTER CREATE | :BF0002 | 105K| 32M| | 27754 (2)| 00:00:02 | |* 18 | HASH JOIN | | 106K| 28M| | 21325 (2)| 00:00:01 | | 19 | JOIN FILTER CREATE | :BF0003 | 106K| 28M| | 21325 (2)| 00:00:01 | |* 20 | HASH JOIN | | 20 | 5380 | | 305 (1)| 00:00:01 | | 21 | JOIN FILTER CREATE | :BF0004 | 20 | 5380 | | 305 (1)| 00:00:01 | |* 22 | HASH JOIN | | 20 | 4520 | | 295 (1)| 00:00:01 | | 23 | JOIN FILTER CREATE | :BF0005 | 21 | 4578 | | 284 (0)| 00:00:01 | |* 24 | HASH JOIN | | 21 | 4578 | | 284 (0)| 00:00:01 | | 25 | JOIN FILTER CREATE | :BF0006 | 21 | 4410 | | 274 (0)| 00:00:01 | |* 26 | HASH JOIN | | 21 | 4410 | | 274 (0)| 00:00:01 | | 27 | JOIN FILTER CREATE | :BF0007 | 4 | 800 | | 272 (0)| 00:00:01 | | 28 | NESTED LOOPS | | 4 | 800 | | 272 (0)| 00:00:01 | | 29 | NESTED LOOPS | | 4 | 800 | | 272 (0)| 00:00:01 | |* 30 | TCHED TABLE ACCESS BY INDEX ROWID BA | PA | 4 | 208 | | 260 (0)| 00:00:01 | |* 31 | INDEX RANGE SCAN | PA_IX5 | 1438 | | | 21 (0)| 00:00:01 | |* 32 | INDEX UNIQUE SCAN | PE_IX1 | 1 | | | 2 (0)| 00:00:01 | |* 33 | TABLE ACCESS BY INDEX ROWID | PE | 1 | 148 | | 3 (0)| 00:00:01 | | 34 | JOIN FILTER USE | :BF0007 | 405 | 4050 | | 2 (0)| 00:00:01 | |* 35 | TABLE ACCESS STORAGE FULL | EA | 405 | 4050 | | 2 (0)| 00:00:01 | | 36 | JOIN FILTER USE | :BF0006 | 128 | 1024 | | 10 (0)| 00:00:01 | |* 37 | TABLE ACCESS STORAGE FULL | ACQ | 128 | 1024 | | 10 (0)| 00:00:01 | | 38 | JOIN FILTER USE | :BF0005 | 189 | 1512 | | 10 (0)| 00:00:01 | |* 39 | TABLE ACCESS STORAGE FULL | CRR | 189 | 1512 | | 10 (0)| 00:00:01 | | 40 | JOIN FILTER USE | :BF0004 | 591 | 25413 | | 10 (0)| 00:00:01 | |* 41 | TABLE ACCESS STORAGE FULL | SR | 591 | 25413 | | 10 (0)| 00:00:01 | | 42 | JOIN FILTER USE | :BF0003 | 685K| 5358K| | 21014 (2)| 00:00:01 | |* 43 | TABLE ACCESS STORAGE FULL | BTC | 685K| 5358K| | 21014 (2)| 00:00:01 | | 44 | JOIN FILTER USE | :BF0002 | 1710K| 81M| | 5566 (3)| 00:00:01 | |* 45 | TABLE ACCESS STORAGE FULL | MRC | 1710K| 81M| | 5566 (3)| 00:00:01 | | 46 | JOIN FILTER USE | :BF0001 | 912K| 27M| | 1887 (2)| 00:00:01 | |* 47 | TABLE ACCESS STORAGE FULL | MBR | 912K| 27M| | 1887 (2)| 00:00:01 | |* 48 | TABLE ACCESS BY INDEX ROWID BATCHED | PA | 2173K| 45M| | 283K (1)| 00:00:12 | |* 49 | INDEX RANGE SCAN | PA_IX6 | 3803K| | | 44886 (1)| 00:00:02 | | 50 | TABLE ACCESS BY INDEX ROWID | PMBU | 1 | 26 | | 2 (0)| 00:00:01 | |* 51 | INDEX UNIQUE SCAN | PBU_PK | 1 | | | 1 (0)| 00:00:01 | |* 52 | TABLE ACCESS BY INDEX ROWID BATCHED | PAA | 1 | 26 | | 6 (0)| 00:00:01 | |* 53 | INDEX RANGE SCAN | PA_IX4 | 3 | | | 3 (0)| 00:00:01 | | 54 | TABLE ACCESS BY INDEX ROWID | PCA | 1 | 112 | | 3 (0)| 00:00:01 | |* 55 | INDEX UNIQUE SCAN | PCA_IX2 | 1 | | | 2 (0)| 00:00:01 | | 56 | TABLE ACCESS BY INDEX ROWID | DEP | 1 | 18 | | 3 (0)| 00:00:01 | |* 57 | INDEX UNIQUE SCAN | DEP_IX1 | 1 | | | 2 (0)| 00:00:01 | |* 58 | INDEX UNIQUE SCAN | LO_IX1 | 1 | | | 2 (0)| 00:00:01 | | 59 | TABLE ACCESS BY INDEX ROWID | LO | 1 | 61 | | 3 (0)| 00:00:01 | | 60 | JOIN FILTER USE | :BF0000 | 7 | 49 | | 2 (0)| 00:00:01 | |* 61 | TABLE ACCESS STORAGE FULL | IL | 7 | 49 | | 2 (0)| 00:00:01 | |* 62 | TABLE ACCESS BY INDEX ROWID BATCHED | ADD | 1 | 55 | | 4 (0)| 00:00:01 | |* 63 | INDEX RANGE SCAN | ADD_IX1 | 2 | | | 3 (0)| 00:00:01 | | 64 | TABLE ACCESS BY INDEX ROWID | AR | 1 | 20 | | 3 (0)| 00:00:01 | |* 65 | INDEX UNIQUE SCAN | AR_IX1 | 1 | | | 2 (0)| 00:00:01 | | 66 | TABLE ACCESS BY INDEX ROWID BATCHED | CRR | 1 | 8 | | 1 (0)| 00:00:01 | |* 67 | INDEX RANGE SCAN | CRR_IX2 | 1 | | | 0 (0)| | | 68 | TABLE ACCESS BY INDEX ROWID BATCHED | CRR | 1 | 8 | | 1 (0)| 00:00:01 | |* 69 | INDEX RANGE SCAN | CRR_IX2 | 1 | | | 0 (0)| | --------------------------------------------------------------------------------------------------------------------------------------------- ***************** SQL MONITOR*********************** Global Information ------------------------------ Status : EXECUTING Instance ID : 1 SQL ID : 33xg53nmfm7bq SQL Execution ID : 16777216 Execution Started : 12/28/2021 13:20:50 First Refresh Time : 12/28/2021 13:20:50 Last Refresh Time : 12/28/2021 13:23:21 Duration : 152s Module/Action : SQL*Plus/- Program : sqlplus.exe Binds ======================================================================================================================== | Name | Position | Type | Value | ======================================================================================================================== | :B1 | 1 | NUMBER | 7 | ======================================================================================================================== Global Stats ====================================================================================================================================================== | Elapsed | Cpu | IO | Application | Concurrency | Cluster | Buffer | Read | Read | Uncompressed | Offload | Offload | Cell | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Bytes | Elig Bytes | Returned Bytes | Offload | ====================================================================================================================================================== | 158 | 12 | 145 | 0.00 | 0.01 | 0.65 | 956K | 127K | 2GB | 787MB | 1GB | 228KB | 99.98% | ====================================================================================================================================================== SQL Plan Monitoring Details (Plan Hash Value=2353836206) ========================================================================================================================================================================================================================================================= | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | | (%) | (# samples) | ========================================================================================================================================================================================================================================================= | 0 | SELECT STATEMENT | | | | | | 1 | | | | . | | | | 1 | HASH JOIN | | 1 | 315K | | | 1 | | | | . | | | | 2 | NESTED LOOPS | | 1 | 315K | | | 1 | | | | . | | | | 3 | STATISTICS COLLECTOR | | | | | | 1 | | | | . | | | | 4 | HASH JOIN | | 1 | 315K | | | 1 | | | | . | | | | 5 | NESTED LOOPS | | 1 | 315K | | | 1 | | | | . | | | | 6 | STATISTICS COLLECTOR | | | | | | 1 | | | | . | | | | 7 | NESTED LOOPS OUTER | | 1 | 315K | | | 1 | | | | . | | | | 8 | NESTED LOOPS OUTER | | 1 | 315K | | | 1 | | | | . | | | | 9 | HASH JOIN OUTER | | 1 | 315K | | | 1 | | | | 65536 | | | | 10 | JOIN FILTER CREATE | :BF0000 | 1 | 315K | | | 1 | | | | . | | | | 11 | NESTED LOOPS | | 1 | 315K | | | 1 | | | | . | | | | 12 | NESTED LOOPS | | 1 | 315K | | | 1 | | | | . | | | | 13 | NESTED LOOPS | | 1 | 315K | | | 1 | | | | . | | | | 14 | NESTED LOOPS | | 1 | 315K | | | 1 | | | | . | | | | 15 | NESTED LOOPS OUTER | | 1 | 315K | | | 1 | | | | . | | | | 16 | HASH JOIN | | 1 | 315K | | | 1 | | | | . | | | | 17 | NESTED LOOPS | | 1 | 315K | | | 1 | | | | . | | | | 18 | STATISTICS COLLECTOR | | | | | | 1 | | | | . | | | | -> 19 | HASH JOIN | | 1 | 315K | 144 | +8 | 1 | 0 | | | 576KB | | | | 20 | NESTED LOOPS | | 1 | 315K | | | 1 | | | | . | | | | 21 | STATISTICS COLLECTOR | | | | | | 1 | | | | . | | | | 22 | HASH JOIN | | 106K | 30121 | | | 1 | | | | . | | | | 23 | JOIN FILTER CREATE | :BF0001 | 106K | 30121 | | | 1 | | | | . | | | | 24 | NESTED LOOPS | | 106K | 30121 | | | 1 | | | | . | | | | 25 | STATISTICS COLLECTOR | | | | | | 1 | | | | . | | | | 26 | HASH JOIN | | 106K | 27754 | | | 1 | | | | . | | | | 27 | JOIN FILTER CREATE | :BF0002 | 106K | 27754 | | | 1 | | | | . | | | | 28 | NESTED LOOPS | | 106K | 27754 | | | 1 | | | | . | | | | 29 | STATISTICS COLLECTOR | | | | | | 1 | | | | . | | | | 30 | HASH JOIN | | 106K | 21325 | | | 1 | | | | . | | | | 31 | JOIN FILTER CREATE | :BF0003 | 106K | 21325 | | | 1 | | | | . | | | | 32 | NESTED LOOPS | | 106K | 21325 | | | 1 | | | | . | | | | 33 | STATISTICS COLLECTOR | | | | | | 1 | | | | . | | | | 34 | HASH JOIN | | 20 | 305 | | | 1 | | | | . | | | | 35 | JOIN FILTER CREATE | :BF0004 | 20 | 305 | | | 1 | | | | . | | | | 36 | NESTED LOOPS | | 20 | 305 | | | 1 | | | | . | | | | 37 | STATISTICS COLLECTOR | | | | | | 1 | | | | . | | | | 38 | HASH JOIN | | 20 | 295 | | | 1 | | | | . | | | | 39 | JOIN FILTER CREATE | :BF0005 | 21 | 284 | | | 1 | | | | . | | | | 40 | HASH JOIN | | 21 | 284 | | | 1 | | | | . | | | | 41 | JOIN FILTER CREATE | :BF0006 | 21 | 274 | | | 1 | | | | . | | | | 42 | HASH JOIN | | 21 | 274 | | | 1 | | | | . | | | | 43 | JOIN FILTER CREATE | :BF0007 | 4 | 272 | | | 1 | | | | . | | | | 44 | NESTED LOOPS | | 4 | 272 | 3 | +2 | 1 | 0 | | | . | | | | 45 | NESTED LOOPS | | 4 | 272 | 3 | +2 | 1 | 5 | | | . | | | | 46 | TABLE ACCESS BY INDEX ROWID BATCHED | PA | 4 | 260 | 4 | +1 | 1 | 5 | 1364 | 11MB | . | 1.97 | cell list of blocks physical read (2) | | | | | | | | | | | | | | | cell single block physical read (1) | | 47 | INDEX RANGE SCAN | PA_IX5 | 1438 | 21 | 3 | +2 | 1 | 5927 | 38 | 304KB | . | | | | -> 48 | INDEX UNIQUE SCAN | PE_IX1 | 1 | 2 | 150 | +2 | 8 | 5 | 2 | 16384 | . | | | | 49 | TABLE ACCESS BY INDEX ROWID | PE | 1 | 3 | | | 8 | | 4 | 32768 | . | | | | 50 | JOIN FILTER USE | :BF0007 | 405 | 2 | | | 1 | | | | . | | | | 51 | TABLE ACCESS STORAGE FULL | EA | 405 | 2 | | | 1 | | | | . | | | | 52 | JOIN FILTER USE | :BF0006 | 128 | 10 | | | | | | | . | | | | 53 | TABLE ACCESS STORAGE FULL | ACQ | 128 | 10 | | | | | | | . | | | | 54 | JOIN FILTER USE | :BF0005 | 189 | 10 | | | | | | | . | | | | 55 | TABLE ACCESS STORAGE FULL | CRR | 189 | 10 | | | | | | | . | | | | 56 | TABLE ACCESS BY INDEX ROWID | SR | 1 | 10 | | | | | | | . | | | | 57 | INDEX UNIQUE SCAN | SR_IX1 | | | | | | | | | . | | | | 58 | JOIN FILTER USE | :BF0004 | 591 | 10 | | | 1 | | | | . | | | | 59 | TABLE ACCESS STORAGE FULL | SR | 591 | 10 | | | 1 | | | | . | | | | 60 | TABLE ACCESS BY INDEX ROWID BATCHED | BTC | 5359 | 21014 | | | | | | | . | | | | 61 | INDEX RANGE SCAN | BATCH_IX5 | | | | | | | | | . | | | | 62 | JOIN FILTER USE | :BF0003 | 686K | 21014 | | | 1 | | | | . | | | | 63 | TABLE ACCESS STORAGE FULL | BTC | 686K | 21014 | | | 1 | | 1160 | 1GB | . | | | | 64 | TABLE ACCESS BY INDEX ROWID | MRC | 1 | 5566 | | | | | | | . | | | | 65 | INDEX UNIQUE SCAN | MRC_IX1 | | | | | | | | | . | | | | 66 | JOIN FILTER USE | :BF0002 | 2M | 5566 | | | 1 | | | | . | | | | 67 | TABLE ACCESS STORAGE FULL | MRC | 2M | 5566 | 3 | +4 | 1 | 0 | 1222 | 294MB | . | 1.97 | cell multiblock physical read (3) | | 68 | TABLE ACCESS BY INDEX ROWID | MBR | 1 | 1887 | | | | | | | . | | | | 69 | INDEX UNIQUE SCAN | MBR_IX1 | | | | | | | | | . | | | | 70 | JOIN FILTER USE | :BF0001 | 913K | 1887 | | | 1 | | | | . | | | | 71 | TABLE ACCESS STORAGE FULL | MBR | 913K | 1887 | 1 | +7 | 1 | 0 | 111 | 103MB | . | 0.66 | cell multiblock physical read (1) | | 72 | TABLE ACCESS BY INDEX ROWID BATCHED | PA | 1 | 284K | | | | | | | . | | | | 73 | INDEX RANGE SCAN | PA_IX4 | 4M | 44886 | | | | | | | . | | | | -> 74 | TABLE ACCESS BY INDEX ROWID BATCHED | PA | 2M | 284K | 145 | +8 | 1 | 1M | 113K | 905MB | . | 78.95 | Cpu (2) | | | | | | | | | | | | | | | cell list of blocks physical read (7) | | | | | | | | | | | | | | | cell multiblock physical read (6) | | | | | | | | | | | | | | | cell single block physical read (104) | | | | | | | | | | | | | | | cell single block read request (1) | | 75 | INDEX RANGE SCAN | PA_IX6 | 4M | 44886 | 144 | +8 | 1 | 1M | 9944 | 78MB | . | 16.45 | gc cr grant 2-way (1) | | | | | | | | | | | | | | | cell single block physical read (24) | | 76 | TABLE ACCESS BY INDEX ROWID | PMBU | 1 | 2 | | | | | | | . | | | | 77 | INDEX UNIQUE SCAN | PBU_PK | 1 | 1 | | | | | | | . | | | | 78 | TABLE ACCESS STORAGE FULL | PMBU | 1 | 2 | | | | | | | . | | | | 79 | TABLE ACCESS BY INDEX ROWID BATCHED | PAA | 1 | 6 | | | | | | | . | | | | 80 | INDEX RANGE SCAN | PA_IX4 | 3 | 3 | | | | | | | . | | | | 81 | TABLE ACCESS BY INDEX ROWID | PCA | 1 | 3 | | | | | | | . | | | | 82 | INDEX UNIQUE SCAN | PCA_IX2 | 1 | 2 | | | | | | | . | | | | 83 | TABLE ACCESS BY INDEX ROWID | DEP | 1 | 3 | | | | | | | . | | | | 84 | INDEX UNIQUE SCAN | DEP_IX1 | 1 | 2 | | | | | | | . | | | | 85 | INDEX UNIQUE SCAN | LO_IX1 | 1 | 2 | | | | | | | . | | | | 86 | TABLE ACCESS BY INDEX ROWID | LO | 1 | 3 | | | | | | | . | | | | 87 | JOIN FILTER USE | :BF0000 | 7 | 2 | | | | | | | . | | | | 88 | TABLE ACCESS STORAGE FULL | IL | 7 | 2 | | | | | | | . | | | | 89 | TABLE ACCESS BY INDEX ROWID BATCHED | ADD | 1 | 4 | | | | | | | . | | | | 90 | INDEX RANGE SCAN | ADD_IX1 | 2 | 3 | | | | | | | . | | | | 91 | TABLE ACCESS BY INDEX ROWID | AR | 1 | 3 | | | | | | | . | | | | 92 | INDEX UNIQUE SCAN | AR_IX1 | 1 | 2 | | | | | | | . | | | | 93 | TABLE ACCESS BY INDEX ROWID BATCHED | CRR | 1 | 1 | | | | | | | . | | | | 94 | INDEX RANGE SCAN | CRR_IX2 | 1 | | | | | | | | . | | | | 95 | TABLE ACCESS STORAGE FULL | CRR | 1 | 1 | | | | | | | . | | | | 96 | TABLE ACCESS BY INDEX ROWID BATCHED | CRR | 1 | 1 | | | | | | | . | | | | 97 | INDEX RANGE SCAN | CRR_IX2 | 1 | | | | | | | | . | | | | 98 | TABLE ACCESS STORAGE FULL | CRR | 1 | 1 | | | | | | | . | | | =========================================================================================================================================================================================================================================================