Re: problem with _optimizer_ignore_hints

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_at_t-online.de < ahmed.fikri_at_t-online.de> 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
> 
>

--
http://www.freelists.org/webpage/oracle-l