Dev team complains about performance of a complex query which join a dozen of tables, they insist that the row count of the tables in the join remain same, but the query elapse time various from 1 hour to 5 hours. I use AWR to analyze the overall query performance, but down to the fact tables and lookup tables, I use the following DBA query to prove that the logical_read various, which caused the variation of query elapsed time, and most importantly, Tune the query to reduce the elapsed time from hours to only a few minutes! DBA query 1: On table snapshot level: this query help to compare visiting blocks of involved tables on snapshot id level. we have snapshot every 10 minutes. col BEGIN_INTERVAL_TIME format a25 col object_name format a20 col start_time format a15 select object_name, to_char(BEGIN_INTERVAL_TIME,'hh24:mi-dd-mon') start_time, LOGICAL_READS_TOTAL logical_total, LOGICAL_READS_DELTA logical_delta, SPACE_USED_TOTAL space_total, SPACE_USED_DELTA spac