Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what are the three common Oracle failures and tuning methods in development". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

We know that in the usual Oracle development work, we sometimes encounter some BUG. The BUG I have encountered can be roughly divided into three categories: 1. Errors such as ora-00600,ora-03113,ora-07445 occurred, which made the program unable to execute 2. 0. The execution of the plan was wrong, which took a long time to get the result. Giving the result of an error due to an error in the execution plan the first category is very speechless. There is nothing wrong with the code written, but Oracle is one of these errors. This is generally caused by Oracle's bug, and a small part is caused by errors in the execution plan. Generally, when you encounter such a problem in the production environment, you can only accept it, and you usually have to change the way to achieve the same function, otherwise you will have to change the development plan, but the development cost will be very high. If you encounter in the development environment, you can also patch the database to solve this kind of problem (if Oracle is released, but in the production environment, patches can not be easily applied, if you must do, you must first do adequate testing). The second category is very frustrating, but it's okay, why do you say "okay"? Because, at least it can come out the right result! This can generally be solved by patching the database, modifying parameters, adding mandatory prompts, and so on. For example, I have encountered the bug of full outerjoin issued by Oracle10.2.0.5 under Linux 64bit (similar to the official bug2927071). It takes two or three minutes for a full outerjoin to come out, but by modifying the parameters.

After alter session set "_ optimizer_native_full_outer_join" = force;, the original incorrect execution plan was changed, and the result came out in a second, and the result was verified to be correct. The third category is the most miserable, it can be said that life is better than death. Why would you say that? If you encounter the first type of bug, Oracle will report an error, at least to remind you that the road is blocked; if you encounter the second type of bug, at least you can instinctively feel that there is a problem here, even if you don't realize it, the result is correct, right? But this third type of bug, I have encountered from 9i to 10g, obviously there is no problem with the SQL, but Oracle just gives you the wrong result, fortunately every time because of careful discovery, timely adjustment of the technical scheme, did not lead to bigger problems. Encounter these bug does not mean you are very unlucky, in fact, from another point of view, first of all, congratulations, because it shows that your sql level has reached a higher level; if you can realize that something is wrong, it means that you are sharp enough; and if you can find a solution, then you are very good! As for how to identify and solve this kind of bug encountered in the development process, this topic is relatively big and deep, and I will share it with you later when I have the opportunity. But what I need to point out here is that many programs that end up with incorrect results are mostly caused by problems with the code itself, while problems caused by Oracle Bug account for very few. With regard to the optimization of Oracle, as the saying goes, "trees move, people move". We can't blow up a stone just because it is stuck in front. Going around is also a way, right? It's the same with system optimization. The performance improvement of the system involves all aspects, from network, operating system, database, application server to programs. Many people now know that the most optimized part is the programs developed by engineers, such as taking the proportion of database performance problems, and reliable statistics show that 70% of the problems are in the SQL written by engineers. What is more fundamental behind this phenomenon is that there are no engineers who are competent for database development! Once there is a system performance problem, everyone's first reaction is to find a master of tuning to optimize SQL, over time, this has become a habit. It's like not paying attention to the prevention of disease. Anyway, if you are ill, you will see a doctor for treatment. But you may not know that one day in the future, facing anxious relatives, the angel in white will shake his head helplessly, take off his mask, sigh and whisper, "We have done our best. Prepare for the future." Many companies, including those specializing in IT, do not realize that improving development technology can effectively improve system performance. the essence of this phenomenon is that bosses do not realize that improving development technology can actually reduce development and later operation and maintenance costs. once this account is calculated clearly, the boss is naturally willing to invest resources to improve the development skills of engineers. Usually the boss who is not a technical background is not aware of it, which requires technical managers to "be reasonable and emotional" to persuade the boss to invest resources. Of course, after the boss invests resources, the technical manager must hold and get things done beautifully, so that the boss who sees the effect will have no doubt. For those who are aware of it but do not have the technical resources to do it, they can only use other resources in exchange for technical resources, such as training talents internally, looking for outsourcing, hiring monks from outside, and so on. Foreigners also have a proverb-"an apple a day, you don't need a doctor." this is also prevention-oriented. Let's change our thinking, if we improve the development level of engineers, or even equipped with full-time database development engineers, it is not difficult to write high-quality SQL. In this way, most of the hidden dangers of performance are eliminated in advance, which naturally reduces the probability of performance problems in the later stage, and eliminates a lot of the cost of hiring people to do tuning, and the cost of improving the development level of engineers is not particularly high. Why not? ISO-9000 tells us that quality is produced, not detected, and that high-quality SQL should be written by developers, not always tuned through DBA. No matter whether the company is aware of it or has the resources to do it, it is a general trend to improve the development technology, especially the development technology on the database side, and it is not advisable to pretend to be an ostrich without solving the problem head-on. The development and operation and maintenance of Oracle is a systematic work. To put it simply, it is not advisable to fully combine theory with practice, and it is also not advisable to know only theory and only practice. We should learn to guide practice with theory, verify theory through practice, and constantly enrich theoretical knowledge in the process of practice. Under the guidance of theory, constantly improve the ability of practice. As far as database development is concerned, it is best to have the following abilities and qualities: 1. Mastering the basics of SQL and the basic theory of databases will help you understand how SQL works and what kind of SQL runs faster. This can be obtained by studying relevant white papers or technical documents. Learn to ask questions. Asking questions is an art, no matter what you learn, you need to master this art. 3. Advanced knowledge of SQL, which can make SQL a powerful tool for you. You can improve your level by reading official documents and often coming to the database development section of itpub to learn. Don't take it for granted when learning, just like trim is not equal to rtrim+ltrim, anyone who reads the document carefully knows it. 4. Mastering at least one related development language, java, php, etc., will help you to understand database development from another perspective. 5. Certain mathematical ability, preferably with mathematics knowledge above senior high school. Good math literacy can bring you new ideas and methods, help improve your development ability, and help you understand. 6. A certain degree of scientific literacy. Similar to the argument that "there are five Fridays, Saturdays and Sundays in a month, this phenomenon occurs only once in 823", it is necessary to see at a glance that it is false (or will be falsified through procedures), knowing that the ten days of October 5-14, 1582 do not exist, and so on.

This is the end of the content of "what are the three common Oracle failures and tuning methods in development". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.