"High-performance SQL Tuning essentials and Case Resolution" on SQL tuning (SQL tuning or SQL Optimization) learning

"High-performance SQL Tuning essentials and Case Resolution" a book has been launched, many enthusiastic readers on the contents of the book and some of the specific questions raised questions, because of the numerous readers plus my daily work busy, here on the SQL tuning learning to discuss and a unified answer to the hot issues.

First, let's talk about what SQL tuning is. SQL tuning is a skill or work in the relational library domain, and it comes from the word SQL tuning, although there are many peers called SQL optimization, the corresponding English word for SQL optimization, but I always think that SQL tuning is a process, SQL Tuning also embodies the process of this technology or work, and the term SQL optimization, I think more popular, but also more to reflect the results of this technology or work, the individual think that SQL tuning is accurate and professional, it is also more accustomed to this skill or work called SQL tuning.

Second, some readers will ask, "High-performance SQL Tuning essentials and Case resolution," whether the previous five chapters are related to SQL tuning, is it optional? I think this is a major feature of this book, that is more attention to the basic theory of the explanation and learning, any one of the skills of Learning, foundation and theory is very important, this is also a highlight of the book and features. Admittedly, many of the similar books in the case is very good, but also very representative, and precisely the lack of these basic theories, it is easy for a large part of the reader to read these books feel very confused. The key to SQL tuning is based on SQL statements, execution plan and specific data Environment comparison, to find out the SQL statement parsing and execution of the problem in the process, in order to pass the most reasonable measures to solve this problem. SQL statement, this generally does not exist problems, want to learn SQL tuning reader, few do not understand the SQL statement, the specific data environment, there is no problem, the key problem is to read and analyze the execution plan, and to do this is to read the implementation plan of the relevant operations and data items, however, Understanding these operations and data items is supported by basic theory, that is to say, the basic theory of the database, such as: To understand the whole table scan (FTS) operation, you need to know the concept of the table, the concept of the paragraph, the concept of HWM, to understand the index related operations, You have to know the concept of indexes, the structure of indexes, the characteristics of indexes, to understand grouping and sorting (group by and order by), we need to know what the private memory area (PGA) is, how it is organized and managed, and to understand the logical reading, we have to understand the buffer, Then you need to understand the concepts of data blocks ... Therefore, to really grasp the SQL tuning, master these basic theories is necessary, otherwise, only to see those specific cases, but also not much significance, because of the specific theory, see those cases at all can not understand the case to do the root cause, only master some methods, but not understand, encountered problems can only mechanically, Most of the time it's not going to work out, and it's probably worse. Of course, I am not saying that those cases are useless, after mastering the basic theory, to study and analyze some cases, it is also very valuable. Just like a magic doctor gave us a secret recipe, can we read it? Can not understand, because we do not know medical knowledge, useful? Estimated not much use, we can not see the patient to use that prescription, SQL tuning to solve the problem, the truth and doctors to the Doctor is the same, but we are faced with the database, the doctor is facing people just. Through these, we know that the first four chapters of the book "High-performance SQL Tuning essentials and Case Resolution" are not only important, but must have: The fifth chapter is about transaction related content, this chapter content to tell the truth, and SQL tuning is not much direct relationship, but why do I write? Because the lock resources in the relational library are very valuable resources, because the lock mechanism implementation aspectOther relational libraries are more valuable, while the Oracle lock mechanism is more advantageous, allowing Oracle applications to be more comfortable with DML operations, but this does not mean that you can do whatever you do, and in reality, even a lot of senior developers, are not very understanding of the characteristics and application of Oracle transactions, so often unnecessary use of manual locking statements, which both cause a lot of lock-related resources waste, but also the main cause of serious lock blocking and deadlock problems, which leads to serious consequences, which is also in this book I add the content of the original intention of the chapter.

Again, some readers will ask, "High-performance SQL Tuning essentials and Case resolution," the main example of Oracle database to explain the SQL tuning, and whether the book is applicable to other relational libraries? The purpose of my writing is to provide a correct and fast path and method for learning and mastering SQL tuning for the readers who want to learn SQL tuning and work, while SQL tuning is a skill that must be well understood and mastered in the context of the Mechanism and principles of the relational library, and we know that The large aspects of the relational library have many similarities, but some specific details will be different, take the index, the details of the implementation of the relationship is slightly different, however, we can not expect all the details of the relational library is exactly the same, after all, even if the same relational library, different versions of the specific details of the difference, and, Some of the differences are still relatively large, we can only do is to constantly learn and improve themselves. Therefore, in order to explain the SQL tuning skills, especially through specific examples to explain, you must choose a type of relational library for specific explanations, no one can be separated from the specific database selection, can be the SQL tuning this skill to speak clearly, because out of the specific database, there is nothing to talk about, also speak unclear. I chose Oracle to explain, one is the relationship Library market Oracle's share is relatively high, I contact more; second, in all relational libraries, Oracle's optimizer is the most advanced in terms of the optimizer. In the case of SQL tuning analysis and problem solving ideas, methods and procedures, the relational library is almost the same, but the specific form, command, method will be different, such as: Oracle acquisition Execution plan, through the GUI tools, can be through the command line, or through the system built-in packages, but also through the trace to obtain , while SQL SERVER can also be obtained through the EM tool through the command line, or through tracing, while the DB2 and open source relational libraries can also get the execution plan of the SQL statement in a variety of ways. In the case of reading and analyzing the execution plan, the relational libraries are almost identical. Oracle provides a large number of system views to get some information, a higher version of SQL Server and DB2, and even a higher version of the open source relational library provides a large number of system views. Oracle's optimization of SQL statements relies on statistics, and SQL Server and DB2 also rely on statistics, and even open source relational libraries rely on statistics, but the mechanisms and details they generate and implement are somewhat different. For example, Oracle provides a large number of Hint,sql server and DB2 also provide hint this tuning method, but the role of specific hint and syntax, some versions of the Open Source Library also provides hint functionality, perhaps not so perfect. In addition, although the book is based on Oracle, but in the process of writing, we also pay attention to the stripping and generalization of Oracle-related specific features and commands, andOther relational libraries are explained and prompted to make it possible for readers of other relational libraries to benefit from reading the book, thus achieving the effect of touch-related.

Finally, thank you very much for your attention, if you have any questions, you can notify me through my email, Weibo or this blog, if you can not reply one by one, also must try to pull time unified reply, forgive me, thanks again.

"High-performance SQL Tuning essentials and Case Resolution" on SQL tuning (SQL tuning or SQL Optimization) learning