It turns out that there is an Oracle Support note regarding the problems for pre-11.2 databases with an OCR in ASM. It’s publish date is March 11 and I was just made aware of it. The note number is 948456.1 and it describes the problem I addressed along with a couple of more. All of these have been filed as bugs and it looks like there are fixes on the way.

So what makes an expert? Just because you tell me you’re an expert, and we’ve all seen or heard from those folks, doesn’t mean that you are one. And why are we so reliant on experts anyway? Does it absolve us of responsibility? After all, if the expert said it was the right thing to do then it’s not our fault it didn’t work out, right?

A person that I consider to be an expert at one thing might not be very good at something else. For instance, an expert in Oracle database technology might be lost trying to replace the water pump in my big block Chevy. In fact, I think I’d consider the guy that can replace the water pump in my V6 Camry to be an expert. I can do the big block and I do have a lot of experience, but the Camry V6 is a completely different matter. So part of being an expert is having done the thing you’re an expert at. That’s why I have a hard time with people who read something, especially an Oracle manual, and then proclaim themselves an expert. They fail my expert test because they have no experience.

Now certainly intelligence has something to do with it as well, but I doubt that the fellow that can replace my Camry V6 water pump has a PhD. That doesn’t mean he’s stupid, but I really don’t think you have to be a brain surgeon to be an expert.  So another requirement is that you have to be smart at the thing you’re an expert in.

The problem so far is that there are lots of folks who are smart and experienced who don’t consider themselves to be experts. Maybe the missing piece is that experts have to be willing to back up what they say with evidence. After all, if you can’t prove what you are saying then that hardly makes you an expert. But if you’re willing to take a stand on something and prove your statement with evidence then maybe that too is part of being an expert. Being willing to do the research to prove or disprove ideas and thoughts is my third requirement of being an expert.

So the next time you hear someone say the they need an expert, or proclaim themselves to be an expert, make sure they have the requirements to back it up, and most importantly make sure that they’re willing to prove what they say.

Recently I’ve been reminded about why it’s so important to consider the rate of work in addition to any “wait events” when optimizing response time. I first coined the term “Rates and Waits” for the paper, “Oracle Workload Management Using Time Based Optimization Techniques” way back in 2003, and the point I was making then, as now, is that it’s the rate of work performed that’s really important.

After all, service time in the formula “response time = service time + wait time”[1] is all about the rate of work performed. The trap of considering only wait events for “tuning” is just that, a trap. Deciding between important wait events or so called idle wait events, and trying to determine the real impact of them is impossible unless the interval being measured and how much work was performed is known. Why? Because there is no context to measure against. Without knowing the impact of the time measured there is no way to judge the time spent waiting. If the interval was 1 second then waiting for 0.5 seconds is probably significant, but if the interval was 10 seconds then it probably isn’t. This is why time based profiles are so important.

When considering system level workload, rates become even more important, since wait events at the system level are essentially useless. Recall that at the system level there is an infinite capacity to wait. After all, more than one process can wait on something, but each service (whether CPU, I/O or network) can only service one request at a time. With system level workload it’s all about capacity and throughput. Capacity is the maximum amount of “service” that a system or resource can perform and throughput is the measure of that service.

The following blog post by Tom Kyte (http://tkyte.blogspot.com/2008/01/why-do-people-do-this.html) prompted me to re-evaluate my error handling in the PL/SQL that I’ve written for a large project that I’m working on. Tom feels very strongly about exception handling, and while I don’t disagree, I hadn’t considered the value of the originating line number as all that important. In reading some of the sources cited at the end of this article I got curious about seeing just how hard it would be to save a little more information about the original error, including the original line number. The following are the results of my investigation.

I should note that all of this was done with Oracle 10g and utilizes the DBMS_UTILITY and its “format” functions. Earlier releases won’t be able to make use of some/all(?) of these features, but I guess that’s the price of progress. I offer the following basic tenets of exception handling with a nod to both Tom and Steven Feuerstein for the inspiration:

1) Trap exceptions as close to the point of the error being raised as possible.

2) Never hide an exception. Exceptions should be raised because of errors and errors should always be propogated to the calling function (the client application in Tom speak).

2a) If a WHEN OTHERS is used then it MUST re-raise the exception.

3) Always capture the line number and error message where the exception is raised (this can be hard if you’re following number 1).

4) Handle all exceptions that can be handled. Do the work up front rather than forcing someone to debug your “generic” exceptions later.

The following example is taken straight from the Oracle documentation (1) with the exception of changes to the “Top_With_Logging” procedure. I’ve commented the original code in the exception handler and added calls for each of the three DBMS_UTILITY “format” functions.


CREATE OR REPLACE PROCEDURE Top_With_Logging IS
-- NOTE: SqlErrm in principle gives the same info as Format_Error_Stack.
-- But SqlErrm is subject to some length limits,
-- while Format_Error_Stack is not.
BEGIN
P5();
EXCEPTION
WHEN OTHERS THEN
-- Log_Errors ( 'Error_Stack...' || Chr(10) ||
-- DBMS_UTILITY.FORMAT_ERROR_STACK() );
-- Log_Errors ( 'Error_Backtrace...' || Chr(10) ||
-- DBMS_UTILITY.FORMAT_ERROR_BACKTRACE() );
--
DBMS_OUTPUT.PUT_LINE ( '----------' );
DBMS_OUTPUT.PUT_LINE ( 'FORMAT_CALL_STACK:' );
DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_CALL_STACK() );
--
DBMS_OUTPUT.PUT_LINE ( '----------' );
DBMS_OUTPUT.PUT_LINE ( 'FORMAT_ERROR_STACK:' );
DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_STACK() );
--
DBMS_OUTPUT.PUT_LINE ( '----------' );
DBMS_OUTPUT.PUT_LINE ( 'FORMAT_ERROR_BACKTRACE:' );
DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE() );
--
DBMS_OUTPUT.PUT_LINE ( '----------' );
END Top_With_Logging;
/


The following will show the creation of each of the test procedures
as shown in the original example:

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Jan 31 09:12:37 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> CREATE OR REPLACE PROCEDURE P0 IS
e_01476 EXCEPTION; pragma exception_init ( e_01476, -1476 );
BEGIN
RAISE e_01476;
END P0;
/ 2 3 4 5 6

Procedure created.

SQL> l
1 CREATE OR REPLACE PROCEDURE P0 IS
2 e_01476 EXCEPTION; pragma exception_init ( e_01476, -1476 );
3 BEGIN
4 RAISE e_01476;
5 END P0;
6*
SQL> CREATE OR REPLACE PROCEDURE P1 IS
BEGIN
P0();
END P1;
/
SHOW ERRORS

CREATE OR REPLACE PROCEDURE P2 IS
BEGIN
P1();
END P2;
/
SHOW ERRORS

CREATE OR REPLACE PROCEDURE P3 IS
BEGIN
P2();
END P3;
/
SHOW ERRORS

CREATE OR REPLACE PROCEDURE P4 IS
BEGIN P3(); END P4;
/
CREATE OR REPLACE PROCEDURE P5 IS
BEGIN P4(); END P5;
/
SHOW ERRORS 2 3 4 5
Procedure created.

SQL> No errors.
SQL> SQL> 2 3 4 5
Procedure created.

SQL> No errors.
SQL> SQL> 2 3 4 5
Procedure created.

SQL> No errors.
SQL> SQL> 2 3
Procedure created.

SQL> 2 3
Procedure created.

SQL>
No errors.
SQL>
SQL> CREATE OR REPLACE PROCEDURE Top_Naive IS
BEGIN
P5();
END Top_Naive;
/ 2 3 4 5

Procedure created.

SQL> CREATE OR REPLACE PROCEDURE Top_With_Logging IS
-- NOTE: SqlErrm in principle gives the same info as Format_Error_Stack.
-- But SqlErrm is subject to some length limits,
-- while Format_Error_Stack is not.
BEGIN
P5();
EXCEPTION
WHEN OTHERS THEN
-- Log_Errors ( 'Error_Stack...' || Chr(10) ||
-- DBMS_UTILITY.FORMAT_ERROR_STACK() );
-- Log_Errors ( 'Error_Backtrace...' || Chr(10) ||
-- DBMS_UTILITY.FORMAT_ERROR_BACKTRACE() );
--
DBMS_OUTPUT.PUT_LINE ( '----------' );
DBMS_OUTPUT.PUT_LINE ( 'FORMAT_CALL_STACK:' );
DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_CALL_STACK() );
--
DBMS_OUTPUT.PUT_LINE ( '----------' );
DBMS_OUTPUT.PUT_LINE ( 'FORMAT_ERROR_STACK:' );
DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_STACK() );
--
DBMS_OUTPUT.PUT_LINE ( '----------' );
DBMS_OUTPUT.PUT_LINE ( 'FORMAT_ERROR_BACKTRACE:' );
DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE() );
--
DBMS_OUTPUT.PUT_LINE ( '----------' );
END Top_With_Logging;
/

2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
Procedure created.

SQL>

Now we’ll run the first example calling the Top_Naive procedure which will simply call the P5 procedure which will invoke the chain of procedures with the P0 procedure raising an error. The Top_Naive procedure has no exception handling so we see the full chain of events. Notice that the initial exception that occurs in P0 tells us the line number of the offending statement (during the install above I listed the PO procedure to show the line numbers).

SQL> Set ServerOutput On
call Top_Naive()SQL>
2 /
call Top_Naive()
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "TEST.P0", line 4
ORA-06512: at "TEST.P1", line 3
ORA-06512: at "TEST.P2", line 3
ORA-06512: at "TEST.P3", line 3
ORA-06512: at "TEST.P4", line 2
ORA-06512: at "TEST.P5", line 2
ORA-06512: at "TEST.TOP_NAIVE", line 3

Now we’ll run the second example and see the bare output of each of the three DBMS_UTILITY “format” functions:

SQL> Set ServerOutput On
SQL> call Top_With_Logging();
----------
FORMAT_CALL_STACK:
----- PL/SQL Call Stack -----
object line object
handle number name
0xee5cbec8 16 procedure TEST.TOP_WITH_LOGGING

----------
FORMAT_ERROR_STACK:
ORA-01476: divisor is equal to zero

----------
FORMAT_ERROR_BACKTRACE:
ORA-06512: at "TEST.P0", line 4
ORA-06512: at "TEST.P1", line 3
ORA-06512: at "TEST.P2", line 3
ORA-06512: at "TEST.P3", line 3
ORA-06512:
at "TEST.P4", line 2
ORA-06512: at "TEST.P5", line 2
ORA-06512: at "TEST.TOP_WITH_LOGGING", line 6

----------

Call completed.

SQL>

We see that the FORMAT_CALL_STACK function only shows us the information from the call to the FORMAT_CALL_STACK function. The FORMAT_ERROR_STACK call shows us the actual error message message only, and the FORMAT_ERROR_BACKTRACE call shows us the full execution path with originating line number. Note that we still need the FORMAT_ERROR_STACK call in order to get the original error message.

Another way to achieve the same thing would be to capture the exception at the source. The following shows a different take on handling the exception and being able to capture the line number and error message. This example simply outputs the messages using DBMS_OUTPUT, but in reality this could be logged to an error table:

CREATE OR REPLACE PROCEDURE P0 IS
e_01476 EXCEPTION; pragma exception_init ( e_01476, -1476 );
BEGIN
RAISE e_01476;
EXCEPTION
WHEN others THEN
dbms_output.put_line(SQLERRM);
dbms_output.put_line(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE());
raise; -- Note that we still raise an exception!
END P0;
/

SQL> CREATE OR REPLACE PROCEDURE P0 IS
e_01476 EXCEPTION; pragma exception_init ( e_01476, -1476 );
BEGIN
RAISE e_01476;
EXCEPTION
WHEN others THEN
dbms_output.put_line(SQLERRM);
dbms_output.put_line(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE());
raise;
END P0;
/ 2 3 4 5 6 7 8 9 10 11

Procedure created.

SQL> call Top_Naive();
ORA-01476: divisor is equal to zero
ORA-06512: at "TEST.P0", line 4

call Top_Naive()
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "TEST.P0", line 9
ORA-06512: at "TEST.P1", line 3
ORA-06512: at "TEST.P2", line 3
ORA-06512: at "CPPTEST.P3", line 3
ORA-06512: at "TEST.P4", line 2
ORA-06512: at "TEST.P5", line 2
ORA-06512: at "TEST.TOP_NAIVE", line 3

SQL>

Now we see that the error message and line number are output and then the exception is propogated back to the initial invoking procedure. This provides an easy way to trap all exception information and provides the possiblity of capturing the entire exception path. This may not be as important for an interactive system, but can be invaluable for a “batch” system running in an automated fashion.

Sources:

(1) Oracle® Database PL/SQL Packages and Types Reference, 10g Release 2 (10.2),
Part Number B14258-01, Section on DBMS_UTILITY

(2) http://www.oracle.com/technology/oramag/oracle/05-mar/o25plsql.html

(3) http://tkyte.blogspot.com/2008/01/why-do-people-do-this.html

(4) http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:433029981484

(5) http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4684561825338

Recently I’ve had the opportunity to get more familiar with the Oracle scheduler. In the example I will talk about here, the scheduler was used to automate the refresh of a materialized view and an Oracle Text (i.e. context) index associated with that view. As of Oracle 10.2 when you create a materialized view Oracle will use the old dbms_jobs interface to automagically build an automatic refresh job. In this case I decided to use the newer dbms_scheduler interface since I wanted to tie the materialized view refresh and the Oracle Text index refresh together since the index is dependent on the materialized view. This requires that you first create the materialized view as an “ON DEMAND” job and then write your own commands to create the scheduler pieces to run the refresh.

As it turns out this is more involved than one might think. To do this involves creating a program definition for each “step”, defining a chain and the steps in that chain, and then chain rules to tell Oracle what to do when a step completes successfully or errors out. Lastly you actually define the job that will run in the scheduler. The following shows the syntax for each of these steps.

--
-- Create the scheduler programs
--
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM (
program_name => 'TABLE_MV_PGM',
program_type => 'PLSQL_BLOCK',
program_action => 'BEGIN DBMS_MVIEW.REFRESH(''TABLE_MV'', ''F'', ''''); END;',
enabled => TRUE,
comments => 'Refresh materialized view');
END;
/
--
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM (
program_name => 'TABLE_MV_CTXIDX_PGM',
program_type => 'PLSQL_BLOCK',
program_action => 'BEGIN ctxsys.drvdml.auto_sync_index(''TABLE_MV_CTXIDX'', 12582912, NULL, NULL, NULL, 0); END;',
enabled => TRUE,
comments => 'Refresh text index');
END;
/
--
-- Define the job chain
--
BEGIN
DBMS_SCHEDULER.CREATE_CHAIN (
chain_name => 'TABLE_CHAIN',
rule_set_name => NULL,
evaluation_interval => NULL,
comments => 'Job Chain to refresh TABLE materialized view');
END;
/
--
-- Define the chain steps
--
BEGIN
DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
chain_name => 'TABLE_CHAIN',
step_name => 'Step1',
program_name => 'TABLE_MV_PGM');
END;
/
--
BEGIN
DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
chain_name => 'TABLE_CHAIN',
step_name => 'Step2',
program_name => 'TABLE_MV_CTXIDX_PGM');
END;
/
--
-- Define the chain rules
--
BEGIN
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
chain_name => 'TABLE_CHAIN',
condition => 'TRUE',
action => 'START Step1',
rule_name => 'TABLE_Rule1',
comments => 'Start the chain');
END;
/
--
BEGIN
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
chain_name => 'TABLE_CHAIN',
condition => 'Step1 COMPLETED',
action => 'START Step2',
rule_name => 'TABLE_Rule2');
END;
/
--
BEGIN
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
chain_name => 'TABLE_CHAIN',
condition => 'Step1 COMPLETED AND Step2 COMPLETED',
action => 'END',
rule_name => 'TABLE_Rule3');
END;
/
--
-- Enable the chain
--
BEGIN
DBMS_SCHEDULER.ENABLE ('TABLE_CHAIN');
END;
/
--
-- Create the job
--
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'TABLE_JOB',
job_type => 'CHAIN',
job_action => 'TABLE_CHAIN',
repeat_interval => 'freq=daily;byhour=23;byminute=0;bysecond=0',
enabled => TRUE);
END;
/

It is possible to set this up in Enterprise Manager, although I had problems with the Grid Control version and found it easier to use the scripts above to create the programs, chain and job. I do however find it much easier to administer the scheduler components in Grid Control once it is set up. I have not been as impressed with the Database Control flavor of Enterprise Manager though, and I’m not sure why the two are different for administering the scheduler.

I’ve been using the timestamp data type to capture timing information for a process and my first try at it didn’t work very well. I don’t think Oracle’s documentation is very good at helping figure them out either, so I thought I’d share the PL/SQL that I used to decipher what I needed to make use of timestamp data:

declare
l_starttim TIMESTAMP;
l_elapsedtim INTERVAL DAY TO SECOND;
p_tottime VARCHAR2(100);
l_loadtime VARCHAR2(100);
begin
l_starttim := SYSTIMESTAMP;
dbms_lock.sleep(5);
IF l_elapsedtim IS NULL THEN
l_elapsedtim := SYSTIMESTAMP-l_starttim;
ELSE
l_elapsedtim := l_elapsedtim + (SYSTIMESTAMP-l_starttim);
END IF;
--
dbms_output.put_line(to_char(l_starttim));
dbms_output.put_line(to_char(l_elapsedtim));
--
l_starttim := SYSTIMESTAMP;
dbms_lock.sleep(5);
IF l_elapsedtim IS NULL THEN
l_elapsedtim := SYSTIMESTAMP-l_starttim;
ELSE
l_elapsedtim := l_elapsedtim + (SYSTIMESTAMP-l_starttim);
END IF;
--
dbms_output.put_line(to_char(l_starttim));
dbms_output.put_line(to_char(l_elapsedtim));
--
p_tottime := TO_CHAR(l_elapsedtim);
dbms_output.put_line('p_tottime: '||p_tottime);
DBMS_OUTPUT.PUT_LINE('Hour: '||extract(HOUR FROM l_elapsedtim));
DBMS_OUTPUT.PUT_LINE('Minute: '||extract(MINUTE FROM l_elapsedtim));
DBMS_OUTPUT.PUT_LINE('Seconds: '||ROUND(extract(SECOND FROM l_elapsedtim),2));
--
l_loadtime := SUBSTR(TO_CHAR(extract(HOUR FROM l_elapsedtim),'00'),2)||':'||
SUBSTR(TO_CHAR(extract(MINUTE FROM l_elapsedtim),'00'),2)||
':'||SUBSTR(TO_CHAR(ROUND(extract(SECOND FROM l_elapsedtim),2),'00.99'),2);
dbms_output.put_line('l_loadtime: '||l_loadtime);
end;
/

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.