Select query on dba_mviews takes too long
Who should tune "select * from dba_mviews" query? 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.SUMDETAIL$, SYS.SMON_SCN_TIME, SYS.TABCOMPART$ etc. When we exclude the three columns (NUM_STALE_PCT_REGIONS, NUM_FRESH_PCT_REGIONS, STALE_SINCE) from query instead of selecting all columns (*), it runs super fast. I will inspect why adding these three columns to