Post #32,621
3/18/02 2:48:08 PM
|
Ask a stupid question. . .
. . . and hope there's a stupid guy to answer it for you :-). I am that guy.
So far as I know, Pro*C should be on the Oracle distribution media and should show-up in when you run orainst. Once installed, the executable itself is called "proc".
Here is a link to some docs:
[link|http://www-wnt.gsi.de/oragsidoc/doc_817/appdev.817/a76942/toc.htm|Pro*C Manual]
Does it have to be C? I haven't even looked at this kind of stuff since I started using Perl + DBI + DBD::Oracle, but I remember supporting some developers who used Pro*C (back in the Oracle 7 days). It was a nightmare.
Heck, if you're not doing any I/O outside of the database or fooling with LONGs, then I strongly recommend using PL/SQL.
Mike Organek
|
Post #32,625
3/18/02 3:02:54 PM
|
here is what we are up to
have a program that shuttles files to and fro other systems and a mainframe. Current thinking is to update a table after each file is handled with a datestamp to get a realtime reporting capability. Currently 15k to 25k files per day handled and thruput is expected to X10 in the near future. Embedded SQL calls was thought faster(by me) also recovery of any failures might be easier to spot inside the program instead of waiting for a hung external program to return. No DBA just my (hazy memories) oracle experience and a couple of c programmers.
thanx,
bill
The Bill of Rights, Void where prohibited.
|
Post #32,631
3/18/02 3:30:46 PM
|
Re: here is what we are up to
Here is my take:
1. There is a somewhat substantial cost for establishing a connection to Oracle. Will the controlling process that handles the shuttling be persistent?
2. At your stated upper limit (250k files/day), you're talking about moving close to three files per second. Even at your current load, you're moving one file every three seconds. I believe that OCI/connection startup time will be a problem here.
3. If you're running multiple processes to do the shuttling, you may want to consider running a "belt-and-suspenders" operation by setting-up a process which listens on a pipe or a socket and simultaneously logs to a local datafile/DBM file and to Oracle (either per-transaction or per some [small] amount of time elapsed) through a persistent connection.
I don't think you want to be bringing the OCI overhead into your shuttling program, but I am hardly expert in matters of Pro*C.
BTW, did you get a look at this:
[link|http://www.poitschke.de/libsqlora8/|libsqlora8]
|
Post #32,633
3/18/02 3:48:14 PM
|
looked at it, very interesting
It turns out pro*c is what they used on another program so they are hoping to cut and paste a solution (the perfect reusable code, snippets!) We figured multiple shuttles, and concerned over the connection times. The overhead will add we figger 5+hrs overhead for updating the table.
thanx,
bill
The Bill of Rights, Void where prohibited.
|
Post #32,659
3/18/02 7:09:30 PM
|
FastCGI
The daemon approach I suggested was a take-off on FastCGI, and now that I think about it, you will probably want to have the receiver process write to the filesystem-based log/DB file and have an UPDATEr process that reads that log/DB file and does the Oracle updates from there.
I like the double writing because the odds of losing your Oracle connection are pretty high compared with not being able to write on your local box (in which case you're pretty much in the toilet anyway).
Farming this out to separate processes also gets the 5+ hours of Oracle UPDATEs out of your shuttling loop. This becomes critical when the connection fails or slows-down. The local logging gives you a chance to replay transactions when the connection comes back.
Serializing your Oracle UPDATEs should reduce contention at the Oracle server end, as well.
BTW, there are plenty of neat opportunities for "Death Spirals" if you keep the UPDATEs in-line. My favorite would be the one where somebody starts a long-running query against your table, Oracle struggles to maintain a consistent view by doing it's hocus-pocus with the rollback segments while the UPDATEs roll-in, DB server performance lags, UPDATEs take longer, either files don't get shuttled because the shuttler is waiting on the UPDATE, or your process table fills-up with new shuttlers. The system may be able to dig a hole deep enough that it can't climb back out.
Sorry about that doom scenario.
Mike
|
Post #32,650
3/18/02 5:59:36 PM
|
Umm...that sounds like a task for a scripting language
Such as the aforementioned Perl/DBI/DBD::Oracle solution. Or equivalent solutions in Python, Ruby, etc.
You are talking 250,000 files per day, each of which is what, 10K? So that is a couple of GB of data per day? Ask broomberg about his 40 GB processing runs done in Perl!
As for the speed win from compiled in SQL, why bother? Prepare a query with parameters, and then execute many times. You only pay the parsing cost when you prepare. Works for me, and works for data warehousing operations the world over. Plus when someone comes along and drops then rebuilds the table, your compiled application is not now horribly broken.
The only case where I didn't use DBI for speed reasons was a latency issue, transfering data over an SSH connection tunnelled through the Internet. And there working in C wouldn't help either. What wound up working was using bcp for the parallelism.
Cheers,
Ben
"... I couldn't see how anyone could be educated by this self-propagating system in which people pass exams, teach others to pass exams, but nobody knows anything."
--Richard Feynman
|
Post #32,718
3/19/02 9:36:39 AM
|
The application is already built
in use for shuttling files, so instead of embedding the sql call an external script for the table update then return to the code when the script is finished to process the next file, or turn over all database handles including error handling to the external script? If the script fails the initial program would have to know that file tracking is broken and handle an exception.
thanx,
bill
The Bill of Rights, Void where prohibited.
|
Post #32,723
3/19/02 10:57:41 AM
|
Take a look at Inline
First of all I would never have built the initial application in C (poor payoff for development effort). But given that it exists, I would strongly suggest reading [link|http://www.perl.com/pub/a/2001/02/inline.html|this article] to get some idea how easy it is to install some modules, take your C code, add #! /usr/bin/cpr at the start, and then embed the Perl database stuff into your C code.
This may sound convoluted - and it is - but there is a lot less of a learning curve than you would think.
I know there is a bias that "scripting languages" are not real languages. But don't just dismiss them. They allow you to get a lot more done with minimal effort than you would have ever believed possible...
Cheers,
Ben
"... I couldn't see how anyone could be educated by this self-propagating system in which people pass exams, teach others to pass exams, but nobody knows anything."
--Richard Feynman
|
Post #32,729
3/19/02 11:32:26 AM
|
Overruled
Been informed that I am a Network guy, not a software engineer so the solution will be built in by the coder. Easy for me I just get a call out when the process dies. :) to restart it.
thanx,
bill
The Bill of Rights, Void where prohibited.
|
Post #32,734
3/19/02 12:12:40 PM
|
In that case...
Point the coder at this discussion and let the coder decide.
Also note that anyone who is building this kind of glue tool in C these days, really owes it to himself to learn how to do it in a higher-order language. With PC class hardware putting data into a database, the usual bottlenecks are your database and your network connection. Given that, C is at the wrong point on the development ease versus raw performance tradeoff...
Cheers,
Ben
"... I couldn't see how anyone could be educated by this self-propagating system in which people pass exams, teach others to pass exams, but nobody knows anything."
--Richard Feynman
|
Post #32,750
3/19/02 2:18:33 PM
|
Sent the inline link to the softwarecoder, he got excited)
The Bill of Rights, Void where prohibited.
|
