David Fitzjarrell

Oracle offers, in releases 12 and later, an enhancement that may improve performance of ‘large’ tables, Automatic Big Table Caching. The feature automatically caches such tables into an area of the SGA reserved specifically for this purpose. The big table cache won’t ‘work’ for direct path reads; in the example that follows those have been […]

Since version 8.1.5, Oracle has provided what can be considered as a true temporary tablespace, utilizing sparse files (or, in Oracle-speak, tempfiles). These files are essentially ‘anchors’, at the operating system level, consuming a file name and an inode (on *nix-type operating systems) and information on how large they can grow. By now this should […]

Large, long-running transactions can generate large volumes of UNDO, which is how Oracle restores the data to its starting point should the transaction fail or be cancelled. Of course, this also generates long rollback times that can seem interminable when someone is waiting for the rollback to complete before other work can begin. Monitoring the […]

Oracle has offered packages for a while now, but until recently it’s been difficult, if not impossible, to prevent ‘out of context’ execution of functions and procedures; in many cases package code is designed to be used within the context of the package, not as stand-alone procedures and functions. In release 12.2.0.1, Oracle has addressed […]

In an Oracle forum recently the following question was posted: hi Friends, I see this wait event latch: parallel query alloc buffer, when a job meant for doing some cleanup ran this query. Why does this wait event come happen? , i searched google,MOS no exact hit for explanation of the exact same event. Looking […]

Query tuning is part art, part science and it depends upon reliable information from Oracle, most often in the form of query execution plans. There are several ways to generate such plans, three of which are ‘autotrace traceonly explain’, ‘explain plan’ and the dbms_xplan package. The first two options won’t return any data, which makes […]

Recently a question was posted on the Oracle General database forum asking how to find the locked object when the “Top Event P1/P2/P3” values section of an ASH report looks like this (obviously the Diagnostic Pack license is required for such an investigation): Top Event P1/P2/P3 Values Event % Event P1 Value, P2 Value, P3 […]

In the last article, latches were discussed; however, there’s another more lightweight process that Oracle uses in a similar way, a mutex. So, what is a mutex? By definition it’s a mutual exclusion object that locks memory structures, so in at least one way a mutex is like a latch. In other ways it isn’t; […]

Latches can be confusing. Sessions can wait for them and when those waits become excessive performance degrades. To many they appear to be the same as locks, but they aren’t, even though lock waits, when the times get excessive, can also degrade performance. So, what are the differences between latches and locks? Let’s examine both […]

Extended string lengths can be very beneficial but they can also be an impediment to index and constraint creation.  Read on to see what the problems are and how to get around them. Creating indexes and unique constraints in an Oracle 12c database configured to use the extended string length (32767) may result in the […]

Histograms, in Oracle or most any other relational database, may be one of the most misunderstood and as a result, underused features. Tales abound of performance problems that histograms create by changing once-good plans to terribly inefficient and wasteful resource hogs. Honestly when the histogram data accurately reflects the data distribution nothing could be further […]

An interesting question was posted on the Oracle General Database forums recently inquiring about creating a B-Tree index on a BLOB to speed queries looking for NULLs in that BLOB. The conventional wisdom states that, because it’s a BLOB, NULLs shouldn’t be present. And because it’s a BLOB, containing binary data, creating such an index […]