Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Shared Pool and ADO Parameters
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 - 10:09:31 CST