David Fitzjarrell

The advent of JSON (JavaScript Object Notation) has changed application programming, and, because of this, database vendors are adapting their products to make JSON easier to use within the database. Oracle is no exception, and with releases from 12.2 onwards Oracle has made JSON easily accessible from the PL/SQL engine. Even though JSON functionality is […]

Transactions are the lifeblood of a relational database and Oracle is no exception. The key to this concept is the ability to replay those transactions should the need arise to recover ‘lost’ data due to a database crash or, worse, corruption requiring a restore and recovery. Yes, recording the transactions does take time but that […]

For every timeless adage there is at least one mistaken thought that can become ‘truth’ simply by virtue of being repeated. Oracle is no exception to this phenomenon especially when single-column Primary Key indexes are involved. The conventional ‘wisdom’ discounts the use of histograms on such an index as unnecessary, and this presumes that the […]

Oracle has provided very useful tools and views to aid the DBA in database management> This includes a number of offerings dealing with the subject of space. One such view is DBA_TABLESPACE_USAGE_METRICS, which provides a ‘thumbnail’ view of overall size and used space. Unfortunately, this view doesn’t accurately report such usage for the UNDO tablespace […]

Automatic Memory Management (AMM) has been available since Oracle version 11, and it does a respectable job of managing the SGA and PGA. When it’s configured, Oracle will manage both the SGA and PGA dynamically, changing pool allocations automatically as memory needs shift. If a pfile is in use the settings will revert to the […]

In-lists can be interesting constructs as far as Oracle is concerned. They allow users to return results for a large number of values with the restriction that the list can contain no more than 1000 items. There are, however, ways to get around this restriction and get Oracle to process longer lists. You’ll see this […]

There are occasions when two or more data sets need to be combined to produce the desired results, and a join is not possible (no common columns, no common data values to join on). Conventional wisdom would point to using UNION to generate such sets, and in many cases, this is a suitable solution. If, […]

Disaster recovery should be on the mind of every Oracle DBA, even though the goal is to never need it. Nonetheless, many enterprises run regular disaster recover tests to ensure that daily business will not be interrupted should a catastrophic event affect the data center. Before the advent of Oracle Managed Files, a ‘proper’ disaster […]

NULL values can be a performance issue as, absent an index of some sort, searching for them involves a full table scan. For small tables (less than 10,000 rows) this usually isn’t a time sink; however, in enterprise tables (with millions of rows) a full table scan can consume considerable time. Oracle provides two ways […]

Occasionally a join using primary key columns in a query can be eliminated because of the data involved. If Oracle determines that all of the ‘required’ data is available in a single table, then the optimizer will rewrite the query to eliminate the ‘unnecessary’ join. Oracle has been doing this for years with the limitation […]

Oracle DBAs should know how Oracle behaves and should also be able to address issues without the aid of a flashy graphical user interface (or GUI). Unfortunately there are those who work as DBAs who seem to be lost without applications like Oracle Enterprise Manager/Cloud Control, TOAD or any of a number of other, similar […]

iOracle version 12 introduced the hybrid histogram as a performance improvement — such histograms are based on ‘rules’ set forth in version 12.1. Those rules are: A value should not be found in more than one bucket. The bucket size is allowed to be extended in order to contain all instances of the same distinct […]