AW: problem with _optimizer_ignore_hints

thanks. The problem seems to be solved in 12.2.        

-----Original-Nachricht-----
Betreff: Re: problem with _optimizer_ignore_hints Datum: 2021-11-04T17:13:25+0100
Von: "Jonathan Lewis" <jlewisoracle_at_gmail.com> An: "ahmed.fikri_at_t-online.de" <ahmed.fikri_at_t-online.de>        

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