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:07:41 CST