After updating to release 19.22, we noticed that the internal index (in the format of SYS_FBA_HIST_IDX1_object_id) on SYS_FBA_HIST_object_id is created with DEGREE DEFAULT INSTANCE DEFAULT option, whereas it should be created with NOPARALLEL. According to 2981801.1 FDA Indexes are created With PARALLEL DEFAULT After Applying Patch 35039202, this is a known bug fixed in 35957640. Bugfix 35957640 included in the 19.22 RU. I’ve verified through opatch lsinventory that both fixes (35957640 and 35039202) are present in my inventory.
Despite the so-called bug fixes, the initial indexes are still created with the DEFAULT DEGREE. Oracle Support Team replied to my service request (SR 3-36930648381) with the following statement, which I don’t find convincing.
” Development team confirmed that it is expected behavior that the index on HIST table is created with “PARALLEL” clause only. It seems that the referenced document ( Doc ID 2981801.1 ) is an incorrect one.”
Last but not least, these indexes become unusable when a split operation occurs on the high partition (HIGH_PART) of the related table (SYS_FBA_HIST_). If the size of the HIGH_PART partition is small (although what constitutes ‘small’ exactly is uncertain), there are no issues. However, if the partition size is not small, when the split operation occurs, the local indexes on these tables gets unusable.
I have debugged FBDA process. To investigate what’s happening behind the scenes, I enabled 10046 event tracing on all instances for the FBDA process.
[oracle@bltdb1 trace]$ vi /u01/app/oracle/diag/rdbms/XXXX/XXXX1/trace/XXXX1_fbda_61972.trc
...
PARSING IN CURSOR #140274087025200 len=142 dep=1 uid=0 oct=1 lid=0 tim=14605835509601 hv=4096064267 ad='45512069e0' sqlid='1t5g44bu29ysb'
alter table "TEST_SCHEMA".SYS_FBA_HIST_2054816 split partition high_part at (812444320056) into (partition part_812444320056, partition high_part)
END OF STMT
...
As you can see above, there is no UPDATE INDEXES clause in the split operation command. It should be written as shown below, or an index rebuild operation should be performed. However, neither of these actions is being taken.
SQL> alter table "TEST_SCHEMA".SYS_FBA_HIST_2054816 split partition high_part at (812444320056) into (partition part_812444320056, partition high_part) UPDATE INDEXES;
Bug 36791812 is raised by support engineer almost 2 months ago. But still in development status.
I have written a blog post about the issue. https://dincosman.com/2024/06/24/unusable-index-fda-tables/
I'm wondering if anyone using the FTT (Flashback Time Travel) feature on 19.22 or higher releases is experiencing the same problem. To trigger the split operation, some(100.000 transactions and much more) update or delete operation should have occurred on the flashback archive-enabled table.
Regards,
Osman DİNÇ