Solved

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

Posted on 2013-01-25
8
506 Views
Last Modified: 2013-01-30
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
0
Comment
Question by:Javier Morales
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 38817928
http://www.orafaq.com/wiki/ORA-00600

log an SR with oracle with the query
probably a bug
0
 
LVL 7

Author Comment

by:Javier Morales
ID: 38817944
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,
Javier
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 38817964
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 ?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Author Comment

by:Javier Morales
ID: 38818154
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,
Javier
0
 
LVL 38

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 200 total points
ID: 38818184
outlines isn't completely "out" yet

using SPM is favored in 11g
http://docs.oracle.com/cd/E25178_01/server.1111/e16638/outlines.htm
0
 
LVL 7

Author Comment

by:Javier Morales
ID: 38818616
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?
0
 
LVL 15

Accepted Solution

by:
Franck Pachot earned 300 total points
ID: 38819161
Hi,

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.

Regards,
Franck.
0
 
LVL 5

Expert Comment

by:Sanjeev Labh
ID: 38822536
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.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question