Database Tuning (MOSC)

MOSC Banner

Constants being used as bind variables

edited Dec 13, 2009 9:56PM in Database Tuning (MOSC) 3 commentsAnswered
 I have a poorly performing query in a cursor in a PL/SQL package.

The explain plan generated at run time for this query is very poor.

When I take this code and replace two of the bind variables with the literal values, the explain plan greatly improves.

The literal values are referenced in the cursor as package constants (from a different PL/SQL package).

Eg: 
my_table_alias.my_column_value = my_other_package.my_constant_value;

as opposed to

my_table_alias.my_column_value = 3';

I'm a bit surprised that Oracle is passing these into the CBO as bind variables, when their values cannot vary (barring recompilation of the referenced package).

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center