It is probably due to how the driver does parameterized queries.
Jim
<tmr777_at_my-deja.com> wrote in message news:941rnj$qve$1_at_nnrp1.deja.com...
> We use 8.05, NT.
>
> Take a look at the following SQL:
>
> select sql_text from v$sql where sql_text like '%LOAD_LIMIT%' and
> executions > 1000
>
> and the results:
>
> 1 begin GET_DLY_DLOAD_LIMIT(12300731,:V0002); end;
> 2 begin GET_DLY_DLOAD_LIMIT(6393018,:V0002); end;
> 3 begin GET_DLY_DLOAD_LIMIT(10686739,:V0002); end;
> 4 begin GET_DLY_DLOAD_LIMIT(1124417,:V0002); end;
> 5 begin GET_DLY_DLOAD_LIMIT(12451454,:V0002); end;
> 6 begin GET_DLY_DLOAD_LIMIT(13486412,:V0002); end;
> 7 begin GET_DLY_DLOAD_LIMIT(6574833,:V0002); end;
> 8 begin GET_DLY_DLOAD_LIMIT(5851891,:V0002); end;
> 9 begin GET_DLY_DLOAD_LIMIT(12328308,:V0002); end;
> 10 begin GET_DLY_DLOAD_LIMIT(5949381,:V0002); end;
> 11 begin GET_DLY_DLOAD_LIMIT(10296451,:V0002); end;
> 12 begin GET_DLY_DLOAD_LIMIT(12126400,:V0002); end;
> 13 begin GET_DLY_DLOAD_LIMIT(12126438,:V0002); end;
>
> Notice how the first input parameter has been inlined as apposed to:
>
> 1 begin GET_DLY_DLOAD_LIMIT(:V0001,:V0002); end;
>
> Obviously, this is creating havoc in my shared_pool. Here is the basic
> ado code:
>
> Sub Snippet()
> Set oConn = CreateObject("ADODB.Connection")
> Set oCmd = CreateObject("ADODB.Command")
> oConn.Open sConnect
> Set oCmd.ActiveConnection = oConn
> oCmd.CommandText = "{ call GET_DLY_DLOAD_LIMIT(?, ?) }"
> oCmd.Parameters.Append oCmd.CreateParameter(, adNumeric,
> adParamInput, 0, lSomeValue)
> oCmd.Parameters.Append oCmd.CreateParameter(, adNumeric,
> adParamOutput)
> oCmd.Execute , , adExecuteNoRecords + adCmdText
> Debug.Print "Return Value: " ; oCmd(1)
> End Sub
>
> Questions is: Why isn't everything being parameterized? Also, all my
> procs are exhibiting similar behavior.
>
> Ideas?
>
>
> Sent via Deja.com
> http://www.deja.com/
Received on Tue Jan 16 2001 - 23:45:22 CST
