Estimating SQL Plan Quality using tuning Heuristics

In practical SQL Tuning we normally focus on areas of the Software the client points out as critical. We use tuning heuristics based on expierence and human reasoning.

I base all reasoning on run time statistics, an exact measurement of every step of a SQL execution plan. They display measured time, effort and result of each step.

Let us now assume we would want to judge the quality of execution plans of whole application.

A manual approach is too much effort. But runtime statistics are queryable in Oracle.

Can we collect the runtime statistics of a whole application? Is it possible to write queries based tuning heuristics to find weak spots in the execution plans? Can this technique be used to judge potential side effects of software changes?

The case used and all results are real.

Value for the audience:
To learn about a technique that will find weaknesses in SQL execution plan quality without the requirement of knowing tuning techniques.
This technique can be used in various ways:
E.g.
for a software user to hint possible ways of improvement of SQL based software to the software vendor
for the software vendor to continously check the quality of his software in automated runs
to train an AI for automated SQL plan tuning
for a database vendor to train an AI for creating optimal execution plans

Problems addressed:
How can we collect run time statistics over the course of several hours without missing some? With which frequency do we have to collect? How much space will the statistics take?

Can we improve the physical schema by finding missing indexs or inefficient index scans?

Talk language: English
Level: Advanced
Target group: Everybody concerned with Software Quality

Company:
Deutsche Apotheker und Ärztebank

Mag. rer. soc.oec. Lothar Flatz