Jonathan Lewis is one of the world's foremost Oracle performance experts; an Oracle ACE Director and a member of the prestigious Oak Table Network, a network for Oracle Scientists.
Jonathan has long been a top presenter at IOUG, Oracle OpenWorld, UK Oracle User Group, and at User Groups everywhere. His very popular "Oracle Scratchpad" is an excellent starting point for all things Oracle.
The cost of two days of world-class training by Jonathan is $600. Register for the seminar or the webinar and receive your choice of Jonathan's "Oracle Core: Essential Internals for DBAs and Developers" or "Cost-Based Oracle Fundamentals" or a $25.00 Amazon gift card to spend as you choose. The recording of this training will be available to all attendees for free, unlimited playback until March 7, 2014. You can register for the webinar here or at www.eventbrite.com/e/oracle-structures-registration-9898231878
What are we trying to achieve with this course, and what topic areas are we aiming to cover. How does better information about available data structures allow us to improve the efficiency of the database and help us spend less time on maintenance operations.
2 - Tablespaces and Files
The large-scale structures in Oracle, where they fit together to make a manageable, recoverable, database. Thoughts on big files vs. small files block sizes, extent and segment space management, logging, default storage, transportable tablespaces, and encryption.
3 - Simple Heap Tables
The basic structure; column types, design considerations and storage mechanisms. Some internals, column ordering and chained rows. The importance of constraints. he costs and benefits of compression.
4 - Simple B-tree indexes
What they are for, how they are structured. How we need to design indexes. The significance of the clustering_factor, the relevance of number of rows per key value. Maintenance issues and strategies.
5 - Views
Views are neither good nor bad, they are just a convenience tool that can be used or abused. How views work, how to manage views.
6 - Clusters and Index Organized Tables
Before we get to partitioning, there are structural options in Oracle that allow us to consider “co-location” of similar data items to allow for maximum efficiency during retrieval. We examine the costs, benefits and suitability, and some of the internals, of these structures.
7 - Simple Bitmap indexes
What they are, when they are relevant, how they are structured. Maintenance issues and strategies. The implications of “btree/bitmap conversions”. Since bitmap Indexes are available only in Enterprise Edition we review an option for “emulating” bitmap plans with btree indexes.
8 - Advanced indexing
Function-based indexes, descending indexes, reverse key indexes; case-insensitive indexes, linguistic indexes, index compression costs and benefits. Problems with indexes.
9 - Clones, Triggers and Materialized Views
Performance comes from doing the least work at the right time. We can design a system to do more work when we have spare capacity so that we can do less work when it really matters. We see how materialized views – and “home-grown” variants on the technology – can help.
10 - Partitioning
If we can break big objects into little pieces we may be able to work more efficiently by ignoring the pieces we’re not interested in, and changing the way we handle the ones we are interested in. For backup and recoverability it’s helpful to be able to copy large objects in pieces, and even to make as many pieces as possible read-only to reduce the maintenance overheads. Partitioning is a big topic, but we will cover the critical ideas. Partitioning is an extra option on top of the Enterprise Edition.
11 - Objects and Large Objects
If we have time at the end of the seminar, this is an optional extra session to cover two of the less frequently used areas of the Oracle database.
The “Object-Relational” database never really acquired much momentum. This is probably a good thing since all it did (at least in Oracle’s case) was decompose objects into a relational form and hide a number of mechanical aids inside the server – making it hard for the developer or DBA to work out how to make their code efficient. We will look at a few details of Oracle’s implementation to explain where the threats lie.
LOBS (large objects) are becoming more popular as CRM (customer relation management) systems start to include geographical information, and companies move to electronic document management. We will examine a few details of how Oracle manages LOBs (and the separately licensed securefiles) and consider the strategies we need to avoid the traps of maintaining very large data items.