Question / 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.
Answer / Solution:
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.
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:
1. delete from mv_user_preference_setting where preference_name in
2. alter table mv_user_preference move;
3. alter index SF_UPF_USER_PREFERENCE_PK rebuild;
4. 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.