Select query on dba_mviews takes too long
Who should tune "select * from dba_mviews" query? I’ve moved my blog from https://insanedba.blogspot.com to https://dincosman.com Please update your bookmarks and follow/subscribe at the new address for all the latest updates and content. More up-to-date content of this post may be available there. In one of our database, whenever "Select * from dba_mviews" statement, with no other filter, gets executed, it takes 45 seconds to complete. Actually this is not an acceptable length and requires a detailed investigation. Basically "dba_mviews" is a data dictionary view. Its query is as shown below, a really complex one, just skip it. A great number of subquery runs behind the scenes. As this database serves in an air-gapped environment, I do not have any execution plans of the real-time scenario. Below is the execution plan in my test environment. Access paths are similar. The execution plan has some full table scans such as SYS.SUMDELTA$, SYS....