Steps to debug SQL Performance issue in Oracle Database

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.

A little snippet:

"My SQL is stuck." 

"My Query is taking long time to execute.".

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
https://developer.oracle.com/databases/my_sql_is_stuck


I would really appreciate your views, or suggestions or feedback on the article!
Happy Oracle Database Performance Tuning!


Read More on Tuning SQL
- SQL Tuning: Partition Pruning and HASH JOIN - Real Life Example
- Why Primary Key Foreign Key Relationship and Join Elimination
- VIEW PUSHED PREDICATE - A Powerful Optimizer Transformation feature
- Why my distinct query is not using index?
- Avoiding unnecessary function calls to optimize SQL statements
- Why do I prefer COALESCE over NVL
- Query optimization tips for Oracle
- Tune Complete Refresh of Materialized View by atomic_refresh
- How to Fast Refresh Materialized View with Joins