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