Oracle Agile Application or Quick Search performance issue and how to resolve?

When you come across any performance issue on agile first, you need to verify schema integrity by running agile9_check.sql.

The agile9_check.sql it verifies Database Schema version with Agile Schema version, and it MUST match. Also, it validates schema objects against Agile schema version and report any errors or warnings.

You may see ERRORS/WARNINGS
ERROR: Missing or INVALID index (columns) XXXXXXXXX on table XXXXX.
ERROR: Missing or INVALID FTS CTX indexes XXXXXXX.
WARNING: ACTIVITY_BASELINE_XXXXX does not belong to AGILE DB. Please Drop the Table if not required.

In case of any Errors/Warnings you need we need to fix the issue. If you have created any custom objects you will see WARNING that object doesn't  below to Agile DB and you can ignore. 

Once user fix the schema integrity download listFTSInfo.sql and run the script as Agile user and it will generate listFTSInfo_xxxxxxxxxx.log file. You need to search for "029", where it says "List Index Percent of Fragmentation" where you can see the indexes that has fragmentation.

If you see any fragmentation, it will effect Quick search and stuck threads will be generated.

<[STUCK] ExecuteThread: '19' for queue: 'weblogic.kernel.Default (self-tuning)' has been busy for "1,247" seconds working on the request "Http Request Information: weblogic.servlet.internal.ServletRequestImpl@2f2fb94[GET /Agile/PCMServlet]
", which is more than the configured time (StuckThreadMaxTime) of "1,200" seconds in "server-failure-trigger". Stack trace:
java.net.SocketInputStream.socketRead0(Native Method)

To resolve the issue, Log into sqlplus as agile database user, and run agile_ctx_recreate.sql found under %Oracle_base%\admin\{SID}\create\{agile_schema_username} to rebuild CTX indexes.

Oracle support recommends stopping Agile application, run agile_ctx_recreate.sql and Start the Application.

It is highly advisable to run complie_Invalid_objects.sql, agile9stats.sql to recompile invalid objects and collect stats to improve the performance.

Refer Oracle Agile support doc for Full Text search (FTS) Enablement, synchronization, Indexing and optimization.

Frequently Asked Questions on Agile Product Lifecycle Management (PLM) Full Text Search (FTS) Enablement, Synchronization, Indexing, and Optimization (Doc ID 1503311.1)

For Agile Stuck thread issue, refer below Oracle support Doc ID

Agile Managed Servers Goes to Warning State, Having Stuck Thread at com.agile.cs.query.QuerySessionBean (Doc ID 2919154.1)

Thanks & Regards

https://oracleracexpert.com, Oracle ACE