************* Create table script*************** CREATE TABLE USER2.TAB1 ( A_ID VARCHAR2(30 CHAR) NOT NULL, A_STMP TIMESTAMP(6), A_ACTN VARCHAR2(1 CHAR) NOT NULL, RNM VARCHAR2(15 CHAR) NOT NULL, FNM VARCHAR2(18 CHAR) NOT NULL, OVL VARCHAR2(254 CHAR) NOT NULL, NWVL VARCHAR2(254 CHAR) NOT NULL, COL1 VARCHAR2(65 CHAR) NOT NULL, COL2 VARCHAR2(65 CHAR) NOT NULL, COL3 VARCHAR2(65 CHAR) NOT NULL, COL4 VARCHAR2(65 CHAR) NOT NULL, COL5 VARCHAR2(65 CHAR) NOT NULL, COL6 VARCHAR2(65 CHAR) NOT NULL, COL7 VARCHAR2(65 CHAR) NOT NULL, COL8 VARCHAR2(65 CHAR) NOT NULL, COL9 VARCHAR2(65 CHAR) NOT NULL, COL10 VARCHAR2(65 CHAR) NOT NULL, COL11 VARCHAR2(65 CHAR) NOT NULL, COL12 VARCHAR2(65 CHAR) NOT NULL, COL13 VARCHAR2(65 CHAR) NOT NULL, COL14 VARCHAR2(65 CHAR) NOT NULL, COL15 VARCHAR2(65 CHAR) NOT NULL ) TABLESPACE TBS1 PCTUSED 0 PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 1M NEXT 1M MAXSIZE UNLIMITED MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT CELL_FLASH_CACHE KEEP ) LOGGING NOCOMPRESS NOCACHE MONITORING; ********************** Test case **************************** SQL Monitoring Report SQL Text ------------------------------ SELECT /*+ monitor test6*/ A_ID, A_ACTN, RNM, FNM, OVL, NWVL, COL1, COL2, COL3, COL4, COL5, COL6/*, COL7*/ FROM USER2.TAB1 WHERE OVL <> NWVL AND ( (RNM = 'XXXX' AND COL1 = '1') OR (RNM = 'YYYY' AND COL1 = '1')) Global Information ------------------------------ Status : DONE (ALL ROWS) Instance ID : 2 Session : USER1 (801:56452) SQL ID : 0btm2vq7jy9sf SQL Execution ID : 33554432 Execution Started : 05/23/2021 10:57:30 First Refresh Time : 05/23/2021 10:57:30 Last Refresh Time : 05/23/2021 10:57:31 Duration : 1s Module/Action : SQL*Plus/- Service : XXXXXXXXXXXXXXXX Program : sqlplus.exe Fetch Calls : 2 Global Stats ===================================================================================================================================== | Elapsed | Cpu | IO | Application | Fetch | Buffer | Read | Read | Uncompressed | Offload | Offload | Cell | | Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Bytes | Elig Bytes | Returned Bytes | Offload | ===================================================================================================================================== | 0.09 | 0.08 | 0.01 | 0.00 | 2 | 908K | 114K | 7GB | 4MB | 7GB | 5MB | 99.93% | ===================================================================================================================================== SQL Plan Monitoring Details (Plan Hash Value=2942457346) ========================================================================================================================================================================= | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) | ========================================================================================================================================================================= | 0 | SELECT STATEMENT | | | | 2 | +0 | 1 | 3 | | | . | | | | 1 | TABLE ACCESS STORAGE FULL FIRST ROWS | TAB1 | 1 | 147K | 2 | +0 | 1 | 3 | 114K | 7GB | 12MB | | | ========================================================================================================================================================================= Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('19.1.0') DB_VERSION('19.1.0') OPT_PARAM('_unnest_subquery' 'false') OPT_PARAM('_optimizer_skip_scan_enabled' 'false') OPT_PARAM('_gby_hash_aggregation_enabled' 'false') OPT_PARAM('_optimizer_partial_join_eval' 'false') OPT_PARAM('_optimizer_aggr_groupby_elim' 'false') OPT_PARAM('_optimizer_reduce_groupby_key' 'false') OPT_PARAM('optimizer_index_cost_adj' 50) OPT_PARAM('optimizer_index_caching' 80) OPT_PARAM('_fix_control' '14033181:0') FIRST_ROWS(1) OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "TAB1"@"SEL$1") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 1 - storage(("COL1"='1' AND INTERNAL_FUNCTION("RNM") AND "OVL"<>"NWVL")) filter(("COL1"='1' AND INTERNAL_FUNCTION("RNM") AND "OVL"<>"NWVL")) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "A_ID"[VARCHAR2,120], "A_ACTN"[VARCHAR2,4], "RNM"[VARCHAR2,60], "FNM"[VARCHAR2,72], "OVL"[VARCHAR2,1016], "NWVL"[VARCHAR2,1016], "COL1"[VARCHAR2,260], "COL2"[VARCHAR2,260], "COL3"[VARCHAR2,260], "COL4"[VARCHAR2,260], "COL5"[VARCHAR2,260], "COL6"[VARCHAR2,260] Statistics ---------------------------------------------------------- 3 recursive calls 0 db block gets 907558 consistent gets 907395 physical reads 0 redo size 1117 bytes sent via SQL*Net to client 500 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 3 rows processed ***************************************** SELECT /*+ monitor test7*/ A_ID, A_ACTN, RNM, FNM, OVL, NWVL, COL1, COL2, COL3, COL4, COL5, COL6, COL7 FROM USER2.TAB1 WHERE OVL <> NWVL AND ( (RNM = 'XXXX' AND COL1 = '1') OR (RNM = 'YYYY' AND COL1 = '1')) SQL Text ------------------------------ Global Information ------------------------------ Status : DONE (ALL ROWS) Instance ID : 2 Session : USER1 (801:56452) SQL ID : 1pkt94kfsfv07 SQL Execution ID : 33554432 Execution Started : 05/23/2021 11:01:27 First Refresh Time : 05/23/2021 11:01:27 Last Refresh Time : 05/23/2021 11:02:40 Duration : 73s Module/Action : SQL*Plus/- Service : XXXXXXXXXXXXXXXX Program : sqlplus.exe Fetch Calls : 2 Global Stats ================================================================ | Elapsed | Cpu | IO | Fetch | Buffer | Read | Read | | Time(s) | Time(s) | Waits(s) | Calls | Gets | Reqs | Bytes | ================================================================ | 79 | 21 | 58 | 2 | 908K | 114K | 7GB | ================================================================ SQL Plan Monitoring Details (Plan Hash Value=2942457346) ================================================================================================================================================================= | 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 | | | | 72 | +2 | 1 | 3 | | | | | | 1 | TABLE ACCESS STORAGE FULL FIRST ROWS | TAB1 | 1 | 147K | 73 | +1 | 1 | 3 | 114K | 7GB | | | ================================================================================================================================================================= Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('19.1.0') DB_VERSION('19.1.0') OPT_PARAM('_unnest_subquery' 'false') OPT_PARAM('_optimizer_skip_scan_enabled' 'false') OPT_PARAM('_gby_hash_aggregation_enabled' 'false') OPT_PARAM('_optimizer_partial_join_eval' 'false') OPT_PARAM('_optimizer_aggr_groupby_elim' 'false') OPT_PARAM('_optimizer_reduce_groupby_key' 'false') OPT_PARAM('optimizer_index_cost_adj' 50) OPT_PARAM('optimizer_index_caching' 80) OPT_PARAM('_fix_control' '14033181:0') FIRST_ROWS(1) OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "TAB1"@"SEL$1") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 1 - storage(("COL1"='1' AND INTERNAL_FUNCTION("RNM") AND "OVL"<>"NWVL")) filter(("COL1"='1' AND INTERNAL_FUNCTION("RNM") AND "OVL"<>"NWVL")) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "A_ID"[VARCHAR2,120], "A_ACTN"[VARCHAR2,4], "RNM"[VARCHAR2,60], "FNM"[VARCHAR2,72], "OVL"[VARCHAR2,1016], "NWVL"[VARCHAR2,1016], "COL1"[VARCHAR2,260], "COL2"[VARCHAR2,260], "COL3"[VARCHAR2,260], "COL4"[VARCHAR2,260], "COL5"[VARCHAR2,260], "COL6"[VARCHAR2,260], "COL7"[VARCHAR2,260] Statistics ---------------------------------------------------------- 3 recursive calls 0 db block gets 908127 consistent gets 907395 physical reads 35394640 redo size 1175 bytes sent via SQL*Net to client 500 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 3 rows processed **************************** alter session set "_serial_direct_read"=always; SELECT /*+ monitor test8*/ A_ID, A_ACTN, RNM, FNM, OVL, NWVL, COL1, COL2, COL3, COL4, COL5, COL6, COL7 FROM USER2.TAB1 WHERE OVL <> NWVL AND ( (RNM = 'XXXX' AND COL1 = '1') OR (RNM = 'YYYY' AND COL1 = '1')) SQL Text ------------------------------ Global Information ------------------------------ Status : DONE (ALL ROWS) Instance ID : 2 Session : USER1 (801:56452) SQL ID : 7c63a9zbuk5m7 SQL Execution ID : 33554432 Execution Started : 05/23/2021 11:21:41 First Refresh Time : 05/23/2021 11:21:41 Last Refresh Time : 05/23/2021 11:21:41 Duration : .092809s Module/Action : SQL*Plus/- Service : XXXXXXXXXXXXXXXX Program : sqlplus.exe Fetch Calls : 2 Global Stats ===================================================================================================================================== | Elapsed | Cpu | IO | Application | Fetch | Buffer | Read | Read | Uncompressed | Offload | Offload | Cell | | Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Bytes | Elig Bytes | Returned Bytes | Offload | ===================================================================================================================================== | 0.09 | 0.08 | 0.01 | 0.00 | 2 | 908K | 114K | 7GB | 4MB | 7GB | 5MB | 99.93% | ===================================================================================================================================== SQL Plan Monitoring Details (Plan Hash Value=2942457346) ========================================================================================================================================================================= | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) | ========================================================================================================================================================================= | 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 3 | | | . | | | | 1 | TABLE ACCESS STORAGE FULL FIRST ROWS | TAB1 | 1 | 147K | 1 | +0 | 1 | 3 | 114K | 7GB | 12MB | | | ========================================================================================================================================================================= Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('19.1.0') DB_VERSION('19.1.0') OPT_PARAM('_unnest_subquery' 'false') OPT_PARAM('_optimizer_skip_scan_enabled' 'false') OPT_PARAM('_gby_hash_aggregation_enabled' 'false') OPT_PARAM('_optimizer_partial_join_eval' 'false') OPT_PARAM('_optimizer_aggr_groupby_elim' 'false') OPT_PARAM('_optimizer_reduce_groupby_key' 'false') OPT_PARAM('optimizer_index_cost_adj' 50) OPT_PARAM('optimizer_index_caching' 80) OPT_PARAM('_fix_control' '14033181:0') FIRST_ROWS(1) OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "TAB1"@"SEL$1") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 1 - storage(("COL1"='1' AND INTERNAL_FUNCTION("RNM") AND "OVL"<>"NWVL")) filter(("COL1"='1' AND INTERNAL_FUNCTION("RNM") AND "OVL"<>"NWVL")) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "A_ID"[VARCHAR2,120], "A_ACTN"[VARCHAR2,4], "RNM"[VARCHAR2,60], "FNM"[VARCHAR2,72], "OVL"[VARCHAR2,1016], "NWVL"[VARCHAR2,1016], "COL1"[VARCHAR2,260], "COL2"[VARCHAR2,260], "COL3"[VARCHAR2,260], "COL4"[VARCHAR2,260], "COL5"[VARCHAR2,260], "COL6"[VARCHAR2,260], "COL7"[VARCHAR2,260] Statistics ---------------------------------------------------------- 3 recursive calls 0 db block gets 907558 consistent gets 907395 physical reads 0 redo size 1175 bytes sent via SQL*Net to client 500 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 3 rows processed