ORA-24909 10.2.0.1

[tseck]

In post http://www.allroundautomations.com/ubb/ultimatebb.php?ubb=get_topic;f=1;t=002355 Marco Kalter said, that this is an Oracle 10.2 Server bug. As a workaround he recommended to set optimize for all TOracleQuery and TOracleDataset Objects to false.

1. what does this optimize-flag do?

2. is this Oracle bug listed at metalink? Does somebody know the bug number?

3. we noticed ORAs-24909 if using a 10.1.0.3 Oracle client and a 10.2.0.1. db. Does this bug also belong to 10.2. clients?

Thanks,
Thomas

 

[Marco Kalter]

1. what does this optimize-flag do?

When enabled, a TOracleQuery or TOracleDataSet will try to reuse the parsed representation of a previously executed statement, to limit the number of parse operations on the server.

2. is this Oracle bug listed at metalink? Does somebody know the bug number?

No. We have not even been able to reproduce it, and only have this workaround.

3. we noticed ORAs-24909 if using a 10.1.0.3 Oracle client and a 10.2.0.1. db. Does this bug also belong to 10.2. clients?

That's difficult to predict. Perhaps you can try and let us know?

 

[tseck]

Hi Marco,

here's a testcase that raises ORA-24909-errors:

1. create a table xx in a 10gr2 DB

2. Create a delphi 6 + doa40d6 app with 1 form, 1 session, 1 dataset, 1 timer, 1 editfield

3. set the sql of the dataset to select count(*) as cnt from xx

4. create a proc that on application-startup logs on to the db and activates the dataset. Set the time intervall to i.e. 2000

5. create the onTimer event proc that executes a refresh to the active dataset and writes the value of the column cnt to the edit control

6. start the delphi app and sqlplus

7. in sqlplus connect to the db and do a truncate table xx. If using an 10gr1-client (oci.dll v 10.1.x) this raises an ORA-24909 the next time the dataset is refreshed. With an 10gr2 oci no error is raised.

I used the oraclehome=xxxxx command line switch to test the behaviour. Instead of truncating the table xx i also tested many insert-into-xx statements sent by sqlplus scripts. This usually raises errors after 2 to 10 minutes while the inserts still active.

Maybe this helps to find out, if this is an Oracle bug or a doa thing.

Best regards,
Thomas

 

[Michael Ellermann]

Hi Marco,
strange, we have the same Problem, but only on a WINDOWS based Oracle Server. Most of our customer Servers 10.2 are running on Linux, only 1 Server is running on Windows. The bug appears only on this server...
It seems that the client version does not matter, as both 9.xx 10.xx clients produce the error.

beste regards
Michael

 

[Greg Stevenson]

Marco,

We just recently encountered this at one of our customer sites as well. Anything new to add regarding this issue? Has it been determined to be an Oracle Issue? Our main DBA would like a little more detail (in Oracle terms) of what you mean by the optimize flag "When enabled, a TOracleQuery or TOracleDataSet will try to reuse the parsed representation of a previously executed statement, to limit the number of parse operations on the server".

Thank,
--Greg

 

[Marco Kalter]

If you execute the same SQL several times, a parse operation will only occur the first time. Subsequent executions will reuse the parsed representation of the SQL. When this option is disabled you will see an increase of parse operations. Whether or not this is significant for the application performance depends on several factors.

 

[Daniel Stoch]

Hi all,

We have the same problem in our application. Of course, the problem is undeterministic and occurs on Oracle 10.2.0.1 when we are using client version 9.x.x.x or 10.1.0.4.
But it seems that with instant client 10.2.0.1 everything works fine (as Thomas notice in his test).

PS. To use instant client 10.2.0.1 you must have MFC71.dll and msvcr71.dll in your system. More info is here:
http://forums.oracle.com/forums/thread.jspa?threadID=334846

Greetings,
Daniel

 

[Greg Stevenson]

Marco,

From my DBA in response to your reply above (I hate being the middle-man):

That doesn

 

[Marco Kalter]

It's difficult to explain, unless your DBA is familiar with Oracle Call Interface programming. If so, it's the difference between doing an OCIStmtPrepare and one or more OCIStmtExecute calls (works on 10.0 and earlier), and always doing one OCIStmtPrepare and one OCIStmtExecute (required workaround on 10.2). See the Oracle Call Interface Programmer's Guide for more information.

By the way: I have heard from a user that this bug is fixed in Oracle Server 10.2.0.2, but have yet to confirm this.

 

[Greg Stevenson]

Thanks Marco.
Haven't heard back from our DBA yet, but I'm sure the explaination you gave is what they were looking for.
--Greg