Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-01-25
8
Medium Priority
?
517 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
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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 800 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 1200 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

886 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