Sunday, September 10, 2006

Oracle Riddles: What's The Point?

I am frequently asked for directions. Sometimes I am not the best to ask and will just be a waste of your time and energy. Other times I am sought exclusively. I try to lead a balanced life. But, hey, I am not perfect. What exactly am I?

Friday, September 08, 2006

Don't Get Caught With Your GUI Down

In a recent wave of interviews I was amazed how little prospective DBA candidates knew about user-managed hot backups. Most could give the BEGIN and END backup stuff and convey that it causes more redo to be generated during this time. But, when asked to give a little more of their insight into the mechanics or performance implications, 9 in 10 had plenty to say - just nothing that was correct. 90% could not explain the significance of putting a tablespace in hot backup mode. That is, why do it? Why not just copy the file while the database is open and cooking? Of course, most understood that Oracle needs us to do this so that the backup is "good", but few knew how Oracle went about doing it. Moreover, few knew why the extra redo was generated. And most amazing, nearly all thought the data files were locked and changes were written to the redo logs and reapplied when the END BACKUP command was given. Where are DBA-types reading this? Irrespective, the DBA population is evolving.

I am not basing my opinion on one simple question concerning user-managed backups, but a host of other questions given as mental exercises. What are some of the Oracle wait events? What do they represent? How would you go about troubleshooting systemic response time degradation in your production database? What is extended SQL tracing and why use it? Time after time candidates struggled to give lucid, well thought out responses. A vast majority of responses could be summarized as, "I would go into OEM and check for A or B." I don't have a problem with using OEM, but usually the A’s and B’s had little relevance to the question.

The herd of available DBAs that are able to navigate the database using native SQL to get at critical performance diagnostic information has thinned dramatically. Sometimes I wonder what would happen to some of these shops being supported by some I interview if OEM, Database Control or Grid Control took the night off. When relegated to digging into the database and troubleshooting armed only with a SQL prompt, many appear to be lost. I certainly appreciate what the Oracle GUI database management tools bring to the table. I even like them. My point is, don't throw away your shovel just because you have a snow blower. The day will come when your GUI will fail you and it will be just you and your SQL prompt.

P.S.> Oracle does not lock the content of its data files during the course of a user-managed hot backup. Actually, Oracle only locks one thing, the master checkpoint SCN inside the file header. Some other constructs in the file header stay mutable. Blocks in data files being backed up can be modified as per normal database operation. The changes to blocks are indeed recorded in the redo, but they are not replayed when the END BACKUP is issued. More redo is possible because Oracle must accommodate the potential presence of fractured blocks.