Full Throttle: Oracle SQL Tuning & the Resource Consumption Approach
The time-based analysis of SQL performance issues is teached 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 to monitor 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.