I’ve been a proponent of using services both to help categorize database connections and to use them to help classify workload. For applications where “you can’t touch the code” this can be the only way to come up with categories to measure application workload. Recently I’ve started using a “backup” service to identify the cost backups contribute to overall system workload. The problem with services however is that there are only limited ways to associate a service to a connection. For “internal” connections a service can be associated with a scheduler job through a job class, and can also be associated with parallel execution or parallel DML, or with advanced queuing (although I’ve only used scheduler jobs with services). As for client connections, the service is assigned with the “SERVICE_NAME” parameter on the connect string. This implies a connection through the listener since this is typically part of Oracle net services. This can pose a problem for backups since this creates, in my opinion, an unnecessary dependence on the listener being up in order to take advantage of running the backup process through a service.

Typically a backup program, and we’ll talk about RMAN here but it could be any product really, will connect to the database through a “bequeath” connection by setting the ORACLE_SID environment variable as part of the environment setup. This type of connection doesn’t require that a listener be available, and also doesn’t need a tnsnames.ora entry. Unfortunately there is also no way to associate the connection with a service that I’ve found. However, I have stumbled upon a solution. It turns out that it is possible to define a “bequeath” connection in the tnsnames.ora file and subsequently use the SERVICE_NAME parameter as part of the connect string. This type of connection, even though part of the tnsnames.ora file, doesn’t require a listener to work. I stumbled upon this in Metalink note 132764.1, and it seems that it has always existed, at least since Oracle7 and SQL Net V2, based on some of the other notes available on Metalink.

The following will show an example of setting up the TNS entry and then setting up the backup service and running RMAN connected to the new backup service.

The first task is to create the service in the database. The following example is for a single instance database, in RAC you should use srvctl.

SQL> show parameters service

NAME                         TYPE        VALUE
---------------------------- ----------- -------------------------------------------
service_names                string      orcl.appsdba.com
SQL> alter system set service_names = "orcl.appsdba.com,backup.appsdba.com" scope=both;

System altered.

SQL> show parameters service

NAME                         TYPE        VALUE
---------------------------- ----------- -------------------------------------------
service_names                string      orcl.appsdba.com,backup.appsdba.com
SQL>

Verify the listener service registration:

$ lsnrctl services

LSNRCTL for Linux: Version 10.1.0.5.0 - Production on 25-JUN-2008 10:12:00

Copyright (c) 1991, 2004, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=orcl)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "backup.appsdba.com" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:1 refused:0 state:ready
         LOCAL SERVER
Service "orcl" has 1 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "orcl.appsdba.com" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:1 refused:0 state:ready
         LOCAL SERVER
The command completed successfully
$

Let’s show the service registration in the database:

SQL> select * from v$services where service_id = 10;

SERVICE_ID NAME                       NAME_HASH
---------- -------------------------- ----------
NETWORK_NAME
------------------------------------------------
CREATION_ CREATION_DATE_HASH
--------- ------------------
        10 backup.appsdba.com         833319543
backup.appsdba.com
25-JUN-08          638794112

SQL>

Now, we’ll show the tnsnames.ora entry. Note that the protocol is “beq” for bequeath and that we’ve used the SERVICE_NAME for our backup service. Also note that as part of the definition we’ve concatenated the “oracle” executable name and our oracle SID name for the ARGV0 value.

ORCL_BACKUP =
  (DESCRIPTION =
    (ADDRESS =
      (PROTOCOL = BEQ)
      (PROGRAM = oracle)
      (ARGV0 = oracleorcl)
      (ARGS = '(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))')
    )
    (CONNECT_DATA =
      (SERVICE_NAME = backup.appsdba.com)
    )
  )

Now let’s test the database connection:

$ sqlplus system@orcl_backup

SQL*Plus: Release 10.1.0.5.0 - Production on Wed Jun 25 10:19:06 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password: 

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select USERNAME, SERVER, PROGRAM, STATUS, SERVICE_NAME
from v$session where username = 'SYSTEM';

USERNAME                       SERVER    PROGRAM                            STATUS
------------------------------ --------- ---------------------------------- --------
SERVICE_NAME
----------------------------------------------------------------
SYSTEM                         DEDICATED sqlplus@appsdba.com (TNS V1-V3)    ACTIVE
backup.appsdba.com

SQL>

So, we’ve connected to the database and verified through the v$session view that we’ve connected to our backup service.
Now let’s connect with RMAN:

$ $ORACLE_HOME/bin/rman nocatalog target /@orcl_backup

Recovery Manager: Release 10.1.0.5.0 - Production

Copyright (c) 1995, 2004, Oracle.  All rights reserved.

connected to target database: ORCL (DBID=3806185278)
using target database controlfile instead of recovery catalog

RMAN>

In another session we’ll query v$session again:

SQL> l
  1  select username, status, program, service_name from v$session
  2* where username = 'SYS'
SQL> /

USERNAME                       STATUS   PROGRAM
------------------------------ -------- --------------------------------
SERVICE_NAME
----------------------------------------------------------------
SYS                            ACTIVE   sqlplus@appsdba.com (TNS V1-V3)
SYS$USERS

SYS                            INACTIVE rman@appsdba.com (TNS V1-V3)
backup.appsdba.com

SYS                            INACTIVE rman@appsdba.com (TNS V1-V3)
backup.appsdba.com

SQL>

Now let’s verify that we can still connect with no listener:

/u01/app/oracle/local/bkup$ lsnrctl stop

LSNRCTL for Linux: Version 10.1.0.5.0 - Production on 25-JUN-2008 10:33:09

Copyright (c) 1991, 2004, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=orcl)))
The command completed successfully
/u01/app/oracle/local/bkup$

Now let’s see if we can connect with our normal TNS alias:

/u01/app/oracle/local/bkup$ sqlplus system@orcl

SQL*Plus: Release 10.1.0.5.0 - Production on Wed Jun 25 10:33:23 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password:
ERROR:
ORA-12541: TNS:no listener

Enter user-name:

And no, we can’t because the listener is down. Now let’s see if our backup connection will work:

/u01/app/oracle/local/bkup$ $ORACLE_HOME/bin/rman nocatalog target /@orcl_backup

Recovery Manager: Release 10.1.0.5.0 - Production

Copyright (c) 1995, 2004, Oracle.  All rights reserved.

connected to target database: ORCL (DBID=3806185278)
using target database controlfile instead of recovery catalog

RMAN> exit

Recovery Manager complete.
/u01/app/oracle/local/bkup$

And indeed it does. We now have a backup service and it is independent of the Oracle network listener.

I’ve been working on a project with a very large subpartitioned table and I’ve been trying to come up with a sensible statistics generation strategy. This application is on a Linux based RAC system currently running 10g R2 (10.2.0.3). I’ve been trying to use every source of information available and of course, many 10053 traces. I came across this little blurb on Jonathon Lewis’ web site:

Sub-Partition stats (12^th June)

Page 39, Second paragraph:

The problem of partitions and table-level statistics echoes on down the chain to subpartitions. If you want to query exactly one subpartition of one partition, then the optimizer uses the statistics for that one subpartition.

This is wrong. It looks as if Oracle hardly uses sub-partition statistics at all. I have a test case on 10.2.0.1 which shows the optimizer using the partition level statistics to calculate the cardinality of a very simple query that has been identified as targeting exactly one sub-partition of one partition.  However, the access path is a full table scan of that sub-partition, and the cost of the scan is clearly derived from the number of blocks in the sub-partition.

Thanks to Adrian Billington for bringing this to my attention.

I must say that this mirrors what I’ve found. For the queries that I’ve been modeling, the 10053 traces lead me to believe that the optimizer only considers the row and block counts for the pruned subpartitions in its calculations. All other information, including histogram statistics, all appear to be taken from the partition level only.   

The following excerpt from a 10053 trace is the only place that I’ve found where the optimizer considers the subpartition statistics:

Table Stats::
Table: mytab Alias: mytab (making adjustments for partition skews)
ORIGINAL VALUES:: #Rows: 286200000 #Blks: 1598304 AvgRowLen: 82.00
SUBPARTITIONS::
PRUNED: 1
ANALYZED: 1 UNANALYZED: 0
Partition [5]
#Rows: 286200000 #Blks: 1598304 AvgRowLen: 82.00
#Rows: 25004700 #Blks: 135309 AvgRowLen: 82.00 <== This is the only place that reflects subpartition stats.

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;
/

I was reviewing the Open World presentations and came across Cary Millsap’s presentation titled “IOUG: Why You Can’t See Your Real Performance Problems“. Now I’ve seen Cary speak many times but I didn’t get to see this presentation at Open World. After reviewing his slides I wish I had. I think he’s really hit the nail on the head as to why you must use extended SQL trace data to identify performance problems. He has actually tied several topics that he’s used in the past together to make a very persuasive argument as to why other methods fail to find performance problems as effectively as trace data does. This made me realize that there was a very heavy emphasis at this Open World conference on using what I’ll call “database” oriented data to identify performance problems. In other words, AWR, ADDM, ASH, direct sampling of v$ views, OEM and all manner of “advisors” were touted as the way to identify your performance problem. It seems that Cary’s “discovery” of using extended SQL trace data to “profile” performance problems has fallen out of favor. Whether this has truly happened or not, it seems to me that it is rare to hear anyone other than a Hotsos employee talk about using extended SQL trace data to start out a tuning exercise. Of course AppsDBA.com has offered a free interval resource profiler for several years now, so maybe we also qualify as profiling practitioners. It just seems surprising to me that so many are still so intent on finding the “holy grail” of performance tuning when it is sitting right under their noses, if they just looked.

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.