Event table bug

While implementing OBIEE physical cache clearance run into a weird behavior of  the event table and the non-relevant row handling. At first couldn't really find out the reason and the cure, but in the end came to the root of the problem. I'll describe it in this entry.

I assume that you're familiar with the OBIEE event table that is used for clearing the cache entries of the database tables, so I'm not gonna go through that.

At first, let's insert two new rows in the table with the SYSDATE as the update time.

Now let's wait for the interval to pass and the entries to be deleted from cache. After a few minutes, when we select from the event table, we can see that instead of two entries there are four ones with "gs" being the value in OTHER_RESERVED column. Why?

Didn't find anything useful in Google, there were people who have had the same problem, but no solution to the problem was in the topic.
My attention was drawn by a sentence in Oracle documentation about the UPDATE_TS column. It said: "This needs to be a key (unique) value that increases for each row added to the event table."
So, I tried inserting the same rows, this time with an incremental SYSDATE value. Like this:

After the regular cache clearance, you can see that there are no entries in the table anymore.

After doing some additional testing, it did turn out that the event table really does handle rows with identical UPDATE_TS columns in an awkward way. From the nqquery.log it didn't seem that the cache entries for the corresponding tables with duplicate rows were continuously purged though.

So, it might not be advisable to use a simple SYSDATE as the update timestamp for the event table. Using CURRENT_TIMESTAMP in a single transaction also wouldn't help, thus you could create a custom procedure that receives the table name and inserts it into the table with the highest current UPDATE_TS value incremented with one second and use SYSDATE, if the table is empty. For example:

create or replace procedure add_to_event_table (database_name in varchar2, catalog_name in varchar2, schema_name in varchar2, table_name in varchar2)
as
max_date date;
begin    
select NVL(max(update_ts+1/86400),SYSDATE) into max_date from S_NQ_EPT;
INSERT INTO S_NQ_EPT (UPDATE_TS, DATABASE_NAME, CATALOG_NAME, SCHEMA_NAME, TABLE_NAME)
VALUES (MAX_DATE, DATABASE_NAME, CATALOG_NAME, SCHEMA_NAME, TABLE_NAME);
COMMIT;
end;