contents of planboard presentation about scheduler

During my presentation about the Oracle Scheduler I demonstrated a little script that generated a lot of jobs, first a series of jobs tied to a window and later the same series of jobs not tied to a schedule. This demonstrated that when the jobs were tied to a window, the order of the executions was mostly in the alphabetical order of the job class that were defined for the jobs. Not really what I expected, can be a bug, it is not documented how the order should be but I expected more random choices of jobs over the job classes.

You can find the code here. Download the zipfile, unzip it, run install.sql as a dba and check the job counts and resource usage for the defined resource consumer groups. Run the remove.sql to get rid of the installed items. For the remove you also need to be connected as a dba.

For monitoring the job counts you can use the following sql:

select sysdate
,(select count(*) from all_scheduler_jobs where owner = 'PLANBOARD' and job_name like 'STATS_PRIO1%') p1
,(select count(*) from all_scheduler_jobs where owner = 'PLANBOARD' and job_name like 'STATS_PRIO2%') p2
,(select count(*) from all_scheduler_jobs where owner = 'PLANBOARD' and job_name like 'STATS_PRIO3%') p3
from dual;

The cpu consumption can be monitored using:

select sysdate
,(select CONSUMED_CPU_TIME from V$RSRC_CONSUMER_GROUP where name = 'PRIO1')PRIO1CONSUMED_CPU_TIME
,(select CONSUMED_CPU_TIME from V$RSRC_CONSUMER_GROUP where name = 'PRIO2')PRIO2CONSUMED_CPU_TIME
,(select CONSUMED_CPU_TIME from V$RSRC_CONSUMER_GROUP where name = 'PRIO3')PRIO3CONSUMED_CPU_TIME
from dual;

In the presentation I had these two queries running as a monitor in DbVisualizer.
To make DbVisualizer more useful in combination with Oracle Scheduler and Oracle Resource Manager you might find it helpful to download and use this oracle.xml file that I made with many contributions of Nathan Aaron in it. I am sure that this will somehow find its way in the production release of DbVisualizer in the near future. For now, download and unpack the file. Place it in the profiles folder in the DbVisualizer package. It is fully up to date until DbVisualizer version 6.5.6

tuning redo apply on zfs

We are running large databases on zfs filesystems. The performance is not yet optimal but sufficient to be able to work. Tuning is an ongoing process. Recently we applied the latest zfs patches and introduced some new tunables on the standby site.

Before these changes the redo apply rate was somewhere around 15MB/s. Not a speed monster but enough for our goal.

After these changes on the operating system the apply rate was 2MB/s. Not the speedboost where we were hoping for, we hoped on a similar factor in the other direction.

This meant digging and find a way to improve the situation, with only 2MB/s we could hardly keep up with production. Simple testing with dd showed that we could easily get files read with 150MB/s. During the monitoring of the redo apply we saw that to read a single archive, the database needed a few minutes. After the archive was read, it took only 10 to 20 seconds to flush the data to disk. A bit strange.

According to metalink note 387343.1

ALTER SYSTEM DUMP LOGFILE ‘’ VALIDATE;

should show the upperbound of the redo read rate. Using the tracefile we found that the file was read with a rate of 140MB/s. Not bad at all and much better than what we could see happening in the databases recovery process. The conclusion is that the database does not use the same code to read the archive during the recovery as it does when dumping the logfile.

One of the ways to control this is the filesystemio_options parameter that we had set to directIO. We changed this

alter system set filesystemio_options=setall;

The result was surprising, the redo read rate went up to 140MB/s, better than we had ever seen before. A quick test with the dump logfile showed that now the dump rate was with 40MB/s. This again shows that the metalink note is not exactly telling the truth. 40MB/s is not the upperbound of the read read rate...

equipment used:

• 32 core Sun M5000.


• Oracle version 10.2.0.4


• Solaris version 10.6 (februari 2009)


• redo file size 400MB.


• db_cache_size=4G


• dbsize=2,5T

Conclusion:
we can use zfs for large databases but need to be aware of strong reactions when tuning zfs on the OS. ZFS is getting better every patch release.