David Fitzjarrell

Oracle 12.1 provides an interesting performance enhancement, the in-memory column store, that can increase performance by reducing physical I/O load by making tables memory-resident (to simplify the description somewhat). Along with the in-memory access to table data Oracle has introduced a new optimization called “Vector Transformation, including Vector Optimization” for in-memory operations. Even when this […]

Oracle 12.1.0.2 provides new functionality for finding pattern matches in a data set; using the MATCH_RECOGNIZE function it’s fairly easy to generate results based on a defined pattern in the data. Let’s look at some examples and see how to use MATCH_RECOGNIZE to find and report user-defined patterns in a data set. The examples provided […]

A useful new feature in Oracle 12c (12.1.0.2) is the Hybrid histogram, a new type of histogram that uses a reduced number of buckets compared to the number of distinct values in the table data and utilizes the repeat frequency of the endpoint values. This type of histogram can adjust the bucket size based on […]

In my previous article an issue was discussed that Oracle Support declared as a bug. Further investigation indicates this is not a bug but expected behavior for locally managed tablespaces with system-managed extents, which is why no solution or workaround has been posted as of now. Let’s look at the problem again, and show that […]

An interesting bug in Oracle 11.2.0.4 was reported to Oracle Support recently, a bug that can cause a DBA to scratch his or her head in either wonder or disbelief. Listed as Bug 24390197 it is fairly easy to replicate. The example starts by creating an empty tablespace of sufficient size to contain several tables, […]

Unused indexes are a major topic for Oracle DBAs, since each index a table has increases the maintenance “cost” of inserts, updates and deletes. Many a blog post has been written providing ways to find such ‘unused’ indexes, many relying on a relatively newer Oracle feature, invisible indexes. Two other articles discuss this, one an […]

Invisible indexes are a nice addition to Oracle, since in general, the optimizer ignores them when generating execution plans. It appears though, that in 11.2.0.4 that isn’t necessarily the case since it can still ‘see’ the index statistic distinct­_key and thus will consider the index at run time. Let’s look at a modifed example, provided […]

Using Oracle’s Auto Degree of Parallelism (Auto DOP) can improve performance if used judiciously, but unless you’re running autotrace at the time the query or statement runs you probably won’t know what degree Oracle decided to set. There are ways around this; let’s look at them and see what information we can glean from Oracle. […]

Recently a PL/SQL blog post was illustrating the difference between MOD() and REMAINDER() and the results from REMAINDER looked … suspicious. The example, modified here to provide some context in the output, shows the ‘problem’ that some remainders are negative. The mathematical definition of remainder states: In mathematics, the remainder is the amount “left over” […]

With the introduction of Auto Degree Of Parallelism (Auto DOP) in version 11 Oracle made it necessary to run a DBMS_RESOURCE_MANAGER procedure named CALIBRATE_IO. Oracle did not say much, really, about the procedure except that “This procedure calibrates the I/O capabilities of storage.” Looking a bit deeper into the documentation reveals this: Running IO Calibration. […]

The mutating table error in Oracle can be a familiar sight throughout a DBA’s career, along with the usual question of ‘Why?’. The error itself is not difficult to understand nor is it hazardous to the table data. Let’s look at why the error is thrown, why it’s not going to create data problems and […]

Every once in a while Oracle produces interesting behavior, and has done so with release 11.2.0.4 using the Grid infrastructure. On AIX an issue was reported regarding an unexplained group ownership change. Let’s look at the situation and see what Oracle did. The reported behavior involves either the srvctl or crsctl utility and creates problems […]