David Fitzjarrell

Extended statistics can be very helpful to the optimizer by establishing a dependency between two or more columns in a table. Such dependencies can improve cardinality estimates the optimizer uses to determine the best plan of attack for a query. Extended statistics have been available since 11.2.0.x, and now Oracle 12.1.0.2 improves on that by […]

Oracle offers a results cache in the database (from 11.2 onwards) and in PL/SQL (again, from 11.2 onwards) which can greatly reduce execution time of repeated statements when insert/update/delete activity is not heavy. The mechanism in PL/SQL, however, may not work as expected with global temporary tables. Let’s look at what you might see when […]

A recent question in an Oracle forum regarding Automatic UNDO Management shows that the topic is still misunderstood by some. The basic idea behind Automatic UNDO Management is that the database essentially does the work for the DBA, freeing him or her from a tedious task of monitoring and adjusting UNDO (or rollback) segments. This […]

An interesting problem recently submitted to Oracle Support involves a possibly little-known aspect of a table called the Hakan Factor. The problem description, in brief, states that the Hakan Factor isn’t preserved for a partitioned table when a single partition is compressed. Why is this a problem? The Hakan Factor is used by bitmap indexes […]

Occasionally forum topics recur and one of these that has reared its head again regards finding ‘unused’ indexes. Of course how one defines ‘unused’ has an impact on what you look for. So, how should ‘unused’ be defined? Let’s look at that question and see what direction that discussion takes. The commonly offered idea of […]

Data integrity is one of the keys to business success and a sure way to provide that is to declare primary keys for critical data. That, sometimes, is easier said than done since a clear choice for such a key may not be available. Primary keys can be natural, meaning they use the existing data […]

There are times when a seemingly simple query ends abruptly with an ORA-01555. Interestingly enough there are no other user processes running, no updates, no deletes, nothing that could generate such an error. The common question is “What caused this?’ The short answer is Delayed Block Cleanout, so what IS Delayed Block Cleanout and how […]

It seems that one of the more confusing statistics in Oracle is one called the clustering factor. Associated with an index it’s actually dependent on the table data, more specifically the distance between ‘jumps’ for a given index key. A ‘jump’, to me, is the number of blocks between rows containing the given index key […]

Backup and recovery tasks for an Oracle database have changed over the years, especially with the advent and further improvement of Recovery Manager, or RMAN. Let’s take a look back at how a DBA would backup a database before Oracle 8i without RMAN and how RMAN has improved the process. Going WAAAAY back in history […]

I often see questions in Oracle newsgroups and forums about monitoring space in tablespaces, and many of those questions are regarding Enterprise Manager 12c and the alerts it generates reporting the percentage of used space. Of course how you monitor free space depends on whether or not the datafiles are autoextensible. Let’s look at several […]

Jonathan Lewis has coined a term and its definition that, I believe, we’ll be using quite a bit: Stoptimisation – the art of optimization by not doing the things you don’t need to do. It’s a term we’ve needed for some time now, given the pendulum swing back to the days of yore (meaning pre-9i) […]

Using BULK COLLECT in PL/SQL blocks and procedures can dramatically speed array processing, but it can, if the DBA isn’t prepared, ‘hide’ any errors that occur in the bulk processing list. A ‘plain vanilla’ EXCEPTION handler may not report all errors that are thrown. Let’s look at an example intentionally set up to fail inserts […]