That's one of those amusing side-effects where one group in Oracle doesn't
know about everthing that another group is doing.
If your traced the gather call I think you'd find that the query that
gatthers index stats has an index_ffs hint in it because the code that
calculates the number of leaf blocks in the index calls sys_op_lbid() in a
way that can only be relevant to an index leaf block. However the SQL
(unhinted) could use a tablescan - which is probably why your use of the
hint resulted in an error in one release of Oracle but not the other. (And
some fixes get back ported only to terminal releases, hence an older
version may be fixed while a newer version breaks.)
Regards
Jonathan Lewis
On Tue, 2 Nov 2021 at 17:41, ahmed.fikri@xxxxxxxxxxx <
ahmed.fikri@xxxxxxxxxxx> wrote:
Hi all,
today I stumbled upon something strange
I wanted to analyze the effects of using hints in a piece of code, so I
used the _optimizer_ignore_hints hidden parameter to disable hints used in
my code.
After running the pl/sql code I got this error:
ORA-00904: invalid identifier
ORA-06512 at "SYS.DBMS_STATS", line 35016
ORA-06512: at line 2
After debugging, I was able to reproduce the problem as follows:
CREATE TABLE t_test (ID NUMBER NOT NULL, year_to DATE DEFAULT
to_date('01012021','ddmmyyyy') NOT NULL)
PARTITION BY RANGE (year_to) (PARTITION MAXVALUE VALUES LESS THAN
(MAXVALUE) TABLESPACE USERS);
ALTER TABLE t_test ADD CONSTRAINT pk_t_test PRIMARY KEY(ID) USING INDEX
TABLESPACE USERS;
INSERT INTO t_test VALUES(1,to_date('01012021','ddmmyyyy'));
COMMIT;
ALTER SESSION SET "_optimizer_ignore_hints"=TRUE;
BEGIN
dbms_stats.gather_table_stats(ownname => USER,tabname =>
'T_TEST',estimate_percent => 100,degree => 4,cascade => TRUE,method_opt =>
'FOR ALL COLUMNS SIZE 1');
END;
The problem only occurs when the table is partitioned and contains data
setting cascade parameter to false it works.
I observed the issue on 12.1.0.2. On 11.2.0.4 is not working fine
It is true that I used hidden parameter, but I find that interesting.
Best regards
Ahmed
