Problem Migrating from RBO to CBO.

Tags:
Oracle
Hi everyone, I'm trying to migrate from RBO to CBO on an 8.1.6.2 database. The problem I am running into is a little wierd. Some, not all, queries that have a distinct or a subquery in it doesn't return results. How do I get around this and what is happening? Example: SQL> select 2 b.po_id, 3 b.line_nbr, 4 b.sched_nbr, 5 b.wa_seq_nbr, 6 B.DUE_DATE, 7 B.qty 8 FROM sysadm.PS_WA_PO_LNSHP_CNF B 9 WHERE B.PO_ID = '0000078871' 10 and B.LINE_NBR = 2 11 and B.SCHED_NBR = 1 12 and b.wa_seq_nbr = (Select MAX (x.wa_seq_nbr) 13 from sysadm.ps_wa_po_lnshp_cnf X 14 where b.po_id = x.po_id 15 and b.line_nbr = x.line_nbr 16 and b.sched_nbr = x.sched_nbr); no rows selected SQL> select /*+ rule */ 2 b.po_id, 3 b.line_nbr, 4 b.sched_nbr, 5 b.wa_seq_nbr, 6 B.DUE_DATE, 7 B.qty 8 FROM sysadm.PS_WA_PO_LNSHP_CNF B 9 WHERE B.PO_ID = '0000078871' 10 and B.LINE_NBR = 2 11 and B.SCHED_NBR = 1 12 and b.wa_seq_nbr = (Select MAX (x.wa_seq_nbr) 13 from sysadm.ps_wa_po_lnshp_cnf X 14 where b.po_id = x.po_id 15 and b.line_nbr = x.line_nbr 16 and b.sched_nbr = x.sched_nbr); PO_ID LINE_NBR SCHED_NBR WA_SEQ_NBR DUE_DATE QTY ---------- ---------- ---------- ---------- --------- ---------- 0000078871 2 1 682402 10-JAN-05 0 SQL> Any help would be greatly appreciated since this is really messing with my mind. Thanks, Magnus
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

Have you analyzed all the tables and indexes after migrating from RBO to CBO? If not, I suggest you do this first using DBMS_STAT package and then see the effect.

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Shripad
    Once you analyze tables and indexes, you will find that some queries are performing better/faster and some not. Then you need to tune those queries/SQLs which are not performing well (by adding hints and/or rewriting the SQLs). And that package is DBMS_STATS (not DBMS_STAT).
    0 pointsBadges:
    report
  • EvanOraDBA
    Look at metalink bug 2994212.8 Doc ID: Note:2994212.8 Subject: Support Description of Bug 2994212 Type: PATCH Status: PUBLISHED Content Type: TEXT/X-HTML Creation Date: 16-FEB-2004 Last Revision Date: 26-MAR-2004 Click here for details of sections in this note. Bug 2994212 A query with correlated subqueries can return wrong results This note gives a brief overview of bug 2994212. Affects: Product (Component) Oracle Server (Rdbms) Range of versions believed to be affected Versions
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: