Performance Issue Encountered When Clicking On The [Status/Notes] Button.
(Doc ID 3000273.1)
Last updated on JANUARY 26, 2024
Applies to:
Oracle Thesaurus Management System - Version 5.3.1 and laterInformation in this document applies to any platform.
Symptoms
1)
Performance issue encountered when:
- navigating to say the 'Classify VT Omissions' screen
- querying a term
- clicking on the [Status/Notes] button.
2)
This was only happening for a 'non-super' user.
3)
Below query was found to be problematic:
4)
Issue goes goes away when the TMS tables are analyzed using the 'Analyze Tables' job from within the TMS UI. This can be seen in the below execution plans:
- Before (click here to download execution plan)
Here we see Actual Time (A-Time) of 2 minutes 56 seconds is being taken to access the TMS_DICT_CONTENTS table using the TMS_DICT_CONTENTS_NK4 index. Cardinality misestimate between estimated and actual can also be seen: E-Rows=26 versus A-Rows=22M!
- After (click here to download execution plan)
Here we see Actual Time (A-Time) of 00:00:00.02 is being taken to access the TMS_DICT_CONTENTS table using the TMS_DICT_CONTENTS_UK1 index. Cardinality is estimated as 1 and actually zero rows are returned.
5)
Issue subsequently returns.
6)
Customer has a heavily skewed TMS_DICT_CONTENTS table i.e.
- 90% of the TMS_DICT_CONTENTS records (22M) are for the MedDRA dictionary
- 20M records are current (have END_TS=3MJ)
Changes
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms |
Changes |
Cause |
Solution |