Richard Foote

Subscribe to Richard Foote feed Richard Foote
Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music
Updated: 3 hours 40 min ago

Announcement: AUSOUG 1 Day Oracle Database AWR/Statspack Workshops with Richard Foote

Wed, 2021-05-19 23:42
It’s with much excitement that I announce a couple of in-person workshops I’ll be running in the coming months in conjunction with the Australian Oracle User Group (AUSOUG). The dates and locations are: Melbourne: Thursday 15th July 2021 Perth: Thursday, 12th August 2021 These will be 1 day workshops that discuss in detail when and […]
Categories: DBA Blogs

Presenting At The “Oracle Horizon Breakfast Series” Throughout May in Canberra

Mon, 2021-04-12 20:38
  If you’re lucky enough to be based close to sunny Canberra, Australia, please join me in cooperation with Oracle Corporation for the “Oracle Horizons Breakfast Series” I’m running throughout May 2021 at the Canberra Hyatt Hotel. Enjoy breakfast while I discuss some exciting Oracle Database related topics: 5 May  : Innovate with Oracle Database […]
Categories: DBA Blogs

METHOD_OPT Default In Oracle Autonomous Databases (She’ll Drive The Big Car)

Tue, 2021-03-02 00:18
  In a recent post on Invisible Automatic Indexes, I was puzzled by a couple of “oddities” in relation to some behaviour in the Oracle Autonomous Database Cloud environments. The first one was how Oracle appeared to be creating Histograms on a much more regular basis than it had previously. As one can see in […]
Categories: DBA Blogs

Oracle 19c Automatic Indexing: Function-Based Indexes? Part II (If You Can See Me)

Thu, 2021-02-04 17:52
In my previous post, I discussed how Automatic Indexing does not currently support creating an index based on a function or expression predicate, even if it’s an equality predicate. You must manually create the associated function-based index. However, if you have access to the application, there’s a better strategy when frequently searching on a function-based […]
Categories: DBA Blogs

Oracle 19c Automatic Indexing: Function-Based Indexes? (No Plan)

Thu, 2021-02-04 00:25
I previously discussed how Automatic Indexing only currently supports Equality based predicates. The question I have today is does Automatic Indexing support function-based indexes? Let’s take a look. The below DAVID table has the key column NAME which is an effectively unique VARCHAR2 column: If we look at the current details of the table columns: […]
Categories: DBA Blogs

Oracle Database 19c Automatic Indexing: Invisible Indexes Oddity (Wild Eyed Boy From Freecloud)

Tue, 2021-02-02 21:11
There have been a couple of “oddities” in relation to both Oracle Autonomous Databases and Automatic Indexing behaviour that I’ve seen frequently enough now (on Oracle 19.5.0.0.0) to make it worth a quick blog article. The following is a simple test case that highlights both these issues. I’ll begin with a basic table, that has […]
Categories: DBA Blogs

Oracle Database 19c Automatic Indexing: Index Compression Update (New Morning)

Tue, 2021-01-26 23:41
  I was reminded in a recent comment by Rajeshwaran Jeyabal that I hadn’t updated my post on Automatic Indexing with Advanced Compression that’s in need of a couple of amendments. Initially when Automatic Indexing was released, the ability to set Advanced Compression was NOT included in the official documentation, although the EXEC DBMS_AUTO_INDEX.CONFIGURE( ‘AUTO_INDEX_COMPRESSION‘ […]
Categories: DBA Blogs

Oracle 19c Automatic Indexing: Non-Equality Predicates Part II (Let’s Spend The Night Together)

Wed, 2021-01-20 21:42
In my previous post in this series, I discussed out Automatic Indexing currently does not consider Non-Equality predicates. Automatic Indexing will index columns based only on Equality predicates. So how does Oracle handle the scenario when an SQL has a mixture of both Equality and Non-Equality predicates? I’ll begin by creating two very similar tables, […]
Categories: DBA Blogs

Announcement: Both Of My Oracle Webinars Scheduled For February 2021 !!

Mon, 2021-01-18 19:56
I’m please to announce that both of my highly acclaimed webinars scheduled for February are now confirmed. These webinars are a must for any Oracle DBA or Developer interested in designing, developing or maintaining high performance, highly scalable Oracle-based applications or databases. However only a few places are currently available on each webinar with numbers […]
Categories: DBA Blogs

Oracle 19c Automatic Indexing: Non-Equality Predicates Part I (Lucy Can’t Dance)

Thu, 2021-01-14 00:43
  I’ve been waiting a while before posting a series on the various limitations associated with Automatic Indexing, in order to see how the feature matures over time. The following have all been re-tested post 1 January 2021 on the Autonomous ATP Database Cloud service, using Oracle Database version 19.5.0.0.0. In the Oracle Documentation (including […]
Categories: DBA Blogs

Oracle 19c Automatic Indexing: Indexing Partitioned Tables Part II (Neighbourhood Threat)

Wed, 2021-01-13 00:38
In my first post on Automatic Indexing on Partitioned Tables, I discussed how Automatic Indexing (AI) can now create a Non-Partitioned index if deemed the most effective indexing structure (this wasn’t supported when AI was initially released). A Non-Partitioned index is indeed likely the most efficient indexing structure if the underlying table has many partitions […]
Categories: DBA Blogs

Oracle 19c Automatic Indexing: Currently Broken In Autonomous ATP Databases Part II (Fix You)

Mon, 2021-01-11 18:43
  Firstly, thank you for all those who contacted me regarding the issue with Oracle Automatic Indexing (AI) having stopped working within the Autonomous ATP Database Cloud service. It appears this issue was indeed widely spread and impacted numerous (if not all) Autonomous ATP Database Cloud services. This was all possibly due to (unpublished) bug […]
Categories: DBA Blogs

Oracle 19c Automatic Indexing: Currently Broken In Autonomous ATP Databases? (What In The World)

Thu, 2020-12-17 00:24
  I’ve been playing with the free tier Oracle Autonomous Databases for quite some time, but unfortunately in recent times, I’ve hit a bit of a snag. The Automatic Index capability appears to be currently broken… The Automatic Indexing task appears to have stopped running, yes even with the AUTO_INDEX_MODE set to “IMPLEMENT” and with […]
Categories: DBA Blogs

Announcement: New Oracle Webinars Scheduled For February 2021 !!

Sun, 2020-11-22 22:46
  After much badgering from a number of you (you know who you all are), I’m pleased to finally announce the scheduling of 2 new webinars for February 2021 !! As usual, places are very strictly limited as I only run small classes to give every attendee the opportunity to get the most from the […]
Categories: DBA Blogs

Oracle 19c Automatic Indexing: Indexing Partitioned Tables Part I (Conversation Piece)

Tue, 2020-10-13 23:47
In this little series, I’m going to discuss how Automatic Indexing works in relation to Partitioning. I’ve discussed Indexing and Partitioning many times previously and how Oracle has various options when indexing a partitioned table: Non-Partitioned Index Globally Partitioned Index Locally Partitioned Index So the question(s) are how does Automatic Indexing handle scenarios with partitioned […]
Categories: DBA Blogs

Oracle 19c Automatic Indexing: Indexing With Stale Statistics Part III (Do Anything You Say)

Wed, 2020-10-07 23:22
In Part I of this series, we saw how Automatic Indexing will not create a viable Automatic Index if there are stale or missing statistics on the underlining segments. In Part II we saw how these SQL statements effectively become blacklisted and when segment statistics are subsequently collected, Automatic Indexing will still not create viable […]
Categories: DBA Blogs

Oracle 19c Automatic Indexing: Indexing With Stale Statistics Part II (Survive)

Wed, 2020-10-07 01:07
    In my previous post, I discussed how having stale statistics, usually a bad idea, is especially problematic with regard Automatic Indexes as it usually results in viable automatic indexes only being created in an UNUSABLE/INVISIBLE state. If we were to now to collect the missing statistics:   If we now repeatedly re-run the […]
Categories: DBA Blogs

Oracle 19c Automatic Indexing: Indexing With Stale Statistics Part I (Dead Against It)

Mon, 2020-10-05 23:56
A “golden rule” when working with Automatic Indexing is that things don’t work properly if there are stale statistics on the dependant objects. Stale statistics can of course be problematic but they can be particularly troublesome when dealing with Automatic Indexing. In the Oracle Autonomous Database environments, this issue is addressed somewhat by the new […]
Categories: DBA Blogs

Oracle 19c Automatic Indexing: Data Skew Fixed By Baselines Part II (Sound And Vision)

Mon, 2020-09-28 01:00
  In my previous post, I discussed how the Automatic Indexing task by using Dynamic Sampling Level=11 can correctly determine the correct query cardinality estimates and assume the CBO will likewise determine the correct cardinality estimate and NOT use an index if it would cause performance to regress. However, if other database sessions DON’T use […]
Categories: DBA Blogs

Oracle 19c Automatic Indexing: Data Skew Fixed By Baselines Part I (The Prettiest Star))

Thu, 2020-09-24 20:53
In my previous few blog posts, I’ve been discussing some issues in relation to how Automatic Indexes handle SQL statements that accesses skewed data. In this post, I’m going to setup the scenario in which Automatic Indexing can potentially use Baselines to help address some of these issues. BUT, as we’ll see, I’m having to […]
Categories: DBA Blogs

Pages