The last day of Open World has come and gone, along with good friends and a good time to explore new ideas. More 11g and RAC was had, along with the Applications Database Optimization Panel. It was lightly attended given the high powered panel members including Mark Farnham, Cary Millsap, Graham Wood and Ahmed Alomari. Rounding out the panel were Mike Brown from Colibri Limited, Sandra Vucinic from the VLAD Group, myself and a little help from Larry Klein of Hotsos. After the panel and lunch it was off to an E-Business Suite panel and then Jerry Ireland and a great BPEL presentation highlighting the differences from Workflow.

One of the things that I took away from the conference is that performance tuning seems to have gone back to knob turning and looking at aggregated data. I guess the Oracle DBA profession is no different than any other human endeavor in that people seem to keep reinventing the wheel. ASH data and OEM are the current hot topics and there must be answers to our performance problems somewhere in all that data. I actually thought the “debate” was over, but apparently someone didn’t get the message. So, just in case anyone is listening, performance tuning is:

Response time optimization using the Hotsos Method R methodology

Workload measurement to insure resource headroom

Workload reduction using the AppsDBA methodology

The after conference party was very nicely done and not too crowded and all in all I think this was the best Open World yet.

Another day filled with RAC and E-Business Suite tuning. The RAC presentations all seem to have the same themes. Partition the workload, configure the interconnect for most efficient bandwidth and run efficient SQL. I’ve been disappointed that nobody has really talked about global locking issues, partitioning for RAC (although I think there was one session that was supposed to address partitioning in more detail), and actual implementation and then supporting RAC in detail. Oh well, maybe that means there is an opportunity to provide that information because there sure seems to be a lot of interest in RAC.

I was pleasantly surprised at the last session I went to. Neal Nelson from Neal Nelson and Associates gave a very nice talk about benchmarking that was very lightly attended. Too bad for everyone else because Neal did a nice job of highlighting the value of independent benchmarking by showing an interesting example of the real cost of virtualization and also a comparison between an Intel Xeon processor and an AMD Opeteron processor. He even took a look at power consumption as an added bonus. I should say that way back in the early 1990’s I worked on a project that used Neal’s RTE tool on some early benchmarking of a UTS system and Sun Solaris machines so I was familiar with some of Neal’s work.

Monday and Tuesday were pretty good days. It’s crowded, but manageable and I haven’t had problems attending the sessions that I’ve wanted to see. I was disappointed to learn that I missed Tom Kyte at an OTN meet and greet on Monday. He said it wasn’t that crowded and everyone had a beer in their hand except him. I did get to see him this evening but it was a very crowded session.

There’s been a lot of good information about 11g. I’m really interested in the new Advanced Compression feature. The Active Data Guard and PL/SQL cache also look like big improvements. I think that the advanced compression feature will be compelling enough to motivate me to upgrade a 10g RAC system that I support to 11g.

On another note, Mark Farnham pointed me to an old ACM paper (you may or may not be able to download this paper – I downloaded it this morning for free, but it looks like that may have changed) that provides a lot of background on Oracle’s B*-Tree indexes, or as it appears from this paper, and should be more accurately referred to as a modified B-link Tree indexes.

Today was the first day of Open World, and being a Sunday and basically a SIG day it was pretty tame. I did see a couple of good presentations with the OAUG DB SIG and the IOUG RAC SIG.  Ahmed Alomari of Cybernoor gave a very good Oracle Applications performance presentation at the OAUG DB SIG meeting, and Murali Vallath gave a nice presentation on RAC parallel query features at the IOUG RAC SIG. It appears from the schedule that RAC is definitely going to be a big topic at Open World this year. Having spent the better part of the last year working on an Oracle RAC project it is interesting to see all of the different aspects of RAC being discussed.

Saying hello to old friends is always a great benefit of any conference and today was no exception. I ran into a lot of people I haven’t seen for too long and I met some new people as well. All in all it was a pleasant day. We’ll have to wait and see what the crowds are like as the conference kicks off tonight and tomorrow.

It was pointed out to me today that probably one of the main reasons not to use RMAN is if you’re doing split mirror backups or any kind of “snapshot” type backups. Sites that do this will typically put their database in “backup” mode and either break their mirror or snapshot their database. The database can then be taken out of backup mode and the actual data can be backed up. This seems like a pretty legitimate reason for not using RMAN. The other reason given is usually one of trust. By that I mean that RMAN is not really “trusted” to be reliable enough as a backup strategy. This one I have a harder time with, but the fact is that the impression is out there that RMAN may not be as reliable or as easy to use or <you insert reason here> than a good old fashioned hot backup.

It always amazes me how few DBAs seem to actually use RMAN. It’s been available since Oracle 8.0, and before that Oracle7 had Obackup and Enterprise Backup Utility (EBU). RMAN really is a powerful product and certainly provides for robust backup and recovery. If you’ve ever struggled during a recovery to figure out which tapes or other media that is needed then I think you’ve found the biggest appeal of RMAN. That is, reliable recovery. So, how come RMAN is not a de facto standard? Well I was training a DBA recently on setting up backups and testing recovery with a database duplication, and I was reminded of how truly difficult it is to explain all of the details and nuances of RMAN, let alone learn how to use it. The command syntax is complicated for anything but a basic backup and recovery, and the error messages and diagnostics are pretty awful even when you think you understand what’s going on.

It’s not hard then to realize why so many DBAs seek other alternatives. OEM appears on the surface to make the RMAN interface easier, but as soon as you try something other than a basic backup (like say a duplication) things get even more complicated because now you have to figure out what OEM expects on top of what is required in RMAN. It’s unfortunate because I think RMAN has a lot to offer and I always recommend it’s use whenever possible. It would just be nice if Oracle would really spend the time and improve the interface and the error messages.

Recently I had a problem with some PL/SQL where I was doing some date arithmetic, addition actually. The code had in it a line like:

l_partdt := l_partdt + 1;

The intent was to add one day to the partition date which was of type DATE. It worked well enough (yes, perhaps I should have used the NEXT_DAY function) until my partition key changed (for another partitioned table) from being day based to being month based (i.e. YYYYMMDD to YYYYMM). Now, there are two SQL functions available for these cases, NEXT_DAY and ADD_MONTHS, but the thought ocurred to me that there really wasn’t a good way to generically add a value to a date based on a particular format. In other words, adding a year, month, day, hour, minute, etc. As it turns out there are two other SQL functions available to help with this, NUMTODSINTERVAL and NUMTOYMINTERVAL. So I wrote a function using those two SQL functions to handle my generic date based on a supplied format:

CREATE OR REPLACE
FUNCTION Add_Dates (
p_date IN DATE,
p_increment IN NUMBER,
p_format IN VARCHAR2,
p_debug IN CHAR DEFAULT 'N' )
RETURN DATE
AS
--
l_interval VARCHAR2(20);
l_retval DATE;
--
exc_badinput EXCEPTION;
exc_badinterval EXCEPTION;
BEGIN
IF v_debug = 'Y' THEN
dbms_output.put_line('Add_Dates');
END IF;
--
-- Decode the input format to determine increment value
--
CASE p_format
WHEN 'YYYY' THEN
l_interval := 'YEAR';
WHEN 'YYYYMM' THEN
l_interval := 'MONTH';
WHEN 'YYYYMMDD' THEN
l_interval := 'DAY';
WHEN 'YYYYMMDDHH' THEN
l_interval := 'HOUR';
WHEN 'YYYYMMDDHH24' THEN
l_interval := 'HOUR';
WHEN 'YYYYMMDDHHMI' THEN
l_interval := 'MINUTE';
WHEN 'YYYYMMDDHH24MI' THEN
l_interval := 'MINUTE';
WHEN 'YYYYMMDDHHMISS' THEN
l_interval := 'SECOND';
WHEN 'YYYYMMDDHH24MISS' THEN
l_interval := 'SECOND';
ELSE RAISE exc_badinput;
END CASE;
--
CASE
WHEN l_interval IN ('YEAR','MONTH') THEN
l_retval := p_date + NUMTOYMINTERVAL(p_increment, l_interval);
WHEN l_interval IN ('DAY','HOUR','MINUTE','SECOND') THEN
l_retval := p_date + NUMTODSINTERVAL(p_increment, l_interval);
ELSE RAISE exc_badinterval;
END CASE;
--
RETURN l_retval;
EXCEPTION
WHEN exc_badinput THEN
-- Your error handler here
-- p_sqlcode=>SQLCODE,
-- p_message=>'Add_Dates - Error - bad input, interval could not be determined',
--
RAISE;
WHEN exc_badinterval THEN
-- Your error handler here
-- p_sqlcode=>SQLCODE,
-- p_message=>'Add_Dates - Error - invalid interval used',
--
RAISE;
WHEN OTHERS THEN
-- Your error handler here
-- p_sqlcode=>SQLCODE,
-- p_message=>'Add_Dates - Exception handler',
--
RAISE;
END Add_Dates;

Examples

Add one month:

SQL> select to_char(add_dates(to_date('200710','YYYYMM'),1,'YYYYMM'),'YYYYMM') from dual;

TO_CHA

------

200711

SQL>

Add one day:

SQL> select add_dates(to_date('10/31/07','MM/DD/YY'),1,'YYYYMMDD') from dual;

ADD_DATES

---------

01-NOV-07

SQL>

Add one hour:

SQL> select to_char(add_dates(to_date('20071031150000','YYYYMMDDHH24MISS'),1,'YYYYMMDDHH24'),'YYYYMMDDHH24MISS') from dual;

TO_CHAR(ADD_DA

--------------

20071031160000

SQL>

orasetup has had the ability for quite a while now to set the PATH to add the OPatch directory for the ORACLE_HOME that opatch is being run in. However, I don’t think I’ve ever told anyone about it. After using Oracle’s opatch utility to patch way too many Oracle installations (both database and app server) I decided to add to orasetup the ability to add the OPatch directory into the PATH so I wouldn’t keep having to do it by
hand. The following is a typical setup:

log in as oracle
$ . orasetup DBSID
$ . orasetup opatch
$ opatch version

Run the “opatch version” command to see which version of opatch
is installed and whether it needs to be updated for the
patch that is being applied. Now in earlier releases (I believe prior
to 10.2) the OPatch directory wasn’t included in the Oracle
distribution. In this case orasetup will tell you that it’s not
there:

. orasetup opatch

$ . orasetup opatch
OPatch directory not found. Make sure to set the environment first.
$

The message about setting the environment is a reminder that first you have run orasetup for the ORACLE_HOME environment, otherwise it doesn’t know where to look for the directory. Of course if you’ve already done that then you know that you don’t have the utility in the $ORACLE_HOME directory.

Once orasetup has been run then opatch is in the PATH and:

$ . orasetup DBSID
$ . orasetup opatch
$ opatch version

Oracle Interim Patch Installer version 1.0.0.0.57
Copyright (c) 2007 Oracle Corporation. All Rights Reserved..

We recommend you refer to the OPatch documentation under
OPatch/docs for usage reference. We also recommend using
the latest OPatch version. For the latest OPatch version
and other support related issues, please refer to document
293369.1 which is viewable from metalink.oracle.com

OPatch Version: 1.0.0.0.57

$