Oracle Learning Performance optimization (i) SQL statement processing

What exactly does Oracle do when it submits an SQL command to Oracle? A good understanding of this problem can help you better analyze the optimization of SQL statements.

Executing an SQL statement from start to finish requires 4 steps:

• Analysis--parsing, semantic analysis, and shared pool checking of submitted statements.

• Optimization-generates an optimal plan that can be used to execute statements in the database

• Row Resource generation – Get the best plan for your session and build an execution plan

• Statement execution-completes the output of the row resource generation step that actually executes the query. For DDL, this step is the end of the statement. For SELECT, this step is the beginning of the data.

The above steps can be omitted, such as optimization, row resource generator phase. This can save a lot of time.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/71/7D/wKiom1XRyoCCObRBAADlo46nyKY605.jpg "title=" 01.PNG "alt=" Wkiom1xryoccobrbaadlo46nyky605.jpg "/>

First, Analysis:

Syntax parsing, whether SQL conforms to the syntax standard.

Sql> SELECT * Form tab;select * Form tab *error at line 1:ora-00923:from keyword not found where expected

Semantic analysis, assuming that SQL is legitimate, but does it make sense? Do you have access to the object you want to access? Does the queried column exist? Whether there is ambiguity waiting.

Sql> conn scott/tigerconnected.sql> Select X from Dual;select x from dual *error on line 1:ora-00904: "x": Inv Alid identifiersql> SELECT * from Dba_objects;select * from dba_objects *error @ line 1:ora-00942:table Or view does not exist

For DML statements, there is a third step.

Shared pool Check, has this statement been used by another user? Can I reuse work that has already been done? If it is, it is soft parse soft parse, if no, that is hard parsing.

DDL is always hard-parsed, and statements are never reused.

The Shared pool is part of the SGA that caches previously executed SQL statements, Plsql, and caches of data dictionary content (caching content as rows, buffer cache caches content as blocks), and many other information for session reuse.

Technically, Oracle's statement parsing is divided into two types:

• Hard parse--the statement executes every step of the statement from analysis to optimization, to row resource generation, to statement execution.

• Soft Parse--some of the steps that statements perform through statements, especially skip optimization steps (the most expensive step). In order to perform soft parsing, you must pass two steps. First, Oracle must make a semantic match to see if the statement submitted to Oracle has been executed. Then, make the environment match. For example, the initialization parameter of a session optimizer_mode=all_rows, the initialization parameter of a session optimizer_mode=first_rows, the environment of the two sessions is different.

In order to begin this process, Oracle must look for statements in the shared pool. To do this efficiently, Oracle makes a hash algorithm of each committed SQL statement, generating a hash_values. Oracle uses Hash_values to find out if there are identical statements in the shared pool.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/71/7D/wKiom1XRz4_hzdwHAAEpSG0kcxo710.jpg "title=" shared Pool check. PNG "alt=" Wkiom1xrz4_hzdwhaaepsg0kcxo710.jpg "/>

Once found, Oracle will conduct semantic and environmental checks, and SQL statements are the same, is there any semantic difference? Let's look at the following example.

1. Build two users

Sql> create User a identified by A; User created. sql> Create User B identified by B; User created.

2. Granting user privileges

Sql> Grant Connect, resource to A; Grant succeeded. Sql> Grant Connect,resource to B; Grant succeeded.

3. Enable a session

Sql> conn a/aconnected.sql> CREATE TABLE emp (id int); Table created. Sql> SELECT * from Emp;no rows selected

4. Enable another session

Sql> conn b/bconnected.sql> CREATE TABLE emp (id int); Table created. Sql> SELECT * from emp;no rows selectedsql> select * from Emp;no rows selected

5. Enable another session, using the SYS user connection, make the following query.

sql> SET linesize 200sql> COL sql_text for a50sql> SELECT address, executions, sql_text from V$sql WHERE UPPER (Sql_text) like ' SELECT * from EMP '; ADDRESS executions Sql_text---------------------------------------------------------------------------- 00000000893df470 2 Select * FROM emp00000000893df470 1 select * FROM empsql>

It can be seen that although the statements issued are the same, but semantically different, there are two records in the V$sql. b user, the same statement executed two times, because the semantics are the same, so it is a record, but the executions is 2.

Let's look at the same semantics, but what happens when the environment is different.

The above connections all exit, create a new connection for the following query.

Sql> Conn/as sysdbaconnected.sql> alter session set Optimizer_mode=all_rows; Session altered. Sql> SELECT * from a.emp;no rows selectedsql> alter session set Optimizer_mode=first_rows; Session altered. Sql> SELECT * from A.emp;no rows selected

View SQL parsing scenarios

sql> SET linesize 200sql> COL sql_text for a50sql> select Address,executions,sql_text from V$sql whe Re Upper (Sql_text) like ' SELECT * from A.emp '; ADDRESS executions Sql_text---------------------------------------------------------------------------- 0000000091cd7810 1 Select * FROM a.emp0000000091cd7810 1 select * FROM A.emp

It can be seen that although the semantics are the same, the environment is different, and Oracle will interpret it as 2 statements.

Analysis Summary:

The analysis phase does the following: Grammar check, hash value calculation, semantic check, environment check, calculation of sub-cursor hash value, etc. In addition, Oracle may also do the following steps:

Find the matching hash_value in the shared pool, if found, confirm access to the object, check the environment. Generates a child cursor hash_value. If the child cursor Hash_value can also match. Then optimizations and row resource generation are skipped. (This department will be discussed in detail in the cursor)

Ii. optimization and Row resource generation

When all DML statements are submitted to Oracle for the first time, they are optimized at least once in their lifetime. The optimization occurs in hard parsing. The semantics and syntax are exactly the same, and execution of statements with the same execution environment can take advantage of previous hard parsing work. In this case, the soft parsing will be performed on them.

Optimization is a laborious, CPU-intensive process that may take longer to optimize than it actually does. Optimization not only consumes CPU, but also results in a high bolt lock rate for shared pool. There are two types of rules for optimization

1. Rule-Based Optimization law (RBO)

2. Cost-based Optimization law (CBO)

Optimization is the creation of a variety of execution plans based on the principle of optimization, and the selection of a best execution plan.

The row resource generator is a software that transforms the execution plan into a data structure that can be leveraged by other parts.

Third, the implementation

Take advantage of the execution plan structure of the row resource generator output to perform specific steps.

This article is from the "Ding Dong" blog, please be sure to keep this source http://lqding.blog.51cto.com/9123978/1685341

Oracle Learning Performance optimization (i) SQL statement processing