Merge, Right?


"There are no wrong roads to anywhere."
 - Norton Juster, The Phantom Tollbooth 

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 from 10g releases (more...)

What’s The Password?

A very interesting question was posted in one of the forums I participate in:


is there any way how I can revoke the right from a normal user to change it's own password in the database?

This, obviously, sparked a fairly lively thread (which I will not re-post here) regarding security and the underlying reason such a request was made. The user who posted the original question continued in the thread to explain:


Of course  (more...)

How Can I Compress Thee


“You can swim all day in the Sea of Knowledge and not get wet.” 
― Norton Juster, The Phantom Tollbooth 

In previous posts compression options have been discussed, and now it’s time to see how Oracle performs basic compression. It isn’t really compression, it’e 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 (more...)

Time Is On My Side, Maybe

A recent MOS document discusses, albeit briefly, an issue with AWR reports that’s been going on since 10.2.0, namely that the elapsed time numbers for queries executed in parallel are considerably greater than the actual elapsed clock time. Let’s look at why that is and what can be done about it.

AWR reports were a tremendous improvement over Statspack reports, primarily due to the depth and breadth of the sample data collected. A (more...)

The Best Laid Plans

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 tests are reproduced below, in abbreviated form, so let’s look at what was executed and what execution plans were used.

(more...)

You Can Get There Making All Right-Hand Turns But …

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 to see why this might do more ‘harm’ than good.

The optimizer_index_cost_adj parameter was first provided in Oracle 9i as (more...)

In Deference

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 how that can create a confusing situation,

Deferred segment creation allows tables and indexes to be created without physical segments (more...)

Memory Improvement

Oracle 12c (version 12.1.0.2) offers the option of using in-memory processing to speed things along. Called the In-memory option it’s installed when you install the 12.1.0.2 software. Using it can make a considerable difference in processing speed, provided you have sufficient resources (RAM) available. Let’s revisit an older example, on Bloom filters, and see if Oracle processes things any faster in-memory.

Looking again at the Bloom filter example using (more...)

To Skip, Or Not To Skip

An interesting issue presented itself just recently with a logical standby database I manage. The database is used for generating reports and the client wanted to skip all DML activity for a given schema as it wasn’t necessary for reporting purposes. I had done this in version 10.2.0.x; it was a simple procedure on a low-traffic database:


alter database stop logical standby apply;
exec dbms_logstdby.skip('DML','','%')
alter database start logical standby apply;

(more...)

Map Reading

Consider the following concept: When you are born you are issued a map showing the direction your life will take. Along the way people will come into your life and help you make sense of parts of that map. You may not know these people at the time but they will be important in establishing where you are to be headed and possibly what you should be doing. Eventually you can read the entire map (more...)