Shulou(Shulou.com)06/01 Report--

I the client sends the statement to the server for execution

1 when we execute the SQL statement on the client side, the client will send the SQL statement to the server through the service process process and let the server process process the statement. In PGA, it is divided into UGA and CGA.

UGA user global area, user private information, size 20m, soft parsing session_cached_cursors controls the number of soft parsing, 50-100.

CGA sorting order by, bitmap merging, etc.

The process of the client corresponds to that of the server one by one. In other words, after the client connects to the server, a process will be formed on both the client and the server. We are called the client process on the client side and the server process on the server.

Parsing of II statement

one

When the client passes the SQL statement to the server, the server process parses the statement. This parsing work is carried out on the server side, and the parsing action can be divided into many small actions.

1) query cache (library cache)

When service process receives the SQL statement sent by the client, it does not go directly to the database to query. The service process process converts the characters of the SQL statement into the ASCII equivalent numeric code, which is then passed to a HASH function and returns a hash value, and the server process then goes to the library cache (cache) in the shared pool to find out if the same hash value exists. If present, the server process will use the parsed version of the statement that has been cached in SHARED POOL's library cache to execute, eliminating subsequent parsing work, which is called soft parsing. If the adjustment cache does not exist, you need to do the next step, which is hard parsing. Hard parsing is usually an expensive operation, accounting for about 70% of the total SQL execution time, consuming cup resources, hard parsing generates execution trees, execution plans, and so on.

After performing soft parsing for 3 times, it becomes soft parsing.

2) Syntax reasonableness check (row cache)

When the corresponding SQL statement is not found in the cache, the server process starts to check the validity of the statement. The main purpose of this paper is to check the syntax of the SQL statement, and if the server process thinks that the statement does not conform to the syntax rules, it will feedback the error message to the client.

3) language meaning check (row cache)

If the SQL statement conforms to the grammatical definition, the server process then parses the tables, indexes, views and other objects involved in the statement, and checks the names and related structures of these objects against the data dictionary to see if these fields, tables, views, and so on are in the database. If the table name and column name are not accurate, the database will feedback the error message to the client.

Therefore, sometimes when we write select statements, if the syntax and table name or column name are miswritten at the same time, the system will first prompt for syntax error, and then prompt for column name or table name error after the syntax is completely correct.

4) acquire object parsing lock (control structer)

When the syntax and semantics are correct, the system will lock the objects we need to query. This is mainly to ensure the consistency of the data and prevent other users from changing the structure of this object during the query process.

5) check of data access permissions (row cache)

When the syntax and semantics pass the check, the client may not be able to get the data, and the server process will also check whether the connected user has the right to access the data. If the user does not have access to the data, the client cannot obtain the data. It is important to note that the database server process checks syntax and semantics before checking access permissions.

6) determine the best implementation plan

When the syntax and semantics have no problem and the permissions match, the server process still does not query the database file directly. The server process optimizes this statement according to certain rules.

When the optimizer of the server process determines the best execution plan for the query statement, the SQL statement and the execution plan are saved to the data cache (library cache). In this way, when there is still this query in the future, the above steps of syntax, semantics and permission checking will be omitted, and the SQL statement will be executed directly to improve the efficiency of SQL statement processing.

III binding variable assignment

If a bound variable is used in the SQL statement, scan the declaration of the bound variable, assign a value to the bound variable, and bring the variable value into the execution plan. If SQL exists in the cache in the first step of parsing, skip to that step directly.

IIII statement execution

For SELECT statements:

1) first, the server process determines whether the required data exists in the buffer cache. If it exists and is available, it directly obtains the data instead of querying it from the database file, and increases its access count according to the LRU algorithm.

2) if the data is not in the buffer, the service process process will query the relevant data from the database file and put the data into the data buffer (buffer cache) through the service process process for the convenience of future generations.

For DML statements (insert, delete, update):

1) check whether the required database has been read into the buffer cache. If the buffer cache already exists, perform step 3 directly

2) if the required database is not in the buffer cache, service process reads the data file into the buffer cache

3) Row Exclusive Lock the data rows of the table you want to modify, and then acquire exclusive locks on the data rows that need to be modified

4) record the Redo generated by the data to redo log buffer

5) generate undo data for data modification

6) modify buffer cache

7) dbwr writes the modified buffer cache to the data file

IV extract data

When the statement is executed, the queried data is still in the server process and has not yet been transferred to the client's user process. So, in the server-side process, there is a piece of code that is specifically responsible for data extraction. His function is to return the results of the query to the client process so as to complete the whole query action.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.