£1,860 – £2,280

Oracle Indexing Internals & Performance Tuning Seminar Combo with Richard F...

Event Information

Share this event

Date and Time

Location

Location

Hilton London Kensington

179-199 Holland Park Avenue

London

W11 4UL

United Kingdom

View Map

Refund Policy

Refund Policy

No Refunds

Event description

Description

This is a combination of both seminars that I'll be running this week, "Oracle Indexing Internals and Best Practices" and "Oracle Performance Diagnostics and Tuning" seminars at one special price.

Note: Numbers are strictly limited due to the small class nature of the seminars. Please book early to avoid disappointment as seminars are not scheduled regularly.

All seminars include:

  • Detailed course notes
  • Tea/Coffee
  • Lunch


"Oracle Indexing Internals and Best Practices" Seminar Details

Indexes are fundamental to every Oracle database and are crucial for optimal performance. However, there’s an incredible amount of misconception, misunderstanding and pure myth regarding how Oracle indexes function and should be maintained. Many applications and databases are suboptimal and run inefficiently primarily because an inappropriate indexing strategy has been implemented.

This seminar examines most available Oracle index structures/options and discusses in considerable detail how indexes function, how/when they should be used and how they should be maintained. A key component of the seminar is how indexes are costed and evaluated by the Cost Based Optimizer (CBO) and how appropriate data management practices are vital for an effective indexing strategy. It also covers many useful tips and strategies to maximise the benefits of indexes on application/database performance and scalability, as well as in maximising Oracle database investments. Much of the material is exclusive to this seminar and is not generally available in Oracle documentation or in Oracle University courses.

This is a must attend seminar that would be of much benefit to not only DBAs, but also to Developers, Solution Architects and anyone else interested in designing, developing or maintaining high performance Oracle-based applications. It’s a fun, but intense, content rich seminar that is suitable for people of all experiences (from beginners to seasoned Oracle experts). The seminar is developed and personally delivered by Richard “Mr Index” Foote, a well-respected expert in Oracle Database technologies. All seminars are small class environments, with plenty of opportunity for attendees to ask questions specific to their particular environment.


Seminar Content (Subject To Minor Changes)

In no particular order, the following Oracle Indexing “myths” (or misleading generalisations) are discussed throughout my seminar and are still prevalent out there in the Oracle universe:

  • Oracle B-Tree indexes become “unbalanced” over time and need to be rebuilt
  • Deleted space in index is “deadwood” and over time requires index to be rebuilt
  • If index height greater than ‘x’, it becomes inefficient and needs to be rebuilt
  • If index grows to 2x its height, it is 2x more costly to use
  • PCTFREE enables space for index entries to grow within current leaf block
  • If index has a poor (very high) Clustering Factor, rebuild the index
  • To improve the Clustering Factor, you have to rebuild the underling table
  • Clustering Factor “as good as it gets” after gathering 100% estimate index statistics
  • To improve performance, regularly rebuild indexes
  • You never have to rebuild an index to improve performance
  • Statistics from INDEX_STATS provides reliable metrics on when to rebuild an index
  • If delete rows as reported in INDEX_STATS > x%, rebuild
  • If delete rows as reported in INDEX_STATS < x%, don’t rebuild
  • Analyze Index Validate Structure is a safe method of collecting index metrics
  • Index rebuilds are inexpensive and unobtrusive
  • Primary/Unique Key constraints require a unique index
  • Drop/disable a constraint, unique index policing index is automatically dropped
  • All Foreign Key constraints must be indexed
  • Indexes should eliminate sorting
  • Only indexed columns require statistics
  • Bitmap Indexes only useful with low cardinality columns
  • Bitmap Index will be used when a B-tree is not for low cardinality columns
  • Null values are not indexed
  • Small tables (say < 100 rows) don’t benefit from indexing
  • Separating indexes from tables in tablespaces improves performance
  • Range scans not possible with Reverse Key indexes
  • Local indexes improve performance
  • Put most discriminating column first in concatenated indexes
  • If SQL references all columns in index, index column order is irrelevant
  • If leading column of index is not referenced in SQL, index not considered by CBO
  • Monitoring Indexes will highlight which indexes can be safely dropped
  • Indexing Tracking will highlight which indexes can be safely dropped
  • Index Compression make indexes smaller
  • B-Tree Index only useful with high cardinality columns
  • Pointless indexing a column with one distinct value
  • If more than x% rows returned, index is inappropriate, where x% between 0 & 100
  • Full Table Scan more efficient than index range scan with table access, when returning 100% of data
  • The CBO cost an internal value of no practical use for tuning/comparison purposes
  • Index is best solution to return 0% of data
  • You don’t need indexes in Exadata
  • Indexes less important in Exadata, as Storage Indexes can take over if database index is missing

It’s important to note it potentially only takes just the one bad row inserted in a table, one inaccurate statistic, one index being used inappropriately, one missing index not created during a application upgrade, to bring down a whole Oracle database, RAC nodes running on an Exadata included…

Session One: Overview of Oracle Index Structures and Options

  • Common Myths and Misconceptions
  • Oracle Indexing Structures
  • Oracle Indexing Options

Session Two: Introduction To B-Tree Indexes

  • Understanding B-Tree Index Structure and Navigation
  • B-Tree Index Structure Internals
  • Tree Dumps
  • Index Block Dump Examination
  • Study of DML Operations on Index Internals
  • Study of Concurrent Transactions on Index Internals

Session Three: Index Statistics

  • Available Index Statistics – DBA_INDEXES, INDEX_STATS, V$SEGMENT_STATS
  • Shortfalls with VALIDATE STRUCTURE
  • Clustering Factor Study
  • Index Height
  • Statistics Collection

Session Four: Indexes and Constraints

  • Indexing PK and Unique Constraints
  • Important Differences Between Unique and Non-Unique Indexes
  • Indexing Options With Database Constraints
  • Impact of Constraint Options on Indexing
  • Nullable Column Indexing Strategies
  • Foreign Keys and Indexing Strategies

Session Five: Rebuilding, Coalescing and Shrinking Indexes

  • Index PCTFREE
  • Index Block Split Internals
  • Index Root Block Internals
  • Deleted Index Space Management
  • Index INITRANS
  • Index Fragmentation Internals
  • Index Rebuild Criteria
  • Index Optimal Size
  • Dangers Of Index Rebuilds
  • Index BLEVEL
  • Index Coalesce Internals
  • Index Shrink Internals
  • When to Rebuild or Coalesce or Shrink

Session Six: Indexes And The Cost Based Optimizer (CBO)

  • Indexes and CBO Case Studies
  • How CBO Calculates Selectivity
  • How CBO Costs Index Accesses
  • How to Use Index To Access 100% of Rows
  • Table Clustering Attribute
  • CBO and System Statistics
  • Indexes vs. Full Table Scans
  • Index Related Access Paths
  • Indexes and Sorting
  • Index Related CBO Parameters
  • Online Table Maintenance and Index Impact
  • SKIP_UNUSABLE_INDEXES

Session Seven: Miscellaneous Index Tips, Tricks and Traps

  • Multiple Indexes on Same Column List
  • Concatenated Index Column Order Study
  • Why Isn’t Oracle Using My Index ?
  • Outliers and Other Statistic Anomalies
  • Using B-Tree Indexes With Low Cardinality Columns
  • Zero Sized Indexes
  • Index Compression Options
  • Index Monitoring
  • Tracking Index Usage
  • Indexing Small Tables
  • Deferred Invalidation During Index Maintenance
  • Indexing Extended Data Types
  • Indexing Encrypted Data

Session Eight: Additional Indexing Options

  • Reverse Key Indexes Internals
  • Index Organized Tables Internals
  • Index Organized Table Secondary Indexes Internals
  • Function-Based Indexes and Virtual Columns
  • Fake Indexes
  • Invisible Indexes
  • Case In-Sensitive Indexes
  • Indexing JSON Document Store

Session Nine: Partitioned Indexes

  • Partitioning Options
  • Partitioned Index Block Dumps
  • Partition Pruning
  • Global Indexes (Partitioned / Non-Partitioned)
  • Local Indexes (Prefixed / Non-Prefixed)
  • Unique Partitioned Indexes
  • Partial Indexes
  • Partition Access Paths
  • Online Partition Index Conversion
  • Asynchronous Global Index Maintenance
  • Partition Statistics

Session Ten: Bitmap Indexes

  • Overview Of Bitmap Indexes
  • Bitmap Index Block Internals
  • Bitmap Index Misconceptions
  • Bitmap Index Size Considerations
  • Bitmap Index Access Paths
  • Star Transformations
  • OLTP and Bitmap Indexes: Locking Implications
  • Bitmap-Join Indexes
  • Bitmap Index Restrictions


"Oracle Performance Diagnostics and Tuning" Seminar Details

This is a must attend seminar aimed at Oracle professionals (both DBAs and Developers) who are interested in Performance Tuning. The seminar will detail how to maximise the performance of both Oracle databases and associated applications and how to diagnose and address any performance issues as quickly and effectively as possible.

When an application suddenly runs “slow” or when people start complaining about the “poor performance” of the database, there’s often some uncertainty in how to most quickly and most accurately determine the “root” cause of any such slowdown and effectively address any associated issues. In this seminar, we explore a Tuning Methodology that helps Oracle professionals to both quickly and reliably determine the actual causes of performance issues and so ensure the effectiveness of any applied resolutions.

Looking at a number of real world scenarios and numerous actual examples and test cases, this seminar will show participants how to confidently and reliably diagnose performance issues. The seminar explores in much detail the various diagnostics tools and reports available in Oracle to assist in determining any database performance issue and importantly WHEN and HOW to effectively use each approach. Additionally, participants are also invited to share their own database/SQL reports where we can apply the principles learnt in diagnosing the performance of their actual databases/applications (we can communicate prior to the event on how to best get any reports of interest).

One of the more common reasons for poor Oracle performance is inefficient or poorly running SQL. This seminar explores in much detail how SQL is executed within the Oracle database, the various issues and related concepts important in understanding why SQL might be inefficient and the many capabilities and features Oracle has in helping to both resolve SQL performance issues and to maintain the stability and reliability of SQL execution.

It’s a fun, but intense, content rich seminar that is suitable for people of all experiences (from beginners to seasoned Oracle experts).


Seminar Content (subject to minor change)

Section One: Basic Tuning Concepts

  • Common (but flawed) Tuning Methodology

  • THE Correct Tuning Methodology

  • Importance of Response Times

  • Importance of Database Time and Concurrent Active Sessions

  • Introduction to Oracle Wait Interface

  • The Correct Mentality To Performance Tuning

  • Importance Of Instrumentation

Section Two: Database Tuning

In Section Two, we explore how to best diagnose any performance issues as quickly as possible to accurately determine any root issue(s) and so apply the most appropriate resolutions. We explore a number of real world examples as well as providing the opportunity to go through any reports participants may wish to share.

As part of determining how to best resolve a number of real world examples, we get the opportunity to explore the internals of the database and the importance of understanding a number of crucial Oracle database concepts (e.g. latch contention, redo log mechanism, RAC cache fusion).

Because of the abundance of Oracle database instrumentation and the wealth of data available in (say) AWR Reports, many Oracle professionals feel swamped and are uncertain how to correctly read these types of reports when trying to diagnose a performance issue. In this section, we look at both WHEN and HOW to read the various reports to very quickly and reliably determine any performance issues and so know with certainty how to apply the most appropriate solution.

We explore the following Oracle supplied database diagnostics methods and when each of them are most appropriate to determine and resolve performance issues using a methodical tuning methodology:

  • Statspack Reports

  • Automatic Workload Repository (AWR) Reports

  • Automatic Database Diagnostic Monitor (ADDM) Reports

  • Active Session History (ASH) Reports

  • (Advanced) SQL Trace Files

Section Three: SQL Tuning

One of the most common reasons for poor performance is related to poorly running, inefficient SQL statements. In this section we explore in details a number of important concepts and Oracle capabilities (up to and including Oracle Database 18c) in relation to maximising the performance and stability of SQL statements. Topics covered include:

  • CBO Internals And How The CBO Costs SQL Statements

  • Why Is The More “Expensive” Costed SQL Often The Faster

  • Differences between ALL_ROWS and FIRST_ROWS(n) optimization

  • Setting Optimizer Based Parameters

  • CPU Cost Model and System Statistics

  • Schema Statistics Gathering Tips

  • Histogram Internals

  • Extended Statistics and Virtual Columns

  • Dynamic Sampling

  • Displaying and Reading Execution Plans

  • GATHER_PLAN_STATISTICS Hint

  • Managing Outlier Data

  • Join Processing, Join Types and Join Methods

  • Adaptive Query Optimization

  • Adaptive Plans

  • Cardinality Feedback

  • Adaptive Statistics

  • SQL Plan Directives

  • SQL Blocks

  • SQL Transformations

  • 10053 Trace Report

  • SQL Monitor Reports

  • SQL Hints (and when/how to use appropriately)

  • SQL Patching

  • SQL Plan Stability

  • SQL Profiles

  • SQL Stored Outlines

  • Migrating Stored Outlines to SQL Plan Management (Baselines)

  • SQL Plan Management (Baselines)

  • Baselines Use Cases

FAQs

How can I contact the organiser with any questions?

Please email richard@richardfooteconsulting.com with any questions.

What's the refund policy?

There are no refunds if seminar proceeds. If unable to attend, attendees may either transfer ticket to another employee (notification required) or attend a subsequent seminar. If seminar is canceled due to unforeseen circumstances, a full refund will be issued.

Do I have to bring my printed ticket to the event?

Keep your ticket handy either in the Eventbrite App or print it out and bring with you.

Is my registration fee or ticket transferrable?

Yes. Please notify richard@richardfooteconsulting.com if you are transfering your attendance to another person.

Is it ok if the name on my ticket or registration doesn't match the person who attends?

Yes. Notify richard@richardfooteconsulting.com if you are transfering your attendance to another person prior to the seminar


FAQs


How can I contact the organiser with any questions?

Please email richard@richardfooteconsulting.com with any questions.


What's the refund policy?

Attendees can receive refunds up to 7 days before your event start date.


Do I have to bring my printed ticket to the event?

Keep your ticket handy either in the Eventbrite App or print it out and bring with you.


Is my registration fee or ticket transferrable?

Yes. Please notify richard@richardfooteconsulting.com if you are transferring your attendance to another person.


Is it ok if the name on my ticket or registration doesn't match the person who attends?

Yes. Notify richard@richardfooteconsulting.com if you are transferring your attendance to another person prior to the seminar.

Share with friends

Date and Time

Location

Hilton London Kensington

179-199 Holland Park Avenue

London

W11 4UL

United Kingdom

View Map

Refund Policy

No Refunds

Save This Event

Event Saved