Wednesday, May 14, 2008

Support for third party database migrations to Oracle Using SQL Developer

As you all may or may not be aware of, in the past year or so, I have been busy writing a book on database migrations to Oracle 10g/11g using the freely available migration tools from Oracle. Originally these tools were in the form of what is called the Oracle Migration Workbench or OMWB for short. In 2006, Oracle released a development environment called SQL Developer.

Oracle has added migration tools to their flagship development product called SQL Developer which used to be called Raptor in the first beta version that will be the focus for migration and development tasks.

However, there are a few gotchas with SQL Developer tool in terms of third party migrations. Oracle as of yet after discussions with product management at Oracle on the SQL Developer team has no support in SQL Developer yet for IBM DB2 UDB and Informix migrations to Oracle using SQL Developer. SO, in spite of what Oracle may say, you still need to use the Oracle Migration Workbench if you wish to migrate off of IBM DB2 UDB or Informix to Oracle. After a brief chat with Sue Harper whom is the product manager at Oracle for SQL Developer, she was kind enough to let me know that support is coming eventually within SQL Developer in a future release to support Informix and IBM DB2 migrations to Oracle.

Saturday, April 19, 2008

The function of the High Water Mark in Oracle

Recently a potential client in San Diego quizzed me on the high water mark and performance tuning in Oracle.

Basically the high water mark (HWM) has a function that comes into play with tables that have heavy insert, update and delete activity. Every time data is changed in a table, the HWM moves to a setting in each table that shows how much free space is left in terms of blocks used and free in the segment object. What this means is that say I have a segment object type table A that has 100,000 rows inserted and then I perform a delete operation to delete 10,000 rows from that table, Oracle will move the HWM to a value dependent on the PCT_FREE and PCT_USED values based on the free list available in that table.

Things get tricky when we go to Oracle 9i which added the option to create automatic segment space management (ASSM) functionality with the table. Prior to Oracle 9i, all tables were manual segment managed which meant that a DBA had to play around with various parameters for PCT_FREE, PCT_USED, etc. to get the balance right for performance optimization of that table. ASSM allows bitmaps to manage that table and improves performance and management is far easier than tweaking all of the previous parameters.

Which leads us to the main issue of HWM and Oracle performance. When a DELETE operation is taking a long time, that is because the HWM value has not been reset. To clean this issue up and improve performance, the HWM has to be reset.

Now there are a couple of ways to do this: one is to perform a TRUNCATE operation on the table which will reset the HWM values and run a lot faster than a DELETE operation.

A second option is to reorganize the table using either export or import for Oracle 8i/9i releases or to use the data pump (expdp/impdp) utility available with Oracle 10g and Oracle 11g to reset the HWM values.

Lutz Hartman has an excellent discussion on the new high watermark settings for Oracle 10g and 11g:

To quote Oracle ACE Lutz Hartman on the concept of the HWM for Oracle:

"Starting with ORACLE 10gR1 we can use a new feature for adjusting the high watermark, it is called segment shrinking and is only possible for segments which use ASSM, in other words, which are located in tablespaces which use Automatic Segement Space Management."

Which brings us to a new discussion with terms that there is now a HHWM and LHWM values for segment objects for tables and other objects.

To further enhance performance, in terms of the values for tables, it is now possible with Oracle 10g and 11g to shrink segments in a table.

The nice thing about Oracle 10g and 11g is that we are not having to do the export and import tables and move them around to reset the HWM for performance reasons we can use the "ALTER TABLE ... SHRINK " commands after we have enabled row movement for the table and all of this can be done online with no downtime for the table!

Monday, October 29, 2007

Oracle 11g now available for Linux and Windows

I always like to try out and look at new releases of databases. Since Oracle is my forte this happens to be the new version, Oracle 11g.

Now that it is available for free download for both Windows and Linux, it is a good time to learn all about the new features as well as to experiment and see what works well and what is still a new undocumented feature or bug. Here
is a useful link on new features for Oracle 11G.

I plan to blog more on the most useful new features in the future for 11g and Oracle.

Saturday, April 14, 2007

Why a Developer DBA adds value to the organization

Recently have considered the whole discussion surrounding the DBA and Developer relationship.

Typically most IT shops have a developer team that manages the development and test environments and a senior level DBA to focus primary on production mission critical database and applications. However, as the reality sets in a firm's development arean can be extremely critical if it ever goes down and it does because development and test systems tend to be more flakey and less stable than production as changes are made on a frequent basis, tables and schema changes occur on the fly and if the development application and database is not available then the developers cannot get any work done which greatly upsets the status quo of things.

A good discussion of the purpose for a development DBA can be found here

http://www.simple-talk.com/sql/database-administration/what-use-is-a-development-dba/

Saturday, April 7, 2007

Thai beer, writing and learning

Its been a crazy 4 weeks with new work on database technologies, writing a book on database migrations to Oracle 10g, and meeting new colleagues and associates.

While not a big consumer of alcoholic beverages, I can appreciate the benefit of washing down spicy foods with Thai beer. Of course, one of my new colleagues has the amazing talent of not only smoking like a chimney (8 packs of cigarettes a day!) but can eat 2 sushi combination dinners and drink between 3-8 beers at one sitting!! I am amazed at how some folks have god-like genetics and can do such feats of hard living.

I have been consumed with so many things lately between work, travel, and professional development that its a good busy schedule.

Sunday, February 25, 2007

Crazy neighbors and a bottle of beer

Last night at aproximately 12:25am while I was sound asleep, a knock came on my front door.

Since I was half asleep and do not answer my door to unknown strangers, I went back to bed. The man was persistent but finally my ability to pass into deep slumber won the contest.

This morning after waking and taking out the trash, I found the following item on my doorstep:


Samuel Adams cranberry beer




Yuck! I am not a big fan of beer begin with and the very notion of fruit beer makes me want to vomit.



Since I rarely if ever consume alcoholic beverages (booze destroys brain cells, pickles the liver, and helps make one obese), I disposed of the bottle in the local trash receptacle.

Wednesday, February 14, 2007

ETL design and methods revisited

The theme is ETL tools and ETL design patterns:

I. ETL methodology
Current data warehouse and ETL architects and developers have devised numerous theories and practices covering ETL design and support. Kimball has an excellent analysis of the ETL process from an architect role in his excellent text, The Data Warehouse ETL Toolkit. In a nutshell, performance issues can be addressed in a couple basic ways:

1. Drop or disable primary key constraints and indexes before load and transformations are performed.
2. Separate UPDATE and INSERT transformations to optimize ETL processing.


To build on the last discussion concerning ETL design methods and tools, I would like to analyze various offerings from home grown scripts to vendor tools. The major players in the current ETL market:

Informatica
GoldenGate
Business Objects
Microstrategy

In my last discourse on ETL design, I took the devils advocate and offered pros and cons on home grown ETL ie) roll your own versus implementation with a robust ETL tool such as Informatica PowerCenter. With the reality of heterogeneous mixed environments and many different source systems, it is not realistic to use home grown ETL unless one has a very large and highly skilled staff of developers and DBAs UNLESS one has a single platform such as Oracle on UNIX or Microsoft SQL Server on Windows (which can use the built in ETL tool called DTS). Most shops would actually be better off using a modern time tested and robust tool such as Informatica or another tool such as Business Objects or Golden Gate ETL software.