Portal Performance is Slow After Upgrading Repository Oracle Database from 10.2.0.4 to 11.2.0.3
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:
- Oracle Portal 11g (upgraded or newly installed) in Oracle Server 10g.
- Oracle Portal 11g (newly installed) in Oracle Server 10g.
- Oracle Portal 11g (newly installed) in Oracle Server 11g.
Performance issues noted in the following scenarios:
- Oracle Portal 11g (in which the repository resides) is upgraded from Oracle Server 10g to 11g.
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
/
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)
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