My Oracle Support Banner

How to Diagnose Issues Where Plans are Different From Different Clients (Doc ID 1386036.1)

Last updated on MARCH 03, 2022

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.1 and later
Oracle Database - Personal Edition - Version 9.2.0.1 and later
Oracle Database - Standard Edition - Version 9.2.0.1 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Information in this document applies to any platform.

Purpose

There are times when queries executed on different clients may use different plans. This document assists in diagnosing and resolving plan divergence.

Troubleshooting Steps

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Purpose
Troubleshooting Steps
 Diagnostics
 Review of Diagnostics
 Compare 'bad' and 'good' Execution Plans:
 Execution plan of the slow Application client:
 Execution plan of the fast SQL*Plus client:
 Observations:
 Diagnosing Possible Causes
 1. Bind Peeking 
 Bind Peeking with histograms
 Diagnosis Example:
 2. Cursor Sharing
 3. NLS Settings
 4. Client Parameter Settings
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.