Oracle Learning Notes
Analysis of SQL statement execution Process lectures
This lesson goes into the database by telling a SQL statement
and its entire execution process in the database
String The architecture inside the database.
Let's take a closer look at the relationships between the various processes, storage structures, and memory structures within the Oracle database.
First, there are clients, instances, and databases throughout the system.
There are three types of files in the database
Control file CTL, data file dbf, log file
There are six large pools in the SGA in the example
The first large memory area shared pool
Second largest memory area buffer cache
The third block is redo log.
We mainly talk about the three big pools above they are prone to problems,
In particular, the shared pool and buffer cache.
The other three pools don't go wrong.
The next few chapters focus on the shared pool, buffer cache.
A) shared pool
The user connects to the instance,
Instance will open a process specifically for this connection,
This process is called the foreground process, also called the server process, which translates into English called server processes.
It is the process of the instance,
Oracle assigns it a single PGA space.
Enter a SQL on the client,
Enter Back,
Such as:
Sql> select * from Dba_data_files;
Get a bunch of data after execution.
This statement is entered on the client,
The server process is then sent through the client-to-instance chain.
On the service side,
SQL statements are sent to Oracle over the network and accepted and received by Oracle's server process.
The server process accepts this SQL statement
And then do the things
1. Oracle recognizes this statement, but cannot run directly
Oracle needs to parse the SQL statement into an execution plan before it can execute.
2. After parsing, Oracle takes the execution plan to execute.
The process of parsing should be more complex,
Includes many steps
The server process will first determine if there is a problem with the SQL statement syntax and will not perform the subsequent operations if there is a problem.
Also look at the SQL statements involved in the table, the view in the database in the end there is no.
Next look at the user who entered the SQL statement has no permissions on the tables and views involved in the SQL statement.
There's another important job,
Determine exactly how the SQL statement should be executed.
An SQL statement can have n execution scenarios,
Some of the execution scenarios in the N program are good or bad.
At this time serverprocess to find an optimal execution scheme from the N execution scheme of this SQL statement.
The execution plan is then generated.
In the process of choosing the optimal execution scheme, it accesses many objects, accesses a lot of data,
Otherwise it can't judge which scheme is the best in thin air.
You need to consume a lot of computer resources
The main consumption of CPU resources, followed by IO resources, and in addition, memory resources.
Since parsing consumes resources, another problem comes to mind
A user executes an SQL statement
This SQL statement requires parsing and parsing the build execution plan.
A user has executed.
b The user is connected and a user does the same business,
For example, two salespeople do the same thing as a withdrawal.
It is possible to execute the same SQL statement.
A execution plan that is generated when the user executes, if it can be cached.
b When the user comes up, if the cache execution plan can be found,
It does not need to be parsed, you can directly take the execution plan to execute.
There's a problem in the database,
Do we need to cache the execution plan for SQL statements and SQL statements?
Now it is necessary to see.
The execution plan that corresponds to the SQL statement and SQL statement is cached in the shared pool.
Look Back:
An SQL statement is entered on the client,
Reach Oracle instances over the network,
Instance in server process to receive this statement,
The server process receives an SQL statement after it is received
First into the shared pool,
To see if this SQL statement is cached in the shared pool.
If there is a cache,
The server process then finds the SQL statement and the corresponding execution plan in the shared pool.
And then go back to execution.
Reduced the steps for parsing.
If server process does not find this SQL statement and the corresponding execution plan in Sharedpool,
The server process then parses the SQL and eventually generates the execution plan.
Then proceed to the next step.
The first thing the server process gets to do with the SQL statement is:
Find
If not find it will parse.
Both of these processes are done by the server process.
The main thing about the shared pool is caching the SQL statement and the execution plan for the SQL statement.
Access to the shared pool and modifications to the Sharedpool are all serverprocess processes.
II) buffer Cache
When the SQL statement is resolved, the execution plan is generated and then executed.
When the SQL statement executes, you want to fetch the data
such as statements:
SELECT * FROM Dba_data_files
The data to be taken from the Dba_data_files table.
The data in the table is in the DBF file of the disk,
The server process is then executed according to the execution plan of the SQL statement.
The execution plan for this SQL statement takes the data out of DBF and returns it to the user.
Buffer caches are used to cache DBF data.
It's very necessary to cache.
Because a user accesses a table, B users are more likely to access the table.
A user's chance to access this table is also larger.
If there is no buffer cache,
Each time you access DBF, physical IO occurs, and the performance of the database is very low.
When a process accesses DBF to access our data,
To access data from a table,
Because the server process knows the concept of caching buffer cache,
First, find the data in the buffer cache that corresponds to the table in buffer cache.
If so, the server process accesses the buffer cache directly,
Take all the data out and return it to the client over the network.
If the Buffercache does not have the data it is looking for, this time it will go to the disk dbf to find.
The data is removed from DBF and is not returned directly to our users,
Take it out and put it in the buffer cache.
And then return it to our users from the Buffercache.
For Oracle, data is taken from DBF and taken to the Buffercache, for physical reading,
This operation is done by ServerProcess.
Speak Two concepts:
Logic IO
Logic IO
Physical IO
Physical IO
Logical Io,
ServerProcess Execution of SQL statements
Read from Buffercache, called logical reading, also called memory read.
Physical IO,
No data found in Buffercache,
It goes to disk to read, this read called physical read, also called Disk read.
We want to read more logically and read less physics.
This greatly reduces the number of physical reads, which in turn increases the access speed of the database.
The concept of Hit ratio
The hit rate is raised in the cache,
In the Buffercache there is a hit rate, the calculation method L/l+p, is: logical reading/logical reading + physical reading
The higher the hit rate, the more logical reading is relatively large, the physical reading is relatively small.
This is relative.
We hope the hit ratio is relatively good.
High hit rate means less physical reading and more logical reading.
Physical read-high IO will be very busy, and the database will be slower.
Like what:
Oracle takes 100 reads, with only one physical read of 99 logical reads.
Algorithm 99/99+1
Hit Rate is 99%.
This time we say that Buffercache's hit rate is 99%.
Oracle reads data 100 times with only one physical read and 99 logical reads.
Greatly improves the read performance of the database. That's a good thing.
For Oracle databases,
There must be a problem with low hit rate.
High hit rate is not necessarily a problem.
Example:
Logical reading 100,000, physical reading 10,000
Physical reading is also quite high in a time period.
When we calculate the hit rate, we find that
Because the logic reads very big, the hit rate is also very high,
But the overall database service speed is also very slow.
So there must be a problem with low hit rate, high hit rate is not necessarily no problem
It is possible that the logical reading is very high, even if the physical reading is relatively high, the calculation hit rate will be very large.
We think it's okay, but not necessarily, we have to care about the physical readings per second.
In fact, there are Linux commands to monitor system device performance
1, Vmstat
Vmstat is a comprehensive performance analysis tool that
You can observe the system's process state, memory usage, virtual memory usage, disk IO,
System (interrupt, Context switch), CPU usage, etc.
[Email protected] ~]$ Vmstat
procs-----------Memory-------------Swap-------io------System------CPU----
R b swpd free buff cache si so bi bo in CS US sy ID WA
3 0 204 61596 39456 669880 0 0 13 35 303 174 48 22 30 0
2, Iostat
The iostat is mainly used to monitor the IO load of the system equipment and to display the statistical information of the system at runtime.
[Email protected] ~]$ Iostat
Linux 2.6.9-78.ELSMP (REDHAT4) July 14, 2016
AVG-CPU:%user%nice%sys%iowait%idle
47.79 0.05 22.11) 0.43 29.61
Device:tps blk_read/s blk_wrtn/s Blk_read Blk_wrtn
SDA 6.37 51.81 142.53 3267484 8989120
SDA1 6.17 51.01 137.64 3216970 8680600
Sda2 0.18 0.44 3.86 27814 243424
Sda3 0.02 0.34 1.03 21716 65096
From this data, you can tell if the server is busy.
Not only depends on the hit rate feedback problem, but also depends on the physical reading quantity problem.
serverprocess when executing the SQL statement,
When you need to read the data from DBF on disk,
This data serverprocess read from DBF,
Then read the Buffercache and then return to the user from Buffercache.
III) DBWN LGWR process
The SQL statement is currently read on a table,
It is also possible to modify a table, delete it,
SQL Split and delete change
For example, let's say we want to modify the data for a table
ServerProcess, as before, read the table into memory.
The modification also reads into the memory,
The serverprocess modifies the table in memory.
When modified, the Oracle database will generate a log of all modifications to the DBF database.
Logs are also generated by serverprocess.
The log will be written into the Redologbuffer.
After the changes are completed,
Because the data inside the memory is modified,
The data in the memory is different from the data in DBF,
Not the same, you need to write back to DBF,
Who is responsible for data from memory write to hard disk,
is not serverporcess, is another process, called Dbwrite namely DBWN process
This process is responsible for writing the ServerProcess modified data back to disk,
Who is responsible for writing the log inside of Redlog buffer to the Redlog log file?
It's not serverprocess, it's logwrite, LGWR process.
Dbwn and LGWR are called background processes,
ServerProcess called the foreground process.
ServerProcess is only responsible for reading out the data in memory Buffercache inside the change,
And then generate logs to write to Redologbuffer inside,
It is only responsible for these things.
It is not responsible to write the modified data back to disk, write the log back to disk,
Be responsible by DBWN,LGWR.
Why is serverporcess only responsible for reading and not writing to disk?
This is actually a skill in oralce design.
ServerProcess is a direct service to the user,
The user gave the SQL statement to ServerProcess,
ServerProcess do a few things,
The SQL statement is parsed, then executed, then the data is fetched, and the results are returned to the user after fetching the data.
ServerProcess is always dealing with users,
If the serverprocess is slow, users will feel the database is slow.
We want to let serverprocess do the most meaningful work,
For some serverprocess can not do things, give others to do.
This will give the user the best user experience.
ServerProcess is responsible for reading out the data.
Then the changes are finished and the results are returned to the user.
It is the result of the modification, when it is written back to the disk, it does not go to the tube, let dbwn to do.
Since when to write back to the disk, the user does not care,
The user only cares, I modify a certain data, and then tell me that the data has been modified, it only cares about this problem.
What the user cares about is ServerProcess's concern.
So we put in some backstage things that we can do,
Try not to do it for serverprocess.
Let the background process be dedicated to doing it.
So the Oracle database has the concept of a background process.
The background process is characterized by the user does not know, it in the back quietly do,
The user only cares if I enter an SQL statement and get the result quickly.
ServerProcess don't care about some write disk operations,
Is the background process to do.
So the process of Dbwn and LGWR is produced.
So there are foreground and background processes.
We are in the database optimization, and database performance monitoring,
Our main concern is the serverprocess.
We don't care too much about dbwn and LGWR.
The speed of serverprocess direct feedback to the database speed.
Direct feedback to a user's perception of the database.
No matter how busy the background process is, just serverprocess quickly and easily
Users will find it quick and easy.
Users will feel the database is fast.
It is possible that the background process is very busy at this point, which is an ideal result for us.
The background process is easy, but serverprocess is busy.
Users feel that the database is slow,
You find that the CPU usage of the database is not high.
This is a relatively bad result.
One of the processes that we are very concerned about in the Oracle database is serverprocess.
After the optimization, we often go to see the serverprocess is very busy at the moment.
Which serverprocess is currently very slow.
This is one of our targets.
IV) CKPT process, Smon, Pmon
There is also a checkpoint process, which is also a process for Oracle.
One of the five major background processes.
This process is run periodically,
Dbwn and LGWR are also cycle run, but the frequency is high, the load is relatively large, are relatively busy.
Especially dbwn is very busy.
But Ckpt (checkpoint process) This process is relatively easy,
Just run periodically,
Writes some current state information about the database to the head of the control file and data file.
The header of each data file records some of the status information for this data file.
Checkpoint this process, it is responsible for updating the control file and the header of the data file.
Its load is relatively light.
There's hardly anything to do.
There are two more processes
Smon (System Monitor)
This process is the maintenance of the database instance
Give an example
SQL statements and execution plans in shared pool
It took a long time and there could be a lot of debris inside.
The Smonitor will then integrate the fragments.
In other words, Smonitor is responsible for cleaning and maintaining the database instance internally.
It in the Lord.
Pmon (Process Monitor)
It's out of the main
In the main, there is some maintenance within the SGA.
The serverprocess is maintained outside the main.
Example
The customer network suddenly broke, ServerProcess also has been for the user to start.
The Pmonitor will start periodically,
When it was started, it was discovered that a serverprocess client was dead.
He will clean up the serverprocess.
Including turning this serverprocess process off,
Take this serverprocess corresponding to the PGA memory space to clean it up.
Pmonitor main outside Smonitor master
Five major processes are now being said.
V) ARCN Process
The database file also has an archive log file.
There are many logs in the Oracle database
[[email protected] jiagulun]$ ls
Control01.ctl example01.dbf Redo03.log temp01.dbf
Control02.ctl Redo01.log sysaux01.dbf undotbs01.dbf
Control03.ctl Redo02.log system01.dbf users01.dbf
You can see that there are many. log logs in the database.
Oracle has Redolog, which is divided into groups, by default divided into three groups
First Oracle uses the first set of logs to log in, and the first log file is full for a long time.
It started with the second group, the second group was full with the third group,
The third group was full of it in turn with the first group.
The first group was covered.
Oracle can maintain up to three sets of logs.
Want to keep more logs,
Because the log has a lot of functions,
Oracle has created a working mode for archiving.
Write the first set of logs, which is full.
Oracle initiates a process called ARCN.
This process will archive the log to the bottom of another directory, and another name,
Size is the same as it.
Then start writing the second log file, the second one is full cut to the third one,
This process writes the second log to the directory to which it was filed.
Then write a third, fourth.
In the archive this location,
All Oracle logs are retained
We're looking for an earlier log of older data to be found in the archive.
Let's say a SQL statement,
A workflow that comes in from the request to get the data back to the user.
Further Oracle instance structure, give you a rough talk about, later will be detailed.
VI) data blocks in the Buffercache
Server process It will first read the data from the database data file,
At the same time the server process to find data when the first in memory to find, in Buffercache inside.
The server process also modifies the data inside.
The revised data is dbwn responsible for writing;
The server process reads the Buffercache and reads only the disk data files.
Buffercache write back data file requires DBWN.
There are several cases of data in the buffer.
is connected
Clean
Idle or unused
Dirty
The data in DBF reads into the memory SGA,
The data in the Buffercache is consistent with the data in the DBF file of the disk,
Call this data to be clean.
There's also some memory space that's not used
This is idle or unused.
And to this block of data,
ServerProcess in the memory of its data modified,
The data in the memory is inconsistent with the data on the disk.
The data inside this memory is called dirty data.
Dirty data needs to be written back to the DBF file.
After writing back, the two were in agreement again and became clean data.
And the data in the memory.
Currently serverprocess is reading it or is writing it,
At that instant, the data block was called a pin.
The translation is called connected.
After writing, it became a dirty data immediately.
PIN is read and write for a moment,
The read-write instantaneous speed of memory is very fast.
Connected is pin live.
As the database runs,
Buffercache slowly in use,
The data blocks are dirty, clean, free
Once again, when a data in DBF is transferred to memory, it takes precedence over idle unused.
If all the data in the Buffercache has been used, there is no idle,
It goes on to use clean.
Because clean data means that there is a data on the disk that is the same as it.
This allows the new data to be overwritten with clean blocks.
When the original clean data block is needed, the original clean data can be transferred into memory again.
Which means, for Buffercache,
With clean or idle data, memory data can be reused.
Can not be directly covered by the dirty?
No!
In order to use the memory space that this dirty block of data occupies,
For example, it's all dirty inside,
I still need to use the memory again,
The dbwn is triggered and the dirty block is written back to disk.
After writing back to the disk, the original block becomes clean.
The clean can be reused.
This is a few states of the data block in the Buffercache.
VII) Summary
Before you tell us an SQL statement that goes into an entire Oracle implementation process.
Further the memory and process structure of the database,
And some information about some of the physical structures.
This is the architecture of Oracle as a whole.
We'll talk about it in a block after class.
including physical structure, memory structure, process structure, and so on.
In the memory structure there is also a block of content, we will also separate to speak.
Oracle itself is a software for the computer,
And the structure of other software is actually the same, although some parts have their own characteristics.
The structure of the computer program,
Only two parts are included:
The data section and the code that processes the data.
These two parts vary according to the needs and the purpose of the procedure.
The ultimate purpose of the program is to process data.
The proportion of the data portion of the database is large,
The proportion of the general program data part is small,
But it's all about dealing with it.
Programmers put most of their energy into the code, but they are all about presenting the final data.
July 17, 2016
Text: Yun Zheng
Oracle Learning Notes SQL statement execution process profiling lectures
