If Santa worked at Oracle, all database back-ups would be kept safe and secure at the North Pole. If he really liked working at Oracle, we would never need to back up the database since it would be 100% reliable and zero chance of any human error.

If Santa worked at Oracle, we?d have a database that only presented information to those who were truly authorized to see it.

If Santa worked at Oracle, I wouldn?t have to know which optimizer hint to use ? the optimizer would know which one to use and I could get back to doing nothing.

If Santa worked at Oracle, there would be no need to ever perform online reorgs.

If Santa worked at Oracle, data would be aged automatically and any row that had not been accessed in n years would be pruned from my database.

If Santa worked at Oracle, we might get more of a price break on Oracle software.

If Santa worked at Oracle, who would deliver presents to my kids on Christmas?

At DBI, we employ Agile software development techniques to allow us to deliver high quality software to the market faster. Our particular brand of Agile is Scrum and I've become quite a fan in some regards while realizing it is challenging at the same time.

Agile is great for developers. It is all about prototyping, working in teams and solving problems. A demo is given every two weeks and everyone can see the progress the product is making. The process is about being nimble and receptive to change - actually embracing it. This is where traditional "waterfall" methodologies fail ... go figure out everything up front (requirements, analysis, design) and then build it. The problem is that things change (they always do) and the more time that elapses, the more changes you will have.

The challenge with Agile from my perspective is implementing traditional roles (QA, tech writing) into the process. Since Scrum believes a feature can be completed as a team within one iteration (usually), this would include QA and docs (online help, manuals, etc.). The problem is that developers are coding up to the 11th hour before the deadline leaving no time for QA and tech writers to do their thing during THAT iteration. Therefore QA and writing typically lag an iteration even though they try to get as much done as early as possible.

Please chime in if you have ideas on how this process can be improved.

I recently presented a paper at the Australian Oracle Users Group in the beautiful city of Perth, Australia. The user group itself was very impressive and well organized. Aussie Chris Muir did a fine job of organizing and running this event. There were many attendees, many fantastic topics and a ton of interest in Oracle technologies. I really enjoyed my time there. The city of Perth is a true gem and completely unknown to most of the world. Isolated in Western Australia, it is a unique blend of beauty and culture unlike any other place in the world. And with a population of 1.5 million people, there are a few Oracle databases there too! The highlight of my trip was meeting Oracle guru Guy Harrison. Guy is a true Oracle expert and one that inspired me early in my career.

40,000 folks and everywhere to go.  Let?s talk Oracle at this years OpenWorld in San Francisco.  Although I have been to this show many times, I?m probably more excited about it this year than any before.   I missed the show last year, the first time in quite awhile.  When you?re an independent consultant, you are not being paid while you are there.  And while you gain more knowledge and contacts and potential future business, it is a more difficult decision ? so I skipped a year.    

I, for one, find the conference very useful and enjoyable.  I?ll attend most of the keynotes (not all) and technical sessions from sun up to sun down.  I tend to focus on performance tuning sessions and prefer a combination of Oracle and non-Oracle sponsored presentations.  Oracle speakers provide new content and direction while non-Oracle speakers tend to discuss solving real-world problems in the trenches. That?s a good balance. 

My official trip report will be posted here within a week after the show.   It will be full of technical content and laced with opinion.

Oh yeah, I?ll be speaking Tuesday morning at 10:00 a.m. ? I hope to see you there.   If you have not already created your schedule on-line, please hurry because the sessions are filling fast and only registered folks will be permitted in each session.     

http://www.oracle.com/openworld/index.html

I waited long enough and decided to finally download, install and use the SQL Developer (formerly Raptor) tool provided by Oracle.  It is written in Java and performs most tasks needed for SQL and PL/SQL development.  The interface is what you would expect ? an object tree on the left and a result view on the right that changes based on the context of the object or action that you have selected.   It is multi-platform (thanks Java) although Windows users will pay a small performance price for that.  I?m not sure what percentage of the Oracle developer does their work on a Linux box, but I?ll bet it?s not near the number on Windows.

I have high hopes for SQL Developer ? in fact I have high hopes for any free product that may help me.   However, I?m not sure I?m ready to trade in other [unnamed] tools that I use just yet.  While Oracle does seem to be on the right track, they have a little ways to go.  The interface is slow and I require something a bit more responsive.  It takes me 45 seconds just to connect to a database on my laptop.    Once connected to the database, it?s pretty neat.  I like the SQL result view including explain plan results as well as the output from DBMS_OUTPUT.  I also like the ease in which it enables debugging PL/SQL code.  Setting breakpoints and inspecting variables is a requirement for any developer tool and this one seems to do it very well.  Dragging around and resizing windows is easy but they all have that capability. 

The best thing about a free tool (as long as it stays that way) is that developers can use it everywhere they go.  For example, I?m not a big fan of vi but I know it?s going to be there for me.  Having a dependency on an expensive development tool can only be a barrier to developers when they change jobs or face reduced budgets in their current organizations.

I want Oracle to meet my expectations with this product ? I really do.   I know they have invested heavily in this tool and I am hopeful they will create a winner.    Is it worth the download?  Sure it is.  And it will only get better. 

The SQL Developer tool can be downloaded at: http://www.oracle.com/technology/products/database/sql_developer/index.html

There, I said it.  I?m not a fan of Oracle optimizer hints. In fact, I find that most of the time they are used as a cop-out to resolving the real problem.  Now I?m not talking about using an APPEND hint when doing a bulk insert load.  Instead, I?m referring to developers or DBAs searching for the one hint that might make the query go a little bit faster.  And they try one hint after another without actually thinking the problem through, just hoping that one hint will stick and provide a marginal performance enhancement.   

Here?s my beef with it ? the Oracle optimizer, while not perfect, is pretty dang good.  I don?t know how many people work on it but I?m sure they are some of the best developers/mathematicians/scientists in the world.   The optimizer is smart, however, there are a few situations in which I know more than the optimizer ? but these are the exception.  I try to resolve these situations by changing things in the environment that will lead the optimizer to a better decision.  And while a hint may do the trick in the short term, the optimizer is forever stuck on that execution path, even when better, more efficient paths are available. 

Remember, I knew more than the optimizer, or so I thought.  So while my statement elapsed time went from 1 minute to 48 seconds with the addition of my optimizer hint, it could have been executed in 2 seconds if I only added an index. 

So before you go for that "quick hit" and work your way through the hint list, analyze why the optimizer is doing what it is and figure out a way to help it without forcing your execution path down its throat. 

Every once in awhile an invention comes along that has the impact of changing lifestyle. I've been through this cycle a few times like we all have.

First, there's Quicken. I've used it religiously since 1990. I'm probably responsible for selling a hundred copies just by word of mouth. I actually look forward to paying my bills! I love creating fancy charts and reviewing our spending habits in monthly review sessions - all family members must attend. I break down and categorize exenses to the nth degree FOOD -> FAST FOOD -> TACO BELL -> BURRITOS. "Honey, we really need to cut back on our burrito consumption". Intuit makes Quicken and is one of the few (the only one) companies that I can think of that have continued to beat Microsoft in a space that Microsoft wants to capture. Msft Money? Give me a break. Quicken please. Keep it coming Intuit!

My latest addiction is the Blackberry. I've had good cell phones before but nothing like this. The features that I expect in a good phone are all there. The features that I expect in e-mail are there. The features I expect in a web browser are there. RIM got it right. Think about how many times you've traveled with your laptop simply for e-mail access. Not me - no more. Sure, it's addictive and I've missed some things in life while ordering Robin Yount memorabilia from E-Bay while watching a piano recital. But I was the lowest bidder! I'm convinced that anything that has such usefulness can be overused. It's a fine line and I'm sure I'll know when I cross it - I'll get an e-mail notifying me on my Blackberry.

What does it take to create a blockbuster product like these I mention? How have these companies been able to hit the bullseye when others have missed badly? I'm on a mission at DBI to create products that people cannot live without - products that they discuss with their neighbors while watering the yard. Products that make them ask "Why didn't I think of that?"

All it takes is ... hold on ... just got an e-mail ...

SQL is the language that we database professionals speak.  We use it to retrieve data from our business critcal systems millions of times each day.  We also use it as database tuners to get vital performance information from the data dictionary so we can optimize database performance.   We have water-cooler discussions about unions, sub-selects, outer joins ... and if we really want to impress our neighbors we start talking about cubes and merge operations - not for the recreational SQL coders!

SQL is fun - it's the challenge of determining the best way to get data out of the database.  It's about problem solving.  Sometimes we can change the schema to make our job easier but many times we cannot.  Instead, we must know what SQL can do and how we can make it hum to provide the data we need in a timely manner.

SQL just got more fun.  My discovery of the Baseball Data Bank (BDB) has improved my SQL skills without me even knowing it.  The BDB contains historical baseball statistics in CSV format.  It didn't take long to load that into Oracle and start hitting SQL homers.  I'm addicted and must get back to my normal life.  But wait ... just one more query ... overall do left handed hitters have a higher batting avg. than right handed hitters?  It won't be long before I devise a new strategy that will dominate baseball for decades, all because I know SQL.  The Yankees will soon be calling, I just know it. 

And just for the record ... Robin Yount is the best shortstop of all time:

SQL> select name from players where position = 6 and ranking = 1;
NAME
---------------------------------
ROBIN YOUNT



[ more.. ]

I've been using TKPROF to analyze Oracle trace files for what seems like forever. I like the tool, I really do. But I like it less since I've been using Trace Analyzer.

TA is a download available on Oracle Metalink. I refer to it as "TKPROF on Steroids" since it is really a TKPROF++ kind of tool. For TKPROF lovers, the switch to TA is an easy one. I've found that it gives me everything TKPROF does and more.

You know the routine ... an application experiences a degradation in performance and the phone starts buzzing. You enable system trace and generate a gazillion trace files. You use tkprof to process each one of the traces, sort by CPU and start reviewing the output looking for problematic SQL. Yeah, tkprof gives you execution stats per statement, explain plan (optionally), waits and binds, but there's more required than that. How about segment information for each segment referenced in the execution plan? How about a system to rank statements based on what I think is important. How about detailed wait event data, complete with the P1,P2,P3 values that we've all come to know and love. How about a nicely formatted HTML report with all of this data and more? Welcome to Trace Analyzer.

And to top all of that off, it's open source PL/SQL. I've learned more about Oracle tracing from TA source code than reading any particular article or book.  I applaud Mr. Carlos Sierra for creating such a useful utility. 

Please join me at IOUG Collaborate 2006 (April 23-27) in Nashville where I will present "Oracle Trace Analysis on Steriods".   I hope to see you there. 

Database performance tuning can be very challenging.   While tuning strategies are constantly evolving with each major release of the database, let me suggest one that is database independent.  I specialize in Oracle, not DB2, but Scott Hayes tells me this approach has merit in DB2, MySQL, Oracle or whatever DBMS you may utilize.  

1) I suggest that we tune an untuned system differently than we optimize a previously tuned one.    What are some of the things that we look at when tuning a database for the first time?  Table structures/design, indexing strategies, poor SQL statements,disk layout,  usage of bind variables, wait events, ratios (some still use them, etc.  If you agree with this, we can move on to #2. 

2)  If you determine what changed between the time when everything was great until now, you are well on your way to finding the problem.  You do not and should not start over at square one by tuning an untuned system (examining hit ratios, wait events, etc.)  - you have already done that!  Now you just need to find out what has changed.   

3)  What are some of the things that can change in our environment that could impact performance?  Many possibilities exist but some include:

• missing indexes (or new indexes)
  
• changed initialization parameters
  
• optimizer statistics
• new users, transactions, SQL
  
• new hardware
  

It makes sense to have an environmental capture script that runs at regular intervals just like performance monitoring scripts.   Environmental deltas over time periods could be correlated to performance snapshots over the same time period and DBAs would then have a more accurate picture of  "What's Changed and What is the Impact"? 

Finally, I will be presenting this topic and one other at the Houston Oracle Users Group on January 16, 2006.  Hope to see you there.