The Cost of Mediocre

Over the years I've written tons of scripts to monitor all sorts of things on the database.  Some of those scripts turned out to be helpful on an ongoing basis and some ended up dying on the vine when new features of Oracle came out.

One of my scripts looks at the top processes in a database from the CPU's perspective.  In other words, how much time did the process use on the CPU over it's entire lifetime.  About a month ago I identified a process that was using a disproportionate amount of time as compared to everything else.  It was a new process, so I was pretty sure something could be tuned.  I assigned another DBA to trace it and make tuning suggestions.  One particular query that was doing a Full Table Scan was identified as the major time consumer and suggestions were made on how to fix the query.

Over the last few weeks, the developer had been busy.  When I enquired as to the status of the slow query the response was that the job runs in an acceptable time and nothing would be done on it until later in the month.

Flash forward to a couple days ago when the process showed up on my report again.  We had burned about three hours of CPU time just doing Full Table Scans all day long.  I created an index on some fields that I thought were appropriate and a single execution of the query when from 44+ seconds to .04 seconds.  My report the next day showed the process down to about 10 minutes of CPU time.

The kicker: The developer said "Oh, I thought of that a month ago but never implemented it."  Meanwhile, I burned about 84 hours of CPU time.

Upgrading to Oracle 11gR2

I am about half way through upgrading all my dbs to Oracle 11gR2 from various versions of 10gR2.  Out of all the upgrades I've performed from 7.0 up to 10gR2, the 10g to 11g upgrade is by far the most hassle free of all.

I have abandoned the GUI upgrade tool this time.  The GUI tool is fine, but internal issues prevented me from running the GUI remotely.  This meant that somebody had to be on-site to push buttons for each and every 9i to 10g upgrade.  I have moved to a command-line upgrade that I have automated through a series of ksh scripts.  I feed the right parameters to the script and two hours later it's done.  No fuss, no muss.

The most important thing I have learned so far is that utlu112i.sql is your friend.  If utlu112i.sql even hints that something might go wrong during your upgrade, you better fix it.  For example, it's a heck of a lot easier to fix JVM issues before the upgrade rather than during the upgrade.  You have to trust the utlu112i.sql script.  If it thinks you don't have enough space in your SYSTEM tablespace, you probably don't.  utlu112i.sql is a little cautious on some stuff like the DBMS_LDAP warnings, but it's best to understand what all the warnings mean.

My general approach has been to upgrade what I have and make sure everything works on 11g before I start to exploit the 11g features.

Full Speed Ahead!  To infinity and beyond!

Seriously?

I haven't done any research on this yet, so I don't know if it is fixed in a subsequent patch or a later version.  But seriously, a space?

SQL> select * from v$version;

BANNER                                                                          
----------------------------------------------------------------                
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi                
PL/SQL Release 10.2.0.3.0 - Production                                          
CORE 10.2.0.3.0 Production                                                      
TNS for Linux: Version 10.2.0.3.0 - Production                                  
NLSRTL Version 10.2.0.3.0 - Production                                          

SQL> select trigger_name, triggering_event from user_triggers;

TRIGGER_NAME                   TRIGGERING_EVENT                                 
------------------------------ ------------------------------                   
DDL1                           DDL                                              
LT1                            LOGON                                            
LT2                            LOGON                                            

SQL> select trigger_name, triggering_event from user_triggers
  2  where triggering_event = 'LOGON';

no rows selected

SQL> select trigger_name, '^' || triggering_event || '^' triggering_event
  2  from user_triggers;

TRIGGER_NAME                   TRIGGERING_EVENT                                 
------------------------------ ------------------------------                   
DDL1                           ^DDL ^                                           
LT1                            ^LOGON ^                                         
LT2                            ^LOGON ^