753 awards invite new and old bloggers to enter the cloud habitat, ask technical staff to advertise

gossip

After looking at the characteristics of the Oracle optimizer, we all know that one of the fascinating aspects of the Oracle optimizer is the design of the Shared pool, specifically the Library Cache in the shared pool. The result of this design is to make the execution plan cacheable. Hence the concept of soft parsing, which ensures that the same SQL can be parsed only once without changing the statistics. Compared to soft parsing, the Oracle optimizer also has a more special behavior, soft parsing, the process of soft parsing SQL will consume less overhead, faster execution.

Cursor

Oracle SQL Cursor Shared Cursor And Session Cursor 1. Shared cursor. The shared Cursor of an Oracle database is divided into Parent Cursor and Child Cursor. The Parent Cursor stores the SQL text, and the Child Cursor stores the SQL execution plan. 2. The Session Cursor. It is cached in the PGA’s private SQL area. It is created during SQL execution and released after SQL execution. The difference between a Session cursor and a shared cursor is that a Session cursor is private to the Session. This is also the difference between a PGA and a SGA.

PGA

Let’s talk about the PGA. 1.PGA=UGA + CGA 2. UGA=user session + CURSOR state(private SQL)

User Session


Stores user permissions, roles, and performance statistics.



private sql


Fixed parts: binding information, data structure information, Pointers. Created with session creation and released when session ends


Dynamic part: Perform intermediate result sets of SQL, such as multi-table lookup, sorting. Create as SQL is created and release as SQL is created

Therefore, the session Cursor corresponds to the dynamic part of the private SQL area in UGA, which is created when SQL statements are executed and is mainly used as the cache area of the SQL intermediate result set. However, when an SQL is executed consecutively for more than three times in the same session, the cursor will be cached. When the same SQL is executed again, the open cursor is used directly.

Meaning of soft and soft parsing

First take a look at the SQL parsing process, which can be summarized as follows. 1. Check syntax, semantics and permissions; 2. Query conversion (statement equivalence conversion, also known as logic optimization); 3. Perform recursive query to obtain statistics. Calculate the cost of each execution path based on the statistics. 5. Select the path with the lowest cost as the execution plan

Soft parsing is designed to save on steps 3 and 4, which are the most inherently performance overhead in the entire parsing process (and actually step 2). What about soft parsing? As mentioned earlier, when the session cursor is cached, the next time the same SQL is executed in the same session, the open cursor can be used directly. This process is called soft and soft parsing. Soft parsing even eliminates steps 1 and 2 and reduces the overhead of opening cursors compared to soft parsing. Using the open cursor directly means that after submitting the SQL request, you can then go directly to the execution plan to execute the SQL, while soft parsing has to go through the SQL hash lookup before hitting the execution plan.

You can query related views to view the status of soft and soft resolution in a session, as follows

session cursor cache count

This number refers to the total number of such SQL Cursors cached by the current session

You can set the maximum number of cursors that can be cached for a single session

Session CURSOR Cache hits Number of times that the session cursor cache is located. Each soft or soft parse that occurs in a session represents a hit to the session CURSOR cache.

conclusion

Soft and soft parsing is essentially a special case of soft parsing in order to save on the performance overhead of parsing. Regardless of the parsing process, ORACLE always generates an execution plan following the following logic when parsing and executing the target SQL. 1. Search for the PRIVATE SQL area of the PGA. If a match is found, soft and soft parsing occurs. 2. Match the HASH value of the SQL in the Library Cache. If soft parsing occurs, go to Step 3. 3. When hard parsing occurs, a recursive query is executed to obtain statistics, which are used to calculate the execution cost, and an execution plan is generated, which is cached in the Library Cache.