Portal Performance is Slow After Upgrading Repository Oracle Database from 10.2.0.4 to 11.2.0.3 (Doc ID 1496338.1)

Last updated on NOVEMBER 15, 2016

Applies to:

Portal - Version 11.1.1.3.0 to 11.1.1.6.0 [Release FMW11g]
Information in this document applies to any platform.
Checked for relevance on 02-Jul-2014

Symptoms

Portal 11g Performance is severely affected once the database (in which the repository resides) is upgraded from Oracle Server 10g to 11g.

No performance issues noted in the following scenarios:


Performance issues noted in the following scenarios:

Problem query may be identified using AWR/Statspak output.

Example query in this case:

SELECT DISTINCT OBJECT_TYPE_NAME,
PRIVILEGE_CODE
FROM
(
SELECT OBJECT_TYPE_NAME,
PRIVILEGE_CODE
FROM WWSEC_SYS_PRIV$
WHERE NAME = 'ALL_OBJECTS'
AND OWNER = 'PORTAL'
AND GRANTEE_TYPE = 'USER'
AND GRANTEE_GROUP_ID = 0
AND GRANTEE_USER_ID = 3
UNION ALL
SELECT
/*+ ORDERED */
P.OBJECT_TYPE_NAME,
P.PRIVILEGE_CODE
FROM WWSEC_FLAT$ F,
WWSEC_SYS_PRIV$ P
WHERE P.NAME = 'ALL_OBJECTS'
AND P.OWNER = 'PORTAL'
AND P.GRANTEE_TYPE = 'GROUP'
AND P.GRANTEE_GROUP_ID = F.GROUP_ID
AND P.GRANTEE_USER_ID = 0
AND F.PERSON_ID = 3
)
ORDER BY OBJECT_TYPE_NAME,
PRIVILEGE_CODE DESC
/

 

 An Explain Plan of the above query indicates it is using SKIP SCAN instead of RANGE SCAN:

SELECT STATEMENT Optimizer=ALL_ROWS (Cost=108 Cardinality=2 Bytes=22)
SORT (UNIQUE) (Cost=107 Cardinality=2 Bytes=22)
VIEW (Cost=106 Cardinality=2 Bytes=22)
UNION-ALL
INDEX (SKIP SCAN) OF WWSEC_SYSP_IX6 (INDEX) (Cost=18 Cardinality=1 Bytes=46)
HASH JOIN (Cost=87 Cardinality=1 Bytes=55)
INDEX (SKIP SCAN) OF WWSEC_FLAT_IX1 (INDEX) (Cost=17 Cardinality=15 Bytes=135)
TABLE ACCESS (BY INDEX ROWID) OF WWSEC_SYS_PRIV$ (TABLE) (Cost=70 Cardinality=1 Bytes=46)
INDEX (SKIP SCAN) OF WWSEC_SYSP_IX2 (INDEX) (Cost=7 Cardinality=854)

 

Changes

  Oracle Portal 11g repository database is upgraded from Oracle Server 10g to 11g.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms