Comparison of Oracle and SQL Server in enterprise applications

  • 2020-06-01 11:12:28
  • OfStack

The ORACLE tutorial you are looking at is :Oracle versus SQL Server for enterprise applications. The company I work for has not only the Oracle database, but also the SQL Server database, so I often see people asking me two kinds of questions.

The first usually begins with "can you show me how to use Oracle?" At first, my polite response was "sure, I'm actually starting an Oracle class; I'll call you when I'm ready." This is the way to be nice, and I didn't tell the inquirer it could have taken me weeks. Sometimes I go one step further and tell them where to download the full version of Oracle so they can install and get used to it. But sometimes this gets me into trouble because it can attract too many followers.

The second type 1 begins like this: "we're having some problems with SQL Server 1, and I'd like to see how Oracle handles it." Ok, we're finally making some progress. I can help them, but it won't take me hours. It should be a statement after careful consideration of a particular problem, and Oracle's answer to this question will point the questioner down a path that may lead them to the problem of SQL Server. The questioner answers and says, "our SQL Server database is getting too big -- how does Oracle handle this?" Alas... I guess I was wrong.

I wanted to help, but...

My methods may be all wrong. Maybe I should write a summary of some of the issues that might be involved. When someone asks me for help, I can give him a list of questions. If that person is getting a general Oracle primer, the list may be a little more extensive. On the other hand, if the questioner is searching for a solution to a specific and quantifiable problem, this manual can also help them move along the right path so that they can find the answer on their own desktop.

But I didn't do it because it took too long. While I do my best to help these "houseguests," I'm really worried that I'm spending too much time on them and going off course. That would be a bad precedent. It encourages them to come back to me at some point when they are in some kind of distress. Instead, I'll just spend a few minutes talking about a design flaw in SQL Server. What is involved in the architecture cannot be changed. If you delve into the technical details of the database platform comparison judgment, you will generally not get those self-evident principles. On the other hand, if you decide to use Microsoft's database platform, these problems will be there. Its features or defects may be handled well by us, but the basic mechanics of the product are not easily changed -- and certainly not by the end users of closed-source software. There are ways to alleviate the pain, of course, but they cannot be overcome. There are big differences between the two platforms, not to mention differences in the origins of many problems.

Remember, I don't maintain the system of the flower shop on the street. Microsoft is doing its best to move into the enterprise, into the territory of Oracle. So here's what I want to talk about: the concerns of enterprise system administrators. All I care about is...

I prefer to use multi-user (multiuser) databases because companies need to use management enterprise-level systems (our company is SAP). Now you might think I'm using this term too lightly. There are actually a lot of users using SQL Server in multi-user environments, right? But are they dealing with a lot of concurrent users because of the platform, or are they dealing with concurrent users anyway? For example, let's look at the problem of locks (locking). At first, Microsoft tells you that the "reader-block-writer" mechanism is good and secure. It will tell you that this mechanism prevents users from performing "misreads" (dirty read). Don't fall for such nonsense; It's just an excuse for poor performance. This reminds me of Sun's long-held position that 64-bit systems are unnecessary. Unnecessary means they don't necessarily exist before they sell the product. Next, one day Scott McNealy is in front of you talking about the advantages of growing physical memory addressing.

So what is misreading? Microsoft will tell you that if someone is updating a row and you are about to read the updated row, an error will occur. Misreading = bad data -- that's what Microsoft wants you to believe. But what happens if an update operation doesn't have a lift for a long time? The data didn't actually change. What happens if that update operation is rolled back at the end of the transaction? What do these readers get out of their wait? Nothing.

It's like being in a supermarket, standing in front of juice, trying to decide which one to buy. I know I will choose one. I know my child needs juice for breakfast tomorrow. I've already put out my hand, but I haven't decided which one to take yet. I bought apple juice last week. What did I buy last week? White grape juice. I wonder if it's time to buy sour grape juice. On the other hand, you're standing right behind me while I'm thinking, and you're probably thinking, "when is this idiot going to get a bottle and not block me? I know what I want! ""

You'll find that we're all standing in front of the Microsoft grocery store, and you'll have to wait until I make a decision before you can see what's available. You see the tempting sour grape juice on the shelf, and I might buy it. It doesn't make any sense to you. Because I might just be looking, but Microsoft knows you want to buy fruit juice, so you have to wait for me to stop hanging out or pick something up, and that happens before you get anything.

Will Oracle make you look at your data smaller and easier?

At Oracle on the street, you can check what's available all day long, even if I've checked it, or I'm in the process of picking it up. Maybe there are more kinds of juice, maybe I'm smaller, who knows? In either case, I haven't really made a decision, so why not show you what it is? Maybe I'll take the last bottle of sour grape juice, maybe I won't. But do you see any harm in seeing something? You could call your wife and tell her, "I forgot what you wanted, but here's something..."

You'll notice that in Oracle, the reader's concern is that there are no long, persistent updates to the data rows. I may modify a row of data in memory that, although relevant to other people, will not be updated until I actually issue the commit command. In fact, I may not be a very good programmer and may hold the data rows for a long time before committing. Or I might decide not to modify the record at all and just roll it back. So, no data changes at all, but at the same time, you can continue your business whether I'm logged in or not, let alone whether I'm updating. However, once I modify the data line and issue the commit command, the data line will not change, and you will not read the new value until then. Because it wasn't until then that the new value officially existed.

You might think that's a small difference. But I can tell you, it's a crucial one. When you have a system that must support several concurrent updates to the same data table of the same scope, if there are several users querying the data you are working on at the same time, you will find that the "reader-block-writer" mechanism will cause the product to stop working. This is simply unacceptable in an enterprise system.

Maybe we'll talk about some other architectural issues in the future. Such as:

· gradual increase of locks due to insufficient memory

· because SQL Server cannot support the 1 tropism of read operations, it returns the wrong data

· time intensive maintenance transactions like DBCC to detect/prevent performance degradation

· Microsoft's "self-tuning" database, which in many cases interferes with database management (DBA)

· no matter what the result of TPC is, SQL Server lacks scalability

Of course, there are more questions to be answered, but that's all for today.

Related articles: