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 huge number of users have incorrectly set the MVT_WIQ_API_ADV_QUERY_FIELDS user preference.
  • Having thousands (1000s) of users set this preference, 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:

 

1. 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