Oracle architecture-SQL statement execution process

Generally, the database processes SQL statements through three processes: parsing (parse), executing (exec), and returning results (fetch)

Generally, the database processes SQL statements through three processes: parsing (parse), executing (exec), and returning results (fetch)

Generally, the database processes SQL statements through three processes: parsing (parse), executing (exec), and returning results (fetch)

1. Resolution

When a user initiates an SQL statement, Oracle receives the SQL statement to the oracle instance through the Server Process, and searches for the existence of the statement's execution plan Cache in the Library Cache in the Shared pool.

If the SQL statement does not exist, perform Hard parse to generate the optimal execution plan and load the execution plan and other information into the Library Cache.

If there is a Soft parse, the database analysis time is reduced.

2. Run

Server process first searches for whether the data block corresponding to the execution plan exists in the buffer cache. If so, DML operations (logical IO) are performed directly ), otherwise, you should read the data block from the data file to the buffer cache and then perform DML operations (physical IO ).

3. Return results

If a SELECT statement needs to return results, it first determines whether the sorting is required. If so, it is sorted and then returned to the user.

For other DML statements (insert/delete/update), no results are returned. When the data block in the buffer cache is modified, the server process automatically records the change process of the buffer to the redo log buffer in the SGA, the DBWR and LGWR processes are responsible for writing the dirty data blocks in the buffer cache and the logs in the redo log buffer to the data file and redo log file on the disk respectively.

Resolution type: Hard resolution, soft resolution, soft resolution
SQL parsing requires frequent access to the data dictionary.
Hard parsing:
Determine whether SQL statements have syntax and semantics Problems
Determine whether the objects (tables and views) involved in SQL statements exist
Determine whether the user executing the SQL statement has the permission on the objects involved
Select the optimal execution scheme and generate the execution plan
The generation of execution plans consumes the most system resources (CPU, I/O, and Memory), especially CPU and I/O resources.
Soft parsing: Only checks the syntax, semantics, and object permissions of SQL statements, without generating execution plans.
Soft parsing: No Parsing
SQL> select name, value from v $ sysstat where name like 'parse % ';
NAME VALUE
----------------------------------------
Parse time cpu 1339
Parse time elapsed 17374
Parse count (total) 23639
Parse count (hard) 3060
Parse count (failure) 149
Parse count (describe) 9

Oracle architecture series related articles:

SCN and instance recovery in Oracle Architecture

Checkpoints of Oracle Architecture

Oracle architecture-SQL statement execution process

For more details, please continue to read the highlights on the next page: