RESULT CACHE query impacting Oracle and MarkView performance
Problem:
- Performance of MarkView impacted by the following query in your Oracle AWR report:
SELECT /*+ RESULT_CACHE ORDERED USE_NL(pdm) INDEX(pdf mv_pdf_preference_name_unq) INDEX(pdm mv_pdm_preference_domain_pk) */ PDF.PREFERENCE_ID, PDF.SYSTEM_LEVEL_YN, PDF.GROUP_LEVEL_YN, PDF.ORG_LEVEL_YN, PDF.USER_LEVEL_YN, PDF.DEFAULT_VALUE, PDF.PREFERENCE_DOMAIN_ID, PDF.USE_DEFAULT_VALUE_YN, PDF.GROUP_LEVEL_PRIORITY_YN, PDF.GROUP_RESOLUTION_METHOD, PDM.DATA_TYPE FROM MV_PREFERENCE_DEFINITION PDF, MV_PREFERENCE_DOMAIN PDM WHERE PDF.PREFERENCE_NAME = UPPER(:B1 ) AND PDM.PREFERENCE_DOMAIN_ID = PDF.PREFERENCE_DOMAIN_ID
- The performance impact can be at specific times of the day, or throughout the day.
- Users may report 500 errors in their browsers.
- Log files may show ORA-0100: maximum open cursors exceeded errors.
Cause:
- This issue can occur if a large number of users have incorrectly set the MVT_WIQ_API_ADV_QUERY_FIELDS or MVT_WEB_RECS_PER_PAGE user preferences.
- Having thousands (1000s) of users set these preferences, instead of using the default, will cause a performance issue, and result in the query above showing as TOP SQL within an Oracle AWR report.
Solution:
To resolve, take the following actions to remove this unrequired user preference settings and simply allow them to return to the default, thus vastly reducing the number of records in the mv_user_preference table, and preventing the performance problem:
- Connect to SQL as the MARKVIEW user and run the following:
delete from mv_user_preference_setting where preference_name in ('MVT_WIQ_API_ADV_QUERY_FIELDS', 'MVT_WEB_RECS_PER_PAGE');
commit;
alter table mv_user_preference move;
alter index SF_UPF_USER_PREFERENCE_PK rebuild;
--
NOTE: you may need to also rebuild the following indexes:
alter index MV_UPF_PREFERENCE_ID_FK rebuild;
alter index MV_UPF_USER_PREFERENCE_PK rebuild;
alter index MV_UPF_MOD_FK rebuild;
NOTE: It is recommended to do this on a quiet MarkView system with the MarkView Server stopped and the DBMS Jobs temporarily stopped.
Additional Note: The MVT_WIQ_API_ADV_QUERY_FIELDS preference is set when a user clicks the following button on the Web Inquiry Screen.
Doing so adds a MVT_WIQ_API_ADV_QUERY_FIELDS user preference record of Y for that single user. And turning it back to "Basic" sets it to N.
Normally, Web Inquiry access in MarkView is limited to a few select Admin users or AP Super Users - so there should not be thousands (1000s) of MVT_WIQ_API_ADV_QUERY_FIELDS User Preferences set in a correctly configured system.
Applies to:
Product | Version |
---|---|
MarkView | All Versions |