jump to navigation

Intro: Initial Thoughts On Oracle Autonomous Database Cloud Services (Automatic For The People) March 22, 2019

Posted by Richard Foote in 18c, 19c, 19c New Features, Autonomous Data Warehouse, Autonomous Database, Autonomous Transaction Processing, Oracle Indexes.
3 comments

automatic for the people

I’m currently writing up a few blog pieces on indexing in the Oracle Autonomous Database environments, but I thought I’ll begin by introducing what exactly are Oracle Autonomous Database Cloud Services and some of my initial thoughts, as there’s still some confusion on all this.

Introduced by Uncle Larry at Oracle OpenWorld 2017, Oracle Autonomous Databases are positioned as “self-driving” Cloud- Based database environments, that automatically address many of the tasks usually performed by DBAs such as patching, backups, security and what is of most interest to me, database tuning.

The first of these cloud environments, the Oracle Autonomous Data Warehouse Cloud Service  (ADW) was first released in March 2018, with the Oracle Autonomous Transaction Processing Cloud Service (ATP) released in August 2018.

So key point number one. These are all Oracle Autonomous Database Cloud Services, there are no actual Autonomous Databases as such. These environments currently consist of the following key components:

  • Oracle Database 18c (and above)
  • Oracle Cloud Environment
  • Oracle Exadata Infrastructure
  • Oracle Policy-Driven Automation

So the Oracle Database alone is not an autonomous database. An Oracle 18c database running on your On-Premises Exadata is still not enough to be an autonomous database platform. It requires all the above components, which is why this is only available on the Oracle Cloud environment (and will likely be soon available on the Oracle Cloud at Customer environment, where all these components can also be replicated).

Now having a database environment is which many of the mundane DBA tasks (such as database provisioning, patching, taking backups, etc.) can be automated can only be a good thing and the Autonomous Database Cloud services delivers on all these. Creating an Autonomous Database environment truly just takes a few minutes and I could easily connect via SQL*PLUS and SQL Developer on my laptop a few minutes later.

However, my key interest here is in database tuning (hey, I’m a database tuning guy) and the capability of the Autonomous Database Cloud Services in being able to self-tune and self-repair itself when database performance issues and inefficiencies are encountered.

So, are we there yet?

So my second key point is that is many ways, we already have a “self-tuning” database with Oracle and have had so for many many years. I’m old enough to remember the Rule-Based Optimizer, when the structure of the SQL was critical to performance. I remember tuning Rollback Segments and manually setting extent sizes to make sure no segment got too close to 121 extents else it couldn’t grow any further. I remember manually setting Freelists, I remember having to write my own jobs to run maintenance tasks and setting tablespaces to be in backup mode etc. etc. etc.

The Oracle Database has evolved over the years, where Oracle DBAs don’t have to worry about these things. If you wish, you can now configure the Oracle database to also automatically adjust memory components, automatically collect necessary schema statistics, automatically create baselines and to tune SQL queries, etc. etc. etc.

All of these Oracle database capabilities are crucial in the new Oracle autonomous database environments, as are new Oracle 18c features and as will be new Oracle 19c features (especially in relation to self-tuning the Autonomous Transaction Processing Cloud Service). The newer autonomous database capabilities are just part of this Oracle database self-tuning evolution, with in the future some new policy-driven based automation thrown into the mix.

So are we indeed there yet with a completely self-tuning database with these new autonomous database services? In a word, no.

Which brings me to my next key point. This is all very very new. All these autonomous database services are effectively at “Edition One” status. This will all take time to eventually evolve to be truly, fully self-tuning database environments. There’ll be some new cool capabilities and features which will assist in some areas but be initially deficient in other areas. But clearly this is the future and clearly in future releases, more and more self-tuning capabilities will be added that will make things easier to both manage and tune Oracle database environments.

Note Oracle Corporation itself (depending on who you talk to) is quite clear that it isn’t there yet, with the web-page on the Autonomous Transaction Processing Cloud services clearly stating that “Workload Optimization* (coming soon)“, but with lots of clues on what’s to come with features such as “Database tunes itself (indexes, memory, partitions, SQL plans, parallelism, optimizer stats) for the incoming workload as data changes over time“.

Do many of these upcoming features sound familiar? If you’re not sure, check out the Oracle Database 19c New Features manual.

Which brings me to my final key point here. Even if you’re not particularly interested in the Cloud, if you view managing On-Premises environments as being your foreseeable future, some the best things that has happened to you in relation to the Oracle Database comes courtesy to you as a result of Oracle’s strategic direction with the cloud. Many of the best new features introduced in the past few Oracle Database releases, especially in relation to the CBO and much of the online stuff such moving tables and partitions online, moving data files online, converting tables to be partitioned online, converting partitioned tables differently online, merging/splitting partitions online, etc. etc. are clearly going to be critical in a self-managing/tuning database. As a DBA of an On-Premises database environment, you can also take advantage of these new capabilities.

It will enable Oracle in its autonomous environments for example to automatically convert that table to be partitioned in this specific manner to improve overall performance, all behind the scenes, without anyone necessarily knowing it’s done so.

Is it there yet? No. Is it coming? You bet.

That said, some newer Oracle Database 19c features that will clearly be critical to a self-tuning autonomous databases moving forward such as Real-Time Statistics, SQL Quarantine and Automatic Indexing will only be available in the Oracle Cloud and Exadata platforms. So take note…

Which brings me to indexing.

When the first Oracle Autonomous Data Warehouse cloud service was announced in March 2018, one of the key “features” of the new autonomous platform was that indexing was disabled (as were other traditional Data Warehouse database capabilities such as Partitioning, Materialized Views, etc.). But how can you effectively “tune” a database when you take away some of the key tuning capabilities? The short answer is that you can’t, unless the database has somewhat simplistic data workloads and data structures.

Remember, this was “Version One” with the first autonomous database environment and the initial strategy was to make the Oracle database not smarter, but dumber. Simplify it such that DBAs can’t easily “break” things and by simply making the Exadata platform do all the heavy lifting with regards to database tuning. A more simplified environment makes things a little easier to “self-tune” as there are fewer moving parts to worry about.

For more simplistic Data Warehouse environments, this might all be adequate. For those of you who follow my blog and my previous discussions on indexing on Exadata, dropping all indexes on Exadata, even on Data Warehouse environments was generally a bad idea. So providing an Oracle database platform, even an autonomous one running on an Exadata platform, where all indexing was effectively disabled, was always going to have performance shortfalls in some scenarios. Try running ad-hoc queries on the supplied SSB LINEORDER table for example. Smart Scans, Storage Indexes, HCC, Result Caches, etc. will only take you so far.

So as I said, Oracle evolves and improves and now allows database indexes to be created in the Autonomous Data Warehouse cloud service.

Which will be the focus on upcoming blog posts, so stay tuned.

Advertisements

Announcement: “Oracle Performance Diagnostics and Tuning” Webinar – 9-12 July 2019 !! March 21, 2019

Posted by Richard Foote in Oracle Indexes, Oracle Performance Diagnostics and Tuning Webinar, Performance Tuning, Performance Tuning Webinar.
add a comment

OMC Training

I’m very excited to announce the first public running of my new “Oracle Performance Diagnostics and TuningWebinar will run between 9-12 July 2019 (6pm-10pm AEST):

Webinar Series 9-12 July 2019 (start 6pm AEST, end 10pm AEST): Buy Now Button

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.

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).

For a detailed look at seminar content, please visit my “Oracle Performance Diagnostics and Tuning” page.

The webinar will run over four days between 9-12 July 2019 between 6pm-10pm AEST.

For the following webinar series, you can purchase your registrations here via credit card (if NOT based in Australia), else contact me if you wish to pay via direct bank transfer or if you’re based in Australia (as GST must be added). Note: Australia, you have actual in person seminars running throughout Winter 2019 you can attend..

Webinar Series 9-12 July 2019 (start 6pm AEST, end 10pm AEST): Buy Now Button

I’ve had heaps of correspondence regarding this so please book early to avoid disappointment as there will strictly be limited places to ensure a quality training experience for all attendees, with plenty of opportunity for questions.

Further webinars will be scheduled for later in the year, so stay tuned.

If you have any questions, don’t hesitate to contact me at richard@richardfooteconsulting.com.

Oracle Database 19c Automatic Indexing: Predicted Back In 2008 (A Better Future) March 19, 2019

Posted by Richard Foote in Automatic Indexing, Autonomous Database, Oracle Indexes.
add a comment

heathen

I’ve recently received a number of correspondences regarding one of my most popular blog posts, dating back to February 2008: Index Rebuild vs. Coalesce vs. Shrink Space (Pigs – 3 Different Ones).

In the comments section, there’s an interesting discussion where I mention:

If Oracle19 does everything for you and all the various indexes structures get automatically created, used, applied, tuned, maintained, partitioned, etc. etc., then I’ll have nothing to write about 🙂.

Actually, when I think about it, it may not take as long as Oracle19 for Oracle to get there.

The fact this was stated some 10 years before the release of Oracle Database 19c with indeed the new Automatic Indexing capability is kinda funny.

I would however like to state a few points.

Firstly, congratulations for the impressive timely predication should go to Robert who made the initial reference to Oracle 19c in the comments.

Although the changing in Oracle database versioning helped in getting the predicted Oracle version correct, I did at least make the comment that I thought some form Automatic Indexing was likely before Oracle 19c.

Although not all the predicted capabilities are there yet (no auto index maintenance, no auto index partitioning, not all index structures are yet supported with auto indexing, etc.), I do predict that these will all be implemented within a few Oracle database releases (early 20s).

I stated there would be nothing to blog about if automatic indexing became a reality, which of course is not true either. The more one understands how Oracle actually works, the more one can proactively identify and address any potential issues and deficiencies. Automatic Indexing is ONLY going to be available on Exadata and the Oracle Cloud platform, so the majority of Oracle On-Premises database users will still need to manually manage indexes. Understanding how Automatic Indexing capability works means one can potentially highlight and learn how to more appropriately manage indexes manually.

So finally, yes I will of course be blogging about Automatic Indexing and about indexing in general in the new Autonomous Database cloud environments.

I’ll start with some initial thoughts and examples of indexing the Autonomous Data Warehouse Database.

Stay tuned.

Announcement: “Oracle Performance Diagnostics and Tuning” Seminar – Australia/NZ Winter Dates March 14, 2019

Posted by Richard Foote in Oracle Indexes, Oracle Performance Diagnostics and Tuning Seminar.
1 comment so far

seminar photo

I’m very excited to announce the first public running of my new “Oracle Performance Diagnostics and Tuning” Seminar throughout Australia and New Zealand this coming winter 2019. (See my Oracle Performance Diagnostics and Tuning Seminar page for all the seminar content and 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 professionals).

As usual, class sizes will be intentionally small to ensure all attendees get the maximum educational benefit from seminar. Early bird rates are available so please book early to avoid disappointment as I don’t get the opportunity to run these events very often.

Dates for the upcoming seminars in Australia/New Zealand:

Sydney: 22-23 July (Training Choice, 60 Clarence St): Registration Link

Canberra: 24-25 July (Training Choice, 54 Marcus Clark St): Registration Link

Brisbane: 29-30 July (Training Choice, 88 Creek St): Registration Link

Melbourne: 31 July – 1 August (Training Choice, 50 Queen St): Registration Link

Perth: 5-6 August (Training Choice, 2/220 St Georges Terrace): Registration Link

Adelaide: 7-8 August (Training Choice, 19 Young St): Registration Link

Wellington: 19-20 August (Auldhouse, Jackson Stone House 11 Hunter St): Registration Link

Auckland: 21-22 August (Karstens, Level 4, 205 Queen Street): Registration Link

 

I’ll soon announce dates for a seminar in my native London, UK and the first series of webinars for this training. Stay tuned !!

If you have any questions at all, please contact me at richard@richardfooteconsulting.com

Unique Indexes Force Hints To Be “Ignored” Part II (One Of The Few) February 19, 2019

Posted by Richard Foote in CBO, Hash Join, Hints, Oracle Indexes, Transitive Closure, Unique Indexes.
2 comments

Final Cut

In Part I, I showed a demo of how the introduction of a Unique Index appears to force a hint to be “ignored”. This is a classic case of what difference a Unique Index can make in the CBO deliberations.

So what’s going on here?

When I run the first, un-hinted query:

SQL> select * from bowie1, bowie2
where bowie1.id=bowie2.code and bowie1.id=1;
we notice something a little odd in the Predicate Information section of the execution plan:
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("BOWIE1"."ID"=1)
6 - access("BOWIE2"."CODE"=1)
Where the hell is the join condition, it’s not listed? Additionally, where does the BOWIE2.CODE=1 condition come from, it’s not a predicate within the above SQL statement?
The answer is “Transitive Closure“, whereby the CBO can automatically infer that BOWIE2.CODE must equal 1, if BOWIE2.CODE=BOWIE1.ID and BOWIE1.ID=1. This is something that the CBO master Jonathan Lewis has blogged about a number of times, including this post on Cartesian Merge Join.
Because the CBO is picking up the fact (based on the column statistics) that BOWIE1.ID is basically a unique column, it can effectively drop the join condition and simply use a  Merge Join Cartesian to get all rows from BOWIE1 that match the BOWIE1.ID=1 predicate (just 1 row), with all those rows from BOWIE2 that match the BOWIE2.CODE=1 predicate (estimated 50 rows).
-------------------------------------------------------------------------------------------------------
| Id | Operation                           | Name          | Rows | Bytes | Cost (%CPU) |   Time     |
-------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |               |   50 |  1150 |       5 (0) |   00:00:01 |
|  1 | MERGE JOIN CARTESIAN                |               |   50 |  1150 |       5 (0) |   00:00:01 |
|  2 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE1        |    1 |    16 |       2 (0) |   00:00:01 |
|* 3 | INDEX RANGE SCAN                    | BOWIE1_ID_I   |    1 |       |       1 (0) |   00:00:01 |
|  4 | BUFFER SORT                         |               |   50 |   350 |       3 (0) |   00:00:01 |
|  5 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE2        |   50 |   350 |       3 (0) |   00:00:01 |
|* 6 | INDEX RANGE SCAN                    | BOWIE2_CODE_I |   50 |       |       1 (0) |   00:00:01 |
-------------------------------------------------------------------------------------------------------
The CBO is picking up the fact that a Merge Join Cartesian is not as bad as it might sound if only 1 row is likely to be returned from one side of this process that can be combined with just the rows of interest from the other table, with no unnecessary row throwaways.
However, the CBO might not get this right, the efficiency of this depends somewhat on there really only being the one row returned from the BOWIE1.ID=1 condition. If there were many more than one row possible, then this can become relatively inefficient.
The second hinted query is therefore directing Oracle to perform a Hash Join, as I potentially know my data better than Oracle and think it a better option in this case:
SQL> select /*+ use_hash(bowie2) */ * from bowie1, bowie2
where bowie1.id=bowie2.code and bowie1.id=1;
The hint is directing Oracle to access the BOWIE2 table to be the probe table in a Hash Join operation.
We notice that the join predicate is now listed in the Predicate Information of the execution plan:
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("BOWIE1"."ID"="BOWIE2"."CODE")
3 - access("BOWIE1"."ID"=1)
5 - access("BOWIE2"."CODE"=1)
The key point being that there could be a many to many join operation that needs to be performed and Oracle can’t perform a Hash Join unless there is a join predicate listed.
As such, the CBO uses a Hash Join as requested by the hint:
------------------------------------------------------------------------------------------------------
| Id | Operation                           | Name          | Rows | Bytes | Cost (%CPU) |  Time     |
------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |               |   50 |  1150 |       5 (0) |  00:00:01 |
|* 1 | HASH JOIN                           |               |   50 |  1150 |       5 (0) |  00:00:01 |
|  2 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE1        |    1 |    16 |       2 (0) |  00:00:01 |
|* 3 | INDEX RANGE SCAN                    | BOWIE1_ID_I   |    1 |       |       1 (0) |  00:00:01 |
|  4 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE2        |   50 |   350 |       3 (0) |  00:00:01 |
|* 5 | INDEX RANGE SCAN                    | BOWIE2_CODE_I |   50 |       |       1 (0) |  00:00:01 |
------------------------------------------------------------------------------------------------------
Note currently, there is no Unique constraint on the BOWIE1.ID column and the index on BOWIE1.ID is non-unique. Although the column statistics suggests the ID is basically unique (the number of distinct values matches the number of rows in the table), there is no certainly that this is correct. The statistics might not be accurate and there could be a bunch of duplicate IDs that would result in a many to many join operation whereby a Hash Join might be preferable.
But by replacing the non-unique index on BOWIE1.ID with a unique index, the CBO now knows there is indeed just the one viable row from the BOWIE1 side of the join, with the BOWIE1.ID=1 predicate. As such, the CBO goes back to using Transitive Closure to again effectively eliminate the join predicate.

 

Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("BOWIE1"."ID"=1)
5 - access("BOWIE2"."CODE"=1)
The CBO can now safely get just the required row from BOWIE1 table via the BOWIE1.ID=1 predicate and the required data from BOWIE2 directly via the BOWIE2.CODE=1 predicate. The CBO makes this decision before considering the most appropriate join strategy, which can now not possibly be the Hash Join, as the Hash Join is only possible with a join predicate in place.
------------------------------------------------------------------------------------------------------
| Id | Operation                           | Name          | Rows | Bytes | Cost (%CPU) | Time     |
------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |               |   50 |  1150 |       5 (0) | 00:00:01 |
|  1 | NESTED LOOPS                        |               |   50 |  1150 |       5 (0) | 00:00:01 |
|  2 | TABLE ACCESS BY INDEX ROWID         | BOWIE1        |    1 |    16 |       2 (0) | 00:00:01 |
|* 3 | INDEX UNIQUE SCAN                   | BOWIE1_ID_I   |    1 |       |       1 (0) | 00:00:01 |
|  4 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE2        |   50 |   350 |       3 (0) | 00:00:01 |
|* 5 | INDEX RANGE SCAN                    | BOWIE2_CODE_I |   50 |       |       1 (0) | 00:00:01 |
------------------------------------------------------------------------------------------------------
As such, the USE_HASH hint is now “ignored”, because it’s simply now not a viable option for the CBO. A Nested Loop is now performed instead, in which the row for the Outer Table (BOWIE1) can be retrieved and all “corresponding” rows for the Inner Table (BOWIE2) can be likewise accessed via just the BOWIE2.CODE=1 predicate.
A Nested Loop is the join type you can have when you’re not necessarily performing a join…

“Oracle Indexing Internals and Best Practices” Seminar – Berlin 8-9 May: DOAG Website February 8, 2019

Posted by Richard Foote in Oracle Indexes.
add a comment

DOAG logo

Just a short note to say that DOAG have now a registration page for my upcoming “Oracle Indexing Internals and Best Practices” seminar running in Berlin, Germany on 8-9 May 2019.

For all the details regarding this acclaimed educational experience and how to book your place, please visit:

https://www.doag.org/de/eventdetails?tx_doagevents_single[id]=577320

Please mention you heard this seminar from me when booking and just note that places are strictly limited for these events, so please book early to avoid disappointment.

This is likely to be the only time I run this seminar in mainland Europe this year, so don’t miss this unique opportunity to learn in person all that’s worth learning in to how to use and maintain indexes judiciously to improve Oracle database/application performance.

If you have any questions, don’t hesitate to contact me at richard@richardfooteconsulting.com.

Unique Indexes Force Hints To Be “Ignored” Part I (What’s Really Happening) February 5, 2019

Posted by Richard Foote in CBO, Hash Join, Hints, Non-Unique Indexes, Oracle Indexes, Unique Indexes.
2 comments

hours album

As I was compiling my new “Oracle Diagnostics and Performance Tuning” seminar, I realised there were quite a number of indexing performance issues I haven’t discussed here previously.

The following is a classic example of what difference a Unique Index can have over a Non-Unique index, while also covering the classic myth that Oracle sometimes chooses to “ignore” hints that might be present in your SQL.

To set the scene, I’m going to create two simple little tables, but importantly initially create only non-unique indexes for columns of interest (Note: I’ve had to remove the “<” in the “<=” predicate when populating the table to avoid formatting issues):

SQL> create table bowie1 as
select rownum id, 'David Bowie' name from dual connect by level = 1000;

Table created.

SQL> create table bowie2 as
select rownum id, mod(rownum,20)+1 code from dual connect by level = 1000;

Table created.
SQL> exec dbms_stats.gather_table_stats(ownname=> null, tabname=>'BOWIE1', method_opt=>'FOR ALL COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(ownname=> null, tabname=>'BOWIE2', method_opt=>'FOR ALL COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

SQL> create index bowie1_id_i on bowie1(id);

Index created.

SQL> create index bowie2_id_i on bowie2(id);

Index created.

SQL> create index bowie2_code_i on bowie2(code);

Index created.

I’m now going to run the following query which does a simple join between the two tables and filters on the ID column from the BOWIE1 table:

 

SQL> select * from bowie1, bowie2
where bowie1.id=bowie2.code and bowie1.id=1;

50 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 4266778954

-------------------------------------------------------------------------------------------------------
| Id | Operation                           | Name          | Rows | Bytes | Cost (%CPU) |   Time     |
-------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |               |   50 |  1150 |       5 (0) |   00:00:01 |
|  1 | MERGE JOIN CARTESIAN                |               |   50 |  1150 |       5 (0) |   00:00:01 |
|  2 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE1        |    1 |    16 |       2 (0) |   00:00:01 |
|* 3 | INDEX RANGE SCAN                    | BOWIE1_ID_I   |    1 |       |       1 (0) |   00:00:01 |
|  4 | BUFFER SORT                         |               |   50 |   350 |       3 (0) |   00:00:01 |
|  5 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE2        |   50 |   350 |       3 (0) |   00:00:01 |
|* 6 | INDEX RANGE SCAN                    | BOWIE2_CODE_I |   50 |       |       1 (0) |   00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("BOWIE1"."ID"=1)
6 - access("BOWIE2"."CODE"=1)

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

 

The query uses a MERGE JOIN which I (incorrectly) think is a concern and decide that a HASH JOIN should be a better option. So I now put in a basic USE_HASH hint:

SQL> select /*+ use_hash(bowie2) */ * from bowie1, bowie2
where bowie1.id=bowie2.code and bowie1.id=1;

50 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1413846643

------------------------------------------------------------------------------------------------------
| Id | Operation                           | Name          | Rows | Bytes | Cost (%CPU) |  Time     |
------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |               |   50 |  1150 |       5 (0) |  00:00:01 |
|* 1 | HASH JOIN                           |               |   50 |  1150 |       5 (0) |  00:00:01 |
|  2 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE1        |    1 |    16 |       2 (0) |  00:00:01 |
|* 3 | INDEX RANGE SCAN                    | BOWIE1_ID_I   |    1 |       |       1 (0) |  00:00:01 |
|  4 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE2        |   50 |   350 |       3 (0) |  00:00:01 |
|* 5 | INDEX RANGE SCAN                    | BOWIE2_CODE_I |   50 |       |       1 (0) |  00:00:01 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("BOWIE1"."ID"="BOWIE2"."CODE")
3 - access("BOWIE1"."ID"=1)
5 - access("BOWIE2"."CODE"=1)

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

And the hint has worked as I had hoped.

I then decide that perhaps a Unique Index on the ID column might be a good idea (perhaps because I read up on all the advantages on Unique Indexes in this blog). So I drop and recreate the index as a Unique Index:

SQL> drop index bowie1_id_i;

Index dropped.

SQL> create unique index bowie1_id_i on bowie1(id);

Index created.

I now re-run my hinted query to again use the Hash Join:

SQL> select /*+ use_hash(bowie2) */ * from bowie1, bowie2
where bowie1.id=bowie2.code and bowie1.id=1;

50 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 4272245076

------------------------------------------------------------------------------------------------------
| Id | Operation                           | Name          | Rows | Bytes | Cost (%CPU) | Time     |
------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |               |   50 |  1150 |       5 (0) | 00:00:01 |
|  1 | NESTED LOOPS                        |               |   50 |  1150 |       5 (0) | 00:00:01 |
|  2 | TABLE ACCESS BY INDEX ROWID         | BOWIE1        |    1 |    16 |       2 (0) | 00:00:01 |
|* 3 | INDEX UNIQUE SCAN                   | BOWIE1_ID_I   |    1 |       |       1 (0) | 00:00:01 |
|  4 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE2        |   50 |   350 |       3 (0) | 00:00:01 |
|* 5 | INDEX RANGE SCAN                    | BOWIE2_CODE_I |   50 |       |       1 (0) | 00:00:01 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("BOWIE1"."ID"=1)
5 - access("BOWIE2"."CODE"=1)

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

And we notice the hint is now being “ignored”. The hint isn’t really ignored, it’s just no longer relevant to how the CBO has now constructed the query and associated plan with the Unique Index now making a key difference (no pun intended).

I’ll discuss in Part II why the Unique Index has made such a difference and why the hint is no longer viable.

Of course, to learn all this and a lot lot more, you can always attend my new Oracle Diagnostics and Performance Tuning” seminar one day 🙂

Announcement: “Oracle Indexing Internals and Best Practices Seminar” – Berlin 8-9 May 2019 !! January 30, 2019

Posted by Richard Foote in Oracle Indexes.
add a comment

I’m very excited to announce I’ll be running my highly acclaimed “Oracle Indexing Internals and Best Practices” seminar in conjunction with the German Oracle User Group (DOAG) in Berlin, Germany on 8-9 May 2019.

The venue will be the DOAG Offices in Berlin (DOAG Office, 6th Floor, Tempelhofer Weg 64, 12347 Berlin).

Because of venue size and for a quality educational experience, numbers will be strictly limited. This will be one of the very few times I’ll have to run this seminar in Europe this year, so please book early to avoid disappointment.

For all the details on how to maximise Database/Application performance with judicious use of indexes and why this seminar has been such a popular and well received educational experience, please visit my Indexing Seminar Page.

The cost of the seminar is as follows:

DOAG Members: 980 Euro Buy Now Button

Other Participants: 1400 Euro Buy Now Button

DOAG membership will need to be validated, else fee will be refunded and place lost.

Note: If you can make it to this seminar in Berlin, you there are still some places available for both webinar series running on 4-8 March 2019 and on 26-30 March 2019. Full details on my Indexing Internals Webinar Page.

Hope to see some of you in Berlin on 8-9 May 2019 for what I’m sure will be a fantastic event !!

If you have any questions, please contact me: richard@richardfooteconsulting.com.

New “Let’s Talk Database” events in Australia/NZ in February/March 2019 !! January 8, 2019

Posted by Richard Foote in Oracle Indexes.
add a comment

seminar photo

Very excited to announce some new “Let’s Talk Database” events scheduled for February 2019 in various locations in Australia/NZ:

Canberra: Wednesday, 20 February 2019: Registration Link

Sydney: Thursday, 21 Feburary 2019: Registration Link

Brisbane: Friday, 22 Feburary 2019: Registration Link

Melbourne: Wednesday, 27 February 2019: Registration Link

Wellington: Friday, 1 March 2019: Registration Link

 

I’ll be covering two interesting topics at these events:

Oracle Database Appliance: During my days at Oracle Corporation, I had quite a bit to do with helping customers on the ODA and always felt it was an great solution that often got drowned out by Exadata and other Oracle engineered solutions. In the session, I’ll be discussing the various unique advantages on running Oracle Databases on the Oracle Database Appliance (ODA), some of the new features available on the ODA (including now Hybrid Columnar Compression), some use cases for deploying Oracle Databases on the ODA and importantly, some of the more common mistakes and misunderstanding regarding how to best maintain an ODA environment.

Indexing and Partitioning: As databases get ever larger in size and complexity, the Oracle Partitioning option has become more common and a more important feature in Oracle database deployments. This presentation will look at some of the important indexing strategies that can be applied in a Partitioned Database environment in order to effectively maximise both database performance and database management practices. It will also discuss some newer features introduced in 12c R2 and 18c that simplifies the migration to an effective partitioning strategy.

The agenda on the day will be as follows:

8:30 a.m.  Registration & Coffee

9:00 a.m.  Oracle Database Appliance

10:15 a.m. Networking break

10:30 a.m. Indexing with Partitioning

12:00 p.m. Networking lunch

1:00 p.m. Event Close

 

So lots of database stuff to talk about and the best part is that’s all free !!

Simply follow the registration link to book your place. But please book early to avoid disappointment as places are strictly limited (all venues are the local Oracle offices).

Hope to see you at one of these events !!

UKOUG “Lifetime Achievement Award” Speaker Award December 19, 2018

Posted by Richard Foote in Lifetime Achievement Award, Oracle Indexes, UKOUG.
add a comment

IMG_2494

I was recently very honoured and flattered to have received the “Lifetime Achievement Award” Speaker Award from the UKOUG. I have only managed to get to the excellent UKOUG Tech conferences on a couple of occasions, so it was both a thrill and a surprise to be so honoured.

Unfortunately, I wasn’t able to make it for my third visit this year and so was unable to be there to accept this award.

However in a sign of true dedication from the UKOUG President, Martin Widlake has come all the way over to beautiful Canberra, Australia just to present the award personally to me (OK, he might have plans do a couple of other things during his visit to Australia).

With plans to run the UKOUG Tech conference next year in Brighton, I’ll have to try and make it for my third visit, if only to annoy Mark Rittman when Crystal Palace no doubt beat Brighton when we next meet…

AUSOUG “Oracle Master” Award November 22, 2018

Posted by Richard Foote in AUSOUG, Oracle Indexes, Oracle Master.
2 comments

IMG_7456

 

I was very flattered and honoured to be named one of the first 4 “Oracle Masters” by the Australian Oracle User Group at the recent AUSOUG Connect 2018 conference in Melbourne.

Thank you to all the AUSOUG members involved for this award. As always, it’s a pleasure to help fellow Oracle Database professionals in Australia in whatever capacity I can, especially at cool events such as Connect 2018.

Congratulations to the other members of the “Famous First Four”, Connor McDonald, Penny Cookson and Dennis Remmer. Exalted company indeed.

 

Presenting at AUSOUG Connect 2018 Conference in Melbourne, 21 November 2018. November 18, 2018

Posted by Richard Foote in Connect 2018, Oracle Indexes.
add a comment

ausoug

 

After initially not being in a position to make it this year, I will now be presenting at the AUSOUG Connect 2018 Conference in Melbourne this coming Wednesday, 21 November 2018.

My presentation will be:

12c Release 2 and 18c – New Indexing Related Features

Oracle Database 12.2 and 18.3 releases have introduced a number of extremely useful new indexing features and enhancements. These include cool capabilities such as automatically tracking index usage, advanced index compression enhancements, deferred invalidation of cursors during index creation/rebuild, automatic index maintenance during new online operations (such as online table moves and conversion to partitioned objects), JSON indexing enhancements, Memoptimized Rowstore and Scalable Sequences. There might even be an Oracle Database 19c surprise thrown in.

These will all be discussed in detail with practical examples on how they can be usefully deployed to improve overall database performance.

 

For all the conference details, see: https://www.ausoug.org.au/whats-on/connect-2018/melbourne/

This conference has a fabulous lineup and I’m really excited at being able to now take part in it all. A special thank you to the AUSOUG team for catering for me at such late notice.

Looking forward to catching up with many of my Aussie Oracle mates there 🙂

 

ausoug

Enable Index To Search For NULLs By Adding Constant to Index List. But Some Constants Better Than Others (Never Let Me Down) November 15, 2018

Posted by Richard Foote in Block Dumps, Index Internals, Indexing NULLs, Leaf Blocks, Oracle Indexes.
9 comments

Never Let Me Down

By default, Oracle doesn’t index an entry if all columns within the index are NULL. However, (as I’ve blogged previously), it’s possible to index all possible NULL values by simply adding a constant value to the index column list. Importantly, the CBO knows when a column has all it’s NULL values indexed and can potentially use the index accordingly.

However, the point of the article is to simple highlight that some constant values are better to use in this scenario than others…

A simple example to illustrate. First, create a table with both the CODE and GRADE columns nullable:

SQL> create table bowie (id number, code number, grade number, name varchar2(42));

Table created.

SQL> insert into bowie select rownum, mod(rownum,100), mod(rownum,1000), 'DAVID
BOWIE' from dual connect by level 1000000;

999999 rows created.

SQL> insert into bowie values (1000000, null, null, 'ZIGGY STARDUST');

1 row created.

SQL> commit;

Commit complete.

If we create an index on both CODE and GRADE columns:

SQL> create index bowie_code_grade_i on bowie(code, grade);

Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=> null, tabname=> 'BOWIE');

PL/SQL procedure successfully completed.

And then run a query looking for any CODE with a NULL value:

SQL> select * from bowie where code is null;

        ID       CODE      GRADE NAME
---------- ---------- ---------- ------------------------------------------
   1000000                       ZIGGY STARDUST

Execution Plan
----------------------------------------------------------
Plan hash value: 1845943507

---------------------------------------------------------------------------
| Id | Operation         | Name  | Rows | Bytes | Cost (%CPU) | Time     |
---------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |       |    1 |    24 |    1115 (3) | 00:00:01 |
|* 1 | TABLE ACCESS FULL | BOWIE |    1 |    24 |    1115 (3) | 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("CODE" IS NULL)

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

We notice the CBO performs a Full Table Scan even though the CBO knows there’s likely only one row that meets the criteria. Not matter what we do, hint the query, beg, whatever, it’s impossible for the CBO to use the index because the null row is simply not indexed.

Now we come to the rub of the post.

A common recommendation is to simply add a constant to the column list. A constant is always present and associated NULL values are indexed if another index column has a corresponding Non-NULL value. Adding a constant value to the index column list guarantees all NULL values for all index columns must always be present within the index. The CBO recognises this and can therefore potentially use the index to fetch the required NULL values.

However, a common recommendation is also to use a number as the constant. There was a recent tweet I saw a few days ago that had the following example of using the number 1 as the constant value:

SQL> create index bowie_code_grade_i_2 on bowie (code, grade, 1);

Index created.

When we run the query again:

SQL> select * from bowie where code is null;

        ID       CODE      GRADE NAME
---------- ---------- ---------- ------------------------------------------
   1000000                       ZIGGY STARDUST

Execution Plan
----------------------------------------------------------
Plan hash value: 3086372235

------------------------------------------------------------------------------------------------------------
| Id | Operation                           | Name                 | Rows | Bytes | Cost (%CPU) | Time     |
------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |                      |    1 |    24 |       4 (0) | 00:00:01 |
|  1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE                |    1 |    24 |       4 (0) | 00:00:01 |
|* 2 | INDEX RANGE SCAN                    | BOWIE_CODE_GRADE_I_2 |    1 |       |       3 (0) | 00:00:01 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("CODE" IS NULL)

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

The index is indeed used to return the row with the NULL value of interest and only 4 consistent gets are performed.

So what’s the problem?

Nothing, except that perhaps a better constant might have been used, such as say a single space:

SQL> create index bowie_code_grade_i_3 on bowie(code, grade, ' ');

Index created.

If we run the query yet again:

SQL> select * from bowie where code is null;

        ID       CODE      GRADE NAME
---------- ---------- ---------- ------------------------------------------
   1000000                       ZIGGY STARDUST

Execution Plan
----------------------------------------------------------
Plan hash value: 3086372235

------------------------------------------------------------------------------------------------------------
| Id | Operation                           | Name                 | Rows | Bytes | Cost (%CPU) | Time     |
------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |                      |    1 |    24 |       4 (0) | 00:00:01 |
|  1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE                |    1 |    24 |       4 (0) | 00:00:01 |
|* 2 | INDEX RANGE SCAN                    | BOWIE_CODE_GRADE_I_2 |    1 |       |       3 (0) | 00:00:01 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("CODE" IS NULL)

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

We get the exact same performance. So, what’s the point?

Well, if we look at the size of the corresponding indexes:

SQL> select index_name, leaf_blocks from user_indexes where table_name='BOWIE';

INDEX_NAME                LEAF_BLOCKS
------------------------- -----------
BOWIE_CODE_GRADE_I               2490
BOWIE_CODE_GRADE_I_2             2908
BOWIE_CODE_GRADE_I_3             2769

We notice the original index has the smallest size as expected, as it doesn’t have to index the constant value. But then we notice that the index with the constant value as the number is somewhat larger than the index with the constant value as a space.

Why?

An index block dump of both indexes will highlight why:

First a partial leaf block dump of index with the “1” as a constant:

Leaf block dump
===============
header address 925073508=0x37238064
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 4
kdxcosdc 0
kdxconro 399
kdxcofbo 834=0x342
kdxcofeo 1652=0x674
kdxcoavs 818
kdxlespl 0
kdxlende 0
kdxlenxt 29444101=0x1c14805
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[8020] flag: ——-, lock: 0, len=16
col 0; len 1; (1): 80
col 1; len 1; (1): 80
col 2; len 2; (2): c1 02
col 3; len 6; (6): 01 c0 80 05 00 d7
row#1[8004] flag: ——-, lock: 0, len=16
col 0; len 1; (1): 80
col 1; len 1; (1): 80
col 2; len 2; (2): c1 02
col 3; len 6; (6): 01 c0 80 09 00 87
row#2[7988] flag: ——-, lock: 0, len=16
col 0; len 1; (1): 80
col 1; len 1; (1): 80
col 2; len 2; (2): c1 02
col 3; len 6; (6): 01 c0 80 0d 00 af

Next, a partial leaf block dump of index with the space ” ” as a constant:

Leaf block dump
===============
header address 925073508=0x37238064
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 4
kdxcosdc 0
kdxconro 422
kdxcofbo 880=0x370
kdxcofeo 1706=0x6aa
kdxcoavs 826
kdxlespl 0
kdxlende 0
kdxlenxt 29447173=0x1c15405
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[8021] flag: ——-, lock: 0, len=15
col 0; len 1; (1): 80
col 1; len 1; (1): 80
col 2; len 1; (1): 20
col 3; len 6; (6): 01 c0 80 05 00 d7
row#1[8006] flag: ——-, lock: 0, len=15
col 0; len 1; (1): 80
col 1; len 1; (1): 80
col 2; len 1; (1): 20
col 3; len 6; (6): 01 c0 80 09 00 87
row#2[7991] flag: ——-, lock: 0, len=15
col 0; len 1; (1): 80
col 1; len 1; (1): 80
col 2; len 1; (1): 20
col 3; len 6; (6): 01 c0 80 0d 00 af

We notice that the number requires 2 bytes, while the space only requires 1 byte.

So storing the constant as a single byte character, rather a 2 byte number is a free saving, which adds up with each and every index entry, by simply being a little more fastidious when selecting which constant value to use in this scenario.

March 2019 – New Webinar Dates Announced for “Oracle Indexing Internals and Best Practices” November 14, 2018

Posted by Richard Foote in Oracle Index Seminar, Oracle Indexes, Oracle Indexing Internals Webinar, Webinar.
add a comment

OMC Training

I’m very excited to announce two new Webinar events for my acclaimed “Oracle Indexing Internals and Best Practices” training event, running in March 2019 !!

For details of all the extensive content covered in the webinars, please visit my Indexing Seminar page.

The webinars will run for 4 hours each day, spanning a full week period (Monday to Friday) in various timezones that are friendly to different parts of the world.

So that’s 15+ hours of extensive and practical content that will be 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.

There are currently 2 webinar series scheduled. They are:

  • Webinar Series 1: 4 – 8 March 2019 (start 7pm AEDT, end 11pm AEDT)
  • Webinar Series 2: 26 – 30 March 2019 (start 5am AEDT, end 9am AEDT)

Webinar Series 1 will be in timezones more agreeable to Eastern Asia/Europe. For example, they will start at 1:30pm local time in Mumbai, at 9:00am local time in Paris.

Webinar Series 2 will be in timezones more agreeable to the American Continents. For example they will start at in 1:00pm local time New York and 10:00am local time in San Francisco. (Note: The dates listed are as in Australia, they will actually run between Monday 25 November to Friday 29 March in the Americas).

The cost of each 5 x day series will be $1200.00 Australian Dollars (+GST if applicable and attending from within Australia).

Note: Numbers are strictly limited to ensure the smooth running of these events and enable the opportunity for all attendees to ask questions. One of my previous webinars was officially FULL, so please register early to avoid disappointment as webinars are not scheduled too regularly. 

Booking and Payment Instructions

To book your place, please email me at richard@richardfooteconsulting.comand I will send you an invoice with payment instructions. You can pay either by credit card via PayPal (you do not need a PayPal account for this), via a PayPal account or via direct bank transfer. Note: payment must be received before you can attend the webinar.

You can also pay for these webinars directly here if NOT attending from Australia:

Webinar Series 1: 4-8 March 2019 (start 7pm AEDT, end 11pm AEDT): Buy Now Button

Webinar Series 2: 26-30 March 2019 (start 5am AEDT, end 9am AEDT): Buy Now Button

 

Once registered, you will be sent a unique link for each booking with instructions on how to attend the webinar. Prior to the webinar, you will also be sent a soft copy of the webinar materials, with 850+ pages of amazing content, that includes many useful tips and strategies to maximise the benefits of indexes on application/database performance and scalability.

Up to date details and terms and conditions can be found at my Indexing Webinar web page.

If you have any questions, please don’t hesitate to contact me.

FIRST_ROWS_10 CBO Is Hopeless, It’s Using The Wrong Index !! (Weeping Wall) November 5, 2018

Posted by Richard Foote in ALL_ROWS, CBO, Exadata, FIRST_ROWS_10, Oracle Indexes, Siebel.
6 comments

low

There’s an organisation I had been dealing with on and off over the years who were having all sorts of issues with their Siebel System and who were totally convinced their performance issues were due directly to being forced to use the FIRST_ROWS_10 optimizer. I’ve attempted on a number of occasions to explain that their issues are not actually due to some unexplained deficiency with the FIRST_ROWS_10 CBO, but due to a number of other root issues, sadly to no avail. I recently found out they’re still struggling with performance issues, so I thought it might be worth looking at a classic example of where it looks simplistically like a FIRST_ROWS_10 CBO issue, but the “real” underlying problem(s) are actually quite different. Just in case other sites are likewise struggling to identify such SQL performance issues when using FIRST_ROWS_10…

This is a somewhat simplified version of their most common issue. Firstly, I create a table with 3M rows that has two columns of interest. The CODE column is initially populated with two evenly distributed distinct values and the GRADE column which only has the one distinct value.

SQL> create table bowie (id number not null, code number not null, grade number not null, name varchar2(42));

Table created.

SQL> insert into bowie select rownum, mod(rownum,2), 42, 'David Bowie'
from dual connect by level > = 3000000;

3000000 rows created.

SQL> commit;

Commit complete.

I then update a few rows (just 5) so that the CODE column now has a few occurrences of a third distinct value and update 5 other rows so the GRADE column has a few occurrences of a second distinct value:

SQL> update bowie set code=2
where id in (42, 4343, 400042, 1420001, 2000042);

5 rows updated.

SQL> commit;

Commit complete.

SQL> update bowie set grade=2
where id in (4212, 434323, 440423, 1440002, 2400642);

5 rows updated.

SQL> commit;

Commit complete.

We now introduce “a root problem”, not collecting histograms on these two columns, such that the CBO doesn’t recognise that the values in these columns are not evenly distributed. The CBO will incorrectly assume the rare CODE values actually occur 1M times as it will assume even distribution across the three distinct values. Now this is NOT the specific root issue at this organisation as they do gather histograms, but they do have numerous issues with the CBO not picking the correct cardinality/selectivity of their SQL.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE', estimate_percent=>100, method_opt=>'FOR ALL COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

We next create indexes on these two CODE and GRADE columns:

SQL> create index bowie_code_i on bowie(code);

Index created.

SQL> create index bowie_grade_i on bowie(grade);

Index created.

Let’s now run the following query using the session default FIRST_ROWS_10 optimizer. The query basically returns just the 5 rows that have a CODE = 2, but sorts the result set by the GRADE column:

SQL> alter session set optimizer_mode=first_rows_10;

Session altered.

SQL> select * from bowie where code=2 order by grade;

Execution Plan
----------------------------------------------------------
Plan hash value: 3133133456

---------------------------------------------------------------------------------------------
| Id | Operation                   | Name          | Rows  | Bytes | Cost (%CPU) | Time     |
---------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |               |    10 |   240 |       4 (0) | 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID | BOWIE         | 1000K |   22M |       4 (0) | 00:00:01 |
|  2 | INDEX FULL SCAN             | BOWIE_GRADE_I |    31 |       |       3 (0) | 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("CODE"=2)

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

The FIRST_ROWS_10 optimizer has come up with a terrible execution plan. Instead of using the index on the CODE column to quickly access the 5 rows of interest and then sort them, it uses an INDEX FULL SCAN via the GRADE column index.

This results in a massively inefficient execution plan (note 17,518 consistent gets), as the CBO has to basically read the entire table via this GRADE index to eventually find the 5 rows of interest that have a CODE=2.

The FIRST_ROWS_10 certainly appears to be dreadful…

But before you go off and demand that Oracle not use this CBO, the key question to ask here is WHY? Why is the FIRST_ROWS_10 CBO deciding to use what is clearly the wrong index?

If we can understand why this is happening, perhaps we can then address what is clearly a problem with an appropriate solution that might not just fix this query but many many like this. And perhaps we can address this problem with an optimal solution and not with a band-aid fix or with a sub-optimal solution that is beneficial for just this one query.

Now there are actually two clues within this execution plan regarding what is really going on.

The first is that the execution plan is estimating that 1000K rows are to be processed by the table access after the filter on CODE=2 has been applied. But this is not correct, there are only 5 such rows.

The second clue that not all is right is that the CBO is estimating 10 rows are to be retrieved via this FIRST_ROWS_10 access plan (as Oracle is trying here to come up with the best plan to retrieve the first 10 rows as efficiently as possible), however there are only 5 rows that meet this SQL criteria. The CBO is not picking up that less than the 10 mandatory rows will actually be fetched and only need to be considered

I always recommend a couple of things to look at if one ever comes across the scenario where the FIRST_ROWS(N) optimizer doesn’t appear to be behaving itself. The first is to look at a 10053 trace and see what the CBO costings are for the various alternative plans. The second is to simply run the query with the ALL_ROWS CBO to see what it’s initial deliberations might be, noting that the CBO has to perform an initial pass with ALL_ROWS to see the data density of the various steps to accurately come up with the optimal FIRST_ROWS(N) costings. Without knowing the potential full result set, The FIRST_ROWS_10 optimizer wouldn’t be able to determine for example how much of a Full Index Scan actually needs to be processed before it likely finds the necessary rows of interest.

So let’s see what costings and plan we get with the ALL_ROWS CBO:

SQL> alter session set optimizer_mode=all_rows;

Session altered.

SQL> select * from bowie where code=2 order by grade;

Execution Plan
----------------------------------------------------------
Plan hash value: 2027917145

------------------------------------------------------------------------------------
| Id | Operation         | Name  | Rows  | Bytes |TempSpc | Cost (%CPU) | Time     |
------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |       | 1000K |   22M |        |   11173 (8) | 00:00:01 |
|  1 | SORT ORDER BY     |       | 1000K |   22M |    34M |   11173 (8) | 00:00:01 |
|* 2 | TABLE ACCESS FULL | BOWIE | 1000K |   22M |        |   3387 (11) | 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("CODE"=2)

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

The root issue now becomes somewhat obvious…

ALL_ROWS is not correctly estimating 5 rows are to be returned, but 1000K rows !! Oracle is not estimating that using the index on the CODE column will only fetch 5 rows, but using such an index would retrieve 1000K rows. Using such a CODE index to access 1M rows would therefore be viewed as being much too expensive.

Importantly, the sort step would therefore not sort 5 rows, but would be required to sort 1000K rows, which would be extremely expensive.

Oracle thinks all this when deciding the best way to access the first 10 rows of interest as efficiently as possible with the FIRST_ROWS_10 CBO.

Rather than using the CODE index to first retrieve all 1000K rows, to then sort all 1000K rows before finally being able to return the first 10 rows of interest, Oracle instead does the following.

It uses the index of the GRADE column to retrieve the first 10 rows of interest. As 1 in 3 of all rows are estimated to be of interest (1M out of the 3M rows, because we’re interested in 1 of the 3 distinct CODE values), it estimates it doesn’t actually have to perform much of the FULL INDEX SCAN to find these initial 10 rows of interest.

As the GRADE index was accessed, it also means these first 10 rows would have been fetched in GRADE order. Therefore, there is no need to perform the SORT BY step as the index guarantees the data to be fetched in GRADE order. Not having to perform this sort makes this plan fantastically cheap compared to any other option that first requires all 1000K  of data to be fetched and sorted.

The execution plan when using ALL_ROWS is therefore deciding to perform a Full Table Scan (FTS) to access efficiently what the CBO thinks will be the 1000K rows of interest. This would be much more efficient than accessing all 1000K of interest via either the CODE index (followed by the sort) or via the GRADE index (in which the sort is not required) but requires all the table to be accessed by the index.

Now for this organisation, this FTS is not an entirely bad thing. Why? Because they run Siebel on an Exadata platform !!

Exadata takes this FTS and performs a Smart Scan. And the associated Storage Index can automatically determine this data is extremely rare and potentially only access the relatively few storage regions within the table where these few values of interest reside.

The query goes from taking 60 seconds to run using the “awful” FIRST_ROWS_10 CBO to just 2 seconds with the “brilliant” ALL_ROWS CBO.

However, the “root issue” here is not the FIRST_ROWS_10 CBO but the fact it is being fed insufficient statistics to make an accurate estimate of the true cost. As with all CBOs, rubbish stats in, rubbish plan out…

If we fix the actual root issue and provide the CBO with the necessary statistics to make the correct cardinality/selectivity estimates (in this example by collecting histograms on the skewed data columns):

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE', estimate_percent=>100, method_opt=>'FOR ALL COLUMNS SIZE 75');

PL/SQL procedure successfully completed.

And now re-run the query again with ALL_ROWS:

SQL> alter session set optimizer_mode=all_rows;

Session altered.

SQL> select * from bowie where code=2 order by grade;

Execution Plan
----------------------------------------------------------
Plan hash value: 2357877461

-----------------------------------------------------------------------------------------------------
| Id | Operation                           | Name         | Rows | Bytes | Cost (%CPU) | Time     |
-----------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |              |    5 |   120 |      5 (20) | 00:00:01 |
|  1 | SORT ORDER BY                       |              |    5 |   120 |      5 (20) | 00:00:01 |
|  2 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE        |    5 |   120 |       4 (0) | 00:00:01 |
|* 3 | INDEX RANGE SCAN                    | BOWIE_CODE_I |    5 |       |       3 (0) | 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("CODE"=2)

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

We notice that the ALL_ROWS CBO is now correctly determining the correct query cardinality (5 rows) and is now using the CODE index to retrieve the correctly estimated 5 rows. It’s happy to now perform the sort as the sort of 5 rows has a trivial cost (the cost just goes up by 1).

If we now run the query using the default session FIRST_ROWS_10 CBO:

SQL> alter session set optimizer_mode=first_rows_10;

Session altered.

SQL> select * from bowie where code=2 order by grade;

Execution Plan
----------------------------------------------------------
Plan hash value: 2357877461

-----------------------------------------------------------------------------------------------------
| Id | Operation                           | Name         | Rows | Bytes | Cost (%CPU) | Time     |
-----------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |              |    5 |   120 |      5 (20) | 00:00:01 |
|  1 | SORT ORDER BY                       |              |    5 |   120 |      5 (20) | 00:00:01 |
|  2 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE        |    5 |   120 |       4 (0) | 00:00:01 |
|* 3 | INDEX RANGE SCAN                    | BOWIE_CODE_I |    5 |       |       3 (0) | 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("CODE"=2)

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

We note it’s also using the same execution plan as ALL_ROWS, as the FIRST_ROWS_10 CBO likewise is correctly determining that using the CODE index is now a very efficient manner in which to access just the 5 rows of interest.

Here’s the thing. If you are returning 10 or less rows, the optimal execution plan for both FIRST_ROWS_10 and ALL_ROWS should ultimately be the same, as they both should cost the associated plans the same way.

By correctly identifying and addressing the root issue here (poor cardinality/selectivity estimates), we get the following considerable benefits:

  • We now have an execution plan that doesn’t take 2 seconds to run, but 0.02 of a second (we are now down to just 8 consistent gets). This is much more efficient than the Exadata FTS and allows for the optimal plan to be selected, not just a better plan.
  • We automatically fix ALL execution plans for all queries that are based on this combination of table and filtering columns
  • We correctly understand and identify issues with any other table that likewise has the same costing issue
  • We don’t unnecessarily have to add ALL_ROWS hints or use ALL_ROWS based baselines to address all such related issues
  • We don’t implement a fix (such as baselines) that becomes ineffective if we were to even change the underlying SQL with any subsequent release
  • We don’t attempt to fix the relatively few problem queries with a global change (such as changing to ALL_ROWS CBO) that can potentially impact negatively as many queries as get addressed
  • We don’t spend years demanding futilely that Oracle Support allow Siebel with ALL_ROWS based session settings

So if you’re running Siebel and having performance issues, don’t just assume it’s some deficiency with the FIRST_ROWS_10 CBO, spend the time to get to the bottom of any root issues (e.g. CBO bugs with getting histograms costs incorrect for CHAR columns, missing statistics on small tables, poor default settings when returning empty result sets, Siebel bugs with Cartesian Joins, missing extended statistics, missing indexes, etc. etc.)…

In a future post, I’ll explain why playing around with the unsupported _sort_elimination_cost_ratio parameter (again, always a bad idea when trying to address specific SQL tuning issues) is ultimately futile when trying to get FIRST_ROWS_10 to not use the clearly inefficient index that eliminates the sort…