[Original link] http://nvd11.blog.163.com/blog/static/200018312201301310585758/
[Original VIDEO] Http://www.jiagulun.com/thread-2674-1-1.html
1. Database files
The Oracle database file can be divided into 3 types, namely:
control files: storing the database's own physical structure information
data files: storing database Data ~
log files: includes redo log files and archive log files to record changes in database data.
Such as:
2. db instance
Users and applications do not have direct access to the data for the database files, and the Oracle server starts (1 or more). RAC cluster). The user can access the database data by connecting to the instance. The
instance has two components, namely:
system global memory Area (SGA) : Servers are specifically partitioned into memory blocks used by Oracle instances.
Oracle process: Includes server processes and background processes, which are parsed later.
2.1 system global Memory Area (SGA)
The SGA can be divided into 6 chunks, namely Java pool, shared pool, database buffer cache, large pool, streams pool, Redo log buffer
[note] The V$SGA view can be used to view the size of the SGA chunks.
This article describes the execution flow of the SQL statement, so it mainly explains the 3 chunks:
share buffer (shared pool)
Database buffering cache (db buffer cache)
Redo log buffers (redo log buffer)
Such as:
2.2 Server Processes
When a user (client) wants to connect to an Oracle database, Oracle creates 1 sessions and creates 1 processes dedicated to the session on the server , which is the server process.
Such as
[note] Whenever 1 new users create 1 new connections to the database, Oracle will create 1 server processes for each one.
2.3 PGA (Program Global area)
For the above server process, Oracle allocates a certain amount of memory on the server for each server process, which is the PGA, noting that several sessions will have several corresponding SGA blocks, so the server needs a lot of memory.
can be used
Select SUM (pga_userd_mem) from v$process
Statement to see the total PGA size currently in use.
:
3. Client -to-server SQL statement transfer At this point, the user enters several SQL statements on the client, such as 1 ordinary stored procedures, with read and write actions.
What does this statement transmit through? The answer is the session, so what does the server use to accept the SQL statement, the answer is server Process.
:
4. Server process will determine whether the SQL statement is legal (syntax, permissions). If the SQL syntax is wrong, or if the corresponding table or view or Procedure does not have permissions, it will return the error message directly.
5. The server process generates an execution plan based on the SQL statement (EXECute PLAN).
Oracle cannot execute SQL statements directly, the execution plan must be generated before Oracle executes according to the execution plan. Generating an execution plan to access many database objects is an action that consumes server resources (cpu,io,memory).
And since the same SQL statement may have multiple users repeating the execution, is it possible to generate an execution plan every time? The Shared pool in the SGA is then functioning, and it caches the SQL execution plan.
Therefore, the server process will first look in the Shared pool for any existing execution plans, if any, directly. If none, generate one yourself, and then see the scenario put this execution plan into the shared pool.
6. The server process is based on the execution plan
to fetch ( write ) data.
All right. When the server process gets the execution plan, it can fetch the data. Where does Oracle's data fit in? Put in the data file, this everyone knows, but server process is not directly to access the data file?
Because the computer's time consumption mainly in the physical IO, so as far as possible to avoid physical read and write, so the database buffer cache inside the SGA is working. To be clear, the database buffer cache is used to buffer data in the files. This avoids the ability to read and write data files.
So after the server process gets the execution plan, the first step is to go to the database buffer cache to find out if there is no ready-made data, if the best, if there is no or the data in the cache is not full, you can only access data files.
When data files are obtained, they are not sent directly to the user client, but are placed in the database buffer cache for use by the current or other user.
7. logical read, physical read, and cache hit ratio.
By knowing that the so-called
Logical Readis to read the data from the cache (typically memory). and
Physical Reading, which is to read the data from the disk (data file).
The cache hit rate is the ratio of logical reads/(logical reads + physical reads) during data fetching. Of course, the closer the ratio of 1, the better, because the physical reading time, unless there is a server with SSD to do hard disk.
Of course, hit rate is not the only indicator of database health, because when the logical reading is very large, even if the physical reading is very big, this ratio is very good, so sometimes care
physical reads per second (TPS)。
You can use the Iostat command under Linux to view the physical reads per second of the current disk.
8.
modifying data in the cache The server process takes out the data into the cache and then modifies the data. Because modifying the data is likely to generate a lot of buffered data, this action is done in the Database buffer cache. This is easy to understand.
9.
modifying data results in redo logs
The above mentioned log is used to record data changes in the database, so the data changes to generate a certain amount of log data. So are these logs written directly to the log file? Write log file is also a physical read, so the SGA has a Redo log buffer, is the log cache, dedicated real-time storage generated log data.
.
The final server Process passes the returned data or information through the session back to the client
After the Server Process has finished reading and modifying the data, the results are returned to the user.
11. Write data cache and log cache to disk
In fact, the entire SQL statement execution process has been completed as of the previous step. One might ask, does the data that the Server process modifies and the logs that are generated are still in the SGA, and they don't have to be written to disk?
The answer is definitely needed, but these actions are not within the SQL execution process, and these actions are not the responsibility of the server process, they are separated by
DBWRAnd
LGWRThis two process is responsible.
:
DBWR:Database writer, one of the background processes, is responsible for writing the modified data in the database buffer cache to the data file.
LGWR:Log writer, one of the background processes, is responsible for writing log data in Redo log buffer to the log file.
12. Why doesthe o racle separate the server process and background processes?
Why write data files and log files to the background process to complete it? In fact, we can find in the process, the server and the user to deal with only one process, is the server process, so the speed of the server process directly affect the user's feelings. No matter how busy the background process is, users still feel the database is fast, as long as the server process responds quickly. Instead, the background process does nothing, the server CPU is idle, but the server process slows down, and the user feels the database is slow.
Therefore, we should try to streamline the server process action, see the background process DBWR and LGWR What is the action, disk write action Ah! So these actions can be done slowly after the SQL process is complete.
The only physical operation that the Server process does is physical reading, which is no way to avoid it because the data is on disk. Unless there is a way to predict the data the user wants to extract, take it out in advance. But it doesn't have that much memory.
13. By the way, the remaining 3 major system processes CKPT, Smon, Pmon
Oracle has 6 large pools of SGA, 3 large database files, and 5 large system processes. DBWR and LGWR have already been introduced. The rest of the remaining 3:
CKPT: Checkpoint checkpoint process, responsible for updating the control file and data file header information, control files at the beginning of this blog has been introduced. The header information of the data file is the status information of the current data block.
Smon: System Monitor monitors the maintenance of SGA and background processes, such as merging fragments from the SGA.
Pmon: Process Monitor, which mainly refers to the server process. For example, a user suddenly dropped the line, but the server process is still on the server, Pmon will be a period of time to clean up the process and release the SGA.
Finally thanked old flame teacher attentively to do the teaching video, really is easy to understand, cut a picture. Ha ha
[GO] Oracle SQL statement execution Process graphic analysis
