Welcome to Thunderdome

I think my new title is officially "Oracle internal error wrangler."

Insane Oracle problem making Malcolm's life a living hell right now:

Problem: a certain simple query fails with ORA-00600 ONLY when the call is made through a .Net DLL. Every other client under the sun succeeds, I'll call this "WTF #1."


Cause: fucking shitty Oracle 11 and a single virtual column buried in a view defintion.



Failed workaround: hack session/system setting

Prototype workaround with some hope: for some unbe-fucking-lievable reason, the DLL is perfectly capable of grabbing the column directly from the underlying table. I've christened this fact "WTF #2."




Edited By Malcolm on 1458095912

The virtual column saga has ended, and in the end it had nothing to do with the titular item.

ACTUAL culprit:

Some dipshit from ages hence needed to do some aggregation over strings. He picked a technique which unnecessarily treats the fields like XML and glues a list of values together because he was too much of a fucking moron to do it without useless transformations. For a reason unknown to all but the Oracle DB itself, in spite of precisely 0 google searches relating my error to that component, and in spite of our DBA ID'ing the issue on the Oracle website as part of a completely different component, that was the fucking problem. I only figured it out after breaking the query down into individual parts and testing each separately. Just oh my fucking god, WTF oracle?



Edited By Malcolm on 1458153935

So basically a subset of your data had been transformed to an incorrect type upstream and fucked everything up downstream?

No idea about the nuances of the oracle DB systems, but your main complaint is that they didn't have a way to assert the input type, or were too lenient with what they accepted which caused problems later?



Edited By Troy on 1458164624

My main complaint is they forcibly jabbed a round peg into a tiny-ass square hole. One does not use XML aggregation to manipulate content unless it's fucking XML. They were simply too stupid to figure out the correct way to group data and manipulate the groups. They picked a function not fucking supported by our DLL. How the fuck it's gone unnoticed until now is mind-blowing. It would explain the 500-something core dumps we had in one environment last month, though.



Edited By Malcolm on 1458165040

Woohoo. We have officially lost all ability to add new columns to existing tables without a complete table recreate. This might goose that upgrade process. Like, we might get it in 1.5 years instead of 2.



Edited By Malcolm on 1458859360

DBA: Hey, did you know your AWR TOPNSQL setting is turned all the way up to maximum?
Malcolm: What in the name of John Wayne's ass is going on?
DBA: Yeah, your SYSAUX is pushing 1TB.
M: FML.

lol newb

Yo catt, query hints...

Are they generally the last option when doing query tuning? DBAs around here hate them.

Yes, query hints are a last resort.

This thread should be renamed, "Oracle: A Cautionary Tale of Overengineering."

We've got a job handled by the DBMS scheduler. It is presently, somehow, in a kind of quantum/Schrodinger state where it's:

1) running, because oracle says so when I try to drop the job or start it AND simultaneously
2) not running, because says so when I try to stop it

Oracle's scheduler is mediocre at best. Jobs should be run and controlled through external schedulers.

I can't remember if I've run into that exact situation before, but I've had numerous other issues with that PoS in the past.

TheCatt wrote:Oracle's scheduler is mediocre at best. Jobs should be run and controlled through external schedulers.

I can't remember if I've run into that exact situation before, but I've had numerous other issues with that PoS in the past.

Someone has.

UPDATE SCHEDULER$_JOB SET JOB_STATUS = 1...
We have the same problem. Restart of DB solve it.

EDIT: updating the job_status field worked.

Do they simply not teach relational database theory anymore?

So we got some new DB workflows that've been having performance issues. Eventually I take a look at them.

1) Missing indexes on FK columns
2) 2-4 joins conditions each that are hideous and causing repeated table scans and ultimately causing the DB jobs to die
3) Apply joins to an entire table when only a fraction of it is needed

This is supposed to be a joint effort spread out over 3 programmers. Every single one of their workflows fails due to those two conditions. That's potentially 3 "senior" engineers who should have the wherewithal to analyze SQL from a performance standpoint. None of them knew the jobs were failing, let along why, or how to fix them.

Today, one of them hands me a script to modify a single column datatype in a single table. The script drops the table and all associated objects, then recreates them with the one column difference.

Malcolm: Why are you dropping and recreating the entire table and all its indexes to fix one column?
Other guy: Otherwise I have to write the script by hand. Toad generated this one for me.
Malcolm: ...
...
...
...

You know what I've found out... being a SQL God pays well.

I'm thinking their SQL skills are barely above basic. I showed one of them a view I made that raided the sys table and column views in a semi-clever manner. It was like I was showing him the Rosetta Stone.