Database Performance Problem Caused by Missing OLS Index

(Doc ID 1271151.1)

Last updated on JANUARY 03, 2013

Applies to:

Oracle Server - Enterprise Edition - Version 10.2.0.3 and later
Information in this document applies to any platform.

Symptoms

The performance of the database operations is considerably slowed down when OLS policies are enabled. The AWR reports show a large number of waits on wait events like "latch free" and "latch: cache buffers chains".
The 2 statements below are found among the statements that appear in the top 10 of the "SQL ordered by Elapsed Time" and "SQL ordered by Gets" areas:

SELECT LAB# FROM LBAC$LAB WHERE ILABEL = :B1
SELECT LAB# FROM LBAC$LAB WHERE POL#=:B2 AND ILABEL = :B1

Changes

The problem appears when the OLS policies are enabled. One of the conditions for this problem to occur is to have a very large number of policies.

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