Hard Parse & amp; Soft Parse, hardparse

Hard Parse & Soft Parse, hardparse

Each DDL execution requires hard parsing.

SQL parsing process

Oracle will take the following steps to process this SQL statement:

1. syntax check: check whether the spelling of this SQL statement is correct.

2. semantic check: such as checking whether the access object in the SQL statement exists and whether the user has the corresponding permissions.

3. parsing SQL statements (prase): parses SQL statements using internal algorithms to generate parse tree and execution plan ).

4. execute the SQL statement and return the result (execute and return)

Five steps:

1: syntax analysis

2: Permission and object check

3: Check whether there are identical items in the Shared Pool. If yes, skip step 4 and Step 5 and run the SQL statement. Then, calculate soft parse.

4: select an execution plan

5. generate an execution plan

3. Explanation:

Oracle will calculate the HASH value of the passed SQL statement using the HASH function, and compare it with the HASH value of the existing statement in the shared pool to see if it is one-to-one. The HASH value of SQL statements in the existing database can be obtained by accessing the HASH_VALUE column in the data dictionary such as v $ SQL, v $ sqlarea, and v $ sqltext.

If the HASH values of SQL statements are consistent, ORACLE needs to re-check the semantics of SQL statements to determine whether the values are consistent. So why does Oracle need to check the statement text again? Which of the following statements does not correspond to the HASH value of the SQL statement? In fact, even if the HASH value of the SQL statement has been matched, it cannot be said that these two SQL statements can be shared.

Dictionary Cache

The data dictionary is a collection of database tables and views containing reference information about the database, its structures, and its users. oracle accesses the data dictionary frequently during SQL statement parsing. this access is essential to the continuing operation of Oracle.

The data dictionary is accessed so often by Oracle that two special locations in memory are designated to hold dictionary data. one area is called the data dictionary cache, also known as the row cache because it holds data as rows instead of buffers (which hold entire blocks of data ). the other area in memory to hold dictionary data is the library cache. all Oracle user processes share these two caches for access to data dictionary information.

Parsing

Parsing is one stage in the processing of a SQL statement. When an application issues a SQL statement, the application makes a parse call to Oracle. During the parse call, Oracle:

• Checks the statement for syntactic and semantic validity

• Determines whether the process issuing the statement has privileges to run it

• Allocates a private SQL area for the statement

Oracle also determines whether there is an existing shared SQL area containing the parsed representation of the statement in the library cache. if so, the user process uses this parsed representation and runs the statement immediately. if not, Oracle generates the parsed representation of the statement, and the user process allocates a shared SQL area for the statement in the library cache and stores its parsed representation there.

Note the difference between an application making a parse call for a SQL statement and Oracle actually parsing the statement. A parse call by theapplication associates a SQL statement with a private SQL area. after a statement has been associated with a private SQL area, it can be run repeatedly without your application making a parse call. A parse operation by Oracle allocates a shared SQL area for a SQL statement. once a shared SQL area has been allocated for a statement, it can be run repeatedly without being reparsed.

Both parse CILS and parsing can be expensive relative to execution, so perform them as seldom as possible.

Tell me why java uses preparestatement

The analysis is divided into Hard Parse and Soft Parse ). After an SQL statement passes the syntax check, Oracle will first go to the shared pool to find whether there is the same SQL statement. If it finds it, it will be called soft analysis and then execute the SQL statement. Hard analysis mainly checks whether all objects involved in the SQL are valid and their permissions. Then, the execution plan is generated based on the RBO or CBO mode before the SQL statement is executed. It can be seen that hard analysis has a lot more actions than soft analysis, and the key here is "whether the same SQL exists in the shared pool", which depends on whether to use the bound variable. In addition, oracle9i introduces soft parse, which is first searched in the session cursor cache list in pga (the length of the session cursor cache list is determined by the session_cache_cursor parameter ), if this SQL statement is not found, check shard_pool. for Oltp systems, the cost of hard analysis is much higher than that of execution. We can learn this through 10046 event tracking. (2) The number of SQL statements in the shared pool is too large, and the reusability is extremely low. This accelerates the aging of SQL statements, resulting in excessive fragments in the shared pool. The number of different SQL statements in the shared pool is huge. According to the LRU principle, some statements gradually ages and are eventually cleared out of the shared pool. This causes the hit rate in shared_pool_size to decrease and the number of fragments in the shared pool to increase, insufficient memory space. To maintain the internal structure of the shared pool, you need to use latch, a lock with a short internal lifecycle, which will use a large amount of cpu resources, resulting in a sharp reduction in performance. The absence of binding variables violates the design principles of the oracle shared pool and the idea of sharing the design. 2. How to check whether the Bind Variable select * from v $ SQL or v $ sqlarea is used to check whether there are many similar statements except variables, others are the same. 3. How can I bind a variable? When writing java programs, we used to define JAVA program variables and put them into SQL statements, such as String v_id = 'xxxxx '; string v_ SQL = 'select name from table_a where id = '+ v_id; the above Code seems to use the variable v_id, but this is a java program variable, rather than the oracle binding variable, after the statement is passed to the database, the java program variable has been replaced with a specific constant value and changed to: select * from table_a where name = 'xxxxx'; assuming that this statement is executed for the first time, hard analysis is performed. Later, the v_id value in the same java code was changed (v_id = 'yyyyyy'), and the database received the following statement: select * from table_a where name = 'yyyy '; ORACLE does not consider the preceding two statements to be the same. Therefore, it performs a hard analysis on the second statement. The execution plans of these two statements are the same! In fact, you only need to change the above java code to the following, and then use the oracle binding variable: String v_id = 'xxxxx'; String v_ SQL = 'select name from table_a where id =? '; // Embed the binding variable stmt = con. prepareStatement (v_ SQL); stmt. setString (1, v_id); // set the value of stmt.exe cuteQuery ()...... remaining full text>

What is the difference between: = and = in oracle?

Variable binding refers to using variables rather than constants in the conditions of SQL statements. For example, there are two SQL statements in the shared pool,
Select * from tab1 where col1 = 1;
Select * from tab1 where col1 = 2;
For oracle databases, this is two completely different SQL statements, which both require hard parse. Because oracle calculates the hash value of each character in the memory based on the SQL statement text, although the preceding two SQL statements have only one character, oracle uses the hash algorithm to obtain different hash addresses in the memory. Therefore, oracle considers this statement to be two completely different statements. If you rewrite the preceding SQL statement to select * from tab1 where col1 =: var1; and then query the variable var1 by assigning a value, oracle will perform hard parse for this statement for the first time, in the future, only soft parse will be performed. Assuming that a statement has been repeatedly executed for several 100,000 times, the benefits of using bind var are enormous. If bind var is not fully used for an application, it will be accompanied by severe performance problems.

Variable binding is relative to text variables. The so-called text variables refer to the SQL statement that directly writes query conditions. Such SQL statements need to be parsed repeatedly under different conditions, to bind a variable is to use a variable to replace the direct writing condition, query the bind value and pass it at runtime, and then bind it for execution. The advantage is that hard Parsing is reduced, CPU contention is reduced, and shared_pool is saved. The disadvantage is that histogram cannot be used, which is difficult for SQL optimization.