• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 520
  • Last Modified:

How may I force ignoring use of CBO in a query execution in Oracle11g

Hi colleagues,

I'm hitting an ORA-600 when use a particular query. Only this one is causing the issue.
That particular ORA-600 is not reported on metalink (as I only have read only privileges now and can't create an SR) and I found a workaround to it.

ORA-00600: código de error interno, argumentos: [qerixGetKey1], [37], [50], [], [], [], [], [], [], [], [], []

When the query is executed using /*+RULE */ hint, the ora-600 is not raised.
When I delete table statistics, (another way to force RBO being used), the ora-600 is not raised.
Also, the query uses a EXISTS clause, and when I change it to IN, the ora-600 is also not raised.

The point is that I CAN'T CHANGE THE QUERY as belongs to a compiled module, or even put a hint on it.

As Oracle11g has deprecated STORED OUTLINES, and sql profile/sql baseline don't seem to work (they raise the ora-600 because, I guess, use of CBO), I need to find a way to make this particular query being executed in RULE optimizing (RBO) to avoid the issue.

NOTE: delete statistics for that particular tables causes the application performance decreases drastically to unacceptable time responses.

Kind regards,
Javier Morales
Javier Morales
2 Solutions
Geert GOracle dbaCommented:

log an SR with oracle with the query
probably a bug
Javier MoralesOracle DBAAuthor Commented:
Geert, I have metalink account and I looked up the Ora-600 error and , as I said, that particular error parameter is not reported [qerixGetKey1]... So:

- no info in metalink about this particular Ora-600
- don't have support to open SR's

Please, read the question completely before answering.

Kind regards,
Geert GOracle dbaCommented:
not every known bug is published on metalink to public
especially the most recent bugs which don't have a solution yet.

if it's a default oracle compiled piece of code your calling, then the only way to solve is through oracle support

if it's a piece of vendor code, contact the vendor
> the vendor will 99% chance log a SR with a oracle for this problem and then reference you
you do have a licensed oracle ?
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Javier MoralesOracle DBAAuthor Commented:
Hi Geert,

No support from vendor or oracle at SR level.

My question is about how may I avoid CBO optimization and make it RBO optimized without deleting statistics (the two tables involved are the main app tables "orders" and "ordtasks")

Before oracle11g it could be done with stored outlines, but can't find a workaround to it (without changing the query).

Many thanks in advance,
Geert GOracle dbaCommented:
outlines isn't completely "out" yet

using SPM is favored in 11g
Javier MoralesOracle DBAAuthor Commented:
I will try out if I can create stored outlines, because as far as I know they were only mantained for compatibility issues with prior versions and was not able to create new...

and so, SPM seems not to allow fix hints for executions... isn't it?
Franck PachotCommented:

11g has the right feature for that: SQLPatch
see: http://docs.oracle.com/cd/E14072_01/server.112/e10595/diag008.htm

SPM is to add hints for performance reasons. SQL Patch is similar, but for bug workaround reasons.

Sanjeev LabhDatabase ConsultantCommented:
Hi techlevel,

I think you are not following the right approach in dealing with your problem. Oracle specifically says from 11g onwards it is not considering RBO it is only CBO henceforth even for future. My suggestion would be instead of looking for workarounds with RBO, change the piece of code though it might take some time so that it properly utilizes CBO so that you do not face problems in future also.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now