Threadbare


"Expect everything, I always say, and the unexpected never happens."
-- Norton Juster, The Phantom Tollbooth

Oracle has, since version 11.2, required standby redo logs to be configured for a Data Guard installation, even though in 11.2 the default log apply process relied on archived redo logs, not the standby logs. That changed in 12.1 and the default became what Oracle labelled “Real Time Apply”; the choice to use archived redo (more...)

Making A List


"Expect everything, I always say, and the unexpected never happens."
-- Norton Juster, The Phantom Tollbooth

In-lists can be a convenient way to search for multiple values in a single select statement. They can be hard-coded or can operate on the results of a subquery, and in the first case the reported limitation is 1000 elements, and that is correct for single-valued elements. If the elements are multi-valued that limit changes, which may be (more...)

Recovery Room


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

The common wisdom is that any operation declared NOLOGGING won’t contribute to the redo stream, and that includes operations on tables created NOLOGGING. [It cannot be stressed enough that NOLOGGING operations in production databases should NOT be executed if for no other reason than recoverability.] Yet there are operations that will defy the NOLOGGING directive, generating the usual amount of (more...)

Final Elimination


"Expect everything, I always say, and the unexpected never happens."
-- Norton Juster, The Phantom Tollbooth

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 the optimizer will rewrite the query to eliminate the ‘unnecessary’ join. Oracle has been doing this for years, with the limitation that the primary keys (more...)

“It’s Nice To Share…”


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

In a valiant attempt to let the adminstrator affect performance Oracle has provided various settings in the spfile to enable or disable features that could, possibly, make things go faster. Long on the list of bloggers has been cursor_sharing, the parameter used to tell Oracle how to treat query text. By default it’s set to EXACT, which means Oracle takes the (more...)

Cloning Around


"We never choose which words to use, for as long as they mean what
they mean to mean, we don’t care if they make sense or nonsense." 
― Norton Juster, The Phantom Tollbooth

Sometimes it’s necessary to clone an existing ORACLE_HOME; one case for this is when the business requires a new ORACLE_HOME when CPU patchsets are applied. With some operating systems cloning a home is simiply a matter of creating a suitable archive (more...)

“The Best-laid Plans…”


"Expect everything, I always say, and the unexpected never happens."
-- Norton Juster, The Phantom Tollbooth

Oracle version 12 introduced the hybrid histogram as a performance improvement — such histograms are based on some ‘rules’ set forth in version 12.1. Those ‘rules’ are:

        1) a value should not be found in more than one bucket
        2) the bucket size is allowed to be extended in order to contain all instances of the same  (more...)

Tuning Time


"“You see,” he continued, beginning to feel better, “once there was no time at all, and people found it very inconvenient.
They never knew whether they were eating lunch or dinner, and they were always missing trains. So time was invented to
help them keep track of the day and get places when they should. When they began to count all the time that was available,
what with 60 seconds in a minute and 60  (more...)

Well, That Didn’t Work …


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

Interesting behavior with the unified audit trail has been reported by Jeff Hunter and verified on at least two platforms, Solaris 10 and RedHat Enterprise Linux 6. While other ports of Oracle behave as expected (Windows and Oracle Enterprise Linux 7, to name two that I’ve tested) the problem platforms fail to return data from V$SESSION because the AUDSID values apparently (more...)

Eviction Notice


"Expectations is the place you must always go to before you get to where you're going. Of course,
some people never go beyond Expectations, but my job is to hurry them along whether they like it or not."
-- Norton Juster, The Phantom Tollbooth

Oracle has decided that Solaris needs to retire, giving those who are running Oracle on Solaris roughly two years to move to another operating system. A possible choice involves commodity (more...)