Comparison between Oracle and SQL Server in enterprise applications

The ORACLE tutorial is: Comparison between Oracle and SQL Server in enterprise applications. My company not only owns Oracle databases but also SQL Server databases, so I often encounter two problems that people ask me.

The first is usually "can you teach me how to use Oracle? "At the beginning, I would like to politely answer this question:" Of course you can. In fact, I am preparing for an Oracle study class. I will call you when you are ready ". This is a good way to do it. I did not tell the inquirer that it may take me weeks. Sometimes I further tell them where to download the complete version of Oracle for their installation and familiarity. But sometimes it brings me trouble because it may attract too many followers.

The second generally starts with "we have encountered some SQL Server problems. I want to see how Oracle handles this problem ". Okay. Now we have made some progress. I can help them, but it does not take me hours. It should be a careful description of a specific problem. Oracle's answer to this question will point out a path for the problem and may guide them to solve the problem of SQL Server. The Inquirer asked, "Our SQL Server database has become too large-How Does Oracle solve this problem "? Alas... it seems I'm wrong.

I want to provide help,...

My methods may always be wrong. Maybe I should write a summary to summarize some problems that may be involved. When someone asks me for help, I can give him a list of questions. If that person is receiving a general Oracle education, this list may be a little more extensive. On the other hand, if the inquirer is searching for solutions to specific and quantifiable problems, this manual can also help them move forward along the right side so that they can find answers on their desktops.

But I didn't do it because it took too much time. Although I try to help these "senders" as much as possible, I am really worried that they will spend too much time and deviate from their course. This would be a bad precedent. It will encourage them to come back to ask for help again when they encounter some distress at some point in time. Instead, I only spend a few minutes talking about the design flaws in SQL Server. The contents of the architecture cannot be changed. If you study the technical details of the database platform comparison and judgment, you generally cannot obtain those self-explanatory principles. On the contrary, if you decide to use Microsoft's database platform, these problems will certainly exist. Its features or defects may be well handled by us, but the basic mechanism of the product is not easy to change-and of course it is not easy to be changed by end users of closed source code software. Of course, some methods can alleviate the pain, but these problems cannot be overcome. There are big differences between the two platforms, not to mention the differences between the origins of many problems.

Remember, I am not maintaining the system of the flower shop on the street. Microsoft is doing its best to enter the enterprise and enter the territory of Oracle. Therefore, I want to discuss the concerns of enterprise-level system administrators. What I care about is...

Because the company needs to use an enterprise-level system (SAP), I prefer multi-user databases. Now you may think that I am using this term too rashly. In fact, a large number of users use SQL Server in multi-user environments, isn't it? But are they affected by the platform to handle a large number of concurrent users, or are they not affected by the concurrent users? For example, let's look at the locking problem. At first, Microsoft told you that the "Reader-blocking-writer" mechanism is good and safe. It will tell you that this mechanism prevents users from executing "dirty read )". Don't trust this nonsense; it is just an excuse for poor performance. This reminds me of Sun's long-standing stance that a 64-bit system is unnecessary. It doesn't have to be said that they didn't have to exist before selling such products. Next, one day Scott McNealy will talk about the advantages of increasing physical memory addressing.

So what is "wrong read "? Microsoft will tell you that if someone is updating a data row and you are preparing to read the data row in the update, an error will occur. Wrong READ = bad data-this is what Microsoft wants you to believe. But what happens if an update operation is not submitted for a long time? The data is not actually changed. What happens if the update operation is rolled back at the end of the transaction? What are the waiting results of these readers? Nothing.

This is like standing in front of juice in a supermarket trying to decide which one to buy. I know I will choose one. I know that my child needs juice for breakfast tomorrow. I have reached out, but I have not decided which one to take. I bought apple juice last week. What did I buy last week? White grape juice. I think it's time to buy Sour grape juice. On the other hand, when I think about it, you are standing behind me. You may wonder, "When will this idiot get a bottle and don't block me? I know what I want "!

You will find that we are all standing in front of Microsoft's grocery store, and you have to wait until I make a decision before you can see what can be used. You see the attractive sour grape juice is placed on the shelf, and I may buy it, which is meaningless to you at all. Because I may just look at it, but Microsoft knows that you want to buy fruit juice, you must wait for me to stop or select something, and this process occurs before you get anything.

Will Oracle make you more small and easier to view data?

In the Oracle store on the street, you can view what can be used all day, even if I have checked it, or I am in the process of selecting something. Maybe there are more types of juice, maybe I am smaller. Who knows? In either case, I didn't really make a decision, so why don't you look at something? Maybe I will take the last bottle of sour grape juice, maybe not. But do you see any harm in something? You can call your wife and tell her, "I forgot what you want, but here is something ..."

You will find that in Oracle, the reader is concerned that there are no long-lasting data row updates. I may modify a data row in the memory. Although it is related to others, the data row will not be updated until I actually issue the submit command. In fact, I may not be a good programmer and may keep the data row for a long time before submission. Or I may decide not to modify the record at all, but to perform a rollback operation. Therefore, there is no data change at all, but at the same time, you can continue your business, whether or not I log on to the system or not, regardless of whether I am performing the update operation. However, once I modify the data line and issue the submit command, the data line will change and you will not read the new value until that time. Because the new value does not exist until that time.

You may think this is a small difference. But I can tell you that it is crucial. When you must support several concurrent updates to a system of the same data table in the same range, if at the same time several users are always querying the data you are operating on, you will find that the "Reader-blocking-writer" mechanism will stop the product operation. This situation is unacceptable in the enterprise system.

We may discuss other architecture issues in the future. For example:

· Locks are gradually increased due to insufficient memory

· Because SQL Server cannot support consistency of read operations, error data is returned.

· Time-intensive maintenance transactions similar to DBCC to detect/prevent performance degradation

· Microsoft's "self-regulating" database may impede Database Management (DBA) in many cases)

· SQL Server lacks scalability regardless of the TPC result

Of course, there are more problems to solve, but today we are here.