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.