Estimating SQL Plan Quality using tuning Heuristics

Kurzbeschreibung

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.

Nutzen für den Teilnehmer:
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

Behandelte Problemstellungen:
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?

Vortragssprache: Englisch
Level: Fortgeschrittene
Zielgruppe: Everybody concerned with Software Quality

Unternehmen:
Deutsche Apotheker und Ärztebank

Vorgetragen von:
Mag. rer. soc.oec. Lothar Flatz

Mag. rer. soc.oec. Lothar Flatz