Oracle Information Integration Part 1

I am working on the change data capture feature of Oracle for our Extract Transform Load(ETL) process. With this feature we may be able to only apply the daily changes from our operational databases to our data warehouse’s staging area. But before that I must negotiate with the operational database’s admins on how much cost this feature will bring to their databases.

In order to do that I plan to produce statspack reports(also 10046 trace files if needed) and compare before and after setup conditions, as statspack and 10046 are standard all excepted performance tools I think decision between doing this extract-transform-load process daily with whole tables and calculating the differences from the previous days whole data on the staging area or doing it with this change data capture feature will be less debatable.

So I started to do some researches on Oracle’s Streams Advanced Queuing(AQ) option since Logminer and AQ is the two most important Oracle features behind this technology. As a starting point I wanted to compare the performance of a Do It Yourself(DIY) queue to Oracle’s Advanced Queue(AQ). AQ has a long history and a much more stable product compared to other options, also has lots of great configure and use features like timeout, delay, multi-consumer and priority. But if you really do not need these features and only your need will be to push and pull without any of these options, is AQ still a good choice for you?

I did a similar research for this question on 8iR2 several years ago and we choosed to use DIY table(an index organized table(IOT)) from the performance results. So the time has come to redo this comparison on 10gR2. Below you will find the setup scripts for both aq and diy-iot queue tables and packages. Also functional and benchmark test scripts are provided, so please try them and give me feedback if I am missing something;

Code listing 69a : DIY queue with IOT setup
Code listing 69b : AQ Setup
Code listing 69c : Responce time and latching results SINGLE based
Code listing 69d : Responce time and latching results ARRAY based

In order to eliminate the caching affect I repeated the tests for several times;
For single runstats AQ for 1000 enqueue and dequeue operations finished within 336 hsecs where as
for single runstats DIY-IOT-Q for 1000 enqueue and dequeue operations finished within 166 hsecs.
~202% of response time.

Also when we look at the latching activity of the two options which is very critical in a concurrent environment;
Again for single runstats DIY-IOT-Q for 1000 enqueue and dequeue operations against AQ had
~154% of latching amount. Dominant difference comes from these three Oracle events; undo change vector size, redo size, IMU undo allocation siz

So the second comparison was on array(bulk) enqueueing and dequeueing;
For array runstats AQ for 1000 enqueue and dequeue operations of a 10 rows array finished within 1242 hsecs where as
for single runstats DIY-IOT-Q for 1000 enqueue and dequeue operations of a 10 rows array finished within 1412 hsecs.
~88% of response time.

Also again when we look at the latching activity of the two options which is very critical in a concurrent environment;
Again for array runstats DIY-IOT-Q for 1000 enqueue and dequeue operations of a 10 rows array against AQ had
~81% of latching amount. This time dominant difference comes from these Oracle events; session uga memory, session pga memory max, session pga memory, IMU Redo allocation siz, undo change vector size, IMU undo allocation siz, redo size

So what are the next steps, first of all I hate do it yourself(diy) approaches since using what is already available, tested and paid a lot is much more efficient. Also there is no need to reinvent a dumper wheel, Oracle gives support for its AQ and there are a lot of great features you may easily configure and use depending on your customers changing needs.

From my primitive loop testings still AQ with bulk option is better, but this decision must be made on the results that are to be monitored under load, not just a primitive isolated database test. So here is a good starting article on how to load Oracle to follow up. Also doing some 10046 research on how to decrease the waits found on bulk AQ option should be very useful.

I always try NOT to be one of those “Question Authorities” you may easily find from a search engine, so please use the test cases provided and contribute to this peer group review.

Testing Information : the scripts mentioned are tested on Oracle Database 10g Express Edition Release 10.2.0.1.0

References Used :
http://www.psoug.org/reference/bulk_collect.html
Oracle® Streams Advanced Queuing User’s Guide and Reference 10g Release 2 (10.2)