Next Generation
Databases: NoSQL,
NewSQL and Big Data

Buy at Amazon
Buy at Apress


Oracle Performance Survival Guide

Buy It
Read it on Safari
Scripts and Examples
Sample Chapter


 MySQL Stored procedure programming

Buy It
Read it on Safari
Scripts and Examples 


Entries in Oracle (38)


Flashback Data Archive performance (pt 1) 


I did some research on Flashback Data Archive (FBDA) for my upcoming Oracle performance book, but decided not to include it in the book due to space limitations and because not many people are using FBDA just yet.

FBDA – also called Total Recall - is described by Oracle like this:



Flashback Data Archive provides the ability to automatically track and store all transactional changes to a record for the duration of its lifetime. This feature also provides seamless access to historical data with "as of" queries. It provides flashback functionality for longer time periods than your undo data. You can use Flashback Data Archive for compliance reporting, audit reports, data analysis and decision support.

Sounds good, but from my brief tests you pay a very high price for this functionality. Also, it looks like the background processing has been changed to foreground processing in 11.0.6.   In 11.0.6, it's the FBDA background process that populates the FBDA tables, but in 11.0.7 this is done by the session that issues the DML.

Let’s review FBDA set up first. Create a tablespace and a data archive:



'/oradata/g11r22a/fb_arc1.dbf' SIZE 1024 M AUTOEXTEND OFF;



/* Create the flashback archive */



TABLESPACE fb_arc_ts1

QUOTA 1024 M


Now we can mark a table for flashback archive:


DML statements run about the same time on a FBDA table, but COMMIT times go through the roof:

SQL> UPDATE fba_test_data

2 SET datetime = datetime + .01;


999999 rows updated.


Elapsed: 00:01:13.43




Commit complete.


Elapsed: 00:24:10.29


That’s right – 1 minute update, 24 minute commit time!! I’ve seen the same performance from DELETEs and INSERTs.

When the COMMIT occurs, Oracle runs recursive SQL to update the data archive. Here’s an example of one of the SQLs that runs (shown in Spotlight on Oracle's trace file viewer):


The statement above is the final in a sequence of at least 4 that are executed for every transaction.

In 11.0.6, there’s a bug in the FBDA SQL. The following SQL has a hint missing the “+” sign. Consequently the intended direct path inserts do not occur and - in some cases - free buffer waits can result:


The free buffer waits are a consequence of creating lots of new blocks for the DBWR to write to disk while at the same time pulling lots of blocks into the buffer cache from the source table.  If the DBWR can't write out to disk as fast as you are creating new blocks then the free buffer waits results.


FBDA will large transactions is therefore not snappy.  For short transactions the effect (at COMMIT time on 11.0.7) is less noticeable:


SQL> INSERT INTO fba_test_data d(id, datetime, data)


3 SYSDATE - 1000 + DBMS_RANDOM.VALUE(1, 1000) datetime,

4 RPAD(SYSDATE - 1000 + DBMS_RANDOM.VALUE(1, 1000), 900,

5 'x')

6 data




9 rows created.


Elapsed: 00:00:00.48




Commit complete.


Elapsed: 00:00:00.53


The SQLs that FBDA generates are interesting case studies for SQL tuning – the use of hints in particular is interesting since it limits the ability of Oracle to respond to different volumes of changes with different SQL plans. I’ll post an analysis of the various SQLs issued in a future post.

For now, the clear lesson is to be very cautious when implementing FBDA – it definitely has some performance implications!



Building ruby with Oracle and MySQL support on windows

If you did the setup neccessary to compile perl with MySQL and Oracle support (), you are well setup to do the same for ruby.  Why this should be so hard I don't know:  python produces very easy to install windows binaries, but if you want anything beyond the basics in perl and ruby you need to try and turn windows into Unix first. Sigh. explains the ruby build procedure.   I'm really just adding instructions for getting the ruby dbi modules for mysql and oracle.

Make sure mingw and msys are first in your path.

Enter the mingw shell:  sh

sh-2.04$ ./configure --prefix=/c/tools/myruby

sh-2.04$ make

sh-2.04$ make test

sh-2.04$ make install

Now, lets do ruby gems:

sh-2.04$ cd /tmp/rubygems
sh: cd: /tmp/rubygems: No such file or directory
sh-2.04$ cd /c/tmp
sh-2.04$ cd rubygems
sh-2.04$ export PATH=/c/tools/myruby/bin:$PATH
sh-2.04$ which ruby.exe
sh-2.04$ ls
sh-2.04$ cd rubygems-0.8.11

sh-2.0.4$ unset RUBYOPT  #If you have cygwin this might be set
sh-2.04$ ruby ./setup.rb
c:\tools\myruby\bin\ruby.exe: no such file to load -- ubygems (LoadError)



Compiling DBD::mysql and DBD::Oracle on windows

Last week my laptop crashed and while installing the new one I decided to update my perl versions. I mainly use the DBD::mysql and DBD::Oracle modules and although I'm confortable building them on Linux/Unix, like most people I use the Activestate binaries on windows.

However it turns out that Oracle licensing changes now prevent Activestate from distributing an Oracle binary, so I was forced to build them from source. It wasn't easy, but now both the Oracle and MySQL modules are working. Here's the procedure in case it helps anyone.

Install Pxperl

Firstly, you probably want to move to the pxperl windows binaries. Pxperl support the familiar CPAN system for updates. Get Pxperl at The installation should be straight forward.

I installed into c:\tools\pxperl

Install MinGW

You'll need a C compiler capable of building native windows binaries. I used the MinGW system. You can't use cygwin, although I believe that Cygwin might be capable of installing MinGW. Anyway, I got the MinGW system from I couldn't use the auto-installer for firewall reasons, so I did a manual download and install.

Firstly, I unpacked the following .gz files into c:\tools\mingw:

  • gcc-java-3.4.2-20040916-1.tar.gz
  • gcc-objc-3.4.2-20040916-1.tar.gz
  • mingw-runtime-3.9.tar.gz w32api-3.5.tar.gz
  • binutils-2.15.91-20040904-1.tar.gz
  • mingw-utils-0.3.tar.gz gcc-core-3.4.2-20040916-1.tar.gz
  • gcc-g++-3.4.2-20040916-1.tar.gz

You probably don't need all of these, and of course the version numbers might be different by the time you read this.

Then I ran the following two executables

  • MSYS-1.0.10.exe
  • msysDTK-1.0.1.exe

...installing both into c:\tools\msys. You must make sure you provide the correct location for MinGW when prompted. Finally, MinGW installs it's own version of perl, so I removed that as well as the make.exe which is inferior.

I added both the bin directories to my path, which now starts something like this:  c:\mysql;c:\tools\msys\1.0\bin; c:\tools\mingw\bin; C:\tools\PXPerl\parrot\bin; C:\tools\PXPerl\bin

Installing DBD::Oracle

Now you can go into cpan (just type CPAN at the command line) and run "Install DBI".  That worked OK for me.

Then I ran "install DBD::Oracle".  That failed.  I can't remember the exact error, but it turns out that a trailing backslash in the include directory for the DBI doesn't work on Windows.  To fix that, run "configure_pxperl" and add an include for that directory in the "Include Directories" section.  For me, the directory was /tools/PXPerl/site/lib/auto/DBI , since I installed pxperl into the tools directory.

Installing DBD::Mysql

For some reason I thought this would be the easy part.  But it actually was really difficult.

In the end, it turns out you need to create your own version of mysqlclient.lib and manually link to that. Check out MySQL Bugs: #8906, for some more details.  Here's the steps that worked for me:

  1. run "install DBD::mysql" from the CPAN prompt
  2. You will get a whole lot of undefined symbol errors which will include the names of the normal mysql client API calls, suffixed with '@4' , '@0' , etc. Make a list of all of these.
  3. Add the missing symbols to the file include/libmysql.def.   
  4. Build your own libmysqlclient library with the following commands (from the directory just above your include directory):
  5. dlltool --input-def include/libmySQL.def --dllname lib/libmySQL.dll --output-lib lib/libmysqlclient2.a -k
  6. Go to the CPAN build area for the DBD-mysql,  for me that was: cd \tools\PXPerl\.cpan\build\DBD-mysql-3.0002
  7.   nmake realclean 
  8. perl Makefile.PL --libs="-L/mysql/lib -lmysqlclient2 -lz -lm -lcrypt -lnsl"
  9. nmake install

And - voila! - you should be OK. The only think you might need to do now is add the top level MySQL directory to your path.  DBD-Mysql wants to find "lib/mysql.dll" so you need to add the directory above that to your path.  I moved all the libraries to c:\mysql\lib and include files to c:\mysql\include, so I added to my path like this:

set PATH=c:\mysql;%PATH%

All done!

Seems to be working OK now for both Oracle and MySQL.  Much more difficult than installing the Activestate binaries but at least now that I'm working from source I can potentially fix bugs although having done it on Linux it's not for the faint hearted (or the incompentent in C++!)

Hopefully pxperl will gain in popularity and as it matures things will work as easily as on Linux.  That would be great.

Page 1 ... 4 5 6 7 8