The gather stats job always fail on one of our core table with below error:
EXEC dbms_stats.gather_table_stats(ownname=> 'SYSADM', tabname=>'gbprestatiegroep', method_opt=>'For all columns size 1',estimate_percent=> 100, cascade=>false, degree=>1, BLOCK_SAMPLE=>false, NO_INVALIDATE=>false ); Error report: ORA-01847: day of month must be between 1 and last day of month ORA-06512: at "SYS.DBMS_STATS", line 23829 ORA-06512: at "SYS.DBMS_STATS", line 23880
With "cascade=>false" in above command, we define to only gather table data stats.
Then, we treid to gather index only stats for the indexes on this table and they all succeeded:
gather_index_stats(OWNNAME=> 'SYSADM', INDNAME=> 'IX_GBP_PATTRACKING', GRANUL gather_index_stats(OWNNAME=> 'SYSADM', INDNAME=> 'DPG17', GRANULARITY=>'ALL', gather_index_stats(OWNNAME=> 'SYSADM', INDNAME=> 'DPG2', GRANULARITY=>'ALL',e gather_index_stats(OWNNAME=> 'SYSADM', INDNAME=> 'DPG14', GRANULARITY=>'ALL', gather_index_stats(OWNNAME=> 'SYSADM', INDNAME=> 'CGBP01', GRANULARITY=>'ALL' gather_index_stats(OWNNAME=> 'SYSADM', INDNAME=> 'CIND_GBP_PK_S_KEY2', GRANUL ............
Above result seems to indicate data (corruption/invalidation/broken) issue on the table while the data in index is fine.
However, below command can return result correctly without any issue:
select /*+full(table)*/ * from table;
select /*+full(table)*/ to_char(column1,'yyyy-mm-dd hh24:mi:ss'), to_char(column2,'yyyy-mm-dd hh24:mi:ss'), ..... from table;
To investigate, I enabled level 3 errorstack, and I captured below issue SQL:
select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_substrb_pad */
count(*),
count("SYS_NC00074$"),
count("SYS_NC00075$"),
count("SYS_NC00076$"),
count("SYS_NC00077$"),
count("SYS_NC00078$"),
count("SYS_NC00079$"),
sum(sys_op_opnsize("SLEUTEL")),
sum(sys_op_opnsize("CLIENTAPPLIC")),
sum(sys_op_opnsize("DEMONSTRATION_FU1")),
count("DEMONSTRATION_FU2"),
sum(sys_op_opnsize("DEMONSTRATION_FU2")),
count("CONTACTTEMPLATE"),
sum(sys_op_opnsize("CONTACTTEMPLATE")),
count("MEDICALVALIDATION"),
sum(sys_op_opnsize("AANVRAGER")),
...................
...................
from "SYSADM"."GBPRESTATIEGROEP" t ;What the hell are these columns? They do not show up if we use "DESC GBPRESTATIEGROEP" command:
count("SYS_NC00074$"),
count("SYS_NC00075$"),
count("SYS_NC00076$"),
count("SYS_NC00077$"),
count("SYS_NC00078$"),
count("SYS_NC00079$"),
After verifying, we confirmed that it is below columns that lead to the issue, and we can reproduce by executing.
SQL> set lines 300
SQL> set pagesize 500
SQL> select
2 count("SYS_NC00074$"),
3 count("SYS_NC00075$"),
4 count("SYS_NC00076$"),
5 count("SYS_NC00077$"),
6 count("SYS_NC00078$"),
7 count("SYS_NC00079$")
8 from "SYSADM"."GBPRESTATIEGROEP" t ;
count("SYS_NC00074$"),
*
ERROR at line 2:
ORA-01847: day of month must be between 1 and last day of monthAbove SYS_NC0007* columns does not exist in DBA_INDEXES/DBA_OBJECTS/DBA_COLUMNS.
However, in col$, I find below:
SQL> select OBJ#,COL#,SEGCOL#,SEGCOLLENGTH,NAME,TYPE#,NULL$,DEFLENGTH,DEFAULT$,INTCOL#,PROPERTY
2 from col$ where name in ('SYS_NC00074$','SYS_NC00075$','SYS_NC00076$','SYS_NC00077$','SYS_NC00078$','SYS_NC00079$') and obj#=15182;
OBJ# COL# SEGCOL# SEGCOLLENGTH NAME TYPE# NULL$ DEFLENGTH DEFAULT$ INTCOL# PROPERTY
---------- ---------- ---------- ------------ ------------------------------ ---------- ---------- ---------- -------------------------------------------------------------------------------- ---------- ----------
15182 0 0 7 SYS_NC00074$ 180 0 81 TO_TIMESTAMP(TO_CHAR("PLANUITVOERING",'DD-MON-RR'),'yyyy-mm-dd hh24:mi:sssss.ff' 74 327976
15182 0 0 7 SYS_NC00075$ 180 0 75 TO_TIMESTAMP(TO_CHAR("PLANUITVOERING",'DD-MON-RR'),'yyyy-mm-dd hh24:mi:ss') 75 327976
15182 0 0 7 SYS_NC00076$ 180 0 78 TO_TIMESTAMP(TO_CHAR("PLANUITVOERING",'DD-MON-RR'),'yyyy-mm-dd hh24:mi:sssss') 76 327976
15182 0 0 7 SYS_NC00077$ 180 0 75 TO_TIMESTAMP(TO_CHAR("PLANUITVOERING",'DD-MON-RR'),'yyyy-mm-dd hh24:mi:ss') 77 327976
15182 0 0 7 SYS_NC00078$ 180 0 75 TO_TIMESTAMP(TO_CHAR("PLANUITVOERING",'DD-MON-RR'),'yyyy-mm-dd hh24:mi:ss') 78 327976
15182 0 0 7 SYS_NC00079$ 180 0 75 TO_TIMESTAMP(TO_CHAR("PLANUITVOERING",'DD-MON-RR'),'yyyy-MM-dd hh24:mi:ss') 79 327976
6 rows selected. For last a few years, constant performance turning has always being on-going for this site.
And during the process, many indexes were created based on poor SQL found or suggested by sql advisor.
It might be that when creating above function indexes, the creating function index command failed, and for some reason, it left behind orphan entries in col$.
After testing, I managed to reproduce the bug in 11.2.0.3.10, while it does not exist in 11.2.0.4 though.
SQL> create table SYSADM.TESTORPHAN as select created from DBA_OBJECTS where 1=2;
Table created.
SQL> insert into SYSADM.TESTORPHAN values (to_date('2000-07-27 14:47:30','YYYY-MM-DD HH24:MI:SS'));
1 row created.
SQL> commit;
Commit complete.
SQL> create index SYSADM.INT_ERROR on SYSADM.TESTORPHAN(TO_TIMESTAMP(TO_CHAR("CREATED",'DD-MON-RR'),'yyyy-MM-dd hh24:mi:ss')) online;
create index SYSADM.INT_ERROR on SYSADM.TESTORPHAN(TO_TIMESTAMP(TO_CHAR("CREATED",'DD-MON-RR'),'yyyy-MM-dd hh24:mi:ss')) online
ERROR at line 1:
ORA-01847: day of month must be between 1 and last day of month
---------here it will leaves orphan entries in col$ table-------------This is an obvious defect in online index (re)build cleanup function.
I checked below tables and I cannot find any entries for above columns there:
IND$
ICOL$
CON$
SEG$
CCOL$
icol$
icoldep$
I believe we can fix the issue by executing below SQLs:
delete from col$ where obj#=15182 and name in ('SYS_NC00074$','SYS_NC00075$','SYS_NC00076$','SYS_NC00077$','SYS_NC00078$','SYS_NC00079$');
update tab$ set INTCOLS=73, AVGRLN=228 where obj#=15182;
update obj$ set SPARE2=1 where obj#=15182;
commit;
shutdown abort
startupAs this is very critical environment and a huge site, I found an outage time to implement the fix by first enabling flashback on. The fix worked as I expected, the gather table stats job then completed successfully for this table.
Cheers.
More......