Introduction to the association between SQL tuning and shared pool Structures

The Association of SQL tuning and shared pool structures introduces three types of computer resources that affect performance: Memory, CPU, and I/O. By adjusting SGA and PGA to make full use of physical MEMORY, making full use of CPU through parallel processing, and making full use of Hard Disk processing power by adjusting I/O distribution. Server process and PGA are "two grasshoppers on one rope", so sp also has an alias "User Experience process ". SMON is responsible for organizing SGA, such as space fragments. PMON is responsible for diplomacy and detecting client process and server process. Shared pool hit rate (hiting) = L/(L + P). L: Logical read; P: Physical read. The hit rate is not always good, for example, 100 w/(100 w + 10 w), 10 w physical read I/O is definitely a problem. The architecture of www.2cto.com shared pool mainly includes: ① free memory: available memory ② library cache: SQL, pl/SQL, java and other code; Execution Plan ③ row cache: the data dictionary information free memory is split into chunks with big and small parameters, and then start with a chain string. The Chunks mounted on each chain are different, for example, chain_A has 4 k mounted, chain_ B has 8 k and chain_C has 12 k. If the current SQL statement requires 10 k chunk in parse, server process will traverse chain_ B. Assume that the chunk of 11 k is found, then 10 k chunks are used to store the SQL code and execution plan, and 1 k chunks are mounted to chain_A. Remember, only hard parse needs to traverse the chain from free memory and determine the appropriate chunk. The 1 K fragments will be sorted by SMON. From the above discussion, we can also know that oracle maintains the shared_pool through the chain, the advantages of doing so: (I) Serial starting memory blocks (ii) can be traversed. We can also realize the two biggest differences between hard parse and soft parse: I is the biggest and the most serious difference is that, hard parse needs to pick out the optimal one from N execution schemes, as the SQL Execution Plan II hard parse needs to extract chunk from free memory, fill in the SQL and execution plan, then, attach it to the library cache to check the number of hard parse and soft parse: [SQL] www.2cto.com 23:55:59 hr @ ORCL (^ ω ^) select name, value from v $ sysstat where name like 'parse % '; name value --------------------------------- Parse time cpu 1281 parse time elapsed 7048 parse count (total) 41603 parse count (hard) 4892 parse count (failures) 6. How does the 10 k chunk obtained from free memory be attached to the library cache? Server process converts SQL statements and execution plans into ASCI codes and then hash them into a hash value through a series of hash operations, this value is the number of a chain in the library cache, and then the 10kchunk is mounted to the library cache. Total number of chunks in the shared pool: [SQL] 10:20:39 sys @ ORCL (^ ω ^) select count (*) from x $ ksmsp; COUNT (*) ---------- 38189 the chunk can be manually changed through alter system flush. For details about the impact, see alter system flush shared_pool www.2cto.com. We can also conclude that for an SQL statement, the chunk can be divided into static and dynamic parts, the static part is sensitive to uppercase and lowercase letters, spaces, and enter keys. Otherwise, it will be inconsistent when it is converted into chain numbers through hash. Therefore, during the development process, unified programming specifications are crucial, which can avoid reducing hard parse. In addition, we can also use bind variables to make choices on the dynamic part of SQL.
Example: [SQL] 10:42:46 hr @ ORCL (^ ω ^) declare 10:44:11 2 v_ SQL varchar2 (50); 10:44:11 3 begin 10:44:11 4 for I in 1 .. 1000 10:44:11 5 loop 10:44:11 6 v_ SQL: = 'insert into v values (: 1) '; 10:44:11 7 execute immediate v_ SQL using I; 10:44:11 8 end loop; 10:44:11 9 commit; 10:44:11 10 end; 10:44:13/PL/SQL process completed successfully. 10:44:14 hr @ ORCL (^ ω ^) select SQL _id, executions from v $ SQL where SQL _text like '% insert into v values % '; www.2cto.com SQL _ID EXECUTIONS failed ---------- 5rfc2sjfm0cmz 1 Listen 1000 4azck0ysur2cg 1 server process takes over an SQL statement. There are three steps to process the SQL statement: ① parse: Go to the shared pool to view the execution plan, determine soft parse or hard parse ② execute ③ fetch: obtain the required data from the buffer cache and decide whether logical I/O is physical I/O.