problem with _optimizer_ignore_hints
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 fineIt is true that I used hidden parameter, but I find that interesting.
Best regards
Ahmed
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Nov 02 2021 - 18:41:08 CET