Full Table Scans On PORTAL.WWCTX_SSO_SESSION$ Cause Random Slowdown Of Website Running Oracle Portal (Doc ID 559668.1)

Last updated on MARCH 08, 2017

Applies to:

Portal - Version 9.0.4 to 10.1.4.2 [Release 10g to 10gR2]
Information in this document applies to any platform.
Checked for relevance on 17-May-2013


Symptoms

From time to time, the performance of the Portal website becomes unacceptably slow. The issue appears to be random and cannot be reproduced at will. During the event, the query below accounts for almost 99% of the wait times in the database:

SQL> SELECT ID, SUBSCRIBER_ID, USER_NAME, DB_USER, NLS_LANGUAGE, HTTP_LANGUAGE,
     NLS_TERRITORY, TRANSLATION_LANGUAGE, LOGIN_TIME, IP_ADDRESS, IS_LOGGED_ON,
     LAST_UPDATED, SESSION_START_TIME, SESSION_EXPIRE_TIME, SECURITY_VALID, COOKIE
     FROM WWCTX_SSO_SESSION$ WHERE COOKIE = :B2 AND ACTIVE = :B1

Inspection of the execution plan for this query shows that the Cost Based Optimizer (CBO) has chosen a Full Table Scan (FTS) on the table portal.wwctx_sso_session$ to retrieve the data from this table. Under normal circumstances, the CBO would choose an index range scan for the execution plan of this query.

(Note that it is also possible for the execution plan of this query to use the wrong index, and not just a Full table Scan - ref <<Note 1584065.1>> - Portal 10g Website Suddenly Performing Badly and Intermittently Down - Query on WWCTX_SSO_SESSION$ High Gets)

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