Portal Performance is Slow After Upgrading Repository Oracle Database from 10.2.0.4 to 18.104.22.168
(Doc ID 1496338.1)
Last updated on NOVEMBER 15, 2016
Applies to:Portal - Version 22.214.171.124.0 to 126.96.36.199.0 [Release FMW11g]
Information in this document applies to any platform.
Checked for relevance on 02-Jul-2014
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:
WHERE NAME = 'ALL_OBJECTS'
AND OWNER = 'PORTAL'
AND GRANTEE_TYPE = 'USER'
AND GRANTEE_GROUP_ID = 0
AND GRANTEE_USER_ID = 3
/*+ ORDERED */
FROM WWSEC_FLAT$ F,
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,
An Explain Plan of the above query indicates it is using SKIP SCAN instead of RANGE SCAN:
SORT (UNIQUE) (Cost=107 Cardinality=2 Bytes=22)
VIEW (Cost=106 Cardinality=2 Bytes=22)
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)
Oracle Portal 11g repository database is upgraded from Oracle Server 10g to 11g.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!