Access Violations with Oracle 10g.

[Speed]

Some of the applications I maintain have started getting random Access Violations since we migrated the Oracle database over to 10g (from 8i). The AVs only seem to occur when the application is either writing to or reading from the Oracle database.

The errors don't happen consistently, but they do sometimes occur more frequently than other times. I'm not certain but I suspect it may be an issue with the load on the Oracle server. Sometimes one application gets a streaming error when it tries to stream data from the database. And there was a case last week where the applications stopped responding altogether. It turned out that the DBAs were running a system job on the server that was chewing up all of the resources.

So my question is, if the application is trying to access the database and the server doesn't respond, is this likely to cause AVs? Could AVs occur if the connection is lost? Does DOA try to automatically reconnect if a connection is lost, or is there something I need to do to reconnect?

I'm just trying to determine if the problem is with my apps or if it's at the server end.

Delphi 7, DOA 4.0.7.1.

Thanks.

Regards,

Steve

 

[Marco Kalter]

Is this an Access Violation at address 00000000? If so, can you set the global variable NoQueryOptimize in the Oracle unit to True at run-time and check if this fixes it?

 

[Speed]

Hi Marco,

I'm pretty sure it's 00000000 but I'll check with the user to confirm. I don't seem to have Oracle.pas, only Oracle.dcu. If I set Optimize to False for all the TOracleDataset and TOracleQuery components will this do the same thing?

Regards,

Steve

 

[Marco Kalter]

Yes, but you can set it to True at run-time:

Oracle.NoQueryOptimize := True;
MySession.LogOn;

 

[Speed]

Ok, thanks Marco. I have set the NoQueryOptimize variable as suggested and so far, so good. Is there a compatibility problem with Oracle 10g and optimized queries?

Regards,

Steve

 

[Marco Kalter]

It seems to be an Oracle Server bug, which started in 10.2, and was "backported" to later 10.1 and 9.2 patch releases..

 

[Speed]

Thanks Marco. Setting Oracle.NoQueryOptimize to True worked a treat. The applications have been rock solid since making that simple change. Thanks very much for your help.

Regards,

Steve

 

[Jens Fudickar]

Hi Marco,

can you tell us, which oracle bug id this case has?

Greetings
Jens

 

[Andrey Maximenko. Ukraine]

maybe 3396162

 

[Joeri Sebrechts]

We're seeing this issue in our software as well. Upgrading to oracle 10.2.0.2 has not resolved it. It cannot be reproduced on SQL/plus, TOAD, or any other way of accessing the database except through our DOA-using delphi applications.

The workaround (noqueryoptimize) did work, but we'd rather not do anything that degrades performance (even if only minimally).

Would it make sense to open a TAR about this? I've found note 342791.1 in metalink which seemed to describe the issue, but it claims it was solved in 10.2.0.1.

 

[Arvid]

We have the problem at one customer site with Oracle 10.2.0.1 but another customer with 10.2.0.3 does not have to problem.

Any one know if there is an oracle bug id for this? Or is this a configuration issue?

 

[nicofari]

Hello,

We are experiencing the same problem but we have
DOA 4.06 and there isn't here Oracle.NoQueryOptimize property. Is there anything equivalent ?
Thanks bye
Nicola

 

[Marco Kalter]

You can set Optimize to False for all TOracleQuery and TOracleDataSet instances. Or upgrade to 4.0.7 and use Oracle.NoQueryOptimize.

 

[nicofari]

I am going to try the second option.
Thanks bye
Nicola

 

[Arvid]

We solved this particular problem on the Oracle side.

We had the problem when we used Oracle Client 9.2.0.6 - upgrading to either 9.2.0.8 or 10 on the client side fixed the problem.