Oracle SQL Advanced Programming Study Notes (I) SQL Execution Theory

Oracle SQL advanced programming learning notes (I) SQL Execution theory 1. Shared Pool SGA1, each SQL statement executed contains parsed content in the shared pool, these statements are stored in the library cache ). no matter how many users want to execute the same SQL statement, Oracle will only parse it once; 2. Data dictionary cache stores all system parameters and other database information; 3, the memory allocated to the shared pool is limited. When new statements are executed, oracle uses the least recently used algorithm (LRU) to manage the memory of the Shared Pool, overwrite SQL statements with a high execution frequency. 4. Soft parsing and hard parsing: parsing includes the syntax of the validation statement, checking the submitted object and verifying the object's user permissions. If the check passes, check whether the statement has been executed, if yes, oracle reuse directly parsed content, which is called soft resolution; otherwise, oralce will execute all the work to generate an execution plan for the current SQL statement, it is stored in the cache for later reuse. This resolution is called hard parsing; 5. You can query the SQL statements currently stored in the cache by querying v $ SQL. 6. When executing SQL statements, even statements that represent the same function are case-insensitive and have no comments, Oracle will be treated as different SQL statements, for example, select * from employee and SELECT * form employee. oracle will generate different execution plans for them. 7. If constant queries are used for conditional queries, for example, if select * from employee where employee ID = 101 and select * from employee where employee ID = 102, oracle performs hard parsing. However, you can use the Bind Variable, for example, select * from employee where employee ID =: v_empl_id. 8. latches: used to protect the Library's high-speed cache from being modified by two concurrent sessions, that is to say, if oracle wants to check whether the SQL statement you want to execute already exists, it needs to obtain a lock in the database cache and check whether the lock is idle. If it is idle, it gets the latch and does its work. Otherwise, it will iteratively round whether the latch is idle. therefore, it is very important to write the code correctly so that the latches are rarely used. 9. The SGA cache is used to store data blocks after the database reads data from the hard disk or writes data to the hard disk. A block is the smallest unit for oracle operations. It is required to verify whether all blocks in the cache must obtain the latches. Write SQL statements to obtain data and try to access as few data blocks as possible. If the database is in the cache area, it is a logical read to access the data block. Otherwise, it is a physical read. 2. query conversion 1. After the SQL statement passes the syntax permission check, the query enters the fast conversion phase into a series of queries. The query converter may change the structure of the query you originally wrote, but will not change the final result set. The purpose of query conversion is to determine whether a better query plan is provided if the query structure is modified. 2. Try to merge 3, push the predicate 4, and use materialized methods to rewrite the query.