Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Shared Pool and ADO Parameters

Shared Pool and ADO Parameters

From: <tmr777_at_my-deja.com>
Date: Tue, 16 Jan 2001 16:09:31 GMT
Message-ID: <941rnj$qve$1@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 - 10:09:31 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US