The time-based analysis of SQL performance issues is taught and lived actively throughout the database world. It proved its value many times, and is often a good way to solve problems. But with only little or no knowledge of the application behind, performance analysts have difficulties monitoring the timing behavior of software components in a reliable way. Especially within an Oracle execution plan, the measured execution times depend on too many factors and variables. Buffer Gets instead, are easy to count and a good “Unit Of Work” to hunt for.

The aim of this lecture is to show, how to analyze execution plans with very small knowledge about the application, and with on-board utilities of the Oracle Database. My way to identify big resource consumers is shown also, but the clear focus will stay on the analysis of the plan itself.

I will also talk about techniques, how to help misled Oracle features “back on track”, always with the objective NOT to limit the dynamics in the Cost-Based Optimizer.