PHOENIX - high-speed Oracle data extraction
The Phoenix utility is something I've written and re-written several times over
the years, and it's pretty handy in a Unix environment with
Oracle. I make it available here as a tarball (
phoenix.tar - 51KB), and the following text
is a formatted copy of the README.phoenix file (also included in
The build instructions reference the BE-ENV.sh script (included in
the tarball) and there are environmental tweaks you'll need to make
to get your compilation to work - I figure that if you've read this
far, you know how to compile a Pro*C program in your environment.
I use the phoenix utility on Solaris 9 and Oracle 9 - your mileage
may vary on other versions of Solaris and Oracle.
The Phoenix utility is quite possibly the fastest Oracle data extraction
utility around. It's also a lot easier to use than SQL*Plus.
This program takes SQL queries on stdin (multiple queries are supported,
but I don't see why you'd want to do that) and writes the results out as
a tab-delimited stdout stream.
This utility is primarily intended for batch use, in which this program
extracts a lot of Oracle data into a delimited file for processing in
The program employs Dynamic SQL Method 4 to accomodate whatever query
you can concoct - if it's a valid SELECT, this program can handle it.
SQL commands may be up to 8K in size, and you can raise that limit if
you need to by changing a couple of constants.
The default setting is to retrieve up to 128 columns without complaint,
and the code will automatically detect queries that yield more columns -
after a brief complaint, the code will reallocate memory on the fly to
handle the extra columns.
The code performs array fetches instead of single-row fetches, which
greatly increases throughput - the default setting fetches 100 rows
at a time, and that's configurable via command-line option. If you
set this value too high on the command-line and the code can't
allocate enough memory, the program will complain and then drop back
to fetching one row at a time.
JUST HOW FAST IS THIS THING, ANYWAY?
I set up a little drag race and ran Phoenix against SQL*Plus on a copy
of the production POD database containing 44,644 rows in the PATCHMAIN
table. Here's the SQL:
SELECT PID, PATCHID, PATCH_REVISION, Y2K_FLAG, SECURITY_FLAG,
POINT_PATCH_FLAG, ENCRYPTION_PATCH_FLAG, RESTRICTED_FLAG,
RELEASED_DATE, SUBMITTED_DATE, ORIGIN_ID, PATCH_REQUESTOR,
DEVELOPMENT_ENGINEER, ESCALATION_NUMBER, CC_LIST,
SOURCE_FLAG, PATCH_TYPE, PATCH_SIZE, PRODUCT_ID,
PATCH_PUBLIC_DATE, OBSOLETE_FLAG, BADPATCH_FLAG,
RECOMMENDED_FLAG, RECOMMENDED_REQUESTOR, RECOMMENDED_DATE,
PATCH_STATE, LAST_MODIFIED_DATE, COMPRESSION FROM PATCHMAIN;
With the default 'gulp' set at 100 rows per fetch, Phoenix fetched
44,644 rows from PATCHMAIN into an output file in THREE SECONDS.
Pool old SQL*Plus took FOURTEEN MINUTES and FOUR SECONDS to execute
the same query. Granted, I could speed up SQL*Plus by turning off the
column headings and so on, but still - there's just no comparison.
I don't have a big enough database to show you the huge difference a
bigger 'gulp size' makes - trust me, though, on million-row tables the
turbocharging effect of setting the gulp size to 1,000 or so is quite
- 'cd' to the top of the test workspace
- Source BE-ENV.sh ( '. BE-ENV.sh' )
- 'cd' to the pro_c directory
- Run 'make'
- Run 'make install' if you'd like a copy in the bin subdirectory
Here's a simple example:
ORACLE_USER=pdb@patch60; export ORACLE_USER
ORACLE_PASSWORD=pdb; export ORACLE_PASSWORD
. /var/opt/oracle/oraset PATCH60
phoenix < phoenix.out
select * from os_main;
Naturally, your application will use more complex SQL spanning multiple
lines and doing more interesting things. Your SQL statements can be up to
8K in size (that's a lot of SQL!), and you can raise that limit by
tinkering in the source code.
WHY IS PHOENIX STATICALLY LINKED?
I link Phoenix statically (even though that results in a HUGE executable)
so that it is a little more portable. Once you've got it compiled for
Solaris 9, for example, you can copy it to another Solaris 9 machine and
run it without having to hassle with some of the finer points of dynamic
linking. After copying the binary to a new system, use the standard
/var/opt/oracle/oraset script to establish the basic Oracle environment
and Phoenix is ready to run.
WHO NEEDS THE PHOENIX?
Anyone who wants a scriptable, automation-friendly Oracle data extraction
tool that provides neatly delimited output in a big hurry. Anyone who has
worked to format the output of 'sqlplus' into machine-readable form.
Anyone who would rather drive a Ferrari than a Volkswagen.
THE FLYING FICKLE FINGER OF FATE
All right, yes, I know most of you are too young to remember "Laugh-In."
Never mind - anyway, have you been frustrated by un-helpful Oracle
messaging when you screw up your SQL? Things like "undefined column"
or "syntax error" that don't tell you WHICH column in your SQL is bad or
EXACTLY WHERE other errors are located?
When Phoenix encounters bad SQL, it prints out the entire SQL statement
along with the error message - AND it points precisely to the cause of
the problem! The Flying Fickle Finger of Fate (actually a caret, or "^")
zeroes in on the error, so that you can get it right the very next time
without playing Twenty Questions to find out exactly which part of your
statement is incorrect.
This feature alone is worh the price of admission if you're writing
non-trivial SQL queries.
EXTENDED ERROR MESSAGING
The standard sqlca.sqlerrm.sqlerrmc string only shows 70 characters of
an Oracle error message. Cute, but not always as helpful as you'd like.
Phoenix takes the extra step and retrieves the entire Oracle error text,
which can be up to 255 characters long and often sheds quite a bit more
light on the problem. You're welcome.
Because I wrote it and that's its name, that's why.
I've done this several times over the years for various different project
teams, and this incarnation is called "Phoenix" because it is the latest
re-invention, re-assembly, and re-integration of the oddest, fastest, and
most interesting Pro*C program I've ever written.