oracle DB workflows

Malcolm
Posts: 32040
Joined: Fri May 21, 2004 1:04 pm
Location: Minneapolis

Post by Malcolm »

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."
Image

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

Image

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."
Image




Edited By Malcolm on 1458095912
Diogenes of Sinope: "It is not that I am mad, it is only that my head is different from yours."
Arnold Judas Rimmer, BSC, SSC: "Better dead than smeg."
Malcolm
Posts: 32040
Joined: Fri May 21, 2004 1:04 pm
Location: Minneapolis

Post by Malcolm »

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
Diogenes of Sinope: "It is not that I am mad, it is only that my head is different from yours."
Arnold Judas Rimmer, BSC, SSC: "Better dead than smeg."
User avatar
Troy
Posts: 7156
Joined: Mon Jun 07, 2004 8:00 am

Post by Troy »

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
Malcolm
Posts: 32040
Joined: Fri May 21, 2004 1:04 pm
Location: Minneapolis

Post by Malcolm »

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
Diogenes of Sinope: "It is not that I am mad, it is only that my head is different from yours."
Arnold Judas Rimmer, BSC, SSC: "Better dead than smeg."
Malcolm
Posts: 32040
Joined: Fri May 21, 2004 1:04 pm
Location: Minneapolis

Post by Malcolm »

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
Diogenes of Sinope: "It is not that I am mad, it is only that my head is different from yours."
Arnold Judas Rimmer, BSC, SSC: "Better dead than smeg."
Malcolm
Posts: 32040
Joined: Fri May 21, 2004 1:04 pm
Location: Minneapolis

Re: oracle DB workflows

Post by Malcolm »

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.
Diogenes of Sinope: "It is not that I am mad, it is only that my head is different from yours."
Arnold Judas Rimmer, BSC, SSC: "Better dead than smeg."
TheCatt
Site Admin
Posts: 53728
Joined: Thu May 20, 2004 11:15 pm
Location: Cary, NC

Re: oracle DB workflows

Post by TheCatt »

lol newb
It's not me, it's someone else.
Malcolm
Posts: 32040
Joined: Fri May 21, 2004 1:04 pm
Location: Minneapolis

Re: oracle DB workflows

Post by Malcolm »

Yo catt, query hints...

Are they generally the last option when doing query tuning? DBAs around here hate them.
Diogenes of Sinope: "It is not that I am mad, it is only that my head is different from yours."
Arnold Judas Rimmer, BSC, SSC: "Better dead than smeg."
TheCatt
Site Admin
Posts: 53728
Joined: Thu May 20, 2004 11:15 pm
Location: Cary, NC

Re: oracle DB workflows

Post by TheCatt »

Yes, query hints are a last resort.
It's not me, it's someone else.
Malcolm
Posts: 32040
Joined: Fri May 21, 2004 1:04 pm
Location: Minneapolis

Re: oracle DB workflows

Post by Malcolm »

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
Diogenes of Sinope: "It is not that I am mad, it is only that my head is different from yours."
Arnold Judas Rimmer, BSC, SSC: "Better dead than smeg."
TheCatt
Site Admin
Posts: 53728
Joined: Thu May 20, 2004 11:15 pm
Location: Cary, NC

Re: oracle DB workflows

Post by TheCatt »

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.
It's not me, it's someone else.
Malcolm
Posts: 32040
Joined: Fri May 21, 2004 1:04 pm
Location: Minneapolis

Re: oracle DB workflows

Post by Malcolm »

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.
Diogenes of Sinope: "It is not that I am mad, it is only that my head is different from yours."
Arnold Judas Rimmer, BSC, SSC: "Better dead than smeg."
Malcolm
Posts: 32040
Joined: Fri May 21, 2004 1:04 pm
Location: Minneapolis

Re: oracle DB workflows

Post by Malcolm »

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: ...
...
...
...
Diogenes of Sinope: "It is not that I am mad, it is only that my head is different from yours."
Arnold Judas Rimmer, BSC, SSC: "Better dead than smeg."
TheCatt
Site Admin
Posts: 53728
Joined: Thu May 20, 2004 11:15 pm
Location: Cary, NC

Re: oracle DB workflows

Post by TheCatt »

You know what I've found out... being a SQL God pays well.
It's not me, it's someone else.
Malcolm
Posts: 32040
Joined: Fri May 21, 2004 1:04 pm
Location: Minneapolis

Re: oracle DB workflows

Post by Malcolm »

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.
Diogenes of Sinope: "It is not that I am mad, it is only that my head is different from yours."
Arnold Judas Rimmer, BSC, SSC: "Better dead than smeg."
Leisher
Site Admin
Posts: 65256
Joined: Thu May 20, 2004 9:17 pm
Contact:

oracle DB workflows

Post by Leisher »

“Every record been destroyed or falsified, books rewritten, pictures repainted, statues, street building renamed, every date altered. The process is continuing day by day. History stops. Nothing exists except endless present in which the Party is right.”
Post Reply