problem with _optimizer_ignore_hints

From: <ahmed.fikri_at_t-online.de>
Date: Tue, 2 Nov 2021 18:41:08 +0100 (CET)
Message-ID: <1635874868753.6887667.f27e0cf39db7ab951920a64bbdb94cc790405d38_at_spica.telekom.de>



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
Received on Tue Nov 02 2021 - 18:41:08 CET

Original text of this message