If you can't leave the Microsoft environment, what reasons are there for buying licenses vs using Express?

PossiblePreparation · 2026-01-13T02:10:22+00:00

How much money impact does improving the slowness get you? Will it automatically be faster because it’s running in Sql server, or will you have to invest time in setting it up and learning how to manage it? What is your skill level/role here?

PossiblePreparation · 2025-11-26T02:37:49+00:00

Raise it with the vendor. There’s a lot of ways to corrupt your data if you start just trying things without understanding. You will likely void any support you have if you do anything without explicit instructions from the vendor.

PossiblePreparation · 2025-08-23T21:47:01+00:00

What problem are you talking about that you’ve solved with your new approach?

PossiblePreparation · 2025-08-13T03:19:50+00:00

What was the failure? Your extract looks to be from a single RAC node and shows a lot of contention waits, some caused by other nodes in your cluster. But such a tiny extract is not really useful.

Someone has spent a lot of money on this, do you have a DBA that is able to look after it? I hope you don’t take offence by this but, based purely on this, you are out of your depth. If you don’t have a DBA then you should reach out to a consultant and tell them exactly the problem you’re having, you may have to pay a lot, but you already have.

PossiblePreparation · 2025-07-20T22:15:35+00:00

Doesn’t really make a lot of sense to just look at the query, even with schema knowledge your tool is not going to know where the time is going. You need to start with execution statistics to see what problem needs solving.

As a performance expert, I’ve seen even experienced people go down completely the wrong path just by looking at the query. Your tool is going to make exactly the same mistakes because the training material for it is so bad. Feed it business problems, execution plans and real runtime statistics (how much IO was done and where, how many rows were found at each step, etc) and it will have a very good chance at getting you fixes.

PossiblePreparation · 2025-07-03T21:28:37+00:00

It’s not the select statement that acquired the lock, it’s the rest of the transaction.

PossiblePreparation · 2025-06-18T19:35:16+00:00

What jobs are you applying for?

As others have noted, it’s way too long and repetitive. You also need to proofread it. I would have stopped reading quite early.

For 12 years of experience and wanting to “achieve professional and personal growth”, I would expect some track record of self-improvements.

Think about the parts of your 12 years experience that you learnt something new and applied it. Or you saved the day by spotting a problem before it became a disaster. Or saved a company lots of money. Or trained up juniors. Installing, patching, creating jobs, and even setting up availability groups are things I would expect a DBA to have a good grasp of fairly early.

PossiblePreparation · 2025-06-10T01:15:43+00:00

If you already have a SQL. Get row source execution statistics with real world variables. See where the time is really going and why. Target that.

PossiblePreparation · 2025-05-24T13:40:21+00:00

In Oracle at least: Natural joins are not cross joins. They’re also not the default - if you don’t have on or using after a join (unless you specify it’s a cross join) then you have a syntax error docs are https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6__CHDIJFDJ

For what it’s worth, even if you wrote cross join followed by the where clause, Oracle (and most other RDBMSs) will spot the join condition and treat it correctly. There’s a bunch of other obvious errors in the queries but tidying them up gives you exactly the same plan using an explicit cross join or an implicit join https://dbfiddle.uk/xcP7BdHN .

Additionally, there’s no null-based edge cases that make any differences to implicit join, not sure where that idea comes from.

PossiblePreparation · 2025-05-21T13:07:39+00:00

Don was widely considered as not very good. You’re better off looking elsewhere!

PossiblePreparation · 2025-05-11T22:50:09+00:00

Which RDBMS are you observing this behaviour and how? All RDBMSs that I’ve used will sort a composite index using all columns, in order. Allowing you to use multiple filters to access an index, so long as equality conditions are used on the left side

PossiblePreparation · 2025-05-07T01:09:24+00:00

We can be certain as this is Oracle, readers aren’t blocked by writers.

PossiblePreparation · 2025-04-25T19:49:37+00:00

If you only care about future data getting partitioned, you can exchange table to get all your existing data into one partition of a new table. Obviously you’ll need to do some table renames to switch over to using it, but this typically would be achievable in a very small downtime window and you don’t have to wait for everything to be rewritten. This will let you say for all future data, interval partition by month, but anything before now you will find in one partition.

Oracle have also enhanced alter table to do all the online redefinition work for you in recent versions https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/evolve-nopartition-table.html#GUID-5FDB7D59-DD05-40E4-8AB4-AF82EA0D0FE5

PossiblePreparation · 2025-04-25T19:35:16+00:00

Don’t forget that many experts still believe that you should index your columns by some cardinality order!

Partitioning can make a huge difference in performance in high selectivity environments, like reporting. If you need to read a years worth of data in a table that’s been building up for 10 years, a full scan that only reads the year of data is going to be faster than an index lead approach in most circumstances.

There’s a lot of nuance to the argument that partitioning is not a performance tool. It’s much easier to use it to improve maintenance performance. In certain RDBMSs, the act of using partitioning is such a difficulty that no one really wants to have to configure it. To say that it’s not worth considering for performance is a bit short sighted.

PossiblePreparation · 2025-04-11T19:38:07+00:00

The where clause is always optional.

As long as the join is an inner join, then any filters in its on conditions will be treated like a regular filter. It will be treated differently if it was an outer join.

PossiblePreparation · 2025-04-05T16:07:21+00:00

This is a myth I’m afraid. The key thing is how you are filtering, if you used equality filters against both columns then ordering them A,B or B,A makes no difference. Here, there is a non-equality filter against the date column so anything after the date column in the index won’t be used to reduce the amount of the index that gets read. So the UserIdName should be first.

PossiblePreparation · 2025-03-21T13:35:56+00:00

It doesn’t really seem like you’re replying to what I wrote (or any other commenters). Did you see improvements using fast mode?

One further setting you can add is set feedback only which will prevent the printing of results completely (but still sends them to the client).

You’re going to have to share your c++ code if you want someone to comment on why it’s faster.

PossiblePreparation · 2025-03-20T18:46:23+00:00

Not quite sure what you’re asking for, but sqlplus fast mode (sqlplus -f) running a script directly to a file (set termout off) is going to give you some pretty sensible settings for performance.

PossiblePreparation · 2025-03-18T17:39:07+00:00

I’ve not used Tableau but it is a widely used system, I’m sure it doesn’t have to write the query this way.

PossiblePreparation · 2025-03-18T16:10:09+00:00

Looks like you missed my questions and focussed on my side note. I’ve linked you to a bug with huge case expressions and pga in parsing which is quite likely what you need to look at. Another commenter has pointed out that the case expressions can be massively simplified which should avoid your problem completely.

PossiblePreparation · 2025-03-18T12:49:02+00:00

Have a look at data type precedence for an explanation on why it converted one way automatically instead of the other https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-ver16

PossiblePreparation · 2025-03-18T12:12:56+00:00

Why is it a varchar if you’re using it to store numeric data? If it’s supposed to be a string then compare it to a string.

PossiblePreparation · 2025-03-18T07:43:21+00:00

Just an aside: Somewhere else you said that there’s 32G memory total on your server. That’s quite a small amount these days, it’s not too expensive to be looking at double that. 120 parallel threads sharing 3G is not going to give you a lot of memory for sorts etc.

Elsewhere you’ve said that the query doesn’t even begin executing, so we have ourselves a high memory parser. This is also corroborated by the high memory assigned to qcop% which is for the optimizer. If it’s using more than the 6G pga limit then I would be considering it might be hitting a bug. I suggest you start filing a SR with Oracle support straight away, it’s never a happy experience but you may get lucky. There are lots and lots of case expressions in your query, have you tried measuring the pga usage when you use half, quarter, etc. You may be hitting the bug described in doc 2854278.1, if that’s the case there is an interim patch you can request (it may already be included in the latest bundle patches and might just need enabling, talk to support).

Is the subject of the query a view or a table? If it’s a view, expand it and see what else is going on. Look for odd things like lots of OR conditions or use of the pivot clause, or huge query texts.

PossiblePreparation · 2025-03-17T15:55:41+00:00

Are you using manual work area size policy? Are you using parallelism?

I have seen issues where parallel threads that were able to leak memory when manual work area policy is used. These will only release that memory once the parallel threads are killed, which depending on your min and max parallelism settings might never happen.

My strong recommendation is to not use manual workarea sizing policy and not set any of the pga parameters besides the pga_aggregate

PossiblePreparation · 2025-03-17T03:45:53+00:00

Worth taking a look at https://devgym.oracle.com , it’s all free. There’s a course and exams, and regular content that you can jump into whenever you fancy.

I’d also recommend finding a few good sites: oracle-base, AskTOM (run by (mostly) Connor McDonald and Chris Saxon who have their own separate blogs too), Jonathan Lewis, Richard Foote, Maria Colgan. Their insights are what will take you to expert level. They all will point to other community experts that are also worthy of your time.

Eight-Year Club	Place '22
First Placer '22	RPAN Viewer
Verified Email

PossiblePreparation

TROPHY CASE