Toad World® Forums

difference between DBA_MVIEWS and DBA_SNAPSHOTS


#1

I find that some of the SB tabs dealing with MVs are quite slow. I wonder if it
has something to do with the view being used. I notice that on our system (an
11g system) DBA_MVIEWS is very much slower than DBA_SNAPSHOTS. To show 543 MVs
DBA_MVIEWS takes over 40 seconds while DBA_SNAPSHOTS takes on 3 seconds.

I am writing this just in case some of the Toad scripts use DBA_MVIEWS instead
of DBA_SNAPSHOTS and could be changed in Toad 10.6.

Though for us it is probably not a big deal because our current servers are end
of life and we are going to real computers in the fall.


#2

We won’t be going back to dba_snapshots .

We made the switch to dba_mviews because dba_snapshots isn’t documented
anymore, and it sometimes contains duplicate rows. DBA_MVIEWS also shows more
information.

If it will help, you can assign optimizer hints for DBA_MVIEWS from the options
screen – or maybe collect stats on the underlying tables of dba_mviews .

-John