Red Gate Source Control for Oracle

The background to this - I was "given" a free license to Red Gate Source Control for Oracle some months ago as part of some marketing and awareness activity Red Gate were doing. I've been busy with other things, so I've only now got around to trying to understand what the product does and see if it can be of any benefit to me. Hence this review.

Before I start my review I want to make two things clear. The first thing is about how you treat Databases in a software development world. Databases are fundamentally different to Application Software. Whereas Application Software is normally rebuilt each time by compiling it from its source code, Databases are never rebuilt - they are always modified in place. A customer's data cannot be thrown away and replaced by a new empty database. Changes to the database must happen in place, in an incremental way. To put it another way, Application Software evolves between each release, with each release being a next generation that replaces the one before it. Databases though metamorphose between releases, with changes being applied directly to the database so that it changes in place and existing data is retained.

This means that a Database must be maintained through a series of "change scripts" that make incremental changes to the database, and these change scripts must be run in a controlled sequence when upgrading the database from one version of its associated Application Software to another version. I would expect any "Database Source Code Control" tool to help in the production of these "database change scripts", and to help manage their check in to the source code tree.

The second point is that I am only interested in source code control tools that truly track "changes" to source code files. I want to use something that knows what changed in a file, and can use this to do things like merges between different branches in a sensible way. I don't want something that just tracks the final contents of each file, and can reverse engineer what changed between two versions by working out the differences after the event. I've used Mercurial and I know that it works this way. And I'm also aware that Git works in a similar way by tracking changes to files. These are the main tools that truly track changes (there may be a few other ones as well), but it means that most other source code control tools do not track changes in the same way. Tracking changes to files is a very powerful way of doing source code control, and enables a whole bunch of advanced features around propagating changes between versions, and merging changes together.

Red Gate Source Control for Oracle

Before I tried to use the product I thought I would read up on it to understand exactly what it thought it was capable of, and how I would need to configure it to get out of it what I wanted. The first disappointment was the slightly weak product information on their web site, which was of the generic "we can do it" type without being specific about what "it" actually was that it did. But then I guess that is marketing for you.

What I did get from the product web page was that it only works with the source code control tools Subversion (SVN) or Team Foundation Server (TFS) from Microsoft. This is the first major shortcoming for this product. I'm not going to stop using Mercurial, which I find to be really good at what it does.

After that I did manage to find documentation for the product online, via the Support link on the Red Gate web site, which was good enough at describing how to use it. It seems that it will generate a set of SQL script files containing the DDL SQL statements to create the database you are using i.e. it reverse engineers from your current database the SQL that would be needed to recreate it. And having generated these SQL files it will then check them into your source code tree for you. When you change the structure of your database in any way, you can make it do a refresh and it will re-generate these SQL files, and re-upload them to your source code control tool. This means that it will update those SQL files that have changed in some way since the last source code update.

This was pretty much confirmed in a review by Lewis Cunningham, which described using Red Gate Source Control for Oracle and the series of SQL files it generated.

Which brings me to the second major shortcoming of the product - as Lewis notes "It generated create scripts rather than alters". Remember the point I made at the very start about deployed production databases must metamorphose in place, keeping the existing data inside them. This means that databases must be maintained through a series of "change scripts" that alter existing database structures to update them. We do not want just another set of "create" scripts that will create a new empty database. This is confirmed in a comment in Lewis's post by a Red Gate employee:-

I am one of the developers ... You were wondering ... how you might get the alter scripts. ... You can checkout a version of the database from SVN and Schema Compare can compare it to a live database and generate the alters you need to deploy the new version.

He is saying that Red Gate Source Code Control for Oracle will not do the "alters" for you, but Red Gate have another product called Schema Compare that will produce the "alters" for you.

Which leads on to the third shortcoming with this tool. How did my local test database get "changed" in the first place, so that Red Gate Source Control for Oracle could detect this change? If the Red Gate tool did not produce the SQL that I ran to change my local database, then it must have come from somewhere else. Most likely it came from another tool, or from SQL statements I wrote myself manually and ran against the database.

Now, if I've already got the SQL statements that "alter" the database structure, and I am sensible enough to put these into a file before running them against my local test database, then I already have the necessary "change script" that updates my database in place. And I could just check in this "change script" directly to my source code tree, and not bother with Red Gate Source Control for Oracle at all.

Summary

To summarise what Red Gate Source Control for Oracle does:

• Red Gate Source Control for Oracle only works with SVN and TFS
• It will not produce "alter" SQL scripts to run against a database - only "create" SQL scripts
• You must manually or otherwise create these "alter" SQL scripts yourself to change your local database
• It only detects changes to a database after you have already run your own "alter" SQL to change it

As a result of its limitations I didn't get around to using it because it would not work with my preferred source code control tool (Mercurial), and it would not deliver what I really needed anyway (database "change scripts").

The "holy grail" of database development tools for me would be one that helped produce the "alter" SQL statements in the first place, as a series of "change scripts". I could then test these "change scripts" locally in my test database, and if happy with the results check them into the source code tree. Unfortunately this tool from Red Gate does not do this at all, and does something completely different - only generating a complete set of "create" SQL scripts all the time, and only after you have already changed the structure of your local test database somehow.

Furthermore, I'm not sure what this tool really delivers beyond running "DBMS_METADATA.GET_DDL" (or the equivalent of it) in a loop to get the SQL DDL statements for each object in the database. I've already got my own set of scripts that do just this. The only thing Red Gate seems to add is a nice GUI in front of it all, and some integration to two source code control tools.

Speeding up Imports

There are a number of techniques you can use to speed up an Oracle Import, some of which I'll describe here. This is not any attempt at a comprehensive list, just some of the main techniques I have used that can really speed up some parts of an import. I've seen a standard import come down from 2.5 hours to about 0.75 hours using these techniques.

The first thing to realise is that an exported dump file is a "logical" dump of the database - it contains the SQL statements necessary to recreate all the database structures, and the data records to be loaded into each table. The import works by executing these SQL statements against the target database, thus recreating the contents of the original database that was exported. We can leverage this to our advantage in various ways.

The objects and their corresponding SQL are in a certain order in the exported dump file, and they are imported in a strict sequence by the "impdp" utility. This order is roughly the following for the main objects (it is not a complete or absolute list, as I have simplified some of the details a bit):

• Sequences
• Tables - Creation, but no data
• Table Data
• Indexes
• Constraints - Check & Referential
• Packages, Functions, Procedures
• Views
• Triggers
• Functional and Bitmap Indexes
• Materialized Views

When you run a full import, each of these sets of objects is done in turn, one after the other. This means that the import is not really parallelised. There is a PARALLEL option, but this only really affects the "Table Data" section, and allows multiple slave readers of the table data to run INSERT statements i.e. different tables can be loaded at the same time, but that is all. The PARALLEL option does not affect other options, such as index builds, which is a shame.

 

Its all about Disk Bandwidth

Before I get into specific techniques, I want to point out that an Oracle Import is fundamentally a disk I/O bound task. The techniques I'll be describing will be using parallelism of various forms to get more work done at the same time on the system, to reduce the total elapsed time. And this in turn will produce more disk I/O operations running at the same time. So you will ultimately be limited by the system's ability to get data off and on to the disks, rather than being limited by the amount of CPU or Memory it has in it.

Consider how each of the object types in turn is created within the database during the import:

• The initial table data load is mainly disk I/O - read all the data records from the dump file and INSERT
• Index creation is mainly disk I/O - read all the data in the table, sort it for the index, write the index to disk
• Constraints are mainly disk I/O - read all the data in one table and verify value or existence in another table
• Materialized Views involve executing the SELECT to populate the MV on disk

Yes, some CPU will be used (certainly for data load and index creation), but the CPU's must each be fed with data from disk to keep them busy. So CPU utilisation can only go up by increasing the disk bandwidth used.

In my experience it is the index creation and the referential integrity constraints that take the longest elapsed time during imports, due to their need to always read all the data records in a table.

 

Divide And Conquer

The main technique I use to speed up an import is to import each object type separately via its own "impdp" command, using the "INCLUDE" option to only do those type of objects. While this does not directly speed up the import itself, it sets the foundation for being able to do so. Now we have a series of individual import commands to import the whole database, we can then sub-divide these up within themselves.

So instead of loading data into all the tables in one command, which does them sequentially one after the other, we can run separate import commands at the same time for each of the largest tables, and run another import command for all the other tables (using the EXCLUDE option to omit the largest tables). Now we have a high level of parallelism within the table data load, which we can fine tune based on the relative sizes of our tables and the disk I/O bandwidth available on the system.

 

Parallelizing Indexes

We could use a similar technique on indexes of doing different tables at the same time, but this can have other issues to deal with e.g. an increase in the amount of temporary tablespace storage used. Also you will probably be limited by the time taken for all of the indexes on the largest table in the database, which would be built serially one after the other.

Instead what I do is use the "SQLFILE" option of impdp with the "INCLUDE=INDEX" option to get the "CREATE INDEX" statements into a single SQL file. Then I edit this file and add a "PARALLEL" clause to the end of each "CREATE INDEX". In fact, import actually puts a "PARALLEL 1" on the end of each "CREATE INDEX" already (I'm using 11.2.0.3.0), so I only need to do a global replace of this by the degree of parallelism I want. You probably want something like the same as the number of disks you have, but you can experiment to get the best value.

Once edited I can then create the indexes by running this SQL script from SQL*Plus, after all the tables have had their data loaded in. As each index is being created using parallelism, each will be maximising the resources on the system, and we can just let it build one index at a time. There may not be any benefit from trying to split the indexes into two separate SQL files and running them both at the same time.

Note that you can create and edit this index SQL script as soon as you have the dump file you want to import. You can do this while the table data is being loaded, so that the index SQL script is ready before the tables have finished loading. This way there is no need for a gap between the end of the table data being loaded and the index creation starting.

 

Skipping Constraints

As mentioned, constraints involve reading all of the data in a table to validate that it is correct against the specified constraint. If we are importing a complete database from another system, and we know for certain that all of the data satisfies all of the constraints in that database, then we can basically skip validating the constraints completely. The speed up from this is quite significant, as we completely avoid enormous amounts of disk I/O for every table in the database, but can still end up with all the constraints in place.

As for indexes I use the SQLFILE option to produce a file containing the SQL for the constraints. I then edit this file and add "NOVALIDATE" to the end of each constraint statement. This means that Oracle will create the constraint, which is what we want, but it will not validate the data i.e. it will assume that the data is valid and not explicitly check it, which is also what we want. In fact each constraint SQL statement ends in the word "ENABLE", so we can do a global replace of this by "ENABLE NOVALIDATE", which is easy.

Again, as for indexes, we can produce and edit this SQL script as soon as we have the dump file, and then run it at the appropriate point in the steps being used to import the database.

 

Export

I've discussed the import side of things so far, as that is where you often want the speed up. But you can apply similar principles to the export of the database, using divide and conquer to run multiple exports at the same time. Again this will increase the disk I/O operations occurring at the same time, but will reduce the total elapsed time of the export. Some of the techniques include the following:

• Do an export of just the metadata i.e. just the SQL to create the objects, using "CONTENT=METADATA_ONLY"
• Export the data from the biggest tables individually to their own dump files, using the "TABLES" option to list each table
• Export the data from all the other tables to a dump file, using the "EXCLUDE=TABLE:" option
  • Beware that the "EXCLUDE" option has a slightly weird syntax where you need to embed quotes around the list of table names

 

Full Database Migration

If you are using export and import to migrate a production database from one system to another, then you can combine all of these techniques together to minimise the total elapsed time before the second database is up and available.

• Once the metadata export has finished, transfer it to the target system over the network
  • Then create the empty tables, and produce and edit the SQL scripts for indexes and constraints
• As each table data export finishes, start its transfer over the network to the target system
  • And as each data dump file arrives on the target system start its load
• Once data load has completed, start creating the indexes, followed by the other steps

This overlaps execution of the export, transfer of the dump files over the network, and import on all of the systems at the same time.