Solved

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

Posted on 2013-01-25
8
488 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 37

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 37

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

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
clob to char in oracle 3 64
Error querying database link from Oracle 10g to Postgresql 7 69
Database Design Dilemma 6 58
oracle date format checking 7 27
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…
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 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.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

808 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