Tahon
I’m going through the same issue!
Which I can provide some feedback yet, I would rather have the grand wizards
like JL ,Tanel and others at the same level talk first to avoid clutter
But I do feel your pain !!!
On Jan 19, 2021, at 1:10 PM, Tahon@xxxxxxxxxxxxx wrote:
Hi,
I'm puzzled with some strange cardinality estimates that the optimizer is
giving on a 19c database (v$version shows 19.4.0.0.0, optimizer_features_enable
is not set and shows 19.1.0 as its default).
I created a test case using a simple table with two columns. The table has no
rows but I've set the statistics to tell the optimizer that there about 45
million rows.
CREATE TABLE T1 (ID NUMBER(20), V VARCHAR2(20 CHAR));
CREATE UNIQUE INDEX PK_ID ON T1(ID);
ALTER TABLE T1 ADD (CONSTRAINT PK_ID PRIMARY KEY (ID) USING INDEX PK_ID ENABLE
VALIDATE);
EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'T1');
DECLARE
srec DBMS_STATS.STATREC;
numvals DBMS_STATS.NUMARRAY;
charvals DBMS_STATS.CHARARRAY;
BEGIN
DBMS_STATS.set_table_stats(ownname => user, tabname => 'T1', numrows =>
45262481, numblks => 1938304, avgrlen => 206);
numvals := DBMS_STATS.numarray (1, 45262481);
srec.epc:=2;
DBMS_STATS.prepare_column_values (srec, numvals);
DBMS_STATS.set_column_stats (ownname => user, tabname => 'T1', colname =>
'ID', distcnt => 45262481, density => .0000000220933536542109,
nullcnt => 0, srec => srec, avgclen => 6);
charvals := DBMS_STATS.chararray ('', '');
DBMS_STATS.prepare_column_values (srec, charvals);
DBMS_STATS.set_column_stats (ownname => user, tabname => 'T1', colname =>
'V', distcnt => 0, density => 0, nullcnt => 45262481, srec => srec, avgclen =>
0);
COMMIT;
DBMS_STATS.SET_INDEX_STATS ( ownname => user, indname =>'PK_ID', numrows =>
45607914, numlblks => 101513,
numdist => 45607914, avglblk => 1, avgdblk => 1, clstfct =>
33678879, indlevel => 2);
END;
/
variable N1 NVARCHAR2(32)
variable N2 NUMBER
begin
:N1 := 'D';
:N2 := 50;
end;
/
select /*+ gather_plan_statistics */ * from ( SELECT A.ID COL0,A.ID COL1
FROM T1 A
WHERE A.V=:N1 AND A.ID > 1
ORDER BY A.ID ) where rownum <= :N2 ;
SQL_ID 871kkxamgy1wy, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from ( SELECT A.ID COL0,A.ID
COL1 FROM T1 A WHERE A.V=:N1 AND A.ID > 1 ORDER BY A.ID ) where rownum
<= :N2
Plan hash value: 2577482738
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost
(%CPU)| A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 3736
(100)| 0 |00:00:00.01 | 1 |
|* 1 | COUNT STOPKEY | | 1 | | |
| 0 |00:00:00.01 | 1 |
| 2 | VIEW | | 1 | 50 | 1300 | 3736
(1)| 0 |00:00:00.01 | 1 |
|* 3 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 452K| 20M| 3736
(1)| 0 |00:00:00.01 | 1 |
|* 4 | INDEX RANGE SCAN | PK_ID | 1 | 5000 | | 14
(0)| 0 |00:00:00.01 | 1 |
----------------------------------------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
2 - :2 (NUMBER): 50
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=:N2)
3 - filter(SYS_OP_C2C("A"."V")=:N1)
4 - access("A"."ID">1)
So for some reason the optimizer thinks it will get 5000 rows from the index
range scan using ID > 1.
When using following query, the optimizer does know that ID > 1 gives about 45
million rows:
SELECT /*+ INDEX (A PK_ID) */ A.ID COL0 FROM T1 A WHERE A.ID > 1;
SQL_ID 1bndnsjhpsvbs, child number 0
-------------------------------------
SELECT /*+ INDEX (A PK_ID) */ A.ID COL0 FROM T1 A WHERE A.ID > 1
Plan hash value: 988767100
----------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)|
----------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 101K(100)|
|* 1 | INDEX RANGE SCAN| PK_ID | 45M| 258M| 101K (1)|
----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."ID">1)
So why is the former query giving an estimate of only 5000 rows?
Regards,
Dirk
Note: bind variable N1 is on purpose defined as NVARCHAR2 as that is also what
our real case is doing.
