SQL parse for Oracle database performance impact

1,sql Types of Parse

SQL Parse is usually divided into hard parsing and soft parsing, and when SQL is executed for the first time, hard parsing occurs, and then execution is performed if the shared pool Can be found in the soft analysis. Therefore, to improve the performance of the data, it is possible to have SQL executed every time found in the shared pool .

Under what circumstances will the 2,sql send hard parsing?

 1   ) Statistics Information Change



2 ) SQL in the table on the Do DDL operations, including Grant and the Revoke .

3 ) execution plan was kicked out Shared Pool

4 ) Opens the Trace

5 ) binding variable length change

6) enabled Outline , 11g enabled SPM

7) SQL statements are the same but the referenced tables are different, for example not the same owner

8) changes in the environment, such as Sort Area Size , Hash Area Size and the locale Setting changed

3. Conditions for soft parsing

When you submit an SQL statementto Oracle ,Oracle first looks for the same statement in shared memory. It is important to note thatORACLE takes a strict match between the two, and theSQL statements must be identical ( including spaces) to achieve sharing , line breaks, etc. ). If it is exactly the same, there will be soft parsing.

4, How can I tell if two SQL statements are the same SQL statement?

1) case and space and letter values are inconsistent:

SELECT * from emp WHERE empno = 1000;

It's different from every one of the following.

SELECT * from emp WHERE empno = 1000;

SELECT * from emp WHERE empno = 1000;

SELECT * from emp WHERE empno = 2000;

in the above statement, the column values are directly SQL statement, we will use this type of SQL called hard-coded SQL or literal SQL

2 ) binding variable names are different

using a binding variable SQL A binding variable with the same name must be used in the statement (bind variables)

For example:

A. the 2 SQL statements are considered identical

SELECT * from emp where empno =: empno;

SELECT * from emp where empno =: empno;

B. the 2 SQL statements are considered to be different

SELECT * from emp where empno =: empno1;

SELECT * from emp where empno =: Empno2;

We call these types of statements above as bound variables SQL .

3) The object name is the same, but the owner is different

Compares the objects involved in the emitted statement with the objects involved in the already existing statement.

For example:

If the user User1 and user user2 have an EMP table, then

Statement issued by user user1: SELECT * from EMP; And

Statement issued by user user2: SELECT * from EMP; is considered to be not the same statement,

Because the EMP referenced in two statements does not refer to the same table.

4) bundle types of bundle variables used in SQL statements must be consistent