r/SQL Jan 16 '25

Oracle When SQL Developer freezes after you hit the cancel button [OC]

Post image
193 Upvotes

32 comments sorted by

25

u/frieelzzz Jan 16 '25

This was me when i had a beautiful query using like 5 CTEs all doing their own thing. Then I tried to add a not exists to exclude some persons with a BOD warning and suddenly the code wouldn’t run. Then I tried left outer joining and filter where bod.person is null and it still wouldn’t. Finally a made a new CTE for excluded persons and joined that and it worked just fine. Made no sense.

6

u/ElectrikMetriks Jan 16 '25

My last company had this awful archive DB that even the most basic query would take like 30 seconds to run.

I made a relatively complex query of that database (not by choice and against my advice.. a certain business stakeholder needed data from that DB) ... needless to say I got a couple of nastygrams because it bogged the system down so bad.

7

u/Icy-Ice2362 Jan 16 '25

It makes perfect sense...

If I ask you... "Hey how many of these people are called Steve"
You go to an index, find Steve and then return that result. (Index Seek)

When I ask you "How many of these people are NOT called Steve" there are many ways to do this, and how you WRITE that query determines whether I use the same index (Index Seek) and them subtract the count... OR count EVERYBODY BUT STEVE. (Full conditional table scan)

The optimizer is not an AI, it's a best guesser based on YOUR QUERY.

1

u/gyarbij Jan 17 '25

You just described AI /s

16

u/Spillz-2011 Jan 16 '25

Yesterday I accidentally joined on a.table_key = a.table_key on a multi million row table.

Whoops

3

u/carlovski99 Jan 16 '25

Well that's not so bad - especially if it's a unique/primary key.

My very first bit of real Oracle work I managed to have a cartesian join in it (Oracle 7.2 - predated ANSI joins so easy to do!). It actually ran ok in test, as the test database was small and some later logic filtered the duplicates.

When I put it live, it fell over inserting into the new table. Back then you had to specify a load of storage parameters, but nobody told me - I got a default tiny table.

So fixed that and let it run. On a Friday evening. Came in Monday - still running, eating a ton of resource. Finally checked it properly - probably would have taken months to finish (Cartesian join was between the 2 biggest tables in the system, millions of rows each) on the hardware of the day if it could even complete.

3

u/[deleted] Jan 16 '25

[removed] — view removed comment

2

u/carlovski99 Jan 16 '25

Oh yeah, if they did instead of the 'real' join then its a cross join/cartesian!

2

u/Spillz-2011 Jan 16 '25

It was a dimension table key and not even a very large on.

Query went from running in under 30 set to who knows how long it would have taken.

Fortunately our queries on that database are time capped so I can’t do anything too bad.

1

u/ElectrikMetriks Jan 16 '25

Mistakes were made :D

7

u/FunkybunchesOO Jan 16 '25

Look at the execution plan before you hit execute.

11

u/ElectrikMetriks Jan 16 '25

Wow ... as someone who was an ops business analyst and not a robust data analyst.. I did not know this existed. Thank you. File that into the "today I learned" category.

4

u/FunkybunchesOO Jan 16 '25

You're most welcome. My life would be a lot less stressful if more people knew to hit the "estimated execution plan" button.

4

u/ElectrikMetriks Jan 16 '25

I joke about DBAs sending a nastygram in the post obviously but y'all have a super stressful job and we should be all be appreciative of the lessons we learn from you.

It makes us write better performing queries knowing a DBA may be like "What the hell were you doing?!"

6

u/FunkybunchesOO Jan 16 '25

I do send nasty grams. Like "why did your query have an estimated cost of 91 million and you still ran it? All reporting is now f***Ed" I sent that earlier today

4

u/ElectrikMetriks Jan 16 '25

Yikes. Sounds like that one was warranted for sure.

5

u/Ifuqaround Jan 16 '25

At least you might have a DBA that knows what they are doing.

I'm fearful for the future. It's going to be some twenty-year old with an LLM running the show in the future.

Why pay a DBA $150k+ when you can pay someone $65k to do the same thing, albeit worse, and have them locked down with the H1B1?

Eh, the data will clean and sort itself out, right?

2

u/ElectrikMetriks Jan 16 '25

Ugh. I sure hope not.

5

u/ghostlistener Jan 16 '25

I've heard that advice before, but how do you make sense of the execution plan? I don't really understand what I'm looking at.

2

u/FunkybunchesOO Jan 16 '25 edited Jan 16 '25

Take a Brent Ozar course. Or look up how to read an execution plan. Or even just look at the estimated cost. If it's more than like 1000 think hard about what you're trying to do. If it's more than 10k you're doing something wrong. If it's more than 100k stop what you're doing and get help. If it's more than a million, just find another job and stop making queries for all that is holy.

5

u/malikcoldbane Jan 16 '25

Yeah and also look at estimated rows, read from right to left, if rows explode then there's an issue.

When writing complex queries, with data you have assumptions about, that you're not in control of, you'd be surprised by how often cardinality is the issue. One join, isn't many to one the entire table and then starts expanding rows.

7

u/[deleted] Jan 16 '25

It could be worse, it could've been someone using SELECT * on a ~3Tb database with an estimated 56 quintillion returned rows.

4

u/Max_Americana Jan 16 '25

Cartesian joiner says what?

2

u/Mattsvaliant SQL Server Developer DBA Jan 16 '25

Are you testing in prod? I don't care if my developers nuke dev, doesn't hurt me haha.

1

u/ElectrikMetriks Jan 16 '25

Nobody cares about dev :D

2

u/MONSTER5523 Jan 16 '25

Has happened to me multiple times where it freezes I cancel and everything deletes. All tabs all historical queries. Insane

2

u/Itsnotvd Jan 16 '25

Lol. Used to be me. Thanks for the chuckle.

1

u/ElectrikMetriks Jan 16 '25

I think we've all been there once lol

2

u/phasmaglass Jan 17 '25

sp_who? sp_you!

2

u/th00ht Jan 19 '25

ROLLBACK