Slow Performance Applying Markup Tools, Entering Comments or LOV's in Viewer Due to Suboptimal Oracle Query

Last updated
Save as PDF

Article # 3024767 - Page views: 621

3024767

Problem:

When end users (entry users or coders) apply various markup tools (Entry Complete), enter comments or try to bring up the list of values (LOV) to pick a user to route to, performance is very slow (4 to 12 seconds for example for one or each).

Cause:

MarkView Support has found and confirmed via Oracle AWR reports an internal Oracle database issue reported by several customers. We found that the performance delay was not actually related to MarkView code; rather to an underlying internal Oracle query that was performing sub-optimally. This internal Oracle query was actually the root cause of the problem that was impacting MarkView and slowing down the placement of markups.

The problem query is as follows and shows the text NOPARALLEL:

SELECT /*+ NOPARALLEL */ 1, U.NAME, O.NAME, O.OID$ FROM SYS."_CURRENT_EDITION_OBJ" O, SYS.USER$ U, SYS.TYPE$ T WHERE T.SUPERTOID =:B1 AND T.TVOID = O.OID$ AND O.SUBNAME IS NULL AND O.OWNER# = U.USER#

and this comes from a call from the Oracle dbms_pickler API.

An AWR report may show the problem Oracle query similar to the following:

9vv8244bcq529 : SELECT /*+ NOPARALLEL */ 1, U.NAME, O.NAME, O.OID$ FROM SYS. "_CURRENT_EDITION_OBJ" O, SYS.USER$ U, SYS.TYPE$ T WHERE T.SUPERTOID = :B1 AND T.TVOID = O.OID$ AND O.SUBNAME IS NULL AND O.OWNER# = U.USER#

An Oracle SR was logged for the problem, SR 3-22001753810 and Oracle confirmed the following defect:

Bug 31098151 - CALLS THAT APPEAR TO BE COMING FROM THE JDBC DRIVER ARE CAUSING A PERFORMANCE ISSSUE

They also referenced

Oracle Bug 31819797 : PERFORMANCE ISSUE AFTER 19C UPGRADE BECAUSE OF TYPE$

Solution:

Oracle were able to provide the other customers with a solution that corrected the execution plan for their problematic query, and that immediately addressed the performance problem it was causing on the MarkView side. Their resolution was to add an additional Oracle SYS Index:

CREATE INDEX SYS.I_TYPE4_TEST ON SYS.TYPE$(SUPERTOID);

TABLESPACE SYSTEM ;

Therefore it is recommended customers create this index, first in Non-Production environments, to address this issue, or customers can open their own Oracle SR with Oracle Support, referencing SR 3-22001753810 and Bug 31098151, and provide the AWR noting the problem query. Oracle should then be able to confirm the same solution to address their poorly performing query – which will in turn, will resolve the markup performance situation on MarkView that the Oracle bug has caused.

Applies to:

Product	Version
MarkView for Oracle	10.x