07-24-2009 11:47 AM
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 MSEGM and then MKPFBUD using Hints
Can any one help me on this.
Regards,
Vipin.
07-24-2009 12:08 PM
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 MSEGM and then MKPFBUD 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
07-24-2009 1:22 PM
Dear Hermann,
Thanks for your response. Now I am able to change MSEG0 to MSEGM 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 MSEGM and then MKPFBUD.
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
07-24-2009 1:31 PM
Hi,
> Can you tell the Syntax for LEADING Hint and USE_NL Hint to influence the order of Selection ie first MSEGM and then MKPFBUD.
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
12-03-2012 12:15 PM
Hi Vipin,
Try this when you enter hint in the box.
INDEX ("MSEG" "MSEG~M")
Regards,
Ketan