Re: Materialized Views in 9.2 Giving Perculiar Performance Behaviour

"Doc Waters" <davidmwaters_at_yahoo.com> wrote in message news:1148277606.089677.211580_at_y43g2000cwc.googlegroups.com...
> Gidday
>
> We have recently moved some Views to Materialized Views and have
> achieved astounding performance increases, reports have decreased from
> twenty hours to four minutes. It was the old story with the developers
> not talking to the Oracle experts before pursuing a path.
>
> Our tables are owned by a user called sysadm (its a PeopleSoft system),
> the materialized views are owned by addonadm and the addon system
> executes selects against the materialized views as addonuser. The user
> addonadm has granted the addonusr access to the materialized views.
>
> We refresh the materialized views once each day at 10pm (Australian
> EST, GMT +10) but have found that one of the reports times out (fairly
> quickly) over the ODBC connector. The addonadm user and the addonusr
> users can access the data fairly fast, in around 15 seconds but if we
> have the addonadm user reissue the grants to the addonusr performance
> decreases to 6 seconds and the explain plans change. The times and
> plans change for both the addonusr AND the addonadm user even though
> the addonadm user has not had their privilages change.
>
> Tomorrow morning I'll be able to provide the explain plans and timings
> before and after the grant is issued.
>
> My question? How can the issuing of a grant change the timings on my
> materialized views.
>
> Regards
>
> Dave
>

Technically

    grant select on table to user
is DDL, so it invalidates the current cursors, which leads to re-parsing - which allows the possibility of a new execution plan.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html