Hello Listers,
Its version 19C of oracle and optimizer_features_enable 19.1.0.
While working on a separate issue, we came across a situation which is a
bit odd. A simple SELECT query fetching ~13 columns from a table - TAB1
showing odd behaviour. Sometimes within seconds and sometimes more than a
minute. When , It was running for more than a minute , it happened to be
clear that it's not doing cell offloading from its sql monitor. And then by
just removing one column from its SELECT list , the smartscan started
happening and the query finished in a few seconds.
Then I started running the same query for all the ~13 columns in the SELECT
list but this time by setting session level "serial_direct_read"= always.
And here we see the smart scan again started happening and the query
finished in quick time. Is this behaviour because of any bug ? Because I
have not seen any such criteria in which the smartscan is restricted by the
number of columns in the SELECT list.
Attached is the sample table script with the test case which produces the
above scenario. Table TAB1 is a non partition table with size ~7GB and
there are ~64million rows in it.
Note- Its a third party database and we see few of the optimizer parameters
set to non default as its clear from the outline section. But at least none
of these should be impacting the choice of smartscan.
SELECT 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'))
Regards
Lok
************* 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
