David Fitzjarrell

It can be frustrating at times to examine an execution plan for a given query against one or more large tables only to see this dreaded step: MERGE JOIN CARTESIAN Oracle ACE Director Jonathan Lewis posted such a problem on his blog referring to a customer of his experiencing that exact problem. As a solution […]

Oracle’s most recent database release has an interesting problem involving ANSI joins and tables having 500 or more columns. Yes, this is likely a rare situation in OLTP systems but it exists, nonetheless. [Data warehouses are different and it’s likely that tables of 500 columns or more could exist.] It was brought to light in […]

Oracle has provided views to assist in managing the SGA and PGA settings when you’re not using Automatic Memory Management (AMM) and a view to manage memory_target when you are using AMM. These views were designed to be fairly easy to interpret but it seems that there has been confusion of late in how to […]

In an Oracle forum that I frequent a user posted that he found cardinality values to be severely inflated when using an analytic function. This user posted an example that will be used here to demonstrate the problem. It will also be used to demonstrate another issue that can make this problem even more confusing, […]

Index-organized tables, or IOTs, combine a primary key index and a table into a single structure. They allow for fast data access since the table is organized and accessed much like a standard B-tree index. To make an IOT even more efficient many of the non-key columns can be stored in an overflow segment; doing […]

Oracle can do some, well, strange things when fixing issues caused by the optimizer. For 10g releases up to 10.2.0.x Oracle chose to silently ignore a message and eliminate an outer join on the MERGE statement under certain conditions. Let’s examine this and see exactly what Oracle implements. Occasionally in a 10046 trace file generated […]

In previous articles compression options have been discussed, and now it’s time to see how Oracle performs basic compression. It isn’t really compression, it’s de-duplication, but it does result in space savings for data that won’t be modified after it’s ‘compressed’. Let’s look at how Oracle saves space with your data. Oracle de-duplicates the data […]

Automatic Workload Repository (AWR) reports were a tremendous improvement over Statspack reports, primarily due to the depth and breadth of the sample data collected. A more detailed report, with extended metrics, provides a better look at what the database is doing during a given interval. Unfortunately this detail can create unusual information, especially in the […]

Many times a problem can appear to be more complicated than it actually is. This is due, I think, to being ‘locked into’ a thought process not conducive to solving the problem. Knowing how to read the problem and discover the information provided can help tremendously in working toward a solution. Let’s look at a […]

It would appear that some DBAs are still using the optimizer_index_cost_adj parameter to make index access paths more ‘desirable’ to the optimizer. In decades past this might have been a good strategy however with the improvement in statistics gathering in recent relesaes of Oracle this might not be the case. Let’s look at an example […]

In a forum I contribute to the following question was asked: “Can adaptive cursor sharing (ACS) depend on execution order?” The issue described a relatively basic query that changed execution plans, apparently due to the order the query statements were run based on bind variable values. It’s an interesting issue that testing has verified. The […]

An interesting ‘problem’ surfaced a while ago, one where a user with zero quota on every tablespace could successfully create tables. Of course once it was time to insert data the inserts failed, but this was confusing the user creating the tables. The ‘problem’ stems from enabling deferred segment creation in the database. Let’s see […]