Hints in Oracle have been around since version 8. Hints are like compiler directives that tell Oracle what path to take when optimizing a SQL statement (generally speaking.) However, Oracle will ignore the hint if it can’t do it or it is formatted poorly.

Most tuning products for SQL will make use of hints if statement re-arrangement doesn’t solve the problem. In later versions of Oracle, outlines became available....

I’ve been searching for a definitive answer to the question: “What is the cost of poor database design?” No doubt you have all seen the cost/benefit graphs for fixing application problems and the pyramid showing how up to 90 percent of performance issues in a running database are SQL and index related, but what about the cost of building an application on a poorly designed database that forces poor SQL usage?

No doubt...

With the use of RAC on the rise it is time to talk about the use of a feature very under-utilized in Oracle. This feature I refer to is cross-instance parallel query. Many times on site visits I see people using single-instance parallel query, but no one whose system I reviewed has used cross-instance parallel. Some weren’t aware it was available while others assumed Oracle did it automatically.

In order to enable...

In my last blog entry we looked at sizing tables in 10g, of course the other side of that coin is the sizing of indexes. As with tables the Oracle8 manuals had an explanation of how to size indexes, but manuals since then have been strangely silent on the subject. In this blog I will address the sizing of normal, b-tree indexes since they are still the in the majority as far as type of index used in most applications.

...

On one of my first Oracle consulting assignments I had to come up with the sizing estimates for tables for a data warehouse for a telecommunications company in California. At that time, (1994) there were few products that provided sizing algorithms for Oracle tables and indexes. In fact, all that was available were the somewhat cryptic sizing formula provided by Oracle (discontinued from the manuals in version 9). Not being...

One of my favorite things to do (when I am not tuning and managing Oracle databases) is to don a scuba tank, mask, and exposure suit and slip beneath the waters surface. For a long time I wondered how to reconcile these two interests and then it came to me.

In industry the one thing that keeps businesses moving, provides them fuel and keeps them from getting choked is data. In scuba, what keeps you moving, provides you fuel and keeps you from choking is air. So using simile data is to business is like air is to a diver. 

...

Of the many databases I have examined on releases 9i and 10g, most are using the automatic undo management feature and on the whole it does a pretty good job of managing the undo segments (for you other old timers, rollback segments).

However, I have been noticing, shall I say, some rather retro behavior as a result of the automatic undo management. In the bad old days when we managed the undo segments manually we...

Caveat Emptor (Buyer beware) has been around since, well, since time began. Whether barter or cash, when doing a deal the buyer has to careful, this is especially true in the complex area of software licensing.

A case in point, Oracle licensing, I hear they are offering a degree in it as at times it is as difficult to figure out as quantum string theory.  A prime example is the wonderful suite of monitoring and analysis...

Many times I am asked how to tailor the initialization parameters for a specific process or user. Essentially the easiest way to set custom initialization parameters for a particular session is to use the ALTER SESSION SET command in a logon trigger keyed to a specific schema or schemas. For example, say we had an application where we wanted to setup cursor sharing and all of the users who will need it are prefixed with “APPL”...

I recently went on a very nice vacation to Curacao. Before I went on vacation the Oracle 10g Grid Control (10.2.0.2) I had installed on my Laptop for monitoring my home office systems was working. I took my laptop with me to allow offload of pictures from my cameras and to track email while I was away. When I returned home and hooked my laptop back up to my home network Grid Control wouldn’t start and of course even with the...

Oracle’s Oracle10g requires more memory than was ever required before. If you utilize any of the new features such as automatic storage management (ASM) and automatic shared memory management (ASMM) then you really need to pay attention to what memory is doing in 10g.

I remember reading “Brave New World” when I was in high school, we needn’t mention the year. I was fascinated by the system described in the book that seemed so logical on the surface but needed so many props underneath to keep it going. It reminds me at times of some of the Oracle management that goes on these days.

What exactly do I mean? Many times Oracle management tools that look good on the surface are actually...

I often see DBAs and developers who don’t know the first thing about tuning SQL. They ask “Why do I need to know that, can’t Oracle tune itself?” The truth of the matter is that Oracle is, for the most part, able to do a pretty decent job of tuning itself if it is given enough information and the queries or tuning tasks aren’t too complex. However, there are times when Johnny must tune.

What do I mean by “enough information”?...

With each new release of a database, be it Oracle or one of their competitors, we hear the cry that this release will make DBAs as we know them obsolete. We hear again and again how this automated feature set or that new GUI interface will automate the DBA job. So far, all of these claims of DBA obsolescence have been for naught, to mis-quote Samual Clements “The reports of the DBAs death are greatly exaggerated.” When...

When I started working with Oracle in 1990 there weren’t many SQL tuning tools available. At most you had the Oracle supplied tools which allowed you to capture an explain plan and minimal tracing. Most SQL tuning consisted of either encouraging the use to a particular index by making sure the column in the index was on the leading edge of the index or, defeating the use of the index through null concatenation to character...