SQL Profiles / SQL Baselines 未生效的一个原因

 query to ignore SQL Profiles/SQL Baselines and was not using desired index. (Doc ID 2570477.1)

Applies to:

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

Symptoms

• On 12.1.0.1 version, SQL_IDs are not picking the desired explain plan after applying the profile 

Changes

Linuguistic Sorting by the use of NLS_COMP = LINGUISTIC  at client side, caused the Oracle Index access path to be ignored by specific runs from Application. Query invoked from SQLPLUS was using desired index.

Cause

When performing SQL comparison operations, characters are compared according to their binary values. A character is greater than another if it has a higher binary value. Because the binary sequences rarely match the linguistic sequences for most languages, such comparisons may not be meaningful for a typical user. To achieve a meaningful comparison, you can specify behavior by using the session parameters NLS_COMP and NLS_SORT. The way you set these two parameters determines the rules by which characters are sorted and compared.

The NLS_COMP setting determines how NLS_SORT is handled by the SQL operations. There are three valid values for NLS_COMP:

• BINARY

  All SQL sorts and comparisons are based on the binary values of the string characters, regardless of the value set to NLS_SORT. This is the default setting.

• LINGUISTIC

  All SQL sorting and comparison are based on the linguistic rule specified by NLS_SORT. For example, NLS_COMP=LINGUISTIC and NLS_SORT=BINARY_CI means the collation sensitive SQL operations will use binary value for sorting and comparison but ignore character case.

• ANSI

NLS_COMP was not BINARY and this setting was invoked as CLIENT SIDE setting on the application sessions running the query on database. This caused the query from application not using the desired Index.

Plan was not reproducible and hence plan stability option did not worked here. Also when the query was invoked from sqlplus it chose the desired index access path, this plan was used for creating baseline for the original query coming from application. However as mentioned this plan won't be reproducible because of NLS_COMP being used as LINGUISTIC and not BINARY
 

Solution

1. Ensure that your application session before running the query on dataabase or SQL job sets NLS_COMP to BINARY, and then run the query.

or

2. Check in the job code, if there are any NLS settings modifying the NLS_COMP and see if it's value is set to BINARY , discuss this with application team.

or

3. Create Functional Index on the sort functions as visible in the predicates information section of execution plan details and capture statistics and then re-acess sql executed from job.