This discussion is archived
0 Replies Latest reply: Nov 14, 2012 2:39 PM by Postie RSS

BUG 11930680 & APEX_040100

Postie Newbie
Currently Being Moderated
I am finding this bug 11930680 is affecting my APEX 4.1.1 install.. I am Running 11.2.0.1 on Redhat linux

eg
I get 21 versions of this SQL in APEX_040100 in v$sql_shared_cursor, with reasons AUTH_CHECK_MISMATCH LANGUAGE_MISMATCH
SELECT SHORTCUT_NAME, ID 
FROM WWV_FLOW_SHORTCUTS 
WHERE FLOW_ID = :B3 AND (BUILD_OPTION IS NULL OR (BUILD_OPTION > 0 AND (:B2 IS NULL OR INSTR(:B2 ,':'||BUILD_OPTION||':') = 0) ) OR (BUILD_OPTION < 0 AND (:B1 IS NOT NULL AND INSTR(:B1 ,':'||(0-BUILD_OPTION)||':') = 0) ) ) 
ORDER BY SHORTCUT_NAME
Any many, many other APEX_040100 SQLs having same issue, which I think is slowing down APEX

Should I
- Grant MERGE ANY VIEW to SYS (as per http://magnusjohanssontuning.wordpress.com/2012/08/01/cursor-not-shared-for-different-users/)
- set optimizer_secure_view_merging=false (as per oracle Workaround)

Anyone have similar problems?

This is My SQL to find problem SQLs.. try it on your apex system
select version_count,address,hash_value,parsing_schema_name,reason,sql_text from (
select
 address,''
 ||decode(max(                UNBOUND_CURSOR),'Y',               ' UNBOUND_CURSOR')
 ||decode(max(             SQL_TYPE_MISMATCH),'Y',            ' SQL_TYPE_MISMATCH')
 ||decode(max(            OPTIMIZER_MISMATCH),'Y',           ' OPTIMIZER_MISMATCH')
 ||decode(max(              OUTLINE_MISMATCH),'Y',             ' OUTLINE_MISMATCH')
 ||decode(max(            STATS_ROW_MISMATCH),'Y',           ' STATS_ROW_MISMATCH')
 ||decode(max(              LITERAL_MISMATCH),'Y',             ' LITERAL_MISMATCH')
 ||decode(max(           EXPLAIN_PLAN_CURSOR),'Y',          ' EXPLAIN_PLAN_CURSOR')
 ||decode(max(         BUFFERED_DML_MISMATCH),'Y',        ' BUFFERED_DML_MISMATCH')
 ||decode(max(             PDML_ENV_MISMATCH),'Y',            ' PDML_ENV_MISMATCH')
 ||decode(max(           INST_DRTLD_MISMATCH),'Y',          ' INST_DRTLD_MISMATCH')
 ||decode(max(             SLAVE_QC_MISMATCH),'Y',            ' SLAVE_QC_MISMATCH')
 ||decode(max(            TYPECHECK_MISMATCH),'Y',           ' TYPECHECK_MISMATCH')
 ||decode(max(           AUTH_CHECK_MISMATCH),'Y',          ' AUTH_CHECK_MISMATCH')
 ||decode(max(                 BIND_MISMATCH),'Y',                ' BIND_MISMATCH')
 ||decode(max(             DESCRIBE_MISMATCH),'Y',            ' DESCRIBE_MISMATCH')
 ||decode(max(             LANGUAGE_MISMATCH),'Y',            ' LANGUAGE_MISMATCH')
 ||decode(max(          TRANSLATION_MISMATCH),'Y',         ' TRANSLATION_MISMATCH')
 ||decode(max(                  INSUFF_PRIVS),'Y',                 ' INSUFF_PRIVS')
 ||decode(max(              INSUFF_PRIVS_REM),'Y',             ' INSUFF_PRIVS_REM')
 ||decode(max(         REMOTE_TRANS_MISMATCH),'Y',        ' REMOTE_TRANS_MISMATCH')
 ||decode(max(     LOGMINER_SESSION_MISMATCH),'Y',    ' LOGMINER_SESSION_MISMATCH')
 ||decode(max(          INCOMP_LTRL_MISMATCH),'Y',         ' INCOMP_LTRL_MISMATCH')
 ||decode(max(         OVERLAP_TIME_MISMATCH),'Y',        ' OVERLAP_TIME_MISMATCH')
 ||decode(max(         MV_QUERY_GEN_MISMATCH),'Y',        ' MV_QUERY_GEN_MISMATCH')
 ||decode(max(       USER_BIND_PEEK_MISMATCH),'Y',      ' USER_BIND_PEEK_MISMATCH')
 ||decode(max(           TYPCHK_DEP_MISMATCH),'Y',          ' TYPCHK_DEP_MISMATCH')
 ||decode(max(           NO_TRIGGER_MISMATCH),'Y',          ' NO_TRIGGER_MISMATCH')
 ||decode(max(              FLASHBACK_CURSOR),'Y',             ' FLASHBACK_CURSOR')
 ||decode(max(        ANYDATA_TRANSFORMATION),'Y',       ' ANYDATA_TRANSFORMATION')
 ||decode(max(          TOP_LEVEL_RPI_CURSOR),'Y',         ' TOP_LEVEL_RPI_CURSOR')
 ||decode(max(         DIFFERENT_LONG_LENGTH),'Y',        ' DIFFERENT_LONG_LENGTH')
 ||decode(max(         LOGICAL_STANDBY_APPLY),'Y',        ' LOGICAL_STANDBY_APPLY')
 ||decode(max(                DIFF_CALL_DURN),'Y',               ' DIFF_CALL_DURN')
 ||decode(max(                BIND_UACS_DIFF),'Y',               ' BIND_UACS_DIFF')
 ||decode(max(        PLSQL_CMP_SWITCHS_DIFF),'Y',       ' PLSQL_CMP_SWITCHS_DIFF')
 ||decode(max(         CURSOR_PARTS_MISMATCH),'Y',        ' CURSOR_PARTS_MISMATCH')
 ||decode(max(           STB_OBJECT_MISMATCH),'Y',          ' STB_OBJECT_MISMATCH')
 ||decode(max(             PQ_SLAVE_MISMATCH),'Y',            ' PQ_SLAVE_MISMATCH')
 ||decode(max(        TOP_LEVEL_DDL_MISMATCH),'Y',       ' TOP_LEVEL_DDL_MISMATCH')
 ||decode(max(             MULTI_PX_MISMATCH),'Y',            ' MULTI_PX_MISMATCH')
 ||decode(max(       BIND_PEEKED_PQ_MISMATCH),'Y',      ' BIND_PEEKED_PQ_MISMATCH')
 ||decode(max(           MV_REWRITE_MISMATCH),'Y',          ' MV_REWRITE_MISMATCH')
 ||decode(max(         ROLL_INVALID_MISMATCH),'Y',        ' ROLL_INVALID_MISMATCH')
 ||decode(max(       OPTIMIZER_MODE_MISMATCH),'Y',      ' OPTIMIZER_MODE_MISMATCH')
 ||decode(max(                   PX_MISMATCH),'Y',                  ' PX_MISMATCH')
 ||decode(max(          MV_STALEOBJ_MISMATCH),'Y',         ' MV_STALEOBJ_MISMATCH')
 ||decode(max(      FLASHBACK_TABLE_MISMATCH),'Y',     ' FLASHBACK_TABLE_MISMATCH')
 ||decode(max(          LITREP_COMP_MISMATCH),'Y',         ' LITREP_COMP_MISMATCH')
 reason
from 
   v$sql_shared_cursor
    
group by 
   address 
) join v$sqlarea using(address) where version_count>2
and parsing_schema_name like 'APEX_0%' 
order by version_count desc,address;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points