There is a bug in ftsindexer for 717 and 718 - it does not pass all required paramaters to the stored procedure used for 'get and lock next available item in queue' resulting in a table scan. If system is busy the table scan will of course take time, and the ftsindexer will "fall behind".
If you are on 717/718, open a support ticket for the "fts indexer hotfix".
If you are on 717, please review the indexes on the fts indexer queeue table and source code for stored procedure sppr_reservequeueitem_b -- the "order by" clause in particular. I believe that 717 shipped with a good 'sproc' and indexes that will work if the ftsindexer bug is patched.
As noted, the system indexer and system work indexer are legacy and should be disabled.
Note that SystemIndexer and SystemWorkIndexer will be in action in an upgraded system (not a fresh installation) till all indexes have been converted to Elastic Search. The user must explicitly re-index from the search landing page to create Elastic Search indices to get this conversion done. Till such time, search will be serviced by the older Apache Lucene library (used as the search library till 7.1.6) and SystemIndexer and SystemWorkIndexer are needed to keep those indices up to date.
FTS indexing should be enabled but in general, work- indexing should not be enabled without reviewing
- application volume
- application requirements
- fundamental nature of 'search' in this applications business. Are you searching by well known common fields, like pyID, customer/policy code/member/subscriber, provider, card #, etc or do you truly need to search every piece of text in every work type?
Keep in mind that FTS indexer for < 71ML9 needs a hotfix and possibly an index change to the ftsindexer queue table
If this is an Oracle system please get an AWR report.
How many items are in the ftsindexer queue currently? How much space is the FTS indexer table using?
what indexes are defined on the FTS indexer table?
Do you have sufficient disk space and access to the actual FTS index tables for updates to be processed?
What alerts do you get with FTS indexer enabled?
Have observed that Oracle may fail to reclaim space from the fts indexer table when rows are deleted and performance will degrade as Oracle will scan a 'mostly empty' table. In such a situation it is best to truncate the fts indexer table and rebuild the full text indexes, and regularly schedule a defragementation command (alter table shrink space cascade) to ensure that table stays healthy
There is something completely messed up about this database
A basic read using the primary key is taking 61,621.01 GETS PER EXEC (500MB of in-memory data access). Primary key reads shold take perhaps 6 GETS
>> select pzInsKey from PEGADATA_CATALYST.pr_sys_queue_ftsindexer where pzInsKey = :1 and pyItemStatus = :2 for update nowait
The query to fetch the next item from the FTS indexer queue is taking 80,710.41 GETS per exec (646MB of in-memory data access). With proper index in place this should be 10-20 GETS
>> select pzInskey from (Select pzInskey from PEGADATA_CATALYST.pr_sys_queue_ftsindexer where pyItemStatus = :1 and pxObjClass = :2 and pyMinimumDateTimeForProcessing <= :3 order by pyMinimumDateTimeForProcessing ASC) where rownum <= 10
The Queue Management stored procedure is consuming 87.92% of the CPU used by your Oracle instance as it is basically doing in-memory scanning of the entire table on every access call.
There are other big issues in this database -- AWR claims that it takes on average 5 seconds to update your work items, and 8 seconds to initially write the work item!
The DB has massive waits for latch: enqueue hash chains. I've never seen hash chains as a resource,
With regard to FTS indexer
a- get a query plan for the two queries listed above. Confirm that Oracle is ignoring the indexes
b- Update statistics for pr_sys_queueue_ftsindexer and then run query plan again. Did that make Oracle start to use the indexes?
c- Find out 'how far behind' you are -- select min(pxcommitdatetime) from pr_sys_queueue_ftsindexer.
d- have you had users complaining regarding missing search results? If you are far behind and users have not noticed, perhaps the application really does not need work search
Not sure if you should tune the indexes and wait for the agent to 'catch up' or truncate/rebuild/restart ... Let's first figure out why Oracle is behaving wrong first
With regard to the slow write performance and Latch Contention - engage a DBA immediately.
You are missing the FTS Indexer agent queue hotfix. The FTSIndexer agent is not passing agent name into the 'reservequeitem' stored procedure, so the procedure is generating a query to find next item to process using pxobjclass rather than agent name.
Per my earlier posts, you have two options
a- get the hotfix for the ftsindexer agent
b- change your indexes to accomodate the bug
* Drop index pegadata.PR_SYS_QUEUE_FTSINDEXER_INDEX2;
* Create index pegadata. PR_SYS_QUEUE_FTSINDEXER_INDEX2 on pegadata.pr_sys_queue_ftsindexer (pyitemstatus, pxobjclass, pyminimumdateTimeForProcessing, pzinskey
I was mistaken when I stated that the fts indexer agent was fixed in 719. I've discovered that the ftsindexer agent bug persists through 7.2 and it is not fixed until 7.21
Note HFIX-25805 will not fix the existing entries in the table. Any new entries added to the table, after the HFIX has been installed, will be added with the agent name and thus the index currently existing will get picked up. You might want to update all the existing entries with the agent name so that it is consistent with what the HFIX provides.