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

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 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_ <http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=189702.1> database_id=NOT <http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=189702.1> &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-l Received on Wed Jun 26 2019 - 18:26:13 CEST