Materialized Views in 9.2 Giving Perculiar Performance Behaviour

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 Received on Mon May 22 2006 - 01:00:06 CDT