4

I have a big but INTERMITTENT problem with a bug in Oracle 10g when we call some SQL within a Java web application. We can't quickly patch or upgrade to 11g - which seems to be the first 'stupid' oracle support response. There is a work around, but I am having trouble doing this within PreparedStatements within my Java code.

The actual error is:

ORA-00600: internal error code, arguments: [kcblasm_1]

The bug is: Oracle Bug 12419392

The work around is running 

alter session set "_hash_join_enabled" = FALSE;

before we run our bug-inducing SQL. However, traditionally a PreparedStatement takes in one single piece of SQL:

PreparedStatement stmt = con.prepareSelect("sql statement2");

Is it possible to have one PreparedStatement call that looks like this:

PreparedStatement stmt = con.prepareSelect("sql statement1; sql statement2;");

Or is this possible just by running a series of sequential PreparedStatements one after the other?

Not the best time to be getting this with Xmas looming and reduced support etc. etc., so I really hope someone can help. Thanks.

Edit: @jonearles asked for the code, so here it is, if it's on any use. Probably very specific to our project, but someone might spot the glaring bug-inducing issue:

SELECT DISTINCT qm.validator_id,
  qm.QM_ID,
  u.EMAIL,
  qm.creation_dt,
  qm.emailed,
  qm.valid,
  qm.resolved,
  qm.new_obs_id,
  o.*,
  nests.*,
  s.*,
  l.*,
  latc.TENKM
FROM query_man qm,
  obs o,
  obs_aux_aon nests,
  sub s,
  location l,
  l_atlas_tetrad_coverage latc,
  users u
WHERE qm.OBS_ID         = o.OBS_ID
AND o.SUB_ID            = s.SUB_ID
AND u.user_id           = qm.user_id
AND o.obs_id            = nests.obs_id(+)
AND s.LOC_ID            = l.LOC_ID
AND latc.ATLAS_REGION   = 'NKNE'
AND (LENGTH (l.gridref) = 6
AND (SUBSTR(l.gridref,1,3)
  || SUBSTR(l.gridref,5,1)) = latc.TENKM
OR LENGTH (l.gridref)       = 4
AND l.gridref               = latc.TENKM)
AND qm.RESOLVED            IS NULL
ORDER BY latc.tenkm,
  l.tetrad
iaindownie
  • 1,046
  • 12
  • 28
  • Are you using connection pooling? and is the connection released and re-acquired after/before each prepared statement? and did you test using 2 sql statements in a row like that? what was the result? – xQbert Dec 09 '11 at 11:35
  • Nope, haven't tested that, wanted some feedback first on whether doing an Alter Session for this instance will effect other users NOT calling this SQL. Yes, we are using connection pooling, and I think in an optimal way (bit vague on this myself). We are running GlassFish Server Open Source Edition 3.1 (build 43) if that's useful. As I say, it's very intermittent and a definite bug in Oracle. – iaindownie Dec 09 '11 at 11:42
  • Remember an issue with connection pooling a and oracle while ago; http://stackoverflow.com/questions/5761175/odp-net-connection-pooling-clientid-client-identifier-never-changes-from-first. Wonder if it's a related issue: basically a session was "dirty" from a previous use when it was returned to the pool but not "everything" was released; so by explicitly running a command they were able to clear the session. it seemed intermittent to them as well. – xQbert Dec 09 '11 at 11:56
  • Thanks - we'll look into that, although the fact that Oracle support tell us this is a bug at their end suggests Connection Pooling not a problem for us. Been working OK for several years. – iaindownie Dec 09 '11 at 12:12
  • 1
    I was more indicating that I'm not sure what the impacts of using the alter session will be when the connection is releeased back to the pool (since the connection is never "Fully" released") thus you may only need to set the alter session once when the connection is first made; and subsequent calls wouldn't need it. only way of knowing is to test, and since the problme can't be recreated; makes testing difficult at best. – xQbert Dec 09 '11 at 12:19
  • OK. Testing the multi-SQL-statement PreparedStatement I described above (PreparedStatement stmt = con.prepareSelect("sql statement1; sql statement2;");) doesn't like the semi-colons. The following: 1. alter session set "_hash_join_enabled" = FALSE; 2. My SQL 3. alter session set "_hash_join_enabled" = TRUE; works on small scale SQL with no disastrous effects on other users. Downside is I'm no-longer optimising the SQL, so takes 5x - 10x longer - an out of temp table space. Solution: Patch or upgrade. Thanks for comments @xQbert, helped to understand and target testing approaches. – iaindownie Dec 09 '11 at 15:27
  • Best of luck, I'm not sure what answer can be provided here. – xQbert Dec 09 '11 at 16:04
  • Is there just one SQL statement that causes this bug? If so, can you post it? Sometimes you can make weird small changes to a query to make it avoid ORA-600 bugs. And if hash joins are the problem, maybe you could just use a no_use_hash hint for that one query. Disabling hash joins for the entire session seems like a really awful work-around. – Jon Heller Dec 10 '11 at 04:23

1 Answers1

1

OK. The answer to my primary question is NO, you can't create a PreparedStatement like so:

PreparedStatement stmt = con.prepareSelect("sql statement1; sql statement2;");

Running individual statements to alter session temporarily for one bit of SQL did work, but agreed seems awful and also unacceptably slowed response. Options seem to be patch or upgrade, or look into the no_use_hash hint (which I think will be slow too). Will look at code.

iaindownie
  • 1,046
  • 12
  • 28