Re: How to switch the RULE hint in outlines hints in 12.2 on standard edition.

Sure, i am reading JL & Chris Antognini books in order to understand about CBO better.

On Wed, Jun 26, 2019 at 12:27 PM Mark W. Farnham <mwf_at_rsiz.com> wrote:

> NODS. AND it should be noted that the fact that some of Oracle’s own
> recursive queries still reporting as using rule does NOT matter regarding
> the desupport note. Oracle is not the customer and they can do whatever
> they need to in order to make the RDBMS engine run fast.
>
>
>
> The “supported” way to simulate RULE is to write the plan with hints (very
> often a set of no_merge with clauses is sufficient to model RULE’s choices,
> which are static and not dependent on cardinality). And once you’ve done
> that, an outline is not actually needed.
>
>
>
> If you cannot simulate the RULE plan with just no_merge hints (and
> possibly some use_index hints), then I recommend you consult JL and Chris
> Antognini sites and books and sqlmaria for how to beat your query into
> submission.
>
>
>
> IF you think RULE can be good for your query, that is a declaration that
> stats don’t matter for that query. You also have no “strait jacket” going
> this route and if there seems to be an effective hash plan here or there,
> you and hint that (whereas if RULE still functioned you would be stuck with
> those (17 or 26, argue amongst yourselves) heuristic rules and capabilities.
>
>
>
> mwf
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Mladen Gogala
> *Sent:* Tuesday, June 25, 2019 9:20 PM
> *To:* oracle-l_at_freelists.org
> *Subject:* Re: How to switch the RULE hint in outlines hints in 12.2 on
> standard edition.
>
>
>
> https://docs.oracle.com/cd/B13789_01/server.101/b10752/whatsnew.htm
>
> Rule-based Optimization (RBO) Obsolescence
>
> RBO as a functionality is no longer supported. RBO still exists in Oracle
> 10*g* Release 1, but is an unsupported feature. No code changes have been
> made to RBO and no bug fixes are provided. Oracle supports only the query
> optimizer, and all applications running on Oracle Database 10*g* Release
> 1 (10.1) should use that optimizer. Please review the following Oracle
> Metalink desupport notice (189702.1) for RBO:
>
> http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_ <http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=189702.1>
>
> database_id=NOT&p_id=189702.1 <http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=189702.1>
>
>
>
> You can also access desupport notice 189702.1 and related notices by
> searching for "desupport of RBO" at:
>
> http://metalink.oracle.com
>
>
>
>
>
> That is from the documentation of RDBMS version 10.1. The current
> pervasive version is 12.2, quite a few version after 10.1. I would be very
> surprised if Oracle decided to re-support RBO.
>
>
>
> On 6/24/19 6:51 AM, kunwar singh wrote:
>
> Refresh completed. Still not working..
>
>
>
> On Mon, Jun 24, 2019 at 6:45 AM kunwar singh <krishsingh.111_at_gmail.com>
> wrote:
>
> I already tried using sql patch , didnt help either.
>
> Refreshing dictionary stats to check if that helps.
>
>
>
> On Mon, Jun 24, 2019 at 6:33 AM l.flatz_at_bluewin.ch <l.flatz_at_bluewin.ch>
> wrote:
>
> Hi,
>
>
>
> i think it would be much easier to use sql_patch.
>
> Have you tried to refresh dictionary stats though?
>
>
>
> Regards
>
>
>
> LOthar
>
> ----Ursprüngliche Nachricht----
> Von : krishsingh.111_at_gmail.com
> Datum : 24/06/2019 - 12:23 (MS)
> An : oracle-l_at_freelists.org
> Betreff : How to switch the RULE hint in outlines hints in 12.2 on
> standard edition.
>
> Hi Listers,
>
> I am trying to fix a good plan using outlines. But it doesn't seem to
> work.
>
>
>
> CREATE OR REPLACE OUTLINE OUTLINE_GOOD ON select /*+ RULE */ COUNT(9) from
> user_objects;
>
>
> CREATE OR REPLACE OUTLINE OUTLINE_BAD ON select COUNT(9) from
> user_objects;
>
>
>
> UPDATE OUTLN.OL$HINTS SET OL_NAME = DECODE(OL_NAME, 'OUTLINE_BAD',
> 'OUTLINE_GOOD', 'OUTLINE_GOOD', 'OUTLINE_BAD')
> WHERE OL_NAME IN ('OUTLINE_BAD', 'OUTLINE_GOOD');
>
> COMMIT;
>
> Still query is not using RBO ...or RULE hints . i see costing in the
> execution plan.
>
> I am using this note thorougly (Doc ID 2254235.1)
>
>
>
>
>
> Can you please suggest.
>
>
>
> --
>
> Cheers,
> Kunwar
>
>
>
>
>
>
>
>
> --
>
> Cheers,
> Kunwar
>
>
>
>
> --
>
> Cheers,
> Kunwar
>
> --
>
> Mladen Gogala
>
> Database Consultant
>
> Tel: (347) 321-1217
>
>

-- 
Cheers,
Kunwar

--
http://www.freelists.org/webpage/oracle-l