Oracle Database Business optimization experience

The products developed by the company basically use Oracle databases. In addition, the data stored and used in the business system is large, and many users use the business system. When the system is busy, about one thousand user colleagues visit the database system. Therefore, users often complain that the system is slow. After clicking the query, the system crashes, the idle value of the minicomputer running the Oracle System in the background is low (even when the idle is 0, the user will be surprised that the database system will crash, even if the idle value of a minicomputer is 0, the machine may not crash, but this will undoubtedly increase users' concerns.) The system runs in an insecure state.

The Oracle optimization I have done is actually at the SQL optimization level (my predecessors once told me three levels about database optimization: one is SQL optimization, for example, if the correct index is used and Oracle prompts are used, the second is to optimize the database objects, such as adding indexes and fine-tuning the table structure. The third is to optimize the business, you need to change the business logic or table results. This kind of optimization generally takes a relatively large cost and rarely performs similar operations on running systems ).

There are many customers in the company. In most cases, the customer's database problems are remotely handled in the company. As a problem locating person, you must find out what problems have occurred to the actual running database system. The site maintenance personnel and the user's favorite adjectives are: The system is slow, the database is locked, and so on. The confidence that users send to the outside is often very vague. Starting from getting started with this problem, we should guide users to help them understand the real situation of the system. There are a few things to check. One is the warning log file of the Oracle system, several statspack reports are reported when the system is running or when the system is busy (generally the interval is 10 to 15 minutes ), the input result of the SAR command in Unix (this command can collect the CPU usage of the Minicomputer System at specified intervals ). Through the three results collected from the site, we can basically understand the operation of the on-site database.

The warning log file of the Oracle system allows us to see if there are any major problems in the running of the Oracle system.

The statspack report summarizes the basic operation conditions of the database system. You can write a book on how to interpret the report. However, the first thing we should note is that the report contains "Top 5 timed events ", this section describes what are the main waiting events in Oracle currently (for the concept of waiting events in Oracle, refer to relevant documents ).

The input of SAR commands focuses on the distribution of three outputs: USR, WIO, and idle. Among them, sys + USR + WIO + idle should be equal to 100%, and usr accounts for a high proportion, which generally indicates a problem in SQL statement execution efficiency, this problem is generally caused by low indexing selectivity, incorrect table connection sequence and method, etc. High WIO generally indicates frequent I/O operations on SQL statements. For specific problems, you need to analyze specific SQL statements. Reading the execution plan during the analysis is an important tool.

After having a certain understanding of the overall situation of the Oracle system, what needs to be analyzed next is the SQL statements with low efficiency in the system running process, which is a starting point for business optimization. If you cannot operate in the actual system at this time, it is time-consuming to understand the SQL running process. In any case, we should first read the execution plan of the SQL statement in the actual system and the data volume of the table involved in the statement, the selection of indexes for accessing tables, the sequence of table connections, and the associations among multiple tables.

To optimize the Oracle application system, we should first consider optimizing the business system, and then optimizing the parameters (such as memory allocation) of the Oracle system ), consider the optimization of the operating system itself. In the optimization of the business system, the first is the related SQL, starting with SQL, whether the analysis table lacks indexes and whether the table connection sequence is correct, check whether the indexes used are correct, and then consider adjusting the table structure and business logic. Therefore, SQL statements are the key to optimizing an oracle business system.

For SQL optimization, Oracle execution plan is a required tool. It is very troublesome to obtain an execution plan based on the methods provided by the Oracle system, however, the current visual tools such as PL/SQL develop or toad provide us with a very convenient way to obtain the execution plan of SQL statements, however, I think the methods provided by Oracle are still necessary, especially when dealing with problems remotely (I don't know, so I have to learn about them ).

there are two main ways to obtain SQL statements with performance problems: one is to report through statspack. Two sections in the report are important: SQL ordered by gets for and SQL ordered by reads for dB. read Memory Database blocks and read physical database blocks by Statement respectively in these two sections (Database blocks refer to the size of Oracle blocks, which is generally an integer multiple of the Minimum Operating System blocks) if the statements are incomplete (too long), you can use the hash_value value to obtain all the SQL statements in the dynamic view v $ sqltext of Oracle. The second is through the dynamic view of the Oracle system, V $ SQL, which records the number of executions of each SQL statement, the execution information of many SQL statements, such as physical read and memory read and execution time, can be selected through the following statement:

Select T. hash_value, T. Executions, T. disk_reads, Round (T. disk_reads/T. Executions) as perdiskreads, T. buffer_gets, Round (T. buffer_gets/T. Executions) as perbufferreads, T. elapsed_time, Round (T. elapsed_time/T. Executions) as perelaytime, T. cpu_time, Round (T. cpu_time/T. Executions) as percputime, T. first_load_time, T. SQL _text From v $ SQL t Where (T. disk_reads/T. Executions> 500 or T. buffer_gets/T. Executions> 20000) And T. Executions> 0 Order by 6 DESC;

The SQL statements queried by this statement may not be complete. You can also use hash_value to find all the SQL statements in V $ sqltext.

we can analyze the execution plan of the SQL statements one by one. Combined with the data volume of the involved tables, we can estimate or test the execution efficiency of the statements, the fields involved in the where condition of the analysis table (called predicates) are determined by the data distribution, selection, and index of the field. This is a very complicated and trivial task, but from these trivial tasks, we can find that the indexes selected during SQL Execution are incorrect, which tables have missing indexes, resulting in full table scanning, and which statements are insufficient, resulting in full table scanning for the partition table. In short, for a given SQL statement, we can find an optimal execution method based on the data size and distribution of the table and the query conditions used in the SQL statement, by adjusting indexes and using Oracle prompts, the Oracle system executes SQL statements in the optimal way. One of the most common principles of how to analyze and determine the execution method of Oracle is to use the most selective index based on its predicates (query conditions) as much as possible (of course, for some small tables, you can consider using full table scan for better performance ). For SQL Execution Methods, You need to accumulate experience at work. For example, during an optimization, you have found that there are many queries for three fields in a table, therefore, we decided to create a composite index for the three fields, but the statement execution efficiency was worse.