The Oracle Database engine is probably one of the most robust DBMS systems available commericially or otherwise with a rich set of features. However , one area that is poorly addressed in the core database offering is data exports. I know , I know ...the native export tools like exp and datapump offer runtime options and speed unmatched in any system I have see so far .
But..how about exporting table data to text files ? I like to export my production database tables to text files for point in time snapshots ( usually at the end of business every night ). I like to load these tables into mysql and postgresql databases ( and sometimes grudgingly into M$SQL :-) for off line reporting or web applications. Unfortunately, Oracle does not bundle any utility to dump to text files in any efficient manner. There are some enterprising individuals that have written sqlplus scripts to accomplish this, but this method is not efficient ( low on speed and high on system resources used ). Why would Oracle not provide an utility export to text files that I can import into databases it competes with??.....hmmm.. enough said.
Fortunately, there is a solution on the market that does offer the functionality I need. Fastreader ( from WishdomForce ) is available for multiple OS platforms. Its only purpose is to offload data from Oracle and it does that very well.
Fastreader uses a Java GUI for setup . Setup consists of selecting the source Oracle database connection, destination settings, table selection and some performance based settings. You can choose to run the data offload from the GUI or run it from the command line like I do. I have a cron job that runs Fastreader every night. The setup is quite painless and I will literally done in 5 mins.
The offload process is very fast and has a very low overhead. Fastreader uses information in the Oracle data dictionary to direcly access data from oracle datafiles. This virtually eliminates any load on the database buffers and oracle processes. There is obviously a spike in IO but that is inevitable.
When I fist ran this tool, I could not believe how fast it was . Some performance numbers from a Itanium2/Redhat/Oracle 10gR2 ( 2 CPUS/8GB Ram ) system:
14 Million rows ( 11GB ) in 7 mins.
Half a Million rows ( 1GB ) in 4 seconds
You may see better or worse based on the system configuration.
Th guys at WishdomForce are really smart and friendly. When I first contacted them, they did not have a build for my platform yet ( Red Hat Itanium ) . I was surprised when they got back to me with a trail version for Itanium in a week. I am looking forward to evaluating and using they latest data export tool ( Data Sync ) . This promises to offload incremetal data from Oracle to other DBMS platforms in near realtime.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment