Real World Performance Tour Vienna 2015

On 25.3. Tom Kyte, Graham Wood und Andrew Holdsworth from Oracle visited Vienna and presented the Real World Performance Tour.

The initial theme of Tom Kyte were the bind variable rules - here my summary:

Rule 1

* You'll get a big problem not using bind variables...

Rule 2

* ... but don’t use them in some queries...

Rule 3

* ... and for other queries - it depends!

Here the other topics I noted from this interesting event:

* APEX is worth a try (an APEX application was used for the demo)

* SQL_MONITOR is an excellent tool (also used in the demonstration)

* keep the number of the connections in pool moderate (it is not true - large is beautiful :)

* do not disturb the database with parsing invalid statement (failed parse is worst that hard parse)

* test your application with a max connection set to one; if it doesn't survive some time - don't ship it!

* check the query first, before starting fiddling with the database parameters.

* Good cardinality estimation is critical for CBO. An improper distribution (broadcast or hash) may be fatal.

* In 11g you got cardinality feedback; in 12c a statistics feedback

* ETL "loop" are not always the best solution for loading data. External tables support load and transformation; incl. parallelism and compressed file support.

* Do not store telephone numbers as numbers!

* Constraints are important event in DWH: set them NOVALIDATE, DISABLE and RELY.

* Remember, you get a DB application you deserve!

At the end a nice demonstration of the evolution of the star schema queries was done:

* B-tree (classical OLTP execution plan - '90 approach - not relevant for fact table above some size)

* Bit map index (merge all dimensional bit map indices and access the fact table; '95 approach - still valid especially in case the intersection of the dimension is small)

* Exadata upload - '06 approach

* swap join with bloom filter and join back

I found that event well prepared, very motivated and with excellent dramaturgy!

Jaromir D.B. Nemec is a freelancer specializing in data warehouse and integration solutions. He can be reached under http://www.db-nemec.com