text: Proud Moment: My Article on debugging "My Query is taking long time to execute." is published at Oracle Website. https://developer.oracle.com/databases/my_sql_is_stuck
  This article contains the step by step process to debug the performance problems of SQLs having a particular issue. I have also tried to include the information about some of the data dictionary tables and their columns. I hope this article will be useful for our database developer community.
  These problem statements are very common, but for the most part the problems as easily solved – if you know how to debug the issue. In this article I will attempt to explain how I debugged and solved a real performance issue. (Note that the objects have been renamed.)
  I received a request to look into a performance issue: “I am unable to refresh GLUSR_OPPORTUNITY_MV materialized view, and the dbms_mview.refresh is stuck for last the 45 minutes.” The person was trying to re-create GLUSR_OPPORTUNITY_MV materialized view after adding one new varchar2 column from the existing base table. There was no other change in the query of the materialized view, no table was added, and no condition was altered. Prior to the problem this materialized view refreshed in less than 5 minutes.
  To Read the Steps to find the root cause of the issue and little details about data-dictionary tables and their column, please visit