Database Performance For Developers (ENG)

Why attend this workshop?

As a developer you hope that a relational database keeps the promise of SQL working in principle like a black box - leaving all the details up to the database engine and not requiring to think about the details how to access the data in the database, but just formulating your queries based on a relational model, everything else the database will take care of.

While this holds true to a significant degree in reality things might look different regarding performance - with increasing complexity and data volume some processing in the database might take longer than expected which can significantly impact the end user's satisfaction.

It therefore makes sense to spend more time on the details of the database regarding performance.

This workshop starts with a general understanding what (database) performance means and how it can be measured and made visible in the database, and the importance of proper application instrumentation in that context.

We have a deeper look at the optimizer, the part of the database engine that turns SQL into executable code and learn - using examples based on real life patterns - what the optimizer does well and what it doesn't and how you can support the optimizer. We also learn how to correctly read execution plans.

We deal with "bad" and "good" hints and learn how they can help us when it comes to writing complex SQL.

We look at bind variables and literals and what needs to be considered when using them, and in particular in the context of static SQL in PL/SQL what the ideal approach could look like.

What makes good indexing is also an important topic. In that context we also look at clustering and what it means and what new features the latest Oracle versions offer in that regard (Attribute Clustering / Zonemaps). We also look at the different possible physical design available in Oracle (Heap, Index Organized, Cluster) - a usually highly undervalued topic when it comes to database performance.

Often performance problems of applications are caused by some basic misconceptions how the database treats and turns SQL into executable code and what can go wrong with that, why it can go wrong and what to do about it, how to avoid it and how physical design can be optimized right from the beginning for performance.

Objectives

• Basic understanding of performance and how it can be measured in the database
  
  
• Application requirements - the importance of instrumentation
  
  
• Understanding the different performance related tools available in the database and how to use them properly (Trace / Row Source Statistics / Active Session History / Real Time SQL Monitoring)
  
  
• Understand the basic principles how the Oracle optimizer calculates the cost of queries and the main driving factors of the cost calculation (optimizer basics, histograms, statistics including 12c/18c/19c new features)
  
  
• Get an idea why some of these calculations can go wrong even for cases that seem to be rather trivial for the human eye
  
  
• Learn what you can do to improve some of these calculations and what to do for those cases where you can't easily improve
  
  
• Learn how to properly read the execution plans generated by the optimizer (properly reading (serial) execution plans)
  
  
• What makes good indexing?
  
  
• What does Clustering mean, what impact does it have on performance and how can I influence this as a developer / designer? (Clustering / physical storage options including new features 12c: Attribute Clustering / Zonemaps, Heap, Index Organized Tables, Cluster)
  
  
• Bind variables and literals: Basic concepts, pro and cons and how to make optimal usage? (Bind variables and skewed data distribution)
  
  
• When does it make sense to use hints, what type of hints are there and how do I use them properly (Hints)

Required skills

Thorough understanding of Oracle Architecture and SQL

Target audience

DBAs and developers

Topics covered

- Instrumentation

- Trace / Row Source Statistics / Active Session History / Real Time SQL Monitoring

- Optimizer basics, histograms, statistics (including new features 12c/18c/19c)

- Reading (serial) execution plans properly

- Indexes

- Clustering / physical storage options including new features 12c: Attribute Clustering / Zonemaps, Heap, Index Organized Tables, Cluster

- Hints

- Bind variables / literals and skewed data distribution