Re: optimizer_secure_view_merging

Actually I can't say anything about optimizer_secure_view_merging but we considered using MVs with refresh on commit. After the first simple tests we rejected this idea, because simply the the time it takes was too long. For example with a simple MV based on one table Oracle executed 70 statements, for MV based on 5 tables join there were 202 statements insted of one base statement :( You can find these numbers in
http://www.gplivna.eu/papers/mat_views_search.htm under the section Bright idea – materialized views.

Of course if you absolutely need refresh on commit then you haven't choice and/or if your DML rate is rather low you don't care about these another statements.

Gints Plivna
http://www.gplivna.eu

2006/12/19, Carlson, Todd <tcarlson_at_tripos.com>:
>
>
>
> Hey Guys,
>
>
>
> We are using materialized views with refreash on commit (don't ask…) on
> 10.2.0.2. We have some performance problems and I have been researching
> optimizer_secure_view_merging, to reduce the CPU load of parsing and
> executing statements against the views based on the materialized views.
> However, I have been unable to get much information on this and I would like
> to know more before I starting testing with it.
>
>
>
> Has anyone set this parameter to false in a production system? If so, what
> were the results?

<skipped>

--
http://www.freelists.org/webpage/oracle-l