About the author
LIANG Jingbin,
Vice Chairman of Fufu Research Institute, the only four-star internal trainer of the company, a well-known database expert in China, and has rich experience in the field of database optimization and training. He has been invited to serve as a speaker at domestic and foreign database conferences for many times, and has a wide influence in the industry. He is the author of many best-selling books, including “Harvest, More than SQL Optimization”.
This chapter will briefly introduce you to the architecture knowledge, and then describe the relationship between architecture and SQL optimization. Finally, we broaden our horizons with relevant optimization cases for series extensions, which gives us a deeper understanding of the principles of the architecture.
First, the knowledge structure of the system
1. Composition
2. Principle
The Oracle architecture consists of two parts: instance and data files. The instance consists of a shared memory area developed by Oracle SGA and a set of processes.
The SGA is divided into 3 parts, and here is the focus of knowledge. First of all, the Shared Pool area is used to parse SQL and save the relevant execution plan, and then SQL obtains data according to the corresponding execution plan, first look at whether there is the required data in the data buffer, if not, read the Databuffer from the disk, and may not need to read from the disk the next time you access it. When the update SQL statement appears, the data in the Databuffer becomes dirty and must be written to disk. In order to protect this data, there is a Log buffer area.
More details will not be repeated, read “Harvest, not only Oracle” for details.
3. Experience
(1) Experience SGA in architecture
The SGA portion of the Oracle architecture is shown in the following diagram:
SGA allocation before starting the database:
SGA allocation after starting the database:
Why is it 2485125120 bytes, please see the following figure:
It turns out that SGA opened up so big.
(2) Experience the processes in the architecture
Oracle process before starting the database:
After starting the database, the Oracle local process:
The process of connecting the application after starting the database:
After starting the database, view the number of Oracle application connections and the total number of connections:
2. System and SQL optimization
Next, we describe the relationship between architecture and SQL optimization from three aspects: shared pool, log, and data buffering, and the general idea is shown in the following figure:
1. Related to shared pools
First, let’s look at the shared pool, one of the three components of SGA, which is the first memory component accessed in the execution of SQL statements.
(1) Parsing optimization makes the second execution faster
Since the first execution of SQL has completed the judgment of syntax and semantics, completed parsing, and saved the preferred execution plan, these things in the Shared Pool can be eliminated during the second execution, so the execution efficiency can be improved a lot. Consider the following example, starting with environment preparation.
Execution 1:
SQL second execution:
Script 1 parsing optimization makes the second execution faster
Conclusion:
The first execution time is 00:00:00.10, and the number of Recursive Calls is 28;
The second execution time is 00:00:00.07, and the number of recursive calls is 0.
Obviously, the optimization of parsing makes the second execution faster.
(2) Think about the performance leap brought about by binding variables
In real life, most users have the experience of entering their mobile phone information to access a system, such as SQL simple construction: select * from t where nbr=18900000001. It is foreseeable that new nbr-valued SQL will soon appear in the system, such as select * from t where nbr=18900000002. With a large number of users, there will be a large number of highly similar only nbr different SQL in the system, these different SQL should actually have the same execution plan, but in the Shared Pool have to be parsed one by one, so a lot of useless work is done, because stored in the shared pool, it also consumes valuable resources.
If you use binding variables, these SQL become select * from t where nbr=:x, which forms an SQL, which reduces a lot of parsing time of the system, saves shared pool resources, and greatly improves performance. Let’s look at a set of examples, as follows:
Binding variables not used:
Script 2 does not use a binding variable script
To use binding variables:
Script 3 uses a binding variable script
It can be seen that the performance difference is very obvious, the unused binding variable is 43s, and only 4s after use.
(3) Experience the number of hard parsing and execution times
Script 4 experiences the number of hard parsing and executions
(4) Experience the AWR test of binding variables
(5) Think about the TRACE experiment of binding variables
(6) Pay attention to static SQL automatic binding variables
2. Data buffering is related
(1) Buffer optimization makes the second execution faster
Script 5 buffering optimizations make the second execution faster
Through the experiment, it can be seen that the value of the physical reads of the first execution is 1038, and the value of the second execution is 0, and the physical reads are greatly reduced instantly, and the performance is naturally improved. However, careful readers here may find that the recursive calls are 282, which has not changed, because here we deliberately canceled the parsing-optimized cache by altering the system flush shared_pool.
(2) Parsing and buffering optimization together
Script 6 parsing and buffering optimizations come together
In the next experiment, please observe both recursive calls and physical reads, and both values are 0 after the second execution, which is the case after the simultaneous optimization of parsing and caching.
(3) Direct path read performance is slightly better
Environment Preparation:
–Environment preparation (constructing a table with about 1 million records)
drop table t purge;
create table t as select * from dba_objects;
insert into t select * from t;
commit;
To test normal insertion:
To test the direct path insertion method:
Script 7 parsing and buffering optimizations come together
It can be seen that the time required for ordinary insertion is 00:00:14.81, while the time for direct path insertion is 00:00:04.22, and the difference is still very large. Why this difference? Continuing to analyze the above experiment, we can see that after ordinary insertion, the physical reading of the continuation query is 0, while after the direct path insertion, the physical reading is 27469. Why is that? Because direct path reads and writes can bypass SGA, which is less work for insertion, performance is certainly better.
But then again, optimization is relative, inserts are fast because they do not cache data, and queries will also be faster because there is no cached data, resulting in the next query will not be fast.
3. Log archiving is related
The log buffer part of Oracle’s architecture is shown in the following figure:
(1) The performance difference between batch submission and non-batch is obvious
Script 8 The performance difference between bulk commit or not
It can be seen that it takes 11.21s to submit without using batches, and 4.26s after use, which is a world of difference.
(2) The impact of log closure on performance
To test the direct path writing mode:
Script 9 Direct Path Write Insert
Test nolgging shutdown log + direct path write mode:
Script 10 Direct path write plus close log insertion
It can be clearly seen from here that the insertion of the unclosed log takes 23.68s, while the insertion of the closed log takes 12.08s, which is a huge difference. Of course, we are not encouraging everyone to turn off the log, but just tell everyone that in some specific scenarios, when the data is not very important, when the log is allowed to be closed, the performance can be greatly improved.
3. Expansion optimization cases
(1) Headache, how to check hard analysis problems
A system encounters a large number of hard parsing performance bottlenecks, and the relevant personnel have no way to find the specific SQL that does not use bound variables, how to deal with it? Let’s simulate such a scenario to see how to locate SQL that does not use bound variables. First, construct an SQL that does not use binding variables and executes frequently, as follows:
Script 11 constructs SQL without binding variables
The idea of capturing SQL that needs to use bound variables is as follows, the principle is that SQL without binding variables is similar, replace similar parts with @, and then extract the same grouping to find out the SQL that does not use bound variables.
Next, you can quickly locate it in the following way:
Script 12 Gets the method that does not use the binding variable SQL
The next situation is shown below, and sure enough, the positioning is very clear, and this is it!
(2) Weird, the logic of SQL reads zero
As you know, the logical read of SQL generally cannot be 0, but there is one case that can really make the logical read of SQL 0, that is, the cache result set. This is set by adding hints such as /* + result_cache */ to the SQL statement or by fixing this feature on the session attribute.
When the setting is completed, the result set of SQL execution is thrown into the SGA Shared pool when it is executed, and it is OK to use it directly as a result when the next time it is executed, because there is no need to access any specified objects at all, so the logic reads as 0, and the test is as follows:
Script 13 caches the result set so that the logic reads as 0
When using cached result sets, you must pay attention to the application scenario, because the cached result set of related SQL will automatically invalidate as soon as the objects (such as tables) corresponding to SQL change. Therefore, this technique is generally used in tables that record few changes.
(3) Surprise, the logic of the function reads zero
Similar to SQL’s cached result set, the result set of a cached function can also make logic read to 0. In some cases, the result set of the function will remain unchanged because the data such as the base table has not changed, so in order to avoid repeated evaluation of the function, the technique of caching the result set of the function can be applied. This result set also exists in the Shared pool. The specific case script is as follows:
Script 14 caches the function result set so that the function logic reads as 0
(1) Thanks, keep makes SQL run faster
As you know, data buffer data will be squeezed out, so sometimes, in order to avoid some important data being squeezed out, we will take some special means to fix this part of the data, which is also a common practice. How to fix this part of the data? The specific ideas and methods are as follows.
When not fixed:
Script 15 The case of unpinned cache
The fixed method and query method are as follows:
Script 16 Fixed the case of caching
(2) Meticulous, check the laws of each dimension of the system
There are many indicators for whether the database is running healthily or not, and we can analyze whether the database has abnormalities in a certain period of time through changes in database running time, logs, logical reads, parsing, number of transactions and other indicators during each period. This is a useful way to monitor database health and locate database problem failures. The script is as follows:
Script 17 queries the health of the database over time
Obviously, this output can be a guide for us to determine the peak time point of the database.
(1) Clever, catch submitting overfrequency statements
Many production systems have performance problems due to failure to pay attention to batch submissions, resulting in related log waits. This is manifested in the alarm log, that is, the log is switched too frequently. At this point, we can also see the problem by looking at the number of transactions in the AWR report and the size of each transaction. However, many times, we can also track the specific SQL that has not been submitted in bulk in time through the following methods, as follows:
Gets the SID for which the number of submissions exceeds a threshold
Obtain the corresponding Sql_id
Obtain the corresponding SQL through Sql_id
Script 18 How to catch statements that commit too frequently
(2) Regularity, log switching is well documented
The output is as follows, so it is clear that the November 12 problem is clear.
Script 19 Query log switching regular statement
(3) Puzzle case, tracking log explosion failure
XXX project points often have the problem that the database cannot be connected recently, and it has been found that the archive space is full, and it returns to normal after cleaning up the archive. From the AWR report load profile, it can be calculated that the daily log volume is about 160GB, while the archive space of XXX project point is about 1TB, so it is full in less than a week.
Follow these steps to find out who generated these redo logs, in this case the failure was caused by a self-monitoring stored process. The script is as follows:
Resolution process:
Script 20 detects SQL statements with bursting logs
End.
Operator: China Statistics Network (WeChat: itongjilove)
Weibo ID: China Statistics Network
China Statistics Network is the earliest big data learning website in China, public number: China Statistics Network
http://www.itongji.cn
1. Related to shared pools
2. Data buffering is related
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
3. Log archiving is related