topic Re: Using Hints Oracle in Application Development and Automation Discussions

Using Hints Oracle

Former Member — Fri, 24 Jul 2009 10:47:12 GMT

Hi All,

I have a statement which selects from WB2_V_MKPF_MSEG2

SELECT 
MBLNR MJAHR ZEILE_I BWART_I MATNR_I WERKS_I LGORT_I CHARG_I SHKZG_I MENGE_I MEINS_I UMMAT_I UMWRK_I  UMLGO_I UMCHA_I 
FROM WB2_V_MKPF_MSEG2 INTO TABLE ITAB_MSEG 
FOR ALL ENTRIES IN ITAB_BATCH WHERE
      MATNR_I = ITAB_BATCH-MATNR AND WERKS_I = STRC_T001L-WERKS AND LGORT_I IN S_LGORT1 AND
      BWART_I IN ('311', '312') AND CHARG_I = ITAB_BATCH-CHARG AND BUDAT IN R_BUDAT  %_HINTS ORACLE
      '&SUBSTITUTE VALUES&' ORACLE '&max_blocking_factor 20&'.

When I observed the trace, I found 2 different execution plans (I had executed the report multiple times)

First -

1. Index Range Scan MKPF~BUD

2. Table Access by Index Rowid MKPF

3. Index Range Scan MSEG~0

4. Table Access by Index Rowid MSEG

Second -

1. Index Range Scan MSEG~M

Index M contains the following fields - MATNR, WERKS, LGORT, BWART, SOBKZ, CHARG

2.Table Access by Index Rowid MSEG

3. Index Range Scan MKPF~BUD

4. Table Access by Index Rowid MKPF

The report running with second execution plan takes less time when compared to first. Though Optimizer is selecting a execution plan which goes through Index in both cases it is unable to select the Best Optimal Plan in all the cases ie Second.

I tried to use Hints,

ORACLE 'INDEX("MSEG" "MSEG~M" "MSEG^M")'

but the optimizer is not considering, still I get the First Execution Plan.

I could observe that Hints are working when I use a single table ie either MKPF or MSEG. But when I use View it is not working.

Is it possible to influence Optimizer to consider first MSEG_{M and then MKPF}BUD using Hints

Can any one help me on this.

Regards,

Vipin.

Re: Using Hints Oracle

HermannGahm — Fri, 24 Jul 2009 11:08:20 GMT

Hi Vipin,

yes, it is possible to use hints for a view.

instead of the table name you have to use VIEWNAME.TABLEALIAS.

The table alias can be found in the view definition.

The view definition can be found in SE14 (object log)

or at db level "SELECT TEXT FROM DBA_VIEWS WHERE VIEW_NAME = '<view_name>';"

Forcing index usage would then look like this:

INDEX(WB2_V_MKPF_MSEG2 .T1 "MSEG~M" "MSEG^M")

if MSEG has alias T1.

back to your question:


> Is it possible to influence Optimizer to consider first MSEG_{M and then MKPF}BUD using Hints
>

this requires much more hints... e.g. LEADING (to specify the starting table) USE_NL (to specify the join mechanism)

and the INDEX hints like the one above...

good luck

Kind regards,

Hermann

Re: Using Hints Oracle

Former Member — Fri, 24 Jul 2009 12:22:12 GMT

Dear Hermann,

Thanks for your response. Now I am able to change MSEG_{0 to MSEG}M using

INDEX(WB2_V_MKPF_MSEG2.T2 "MSEG~M" "MSEG^M").

Can you tell the Syntax for LEADING Hint and USE_NL Hint to influence the order of Selection ie first MSEG_{M and then MKPF}BUD.

And I have one more doubt, when I am going through Oracle Performance Tuning Guide 10g for hints, I found this


Oracle does not encourage the use of hints inside or on views (or subqueries). This
is because you can define views in one context and use them in another. Also, such
hints can result in unexpected execution plans. In particular, hints inside views or
on views are handled differently, depending on whether the view is mergeable into
the top-level query.

I think for my problem, Hints will solve. Can you please give me your advice on this.

Thanks,

Vipin

Re: Using Hints Oracle

HermannGahm — Fri, 24 Jul 2009 12:31:28 GMT

Hi,

> Can you tell the Syntax for LEADING Hint and USE_NL Hint to influence the order of Selection ie first MSEG_{M and then MKPF}BUD.

sure, this hint:

%_HINTS ORACLE 'LEADING(T2)'

ORACLE 'USE_NL(T1)'.

will start with MSEG - T2 (alias from view) and use nested loop to access MKPF - T1 (alias from view)

> And I have one more doubt, when I am going through Oracle Performance Tuning Guide 10g for hints, I found this


> Oracle does not encourage the use of hints inside or on views (or subqueries). This
> is because you can define views in one context and use them in another. Also, such
> hints can result in unexpected execution plans. In particular, hints inside views or
> on views are handled differently, depending on whether the view is mergeable into
> the top-level query.
>

> Can you please give me your advice on this.

hm i think this means that you should not hint IN the view itself because it may be used

from many different call positions.

you didn't hint in the view, you hinted your query that access the view.

Another query without hint may use a different execution plan.

Kind regards,

Hermann

Re: Using Hints Oracle

former_member210621 — Mon, 03 Dec 2012 12:15:51 GMT

Hi Vipin,

Try this when you enter hint in the box.

INDEX ("MSEG" "MSEG~M")

Regards,

Ketan