PL/SQL question

Hi All!

I'v got some strange braking effect using a stored procedure in Oracle. The same procedure with the same parameters, launched from SQL+ ten times
shows next execution times:
63 c
70
99
109
125
180
233
185
273
240
... :-( ...
Any help, if you have some ideas!

This procedure does the folowing:
it gets a string looking like

   'SELECT value1 FROM table1
    WHERE table1.key1= :aKey1 and table1.key2= :aKey2 ' as a parameter. It already get Key1 as second parameter

Then it builds a cursor like

    SELECT SecondKey FROM table2
Then, in the loops through this cursor and calls a second procedure, sending the 'SELECT...' string, Key1 and second key, taken from the cursor as a parameters.
The second procedure builds dynamic cursor from the 'SELECT...' string, binds variables, and executes a SELECT (using DBMS_SQL package). This Select allways retrurns one row and one column. Finaly, this inner procedure checks some table, looking for row with two keys, given as parameters. If it the row is found, it is updated, else - inserted.

As my experiments showed, this braking effect disappeares if I throw out dynamic SQL execution, time becomes stable. If I throw out the cursor, and make the simple loop, it's OK too.

I'll really appreciate any suggestions or experience with this kind of

problem

Thanks in advance.
ps. Please, answer with email
yan_at_lanit.msk.su Received on Fri Oct 04 1996 - 00:00:00 CEST