Database Performance: Tuning and Locking

Database Performance: Query Tuning and Locking

Length: 5 days

 

This 5-day seminar focuses on the ways you can improve the performance of your Oracle Rdb database application by manipulating the "logical" characteristics of your database. Importantly, the seminar also focuses on ways to avoid common pitfalls and known performance issues with the engine. The seminar is divided into the following modules:

Concurrency Control This module addresses the theoretical goals that relational databases must address to preserve transactional integrity for data. Again, the format of this is practical rather than extremely theoretical. the module includes a review of how the lock manager works.

Rdb Locking Strategies This module addresses what Rdb does to lock data resources and how to plan for that. Important characteristics of this discussion include ways to manage Rdb’s locking and ways to improve database concurrency.

Theory of Relational Database Query Optimization It is important that the student be able to understand just what Relational databases have to do to solve queries. Accordingly, this module is a brief introduction to the "theory" of relational database query optimization. Although this subject can be relatively esoteric, the focus of this module is to give the student a "practical" handle on the subject.

Static Query Optimization This module applies the lessons learned in the previous module and reviews just how Rdb performs its work and what the parameters are that go into the optimizer. The strategies that Rdb can use will be reviewed in detail.  How to detect poor strategies will be reviewed. In this module, the student will learn how to "design" good access paths and to determine whether the optimizer selects these designed solutions.

Dynamic Query Optimization The dynamic optimizer is a truly unique part of the Rdb engine. It is especially critical to the success of the current generation of GUI client­server application. This module describes what the dynamic optimizer does and how to interpret and anticipate its functioning so that this important behavior can be planned for by the database tuner.

Helping Rdb to Pick Strategies Naturally Recent additions to the engine allow Rdb to store more data than heretofore on the static structure of the database. This information allows the optimizer to select better paths. This module focuses on ways and methodologies to maintain this information.

Forcing the Optimizer to Select YOUR Strategy When all else fails, one may force the optimizer to do queries "your way". This module tells you how, when, and why.

The JCC Methodology This module reviews the methodology that JCC uses for database tuning. This methodology has proven a valuable resource at many sites.

Seminar Presenter:

Keith W. Hare, President