Re: Satisfying MOSS answer using hints ?
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: Michael.Seiwert@xxxxxx
- Date: Mon, 21 Mar 2011 13:51:36 +0000
That seems like a reasonable remediation to me (though you might find by
experimentation that only one of the hints was necessary). The alternative
would presumably be to set the corresponding parameters instance wide, or to
investigate rewriting the view.
On Mon, Mar 21, 2011 at 1:37 PM, Michael Seiwert <Michael.Seiwert@xxxxxx>wrote:
> Hi all,
>
> I'm not sure if I should be satisfied with an answer from MOSS regarding
> the applying of the following hints in our application to reduce parse time
> due to high cpu usage ?
>
> *+ OPT_PARAM('_new_initial_join_orders' 'FALSE')
> OPT_PARAM('_optimizer_cost_based_transformation' 'OFF')
> OPT_PARAM('_no_or_expansion' 'true') */
>
> Background:
>
> In our application we have a view which encapsulates eight other complex
> views via UNION ALL. The views have lots of objects involved and they
> contain several OR conditions. If I call the top view without the above
> hinting statement tkprof tells me a parsing time of 192 seconds. After
> applying the above hints the parse time reduces to 2 seconds.
>
> I have a bad feeling setting this hints as they contain undocumented
> parameters. For me it seems that they seem to cover some ugly bugs ?
>
> If you do need some detailed information regarding the views please let me
> know.
>
> Please let me know what you think ?
>
> Very best regards
>
> Michael
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
Other related posts:
