Re: How to switch the RULE hint in outlines hints in 12.2 on standard edition.
Date: Tue, 25 Jun 2019 22:28:57 -0500
Message-Id: <E1AF69B0-C0F9-4A61-ACD7-79951E1D0155_at_gmail.com>
I believe the optimizer will calculate the cost even when honoring the rule hint. Did you check and see if the plan changes with or without the hint?
Sent from my iPad
> On Jun 25, 2019, at 20:20, Mladen Gogala <gogala.mladen_at_gmail.com> wrote:
>
> 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 10g 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 10g 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_
> 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
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Jun 26 2019 - 05:28:57 CEST