Oracle Parameters You’d Love to See

Have you ever just WISHED that a parameter existed in Oracle? I know I have, and old coworkers of mine have long joked about the need for things like the “fix_database.sh” script.

So I ask you, what parameters would you just LOVE to see show up by Oracle 20rr (Really Robust)? I’ll start:

1. _allow_all_corruption = TRUE|FALSE
2. make_cbo_work = TRUE|FALSE
3. ignore_bad_queries = TRUE|FALSE
4. ignore_user_list = (comma delimited list of users)
5. auto_create_sr_on_error = TRUE|FALSE

Oh, and also two SQL*Plus commands I’d like:

1. set alias for select = sleect
2. set tab complete on

Any other ideas?

In the world of theoretical physics there is a theoretical cat owned by Erwin Schrödinger. This famously fickle feline posed a problem: he could not decide if he was alive or dead.

In the Schrödinger’s Cat thought experiment, a living cat is placed in a box with a nefarious device. This device consists of a Geiger counter, an extremely small radioactive substance, and some acid. The radioactive substance has a 50/50 chance of decaying. If it does, the flask of acid will burst and the cat will die. If it does not, the flask will remain intact and the cat will live. But because the box is closed (and soundproof), we cannot conclude the final state of the cat, and the formulas of quantum mechanics would tell us that the cat is both living and dead.

The original translated text (Erwin Shrödinger, “The Present Situation in Quantum Mechanics,” Naturwissenschaften, 1935) is as follows:

One can even set up quite ridiculous cases. A cat is penned up in a steel chamber, along with the following device (which must be secured against direct interference by the cat): in a Geiger counter there is a tiny bit of radioactive substance, so small, that perhaps in the course of the hour one of the atoms decays, but also, with equal probability, perhaps none; if it happens, the counter tube discharges and through a relay releases a hammer which shatters a small flask of hydrocyanic acid. If one has left this entire system to itself for an hour, one would say that the cat still lives if meanwhile no atom has decayed. The ψ-function of the entire system would express this by having in it the living and dead cat (pardon the expression) mixed or smeared out in equal parts.

There are three popular interpretations of this experiment (don’t worry; we’ll see what this has to do with Oracle soon):

The Copenhagen Interpretation

In this interpretation, the results of the test are not complete until an observation takes place. Measurement, or observation (whether by a conscious source such as a physicist, or a non-conscious source such as a mechanical device) will eliminate one of the two parts of the superposition (|decayed atom, cat dies> + |non-decayed atom, cat lives>), and the wave containing one possibility or the other will collapse, resulting in a single post-observation outcome. Whether the cat is alive or dead inside the box prior to observation is irrelevant; the fact remains that when you look into the box, the cat will either go on to chase theoretical mice or will be given a proper theoretical burial.

The Many Worlds Interpretation

The many worlds interpretation says that at the time an observation occurs, it does not collapse waveforms but decoheres the possible outcomes. Because the physicist (or device, no special relevance is given to conscious beings) has become entangled with the quantum states of the cat, observer-states are formed for both possibilities and multiple universes are spawned of the possible outcomes. That is to say that a universe will exist where the cat is alive and a universe will also exist where the cat is dead. These universes could theoretically spawn from every true-or-false outcome in existence, resulting in an unthinkable multiverse in which all binary possibilities have been played out in one form or another. A true-or-false decision (|alive> or |dead>) would result in two universes, each with the opportunity to spawn later true-or-false decisions and therefore more opportunities to create additional universes. I can’t help but think of Douglas Adams’ Infinite Improbability Drive from Hitchhiker’s Guide to the Galaxy when I think about this interpretation!

The Many Histories Interpretation

This interpretation is something of a mix of the previous two. It agrees with the many worlds interpretation that waveforms do not completely collapse, but disagrees that this will go on to form multiple futures. When an observation/measurement is made, the observer becomes entangled with the state of the cat and decoherence takes place, just as with the many worlds interpretation. However, the state that did not “really” happen is ignored. The waveforms associated with this possibility still exist, but are seen as inaccessible as only one true future can come of any true-or-false situation once an observation is made.

What the hell does this have to do with Oracle?

Oracle is only a computer program, and therefore does exactly what it is coded to do (most of the time). Quantum mechanics really do not seem to have any relevance in the Oracle world because it is coded with specific outcomes to every true-or-false question. Logic gates are applied to bits, and the programmed outcome takes place. Non-programmed outcomes result in errors (ORA-600, ORA-7445).

However, things get more complex when we are modeling new environments because we end up dealing with conceptual outcomes with multiple possibilities. This is especially true in dimensional modeling, because dimensions are supposed to hold factual lookup data. How can we record a fact when important data regarding that fact may change?

For example, imagine a tax company that keeps track of deductions found in their various offices and wishes to form a star schema that will allow past and present analysis. Its (incomplete) star schema may resemble the picture shown here:

Let CLIENT_DIM record = E. Schrödinger, 3 dependents
Let TIME_DIM record = 2006 tax year
Let LOCATION_DIM record = Virginia Beach, VA

The data will come together in our fact table to show that he had $25,000 in deductions for this combination of dimensional data. This “fact” is currently safe. During the next year, E. Schrödinger and his wife have a lovely baby boy (at least in this universe!).

Let CLIENT_DIM record = E. Schrödinger, 4 dependents
Let TIME_DIM record = 2007 tax year
Let LOCATION_DIM record = Virginia Beach, VA

The data will once again come together in our fact table, this time reporting $28,000 in deductions (I’m not a CPA, don’t get me on tax code) due to the extra dependent. This “fact” is currently safe, just as the last one was.

However, we now have a paradox in our data. In 2007, we report $28,000 in deductions, which was based on the fact that E. Schrödinger had four dependents. In 2006, we reported $25,000 in deductions due to E. Schrödinger’s 3 dependents. When we run our analytic reports for 2007, we will get great results; we will be able to break down the deductions and the number of dependents will play a proper role in these calculations. But when we run our reports against 2006, the deduction calculations will not compute properly. The CLIENT_DIM record will show 4 dependents, but the deduction amount for the 2006 year will have been based upon 3 dependents. Our dimensional data (CLIENT_DIM record) changed over time.

This is known as a slowly changing dimension (or SCD). Though we may not realize it, almost every dimension is in fact slowly changing; stores may move, clients may die (especially if they hang out with sadistic quantum physicists with ready supplies of hydrocyanic acid), and even our human definition of time can change over time (consider the changes to daylight savings this year). However, we don’t have to worry about all of these possible changes; we only have to worry about the ones pertaining to the facts on which we are attempting to report. Our business needs, in the end, determine which dimensions must be slowly changing.

There are three types of slowly changing dimensions: Type 1, Type 2, and Type 3. Each of these types tries to help the designer of the star schema eliminate paradox from their dimensional model (just as the three interpretations of the Schrödinger’s Cat thought experiment tries to eliminate the paradox of the living dead).

• Type 1: Overwrite the old value with the new value and call it a day. This is very useful when dealing with issues such as typos on the client’s name. We don’t care about the history in this case because it was incorrect anyways.
• Type 2: Create a new record in the dimension with a new primary key. In the example we’ve given, there would be two records in CLIENT_DIM for E. Schrödinger, one in which he has 3 dependents and one in which he has 4. Though he is one person from the business point of view, he is two people from a dimensional point of view.
• Type 3: Overwrite the old value with the new value, and add additional data to the table such as the effective date of the change. This type of SCD resolution would be beneficial if there is a change that can happen once and only once (such as death).

These three types of SCD resolution usually help in resolving changes to “factual” lookup data. However, we can see clear correlations between these three types of resolutions and the three interpretations of the Schrödinger’s Cat thought experiment!

Bringing it all together

SCD Management Type 1 clearly matches up with the Copenhagen Interpretation of the Schrödinger’s Cat thought experiment. In the Copenhagen Interpretation, the state of the cat changes and all other states are discarded as the waveform collapses. Criticism to this interpretation applies as well to Type 1 SCD resolution. The Copenhagen Interpretation ignores the possibility of reconstruction; in quantum mechanics, it must be possible to return to any original state before measurement was taken place. In our star schema, it also ignores the possibility of reconstruction; we will not be able to return to the original state or even acknowledge that a previous state existed for the purpose of analytics.

SCD Management Type 2 matches with the Many Worlds Interpretation of the Schrödinger’s Cat thought experiment. Instead of completely destroying the other possible waveform, we simply maintain that the two possibilities decohere and form their own universes that will no longer share any correlation. This interpretation shares a similar problem with Type 2 SCD resolution. By spawning new records (universes) as the outcome of a changing event, we create multiple possibilities that no longer share any correlation. For instance, if E. Schrödinger has a new dependent, there will be two resulting rows: one in which has 3 dependents and one in which he has 4. If he then legally changes his last name to Schroedinger, we will have to record that change since it is important for tax records. Because of this, we will have three total records for this one client (E. Schrödinger with 3 dependents, E. Schrödinger with 4 dependents, and E. Schroedinger with 4 dependents). These three records will not have any correlation unless we create some sort of superkey that properly identifies a single person and their many instances. This will be important if we will be doing mining that incorporates multiple times, clients, and locations in our analysis.

SCD Management Type 3 matches with the Many Histories Interpretation of the Schrödinger’s Cat thought experiment. When an observed outcome occurs (like the birth of a new child), the old record is changed to reflect the new “real” outcome; in this case, the addition of a single dependent. However, the change is noted by the addition of a column such as an effective date, to show that this is not the only outcome that has ever existed, but it is THE outcome that does exist at this time. In effect, the old outcome (3 dependents) still exists, but is discarded, ignored, and irretrievable now that the outcomes are decoherent. In our star schema, this type of resolution will only provide us with a confusing result of “This is the case now, but it was not always so. It changed on ….” For some situations, as with Type 1 resolution, this will suffice, such as the death of a client. We only need to record that death once. However, if any new data enters our model after the death of the client (post-mortem taxes?) or if the status changes again (miraculous recovery!), we may have unreliable report output.

Conclusion

Just as a note, I understand that the Schrödinger’s Cat thought experiment and Slowly Changing Dimensions are not a true match. In quantum mechanics, we are calculating multiple events that happen at the same exact point in time (or over a period of unknown time); whereas in a data warehouse we are dealing with history and fully acknowledge the changes time may have on our data. However, one cannot help but notice the correlation between the two forms of paradox and their resolutions. In fact, in data warehousing the so-called Schrödinger’s Cat paradox becomes even more problematic because we are forced to not only predict future outcomes based on dimensional data, but to also report on past/present information based on the same data. Physicists attempting to provide interpretations of this thought experiment only have to worry about future conditions; the past and present are unmeasured, and therefore have no relevance on the problem except that they are in a quantum state. They seek to explain the future of the cat once observation/decoherence have taken place. If Schrödinger’s Cat were in a data warehouse, we would have to analyze the entire life of the cat, the cat inside that horrible box of doom, and try to figure out whether the cat will want dry food, wet food, or a good burial place after the experiment is finished.

I ran across this article today on Oracle’s new strategy for Standard Edition and Standard Edition One licensing.

As a consultant, there are times when I have to tackle licensing questions, though I usually try to stay away from it and let Oracle’s salespeople do that job.

However, I found this news quite interesting. In order to compete with Microsoft and other database vendors in the small and midsize business market, Oracle has changed their licensing model on Standard Edition and Standard Edition One. Instead of a per-core licensing scheme, licensing for these products is now on a per-socket basis. Not only does this make pricing cheaper on multi-core systems, it also makes things much easier for the accounting department!

Of course, it’s still the same for Enterprise Edition (the article calls it tortuous), but perhaps that will change soon as well. Hey, at least we’re no longer using Power Units!

I’m not a big fan of ignoring errors with OUI (we all have our horror stories), and so when I ran across this one I was loathe to ignore or cancel:

Error in invoking target 'all_no_orcl ipc_g' of makefile

Now, an experienced Oracle installer/patcher/troubleshooter/slave knows full well that most of these problems are compiler related, and this one was no different. I was patching a 10.2.0.1.0 RAC installation to 10.2.0.3.0.

To fix the problem, simply go to Oracle’s Open Source Downloads and download the latest binutils release. Install it with the following command:

rpm -Uvh binutils-2.15.92.0.2-15.0.0.0.2.x86_64.rpm

Once installed, click “retry” on your OUI screen and everything should be taken care of! However, if this is a RAC system, don’t forget to install binutils on both nodes!

If you DID ignore the error and kept going with the patch, you’ll probably find that ASM does not work. To fix this, you will have to install binutils, relink all, and find a good working copy of $ORACLE_HOME/lib/libskgxp10.so from another Oracle installation to replace the one on your patched system.

The moral of the story is, of course, to fix problems before continuing whenever possible.

Errors During CATUPGRD.SQL

While I’m at it, another problem I found was during the execution of the CATUPGRD.SQL script that must be run after installing the Oracle software. I received the following error:


SELECT version_script AS file_name FROM DUAL
*
ERROR at line 1:
ORA-06575: Package or function VERSION_SCRIPT is in an invalid state

Thankfully, this was an easy enough problem to fix. Simply change some initialization parameters:

1. alter system set plsql_code_type string = INTERPRETED
2. alter system set plsql_compiler_flags string = INTERPRETED, NON_DEBUG
3. Rerun ?/rdbms/admin/catupgrd.sql

Or, if you really want to use native PL/SQL compilation, just set your plsql_native_library_dir and then rerun the script.

One Last Note

Make SURE you set your JAVA_POOL_SIZE and SHARED_POOL_SIZE to at least 150M before running catupgrd.sql! If you do not have a large enough shared pool, you will get errors during the install. You may be able to workaround these by flushing the shared pool (alter system flush shared_pool), but you’re better off doing it right the first time.

Good luck with your patch upgrades!

If you’ve ever seen the movie The Shining, you have to see this trailer!

The story, so I hear, is that a film student did this for a contest called Trailer Park. The goal is to take a movie and create a trailer for that movie that makes it seem like a completely different genre. Whatever the case, it is absolutely excellent!

Hello everyone…I know I haven’t posted in quite a while, and I’m not about to start now. Things have been extremely busy lately, starting with my 10g RAC course aboard the Carnival Glory on a 7-day Caribbean cruise (more on that later).

So I will be posting again soon, and hope to have some feedback on RMOUG (where I will be presenting three topics this week) and other things.

Until then, read the Tao of Oracle by Roby Sherman. It’s probably the single most hilarious piece of Oracle literature I’ve ever read!

I was speaking at a small User Group meeting yesterday. A crowd of about 20 was sitting at tables in a “U” configuration. About 70% of the way through the presentation, I asked the fateful question.

Me: “How many of you are DBAs?”

To my surprise, the right side of the room raised their hands. Roughly 10 people, without a single gap in the line.

Me: “How many of you are Developers?”

And the left side raised their hands, roughly 10 people, no gaps, all developers.

How the hell did the DBAs and Developers sit apart like that without any planning? They didn’t ALL know each other, they just happened to sit in such a way that they were 1) apart from each other, and 2) facing each other with the battlefield in between.

We all had a good laugh about it, but it still made me think. Is there some fundamental subconscious difference, some sort of projected vibe, between DBAs and Developers? We fight so often, but that could be easily attributed to the fact that we rely on each other to do a good job. I even did a humorous presentation/discussion on it once, an epic fairy tale explaining the peril DBAs and Developers put a company in with their bickering. You can download it here if you like.

But if it’s just the fact we work so closely yet compete, like siblings, how the heck did they all manage to sit part like that? It reminds me of the “group mind” concept from various science fiction and fantasy books (like the Borg). We are the IT Conglomerate, Developers to the left, DBAs to the right, no further communication is required, thank you.

Hey, maybe it’s pheromones.

Welcome to the 26th edition of Log Buffer, a Carnival of the Vanities Blog for DBAs.

In addition, welcome to a new year! 2007 is shaping up to be a very interesting year for the database world, and some of the recent posts in the blogosphere should help you see why.

Many database professionals are wondering what they can look forward to in 2007 from their database vendor of choice. Some bloggers have gone beyond idle speculation and have published their opinions, predictions, and ideas regarding the New Year’s impact on their job.

Ronald posted his predictions for the MySQL database for the year in the ARABX blog. One of his predictions has already come true regarding the Falcon storage engine, which we will see later. I find his idea on government use of open source technology both intriguing and plausible as well.

Mark Rittman has a very lengthy and in depth article on his views of 2006 and upcoming technologies for 2007 in his Oracle BI Blog. It is definitely worth a read!

Even more Oracle plans for 2007 have been posted in an article by Don Burleson. Don makes several references to Oracle’s most recent acquisitions and marketing schemes, as well as the push for multiple database support within Oracle products. Another article by Don lists new features that will be in Oracle 11g and is updated frequently.

Speaking of Oracle’s competition, Kevin Closson has a great post referencing a recent article by Time Magazine that pits “Truly Dedicated” unpaid workers against “Geeks in Cubicles.” Though the original Time Magazine article is about web browsers, Kevin (and commenters to the post) bring up several parallels in the database world.

Thomas O’Connell posts a similar message in Infoworld’s Database Underground column citing several shortcomings of MySQL, which is widely regarded as the most popular Open Source database available.

But hey, you use what you use, and as DBAs we have to make the best of whatever database we work with. Frank Mash has a post about slow deletes on MyISAM tables in MySQL. The most interesting part of the post is his update, where he mentions a horrible practice that was found to be in place in application code. Beware, it could happen to you!

Oracle Partitioning has been touted as a great way to speed up access to large tables. Jonathan Lewis answers a very common question in his blog regarding partitions: how many should you have? Jonathan also gives a comparison with Oracle 8i for those that remember some of the old partitioning pitfalls.

Speaking of partitioning, David Aldridge posted a recent article regarding the need for partition key statistics. He also finds an interesting and somewhat disturbing decrease in performance related to his test case when using check constraints. David knows his partitioning; while nosing through his site I found an older post detailing his ultimate performance conquest, a 180,000x performance improvement using partition pruning.

Peter Zaitsev goes over some major enhancements for MySQL databases utilizing InnoDB including several bug fixes. His benchmark results from a variety of tests are also posted.

Moving back to Oracle, Kent Crotty has performed a study using the PL/SQL FORALL operator to test loading speeds. In his tests, he found that FORALL loading performed roughly 30 times faster than vanilla SQL inserts. This method is also highly touted by Dr. Tim Hall, owner of the highly popular Oracle-Base site, Oracle ACE of the Year 2006, and author of “Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming.”

Kevin Closson wrote an in-depth article on Oracle over NFS using EMC or NetApp products. Citing several papers on the subject, he provides a great level of detail for DBAs thinking of deploying their Oracle databases via NFS storage. Anyone who has read the works of Kevin Closson knows about his uncanny ability to read between the lines of any whitepaper (and inject commendable amounts of wittiness), and this article is no exception.

Don Burleson has written an article regarding a common debate: should tables and indexes really be segregated? Don inspects the reasoning behind both sides of the argument and gives some food for thought when planning your object layout.

Moving on to general tips, tools, and news, there is a lot of buzz in the MySQL world regarding the new Falcon storage engine. Pablo J notes some specific information gleaned from the new documentation surrounding the engine, which replaces choices such as InnoDB and BDB which are now owned by Oracle. He also mentions a performance concern regarding table and index segregation, just as we saw in the previous note.

Greg Sabino Mullane has developed and presents a tutorial on a Postgres type to find a row’s neighboring rows. While his implementation of the function was used to find (and hopefully repair!) a corrupt object (called relations in Postgres), it can be used for other purposes. The tutorial also shows the use of polymorphic functions, which can be highly beneficial for scalable coding.

I have always found dates and times to be a pain to work with in any database or language, and it appears Jeff Smith would agree when using SQL Server. He posts an excellent package of functions he calls the essential date and time functions that any SQL Server database should have. There are truly some excellent functions in here that no SQL Server DBA should go without.

Getting back to Oracle (hey, it’s my favorite), Andrew Clarke has Yet Another Null Article regarding the differences between NULL and empty, and what they mean to Oracle DBAs. It’s an important concept, especially for DBAs that must manage other database platforms that may behave differently. In fact, he notes a point made by Laurent Schneider (Oracle Certified Master and Oracle ACE) in his article that even Oracle treats zero-byte values differently depending on datatype!

Tom Kyte answers a question (surprise, surprise!) regarding how redo logs work in RAC environments. As a RAC instructor, I can say that I’ve heard the same question a time or two as well. Tom and Arup Nanda both provide great detail in response.

René Nyffenegger of ADP GmbH provides a great article on using flashback query to find changed values in a table. As the post mentions, one highly convenient use for this ability is to compare current data in a table against a previous data set.

Texas Memory Systems is continuing work on their free Statspack and AWR tool, StatspackAnalyzer. It’s actually quite good, an evolving example of an expert system for Oracle tuning. I have been invited to contribute new decision rules and refine alert thresholds, and TMS invites anyone to submit their own feedback to improve the tool.

Of course, don’t forget to check out the latest issue of Oracle Magazine (January/February), which has some great tips by Steve Feuerstein, Arup Nanda, and Tom Kyte. Of course I AM a bit biased, since I was interviewed for this issue’s Peer-to-Peer section.

Lastly, don’t forget to sign up for Rocky Mountain Oracle User Group’s (RMOUG) Training Days, February 14th and 15th in beautiful Denver, CO. The conference will feature several prominent speakers and promises to be an outstanding event. I will be presenting on three topics, so I hope to see you there!

Hello all! It’s been quite a while due to the holidays, but I’m back online and doing fine. I hope all of you had a great holiday season.

And after all this time, what do I have for you? The 10 most dangerous toys of all time, of course!

It’s amusing, though the descriptions of consequences can be a bit frightening at times. Have a look.

Radar Online

My favorite has to be the Gilbert U-238 Atomic Energy Lab.

For a mere $49.50, the kit came complete with three “very low-level” radioactive sources, a Geiger-Mueller radiation counter, a Wilson Cloud Chamber (to see paths of alpha particles), a Spinthariscope (to see “live” radioactive disintegration), four samples of Uranium-bearing ores, and an Electroscope to measure radioactivity.

And what nuclear lab for kids would be complete without an Atomic Energy Manual and Learn How Dagwood Splits the Atom comic book? (The latter was written with the help of General Leslie Groves, director of the Manhattan Project.)

I’ve gotten quite a few requests to do another parody song (probably thanks to Lisa for mentioning me in in the Oracle Newbie’s Blog!), so I went ahead and wrote one. This is is The Life of DB Admins, sung to the tune of Hotel California by the Eagles. Enjoy!

—
Late at night at the office, I’m the only one there
Many new tasks to get done, lots of things to repair.
While setting up some table grants, to keep security tight
My head grew heavy and my sight grew dim
I want to stop for the night
There it was on the display;
A piece of query hell
And I was thinking to myself
Let’s give up Oracle and just go Excel
But I made a new index, and I started to pray
For an ending to the query war,
That it would end today…

Welcome to the life of DB Admins
Fix the database
At an urgent pace
Always at work in the life of DB Admins
Any time of year, you will find us here.

Their queries don’t use indexes, and the, where clause never ends (uh)
They got a lot of inefficient joins no DBA recommends.
How they fill up the ORDER BY, disk sorts galore
And then for no reason, they add even more.

So I grow the PGA
Hoping sorts will decline
But then I have to free up some more RAM from my well planned design
And now there’s more full table scans, tons more each day.
With every new line of code that they write
I feel more dismay.

Welcome to the life of DB Admins
Fix the database
At an urgent pace
Living to work in the life of DB Admins
What a nice surprise, loads are on the rise….

CBO is reeling,
Gathered stats once or twice
And I thought, ‘maybe I should just query for some DB_CACHE_ADVICE,’
Or I’ll add a new MView,
Hints are last but not least
I’ll do anything I can do
Until disk reads have all ceased.

Last thing I remember, I was
Looking to explore
A way to get the whole server
Off the rack to smash to the floor
‘Relax,’ said my manager,
Though the code does make you grieve.
You still have a lot of work to do,
Get it done or you’ll never leave!