Solved

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

Posted on 2013-01-25
8
459 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 36

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 6

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 36

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
 
LVL 6

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 36

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 6

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now