Re: Optimizer Instability on 10.2.0.2

From: <mccmx_at_hotmail.com>
Date: 27 Apr 2007 00:29:02 -0700
Message-ID: <1177658942.371931.113820@n35g2000prd.googlegroups.com>

> Do you have just one connection, or many
> connections - which may be using different
> versions of intermediate layers ? If not, it may
> be that there are a couple of different ways that
> the application addresses the database, and one
> of them fails to pass in binds.

The client program is sqrw.exe and connects to the database as a 2- tier process every 30 minutes. It uses the same method for connecting to the database every time.

I am logging this as a TAR because I'm pretty sure that Oracle is losing the binds somewhere along the line. At first I thought it was the program that was sending NULL values but I disproved this theory in sqlplus by sending a NULL bind and seeing the following:

SQL> VARIABLE MATT VARCHAR2(10);
SQL> SELECT /* findit */ count(*) from ps_job where emplid = :matt;

COUNT(*)

SQL> select name,value_STRING,to_char(LAST_CAPTURED, 'dd MON yyyy hh24:mi:ss')
LAST_CAPTURED from v$sql_bind_capture where ADDRESS='&ADDRESS' Enter value for address: 86634D48

NAME VALUE_STRING
LAST_CAPTURED

-------------------- :MATT NULL 26 APR 2007 13:07:01

So if the bind is NULL then v$sql_bind_capture actually shows NULL in the VALUE_STRING column.

Whereas in our situation we can see occurences of:empty binds:

NAME VALUE_STRING LAST_CAPTURED

-------------------- :1 04/26/07 08:12:35 26 APR 2007 09:00:19 :2 TMMF001 26 APR 2007 09:00:19 :2 TMMF001 26 APR 2007 09:00:19 :1 :2 :2

Matt Received on Fri Apr 27 2007 - 02:29:02 CDT