For those still on Oracle, why? Is it regulations or executives demanding certified solutions?
And if so then with so many bugs at what point does certification become a negative signal?
And if so then with so many bugs at what point does certification become a negative signal?
- planner hints
- better statistics (including ways to manually create statistics)
- no vacuum
- automatic planner adjustment when true cost was off
- better planning for prepared statements
- active-active clustering with RAC
- vendor support for running on SAN including snapshots
- usable query analysis tools provided by the vendor (pg_stat_statements alone doesn't cut it)
- datapump much faster than pg_dump
- better connection management
- out of the box backup tooling
edit: I haven't used Oracle in 4 years, all of that was available to me back then.
So, granted, other databases don't have Oracle's hacks to fix their broken stuff but I'm not sure why that is an argument FOR using Oracle rather than AGAINST. Hints are fragile hacks.
> automatic planner adjustment when true cost was off
More often than not, this "automatic adjustment" makes things worse.
> active-active clustering with RAC
But it's still shared storage, which doesn't really make it a true cluster in my opinion
> out of the box backup tooling
While this is true, there are several (free) backup tools that are easy to install and use and can rival rman's features.
I'm not sure why shared storage would be a problem, the world runs on SANs and making them redundant is pretty much a solved problem. For running a RAC cluster you are required to have at least two network paths for each cluster node. Furthermore cluster communication is separated from storage communication.
Usually it's some three-way join with n:n:n relations which breaks Postgres' neck. I know exactly where the query should start from, statistics can't possibly know because they have no knowledge about the data distribution in adjacent tables.
I use CTE to "fix" this but the query would be faster without it.
Another issue is that plans can just fall over and you have absolutely no tools available to see what the old plan was, unless you ran explain before and saved the plan. You also have no way to force the old plan while you analyze the issue. This has caused more production outages than I'm willing to admit. With Oracle it would take me one minute to see what the issue is, and I could make the database use the old plan without changing a single line of code.
Disclosure: I implemented the Optimizer Query Hints feature at EnterpriseDB.
[1]: https://www.enterprisedb.com/edb-docs/d/edb-postgres-advance...
Even if updating analyses, creating indices, changing the query, etc. fails, you can always either use CTEs or temporary tables, or use a stored procedure that manually iterates over results to implement whatever strategy is desired.
It might be more time consuming than having query hints, although this is compensated by the fact that almost always queries just work after creating appropriate indices.
The queries in question can't be allowed to get any slower than they already are. They bottleneck certain critical uses.
And yes, this approach is fragile even in Postgres (version or data changes might affect the performance, or you might be stuck with a worse query when query planner becomes smarter), so I imagine query hints in Oracle have the same problem.
As for why they use it... as far as I know it‘s the old tale: historical reasons. Classical lock-in.
Comes as a no-cost option with the database, making Oracle the only complete, end-to-end, data management system I know of, where you can build and deploy rich web apps, "out-of-the-box", without installing or integrating anything else.
A friend of mine (experienced developer) built a web app with React, (CRUD and Dashboard) and it took him the better part of a day. Built the same thing with Apex in about 15 minutes.
That's a big reason we use it. Simply can't stand-up data centric, business focused web apps with as quickly with anything else.
YRMV