Exadata makes me hate Oracle a little less
We're planning to migrate all our databases onto a new Exadata machine. They've got a copy of the first one up and running now, and wanted me to test it out by chucking a large query at it. (I'm responsible for the BI processes that take data out of this database and put it into our data warehouse.) So I threw one of our change capture queries at it, which normally takes about an hour and a half to run.
It ran it in 2 minutes and 46 seconds.
I can't wait until they get our data warehouse on the Exadata machine as well so we can do a whole nightly ETL and see what kind of speed boost we'll get.
You speak my language. ETL, BI, Data Warehouse. Nice to meet someone on this forum that does.
I'm not very good at dick jokes, though.
@Scarlet Manuka said:
I'm not very good at dick jokes, though.Well, nobody's perfect.
Incidentally, we did a test run yesterday of the whole nightly ETL on the Exadata box (that is to say, BI and source databases are both on the Exadata box), and it ran it in 1 hour 19 minutes instead of the more usual 6-7 hours. And that's without any tuning, or even removing the tuning we had in place for the old database. It made me happy for, oh, half an hour at least.
Well, Oracle are still Oracle, after all. It may have taken a month and a half of running in production to get there, but Exadata is making me hate Oracle again.
To be fair, it's not absolutely certain that it's Exadata's fault, or even Oracle's fault. We upgraded to the Exadata machine and to database version 11gR2 at the same time. And we've only seen the problem in Informatica (our ETL tool) workflows. (Of course, we do much more of those than standalone queries, so that's expected.) So it could be an Exadata thing, an 11gR2 thing, or an interaction of Informatica with either of those.
But, since we upgraded our main database to Exadata, at least two queries have been occasionally returning fewer results than they ought to. One of those is the main query that picks up new transactions for processing and payment. The other drives the allocation of bonus payments for certain staff members.
Of course, the real question is how many other queries are also occasionally dropping records, but we haven't noticed it because it wasn't as easy to tell. At the moment, all information in the data warehouse for the last month and a half is suspect.
Even if a miracle occurs and we get confirmation of a bug and a fix from Oracle tomorrow, I forsee a very nasty month or two of auditing, cleaning up and tracking for me. Of course, it's more likely that we'll get something useful in, say, three months or so. And the audit job by then will be SO MUCH FUN.
In case anyone else is paying attention to this thread, it did turn out to be an Exadata bug. Oracle have given us a workaround, and a cell software patch which may fix the problem properly if we cross our fingers really hard. Of course, there's no way to tell if the patch works other than removing the workaround and trying to spot the problem happening again, and if nothing shows up within a few months just assuming that it must have been fixed.
On the other hand, it looks like the audit job is being shelved because we have too much other urgent stuff to do. Who cares about potentially incorrect data anyway?