SQL Performance Tuning Case Study – Optimizing Dev Gym Leaderboards

Loading the Oracle Dev Gym leaderboards was taking a looooooong time. The culprit? Slow SQL statements. To make the leaderboards load fast we needed to tune them. But first we needed to identify what was slow. Watch this session to see how we used Oracle APEX debugging tools to profile the pages. Then reviewed the execution plans to figure out how to make the slow queries fast. To learn more about Oracle SQL tuning, join the free class Databases for Developers: Performance https://devgym.oracle.com/pls/apex/dg... Session highlights include: 00:00 - Intro 01:00 - Problem: leaderboard page taking a long time to load 03:35 - Introduction to Oracle Dev Gym 08:15 - The performance tuning process 12:20 - Profiling code using Oracle APEX 15:35 - Explain plan vs execution plan for optimizing queries 20:35 - Reviewing APEX debug profile 22:10 - Tuning the problem query 36:25 - Tuning championship results recap 39:10 - Tuning the “Year in Review” page 41:15 - Using lazy loading to help pages load faster 43:10 - Tuning year and lifetime summary stats 45:20 - Refactoring query to reduce full table scans 51:00 - Materialized views for yearly summary 55:50 - Summary of tuning “Year in Review” 59:40 - Free SQL Performance tuning class