jump to navigation

Costing Concatenated Indexes With Range Scan Predicates Part I (Nothing To Be Desired) July 22, 2022

Posted by Richard Foote in BLEVEL, CBO, Clustering Factor, Concatenated Indexes, Index Access Path, Index Column Order, Index Column Reorder, Leaf Blocks, Non-Equality Predicates, Oracle, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Performance Tuning, Richard Foote Consulting, Richard Foote Training, Richard's Blog.
1 comment so far

In my previous post, I discussed how Automatic Indexing ordered columns when derived from SQLs containing both equality and non-equality predicates.

I’ve since had offline questions asking why indexes are more effective with leading columns addressing the equality predicates rather than the leading columns addressing non-equality predicates. Based on the theory that for everyone who asks a question, there are likely numerous others wondering the same thing, I thought I’ll try to explain things with these posts.

I’ll start by creating the following simple table that has two columns (ID and CODE) that are both highly selective (they both have 10,000 distinct values in a 100,000 rows table, so 10 rows approximately per value):

SQL> CREATE TABLE radiohead (id NUMBER, code NUMBER, name VARCHAR2(42));

Table created.

SQL> INSERT INTO radiohead SELECT mod(rownum,10000)+1,

ceil(dbms_random.value(0,10000)), 'RADIOHEAD' FROM dual CONNECT BY LEVEL <= 100000;

100000 rows created.

SQL> commit;

Commit complete.

I’ll next create an index based on the ID, CODE columns, with importantly the ID column as the leading column:

SQL> CREATE INDEX radiohead_id_code_i ON radiohead(id, code);

Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'RADIOHEAD',

estimate_percent=> null, method_opt=> 'FOR ALL COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

 

When it comes to costing index accesses, some of the crucial statistics including the Blevel, Leaf_Blocks and often most crucial of all, the Clustering_Factor:

SQL> SELECT index_name, blevel, leaf_blocks, clustering_factor FROM user_indexes WHERE index_name = 'RADIOHEAD_ID_CODE_I';

INDEX_NAME               BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
-------------------- ---------- ----------- -----------------
RADIOHEAD_ID_CODE_I           1         265             99034

 

We begin by running the following query, with an equality predicate on the ID column and a relatively large, non-selective range predicate on the CODE column:

SQL> SELECT * FROM radiohead WHERE id = 42 AND CODE BETWEEN 1000 AND 5000;

Execution Plan
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                     |     4 |    72 |     6   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| RADIOHEAD           |     4 |    72 |     6   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | RADIOHEAD_ID_CODE_I |     4 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        824  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

As (perhaps) expected, the CBO uses the index to retrieve the small number of rows (just 5 rows).

However, if we run the following query which also returns a small number of rows  (just 4 rows) BUT with the relatively unselective, non-equality predicate based on the leading indexed ID column:

SQL> SELECT * FROM radiohead WHERE id BETWEEN 1000 AND 5000 AND CODE = 140;

Execution Plan
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     4 |    72 |   105  (11)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| RADIOHEAD |     4 |    72 |   105  (11)| 00:00:01 |
-------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        363  consistent gets
          0  physical reads
          0  redo size
        770  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

We notice (perhaps unexpectedly) that the CBO now ignores the index and uses a Full Table Scan, even though only 4 rows are returned from a 100,000 row table.

This is a common area of confusion. Why does Oracle not use the index when both columns in the index are referenced in the SQL predicates and only a tiny number of rows are returned?

The answer comes down to the very unselective non-equality predicate (ID BETWEEN 1000 AND 5000) being serviced by the leading column (ID) of the index.

The “ID BETWEEN 1000 AND 5000” predicate basically covers 40% of all known ID values, which means Oracle must now read 40% of all Leaf Blocks within the index (one leaf block at a time), starting with ID =1000 and ending with ID = 5000. Although there are very few rows that then subsequently match up with the other (CODE = 140) predicate based on the second column (CODE) of the index, these relatively few values could exist anywhere within the 40% ID range.

Therefore, when costing the reading of the actual index, the CBO basically stops its calculations after the non-equality predicate on this leading ID column and indeed estimates that a full 40% of the index itself must be scanned.

If we force the CBO into a range scan via a basic index hint:

SQL> SELECT /*+ index(r) */ * FROM radiohead r WHERE id BETWEEN 1000 AND 5000 AND CODE = 140;

Execution Plan
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                     |     4 |    72 |   116   (4)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| RADIOHEAD           |     4 |    72 |   116   (4)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | RADIOHEAD_ID_CODE_I |     4 |       |   112   (4)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        114  consistent gets
          0  physical reads
          0  redo size
        806  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

We notice that the overall cost of this index based plan is 116, greater than the 105 cost of the Full Table Scan (and hence why the Full Table Scan was selected). We also notice that the vast majority of this 116 cost can be attributed to the index scan itself in the plan, which has a cost of 112.

If you have a calculator handy, this is basically how these costs are derived.

Range Selectivity = (Max Range Value–Min Range Value)/(Max Column Value–Min Column Value)

Effective Index Selectivity = Range Selectivity + 2 x ID density (as a BETWEEN clause was used which is inclusive of Min/Max range)

= (5000-1000)/(10000-1) + 2 x (1/10000)

= 0.40004 + 0.0002

= 0.40024

Effective Table Selectivity = ID selectivity (as above) x CODE selectivity

= 0.40024 x (1/10000)

= 0.40024 x 0.0001

= 0.000040024

These selectivities are then inserted into the following index costing formula:

Index IO Cost = blevel +

ceil(effective index selectivity x leaf_blocks) +

ceil(effective table selectivity x clustering_factor)

 

Index IO Cost = 1  +  ceil(0.40024 x 265) + ceil(0.000040024 x 99034)

= 1 + 107 + 4

= 108 + 4 = 112.

 

Index Access Cost = IO Costs + CPU Costs (in this plan, 4% of total costs)

= (108 + (112 x 0.04)) + (4 + (4 x 0.04))

= (108 + 4) + (4 + 0)

= 112 + 4

= 116

 

So we can clearly see how the CBO has made its calculations, come up with its costs and has decided that the Full Table Scan is indeed the cheaper alternative with the current index in place.

So Automatic Indexing is doing the right thing, by creating an index with the leading column based on the equality predicate and the second indexed column based on the unselective non-equality predicate.

I’ll expand on this point in an upcoming Part II post.

Upcoming Webinar Series Now Sold Out!! (“White Light White Heat”) July 13, 2022

Posted by Richard Foote in Richard Foote Consulting, Richard Foote Seminars, Richard Foote Training.
add a comment

My upcoming webinars now have the maximum number of attendees I’m comfortable in having in one session and are now officially sold out!!

Taking my inspiration from David Bowie’s famous 1973 farewell speech, not only is this the last webinar series of the year, but it’s likely the last webinar series I’ll ever do.

A huge thank you to everyone who has enrolled, I’m absolutely sure we’re all going to have a blast 🙂

If you were interested in attending this training, please contact me to get yourself on a waiting list (although I unfortunately have no timeframe in which I’m likely to run another series) : richard@richardfooteconsulting.com.

Announcement: Registration Links For Upcoming Webinars Now Open (“Join The Gang”) May 25, 2022

Posted by Richard Foote in 18c New Features, 19c New Features, 21c New Features, Index Internals, Index Internals Seminar, Indexing Tricks, Oracle 21c, Oracle General, Oracle Index Seminar, Oracle Indexing Internals Webinar, Oracle Performance Diagnostics and Tuning Seminar, Oracle Performance Diagnostics and Tuning Webinar, Oracle19c, Performance Tuning, Performance Tuning Seminar, Performance Tuning Webinar, Richard Foote Consulting, Richard Foote Seminars, Richard Foote Training, Richard Presentations.
add a comment

The registration links for my upcoming webinars running in August are now open!!!

The price of each webinar is $1,600 AUD. There is a special price of $2,750 AUD if you wish to attend both webinars (just use the Special Combo Price button).

(Note: Do NOT use the links if you’re an Australian resident. Please contact me at richard@richardfooteconsulting.com for additional payment info and tax invoice that includes additional GST).

Just click the below “Buy Now” buttons to book your place for these unique, highly acclaimed Oracle training events (see some of my testimonials for feedback by previous attendees to these training events):

 

Oracle Indexing Internals Webinar: 8-12 August 2022 (between 09:00 GMT and 13:00 GMT daily) – $1,600 AUD: SOLD OUT!!

Oracle Performance Diagnostics and Tuning Webinar: 22-25 August 2022 (between 09:00 GMT and 13:00 GMT daily) – $1,600 AUD: SOLD OUT!!

Special Combo Price for both August 2022 Webinars$2,750 AUD: SOLD OUT!!

 

The links allow you to book a place using either PayPal or a credit card. If you wish to pay via a different method or have any questions at all regarding these events, please contact me at richard@richardfooteconsulting.com.

As I mentioned previously, for those of you on my official waiting list, I will reserve a place for you for a limited time.

As this will probably be the last time I will run these events, remaining places are likely to go quickly. So please book your place ASAP to avoid disappointment…

 

Read below a brief synopsis of each webinar:

Oracle Indexing Internals

This is a must attend webinar of 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 webinar that is suitable for people of all experiences (from beginners to seasoned Oracle experts).

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 webinar 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 webinar 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 webinar and is not generally available in Oracle documentation or in Oracle University courses.

For full details, see: https://richardfooteconsulting.com/indexing-seminar/

 

Oracle Performance Diagnostics and Tuning

This is a must attend webinar aimed at Oracle professionals (both DBAs and Developers) who are interested in Performance Tuning.  The webinar details 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 webinar will show participants how to confidently and reliably diagnose performance issues. The webinar 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.

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 webinar that is suitable for people of all experiences (from beginners to seasoned Oracle experts).

For full details, see: https://richardfooteconsulting.com/performance-tuning-seminar/

 

If you have any questions about these events, please contact me at richard@richardfooteconsulting.com

 

London March 2020: “Oracle Indexing Internals and Best Practices” and “Oracle Performance Diagnostics and Tuning” Seminars January 21, 2020

Posted by Richard Foote in Richard Foote Consulting, Richard Foote Seminars, Richard's Blog.
add a comment

 

Time is fast running out to enroll for one of my acclaimed seminars I’ll be running in London, UK in March 2020. The dates and registration links are as follows:

23-24 March 2020: “Oracle Indexing Internals and Best Practices” Seminar – Tickets and Registration Link

25-26 March 2020: “Oracle Performance Diagnostics and Tuning” Seminar – Tickets and Registration Link

You can also purchase tickets to both seminars at a special 20% combo discount (all enrollments so far have gone with this option, so it’s obviously the way to go):

23-26 March 2020:  Both “Oracle Indexing Internals and Best Practices” and “Oracle Performance Diagnostics and Tuning” Seminars – Tickets and Registration Link

 

The cost for each individual seminar is:

  • Early Bird Rate (enrollments prior to 31 January 2020) £990.00 (+ VAT)
  • General Rate  (enrollments post 31 January 2020) £1190 (+VAT)

The cost for the seminar combo is:

  • Early Bird Rate (enrollments prior to 31 January 2020) £1550.00 (+ VAT)
  • General Rate  (enrollments post 31 January 2020) £1900 (+VAT)

 

The venue is the rather nice Hilton London Kensington.

Prices include attendance to the seminar, both soft and hard copy of the extensive seminar materials, lunch and morning/afternoon tea/coffee.

Both seminars are very highly acclaimed, with past attendees universally applauding the quality and educational outcomes of the training.  They’re both aimed at Oracle Professionals (DBAs and Developers) who are interested in Performance Tuning and how to maximise the performance of both Oracle Databases and associated applications.

All the details of the Oracle Indexing Internals and Best Practices Seminar.

All the details of the Oracle Performance Diagnostic and Tuning Seminar.

Both seminars have strictly limited places to ensure a quality event for all attendees with venues booked with only small classes in mind. So I recommend booking early (as it’s cheaper) and to avoid possible disappointment. I don’t get to run these kind of events in the UK very often (it would be over 2 years since I last run seminars in London) so do take advantage of attending what will be a unique training opportunity while you can.

If you have any questions, please leave a comment or contact me at richard@richardfooteconsulting.com.

Hope to see you at one or both of these seminars next year !!

London March 2020: “Oracle Indexing Internals and Best Practices” and “Oracle Performance Diagnostics and Tuning” Seminars December 19, 2019

Posted by Richard Foote in Richard Foote Consulting, Richard Foote Seminars.
add a comment

Places are filling up, but there are still some available at both of my acclaimed seminars that I’ll be running in London, UK in March 2020. The dates and registration links are as follows:

23-24 March 2020: “Oracle Indexing Internals and Best Practices” seminar – Tickets and Registration Link

25-26 March 2020: “Oracle Performance Diagnostics and Tuning” Seminar – Tickets and Registration Link

You can also purchase tickets to both seminars at a special 20% combo discount:

23-26 March 2020:  Both “Oracle Indexing Internals and Best Practices” and “Oracle Performance Diagnostics and Tuning” Seminars – Tickets and Registration Link

The cost for each individual seminar is:

  • Early Bird Rate (enrollments prior to 31 January 2020) £990.00 (+ VAT)
  • General Rate  (enrollments post 31 January 2020) £1190 (+VAT)

The cost for the seminar combo is:

  • Early Bird Rate (enrollments prior to 31 January 2020) £1550.00 (+ VAT)
  • General Rate  (enrollments post 31 January 2020) £1900 (+VAT)

 

The venue is the rather nice Hilton London Kensington.

Prices include attendance to the seminar, both soft and hard copy of the extensive seminar materials, lunch and morning/afternoon tea/coffee.

Both seminars are very highly acclaimed, with past attendees universally applauding the quality and educational outcomes of the training.  They’re both aimed at Oracle Professionals (DBAs and Developers) who are interested in Performance Tuning and how to maximise the performance of both Oracle Databases and associated applications.

All the details of the Oracle Indexing Internals and Best Practices Seminar.

All the details of the Oracle Performance Diagnostic and Tuning Seminar.

Both seminars have strictly limited places to ensure a quality event for all attendees with venues booked with only small classes in mind. So I recommend booking early (as it’s cheaper) and to avoid possible disappointment. I don’t get to run these kind of events in the UK very often (it would be over 2 years since I last run seminars in London) so do take advantage of attending what will be a unique training opportunity while you can.

If you have any questions, please leave a comment or contact me at richard@richardfooteconsulting.com.

Hope to see you at one or both of these seminars next year !!

Great Britain and Northern Ireland February 2018 Dates: “Oracle Indexing Internals and Best Practices” Seminar (Battle For Britain) November 17, 2017

Posted by Richard Foote in Index Internals Seminar, Oracle Indexes, Richard Foote Consulting, Richard Presentations.
1 comment so far

seminar photo

UPDATE: ALL TICKETS ARE NOW AVAILABLE FOR PURCHASE !!

Attention Oracle Professionals in the United Kingdom !!

I have now finalised all the dates and venues for a series of my popular and critically acclaimed “Oracle Indexing Internals and Best Practices” seminar I’ll be running in the UK in February 2018.

I’m extremely excited as this will be the first time I’ve delivered this seminar in my old homeland, despite having now delivered versions of this seminar in some 18 countries.

This is a must attend seminar of 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/databases. It’s a fun, but intense, content rich seminar that is suitable for people of all experiences (from beginners to seasoned Oracle experts). Full details of the 2 day seminar content can be found here.

Location, dates and registration for these events have been finalised as follows:

London 12-13 February 2018: (Hilton London Kensington). Tickets & Registration Link.

Birmingham 15-16 February 2018: (Novotel Birmingham Centre). Tickets & Registration Link.

Manchester 19-20 February 2018: (Novotel Manchester City Hotel). Tickets & Registration Link.

Glasgow 22-23 February 2018: (Glasgow Training Rooms). Tickets and Registration Link.

Belfast 26-27 February 2018: (Ormeau Business Park). Tickets and Registration Link.

 

UPDATE: ALL TICKETS ARE NOW AVAILABLE FOR PURCHASE !!

Early Bird Rate (enrolments prior to 12 January 2018) £990.00 (+ VAT)

General Rate  (enrolments post 12 January 2018) £1200 (+VAT)

Prices include attendance to the seminar, both soft and hard copy of the extensive seminar materials, lunch and morning/afternoon tea/coffee.

All seminars will have strictly limited places to ensure a quality event for all attendees and all venues are booked with only small classes in mind. So I recommend booking early (as it’s cheaper) and to avoid possible disappointment. I won’t get to run these kind of events in the UK very often so take advantage of attending what will be a unique training opportunity while you can.

If you have any questions, please leave a comment or contact me at richard@richardfooteconsulting.com.

Hope to see you at one of these events next year !!

 

Indexing Seminar: Why Small Classes? (A Small Plot Of Land) October 5, 2017

Posted by Richard Foote in Index Internals, Index Internals Seminar, Oracle Indexes, Richard Foote Consulting, Richard's Musings.
4 comments

outside2

Firstly, a huge thank-you to everyone who has enroled in one of my “Indexing Internals and Best Practices” Seminars running soon is Australia. The feedback and overall reaction has been extremely positive and I can’t wait to present all the material I’ve been putting together these past few weeks.

There are currently limited places still available at each of the 6 venues, but places are selling out fast. All enrolment details can be found here: https://www.eventbrite.com.au/o/richard-foote-consulting-pty-ltd-15076769762

A question I’ve been asked a couple of times is why the insistence on only running small classes? I’ve previously run similar events overseas to large audiences, I’ve had classes of 40+ a number of times in Europe and I’ve run a one day version of this at Hotsos last year to more people than I could count (I’m guessing 80+). And while they were all highly successful, I will be restricting classes to much smaller class sizes in this Australia tour and into the future.  There are a number of reasons for this, with the key points being:

  • The seminar has been fully revised and updated and is packed with heaps of new content. I want to be able to cover as much material as is practical and this is only possible with small classes. The more I cover, the more attendees have the opportunity to learn.
  • I really want these seminars to be interactive events, with attendees having plenty of opportunity to ask questions. This makes it a more valuable experience as any ambiguities, doubts, environment specific questions, important bits that people don’t get first time, etc. etc. can clarified and addressed.
  • The seminar goes for 2 days, but if I had 50 people and each person asked 15 mins of questions that needed answering, that’s almost the entire time gone. So larger class sizes makes it impractical to have any meaningful Q&A
  • Attendees bring vast amounts of experiences and solutions to Oracle Indexing related scenarios as all Oracle databases out there utilise indexes. Even on Exadata. Even in future autonomous databases. Small classes gives me the opportunity to tap into this.
  • I want to learn just as much as everybody else and if I do all the talking, I’ll learn nothing new. Small classes gives me the opportunity to learn, based on questions asked and experiences shared.
  • All attendees have their own requirements and expectations from attending the seminar. If someone has specific issues at their environments, what a wonderful opportunity to learn and perhaps get these issues addressed by attending the seminar. Again only practical in small class environments.
  • I will be contacting all attendees BEFORE the seminars to determine what their individual expectations are from the seminars. I will then ensure that any appropriate material is fully covered to meet those expectations. This again is only practical with small class sizes.
  • Finally, you’re spending 2 days with the one and only Richard Foote 🙂 As well as learning more about indexes than you thought possible, as well as learning how an appropriate indexing strategy can dramatically improve database/application performance, as well as discovering an appreciations for the works of David Bowie, it’s also an opportunity to have a sense of 1 on 1 with myself  and that I’m there to directly assist “you” with truly understanding how Oracle indexes should be properly utilised. There is little sense of 1 on 1 with large class sizes.

 

So these seminars are designed very intentionally to be “premium” events, offering a training experience that really is second to none. These seminars have always been very well received so I’m truly excited that I’ll be running these events with the best content I’ve ever compiled. Small class settings gives all attendees the very best opportunity to absorb it all in and apply directly what they’re learnt to improving the performance of their database/application environments..

Like I said, there are only limited places now remaining for the Australian Tour so get in early or else risk missing out.

Events in other parts of our beautiful planet are currently being planned and will be announced in the near future. If you have any questions or requests on where to run these seminars, just ask: richard@richardfooteconsulting.com

“Oracle Indexing Internals & Best Practices” Seminar: Australia 2017 Tour (Stage) September 19, 2017

Posted by Richard Foote in Index Internals Seminar, Oracle Indexes, Richard Foote Consulting.
1 comment so far

seminar photo

I’ll be running a fully revised and updated version of my acclaimed “Oracle Indexing Internals and Best Practices” seminar throughout Australia in Oct/Nov 2017. Previous versions of this seminar have been hugely popular and run in some 18 countries. This updated version will feature lots of new material including 12c related indexing capabilities and recommendations.

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 of benefit to DBAs, 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). All seminars are small class environments, with plenty of opportunity for attendees to ask questions specific to their particular environment.

Full seminar details can be found on my new website: https://richardfooteconsulting.com/seminardates

The full Australian itinerary and enrolment details as follows:

Perth 30-31 October 2017: For full details and how to enrol (enrolments close on 22 October or when event is full): https://www.eventbrite.com.au/e/oracle-indexing-internals-and-best-practices-seminar-with-richard-foote-tickets-37783577698

Adelaide 1-2 November 2017: For full details and how to enrol (enrolments close on 22 October or when event is full): https://www.eventbrite.com.au/e/oracle-indexing-internals-and-best-practices-seminar-with-richard-foote-tickets-37793217531

Canberra 6-7 November 2017: For full details and how to enrol (enrolments close on 28 October or when event is full): https://www.eventbrite.com.au/e/oracle-indexing-internals-and-best-practices-seminar-with-richard-foote-tickets-37793625752

Brisbane 8-9 November 2017: For full details and how to enrol (enrolments close on 1 November or when event is full): https://www.eventbrite.com.au/e/oracle-indexing-internals-and-best-practices-seminar-with-richard-foote-tickets-37793828358

Melbourne 13-14 November 2017: For full details and how to enrol (enrolments close on 4 November or when event is full): https://www.eventbrite.com.au/e/oracle-indexing-internals-and-best-practices-seminar-with-richard-foote-tickets-37794100171

Sydney 15-16 November 2017: For full details and how to enrol (enrolments close on 7 November or when event is full): https://www.eventbrite.com.au/e/oracle-indexing-internals-and-best-practices-seminar-with-richard-foote-tickets-37794680908

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.

 

For additional information on seminar content, upcoming seminars or training requests, contact me at richard@richardfooteconsulting.com.

 

 

Richard Foote Consulting Has Arrived (New Angels Of Promise) September 18, 2017

Posted by Richard Foote in Index Internals Seminar, Oracle Indexes, Richard Foote Consulting.
6 comments

Today is the official launch of Richard Foote Consulting, my new independent company in which I’ll be providing specialist Oracle Database consulting and training services, focusing on database performance tuning, database problem resolutions and database health check assessments (at least until everyone moves across to the new self-tuning Oracle Cloud database). Based on all the interest I’ve already received, this journey should be a blast.

For all the details, please visit the new website: richardfooteconsulting.com.

I will also be running training seminar events, starting with a fully revised and updated version of the very popular “Oracle Indexing Internals and Best Practices” seminar. The first series will be an Australian Tour in October/November. For all currently scheduled seminars, see my Seminar Dates Page or just check out the Blog slide bar for a list of upcoming seminars.

And most important of all, I might now have time to post blog articles more regularly 🙂

For information on upcoming seminars, consulting services, rates or availability, please email richard@richardfooteconsulting.com.