Home » RDBMS Server » Performance Tuning » /*+ RULE */ hint in SQL Queries
/*+ RULE */ hint in SQL Queries [message #118819] Mon, 09 May 2005 01:27 Go to next message
sujit_ocp
Messages: 7
Registered: May 2005
Location: Chennai
Junior Member
Hi Everybody,

I am using the Oracle Database 9.2 and I am using /*+ RULE */
hint into some queries to obtain the correct Join orders and they are working fine.
My doubt is will this hint create any problem if my DB is upgraded to 10g. Please give me all the negative impacts of this hint in 10g.

Please Reply me soon as it is really urgent.

Thanks in Advance,

Regards,

Sujit
Re: /*+ RULE */ hint in SQL Queries [message #118869 is a reply to message #118819] Mon, 09 May 2005 08:09 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10613
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
I dont like any HINTS .
why are you using a RULE hint,which forces to use RBO and lost its value after 7.x.
In oracle 8i/9i and above, CBO is more stable and oracle strongly recomends it.

[Updated on: Mon, 09 May 2005 15:06]

Report message to a moderator

Re: /*+ RULE */ hint in SQL Queries [message #118928 is a reply to message #118819] Mon, 09 May 2005 14:19 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Rule is depracated, but I believe still usable in 10g. Thing is, the only way to know the affect on any given query is to test it. And since that means running a full test of every test on your system and making sure they perform well, you can:

a) test every single query now for rule, get your system perfected, and then when rule does truly go away, do it all again for the CBO. Basically do it twice.

b) test every single query now for cbo, get your system perfected just once and be done with it. Plus get the added benefits of cbo that dynamically adjust your plans based on their sizes, skewness, and load characteristics. All the benefits of an optimizer that was programmed this century.

If it was me, I'd choose b.
Re: /*+ RULE */ hint in SQL Queries [message #119361 is a reply to message #118928] Thu, 12 May 2005 03:44 Go to previous messageGo to next message
dmitry.nikiforov
Messages: 723
Registered: March 2005
Senior Member
Sometimes RULE hint is still usefull
in very specific cases when CBO is confused with absence
of statistics (for example when table functions
are using)
One example (getcoll is the table function):

SQL> DELETE FROM nc_params WHERE (object_id, attr_id)
  2  IN (SELECT object_id, attr_id FROM TABLE(getcoll));

0 rows deleted.

Execution Plan
----------------------------------------------------------
   0      DELETE STATEMENT Optimizer=CHOOSE (Cost=91860251 Card=1 Byte
          s=29)

   1    0   DELETE OF 'NC_PARAMS'
   2    1     NESTED LOOPS (SEMI) (Cost=91860251 Card=1 Bytes=29)
   3    2       TABLE ACCESS (FULL) OF 'NC_PARAMS' (Cost=10306 Card=83
          49995 Bytes=242149855)

   4    2       COLLECTION ITERATOR (PICKLER FETCH) OF 'GETCOLL'

SQL> DELETE /*+ INDEX(nc_params xif12nc_params)*/ FROM nc_params WHERE (object_id, attr_id)
  2  IN (SELECT object_id, attr_id FROM TABLE(getcoll));

0 rows deleted.

Execution Plan
----------------------------------------------------------
   0      DELETE STATEMENT Optimizer=CHOOSE (Cost=94784683 Card=1 Byte
          s=29)

   1    0   DELETE OF 'NC_PARAMS'
   2    1     NESTED LOOPS (SEMI) (Cost=94784683 Card=1 Bytes=29)
   3    2       INDEX (FULL SCAN) OF 'XIF12NC_PARAMS' (NON-UNIQUE) (Co
          st=32663 Card=8349995 Bytes=242149855)

   4    2       COLLECTION ITERATOR (PICKLER FETCH) OF 'GETCOLL'

SQL> DELETE /*+ RULE*/ FROM nc_params WHERE (object_id, attr_id)
  2  IN (SELECT object_id, attr_id FROM TABLE(getcoll));

0 rows deleted.

Execution Plan
----------------------------------------------------------
   0      DELETE STATEMENT Optimizer=HINT: RULE
   1    0   DELETE OF 'NC_PARAMS'
   2    1     NESTED LOOPS
   3    2       VIEW OF 'VW_NSO_1'
   4    3         SORT (UNIQUE)
   5    4           COLLECTION ITERATOR (PICKLER FETCH) OF 'GETCOLL'
   6    2       INDEX (RANGE SCAN) OF 'XIF12NC_PARAMS' (NON-UNIQUE)


The most effective execution gives by RULE hint.

Rgds.
Re: /*+ RULE */ hint in SQL Queries [message #119449 is a reply to message #118819] Thu, 12 May 2005 10:50 Go to previous messageGo to next message
Daljit Singh
Messages: 290
Registered: October 2003
Location: Texas
Senior Member
In single line the answer of your query is Oracle 10g doesn't support RULE optimizer so your query will not perform in same way. You have to use CBO if you are on 10g.

According to me on your current oracle version test the query with CBO after collecting proper statisitcs and then deploy it on 10g after getting satisfied results.

Daljit Singh
Re: /*+ RULE */ hint in SQL Queries [message #121219 is a reply to message #119449] Thu, 26 May 2005 23:50 Go to previous messageGo to next message
d.dineshkumar
Messages: 211
Registered: April 2005
Location: Kolkatta
Senior Member
Kuin Sujit Sir,
Itna Sara to answer milgaya ,what more ,Why u r using rule.Use CBO,that is the best.What is the use of Table () as indicated by other member.

Dinesh
Re: /*+ RULE */ hint in SQL Queries [message #122355 is a reply to message #118819] Mon, 06 June 2005 02:43 Go to previous messageGo to next message
nabeelkhan
Messages: 73
Registered: May 2005
Location: Kuwait
Member

The RBO is "dead" in the next release after 9.2

Cheers
NK
Re: /*+ RULE */ hint in SQL Queries [message #122376 is a reply to message #122355] Mon, 06 June 2005 04:52 Go to previous messageGo to next message
dmitry.nikiforov
Messages: 723
Registered: March 2005
Senior Member
nabeelkhan wrote on Mon, 06 June 2005 11:43

The RBO is "dead" in the next release after 9.2



SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
PL/SQL Release 10.1.0.2.0 - Production
CORE    10.1.0.2.0      Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production

SQL> show parameter optimizer

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      10.1.0
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
optimizer_mode                       string      CHOOSE

SQL> desc bin_idx_tab;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------
 A                                                  NUMBER
 B                                                  VARCHAR2(2)

SQL> create type bin_obj as object(a number, b varchar2(2));
  2  /

Type created.

SQL> create type bin_obj_tab is table of bin_obj;
  2  /

Type created.

SQL> create package bin_pkg
  2  is
  3    objs bin_obj_tab := bin_obj_tab();
  4    function get_data return bin_obj_tab;
  5  end;
  6  /

Package created.

SQL> create package body bin_pkg
  2  is 
  3    function get_data return bin_obj_tab
  4    is
  5    begin
  6     return objs;
  7    end;
  8  end;
  9  /

Package body created.

SQL> exec bin_pkg.objs.extend(1); bin_pkg.objs(1) := bin_obj(1,'C');

PL/SQL procedure successfully completed.

SQL> select * from table(bin_pkg.get_data);

         A B
---------- --
         1 C

SQL> create index b01 on bin_idx_tab (a,b);

Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'bin_idx_tab', cascade=>true);

PL/SQL procedure successfully completed.

SQL>set autotrace traceonly expl
SQL> select a.* from bin_idx_tab a, table(bin_pkg.get_data) b
  2  where a.a=b.a and a.b = b.b
  3  /

1000 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=31 Card=8168908 Byte
          s=57182356)

   1    0   HASH JOIN (Cost=31 Card=8168908 Bytes=57182356)
   2    1     COLLECTION ITERATOR (PICKLER FETCH) OF 'GET_DATA'
   3    1     INDEX (FAST FULL SCAN) OF 'B01' (INDEX) (Cost=6 Card=900
          1 Bytes=45005)

SQL> select /*+RULE*/ a.* from bin_idx_tab a, table(bin_pkg.get_data) b
  2  where a.a=b.a and a.b = b.b
  3  /

1000 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   NESTED LOOPS
   2    1     COLLECTION ITERATOR (PICKLER FETCH) OF 'GET_DATA'
   3    1     INDEX (RANGE SCAN) OF 'B01' (INDEX)

SQL> alter session set optimizer_mode = rule;

Session altered.

SQL> select a.* from bin_idx_tab a, table(bin_pkg.get_data) b
  2  where a.a=b.a and a.b = b.b
  3  /

1000 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   NESTED LOOPS
   2    1     COLLECTION ITERATOR (PICKLER FETCH) OF 'GET_DATA'
   3    1     INDEX (RANGE SCAN) OF 'B01' (INDEX)


Such an artful decedent...

Rgds.


icon14.gif  Re: /*+ RULE */ hint in SQL Queries [message #122418 is a reply to message #118819] Mon, 06 June 2005 08:03 Go to previous messageGo to next message
nabeelkhan
Messages: 73
Registered: May 2005
Location: Kuwait
Member

dmitry.nikiforov Mon, 06 June 2005 12:52
Quote:


Such an artful decedent...

Rgds.




Good work I like the example thingi but here is something for ya Wink

Creation Date: 15-MAY-2002
Last Revision Date: 06-AUG-2004



Desupport of the Rule-Based Optimizer

The rule-based optimizer (RBO) will no longer be supported when Oracle9i is de-supported.

Oracle10g will only support the cost-based optimizer (CBO). Hence Oracle9i Release 2 is the last release to support the RBO. Partners and customers should certify their applications with the CBO before that time. The CBO has been Oracle's primary optimization method for many years now, and most major independant software vendors use the CBO by choice. Our surveys indicate that over 80% of customers are using the CBO with Oracle8i, and this number is expected to increase with Oracle9i. For more information on the CBO please refer to the Query Optimization in Oracle9i white paper which can be found at: http://otn.oracle.com/products/bi/pdf/o9i_optimization_twp.pdf

What is being Desupported?

The RBO is Oracle's legacy optimizer originating from Oracle Version 6 and earlier. The RBO is being desupported in Oracle10g. The RBO will still exist in Oracle10g, but will be an unsupported feature and will be removed in a future release. No code changes will be made to the RBO code and no bug fixes will be provided. The RBO was superseded in Oracle7 by the CBO and has continued to be available for backwards compatibility. Oracle Oracle10g will support only one optimizer, and all applications running on that release should use that optimizer.

Versions Affected

Support for the RBO will be removed in Oracle10g. The last release that supports the rule-based optimizer will be Oracle9i Release 2.

Why is the RBO being Desupported?

The existence of the RBO prevents Oracle from making key enhancements to its query-processing engine. The removal of the RBO will permit Oracle to improve performance and reliability of the query-processing components of the database engine.

Furthermore, using the RBO prevents customers from taking advantage of all the query-processing technologies introduced since Oracle 7.3. For example, the RBO cannot take advantage of partitioned tables, bitmap indexes, hash join, parallel query, index organized tables, function-based indexes, materialized views and many others. As a result, customers never realize the benefits and the superior performance gained by these echniques.

Cheers Bro,
NK
Re: /*+ RULE */ hint in SQL Queries [message #122456 is a reply to message #122418] Mon, 06 June 2005 10:36 Go to previous messageGo to next message
dmitry.nikiforov
Messages: 723
Registered: March 2005
Senior Member
Nabeel,

I know what it's declared in Oracle 10G (RULE hint
is not supported and RBO is not supported), but RULE hint
and OPTIMIZER_MODE=RULE still affect execution plan. This is just my doubt - I think it still works but Oracle doesn't recommend to use it. But these are just my conjectures.

Rgds.
icon5.gif  Re: /*+ RULE */ hint in SQL Queries [message #122524 is a reply to message #118819] Tue, 07 June 2005 00:37 Go to previous messageGo to next message
nabeelkhan
Messages: 73
Registered: May 2005
Location: Kuwait
Member

Hi Dmitry,

Though RBO will still exist in Oracle10g, but will be an unsupported feature and will be removed in a future release. No code changes will be made to the RBO code and no bug fixes will be provided, the only version which will be supported for RBO will be 9iR2 and they will provide bug fixes as well...

Something which really Im not sure about is many be it will not be there at all in 10gR2... and its really strange that why Oracle have not removed it from 10g?? May be one reason of this is the v$ queries still use RBO? just a doubt...

Will post info as I will get it....


Cheers,
NK
Re: /*+ RULE */ hint in SQL Queries [message #128130 is a reply to message #122524] Fri, 15 July 2005 05:38 Go to previous message
d.dineshkumar
Messages: 211
Registered: April 2005
Location: Kolkatta
Senior Member
Great ...

regds
Dinesh Laughing
Previous Topic: How to monitor query
Next Topic: Query optimization
Goto Forum:
  


Current Time: Thu Apr 17 23:52:06 CDT 2014

Total time taken to generate the page: 0.13002 seconds