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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Mladen Gogala
Sent: Tuesday, June 25, 2019 9:20 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxx> 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@xxxxxxxxxx <l.flatz@xxxxxxxxxx> 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@xxxxxxxxx
Datum : 24/06/2019 - 12:23 (MS)
An : oracle-l@xxxxxxxxxxxxx
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