Shulou(Shulou.com)06/01 Report--

What exactly did Oracle do when submitting a sql command to Oracle? Having a good understanding of this problem can help you better analyze the optimization of sql statements.

There are four steps to execute a sql statement from start to finish:

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

Optimize-generate the best plan that can be used to execute statements in the database

Line resource generation-get the best plan for the session and establish an execution plan

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

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

1. Analysis:

Syntax analysis, whether sql conforms to grammatical standards.

SQL > select * form tab;select * form tab * ERROR at line 1:ORA-00923: FROM keyword not found where expected

Semantic analysis assumes that sql is legal, but does it make sense? Do you have access to the object you want to access? Does the column of the query exist? Whether there is ambiguity waiting.

SQL > conn scott/tigerConnected.SQL > select x from dual;select x from dual * ERROR at line 1:ORA-00904: "X": invalid identifierSQL > select * from dba_objects;select * from dba_objects * ERROR at line 1:ORA-00942: table or view does not exist

For the DML statement, there is a third step.

Shared pool check, has this statement been used by other users? Can you reuse work that has already been performed? If it is, it is soft parsing soft parse, if not, it is hard parsing.

DDL always parses hard, and statements are never reused.

Shared pool is part of SGA that caches previously executed sql statements, PLSQL, caching of data dictionary contents (caching content as rows, while buffer cache caches content as block), and many other information for session reuse.

Technically, there are two types of statement parsing for Oracle:

Hard parsing-every step that the statement executes through the statement, from analysis to optimization, to line resource generation, to statement execution.

Soft parsing-certain steps that a statement performs through a statement, especially skipping the optimization step (the most expensive step). In order to perform soft parsing, you must go through two steps. First, Oracle must do semantic matching to see if the statement submitted to Oracle has been executed. Then, the environment matching is carried out. For example, the initialization parameter optimizer_mode=ALL_ROWS of a session, and the initialization parameter optimizer_mode=FIRST_ROWS of a session, the environment of the two sessions is different.

To start this process, Oracle must look for statements in Shared pool. In order to accomplish this operation efficiently, oracle performs the hash algorithm for each submitted sql statement to generate a hash_values. Oracle uses hash_values to find out if there is the same statement in Shared pool.

Once found, Oracle will check the semantics and environment. The sql statements are all the same. Is there any semantic difference? Let's look at the following example.

Set up two users

SQL > create user an identified by a, user created.SQL > create user b identified by b, user created.

two。 Give the user permission

SQL > grant connect,resource to grant connect,resource to Grant succeeded.SQL > grant connect,resource to BitterGrant Grant.

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 and use the sys user connection to 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, the semantics are different, so there are two records in the v$sql. B user, the same statement is executed twice, because the semantics are the same, so it is a record, but executions is 2.

Let's see what happens when the semantics are the same, but the environment is different.

All the above connections are exited. Create a new connection and make the following query.

SQL > conn / as sysdbaConnected.SQL > alter session set optimizer_mode=ALL_ROWS;Session altered.SQL > SELECT * FROM A.EMPTION no rows selectedSQL > alter session set optimizer_mode=FIRST_ROWS;Session altered.SQL > SELECT * FROM A.EMPTION no rows selected

View sql resolution

SQL > SET LINESIZE 200SQL > COL SQL_TEXT FOR A50SQL > select address,executions,sql_text from v$sql where 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, but the environment is different, Oracle will also be parsed as two statements.

Analysis and summary:

The following operations are done in the analysis phase, such as syntax checking, calculating hash values, semantic checking, environment checking, calculating the hash values of sub-cursors, and so on. In addition, Oracle may have done the following steps:

Look for a matching hash_value in Shared pool, and if you can find it, confirm the permissions of the access object and check the environment. Generate a child cursor hash_value. If the child cursor hash_value can also match. Then optimization and row resource generation are skipped. (we will discuss this department in detail in cursors.)

II. Optimization and row resource generation

When all DML statements are first submitted to Oracle, they are optimized at least once in its lifetime. Optimization occurs in hard parsing. The execution of statements with the same semantics and syntax and the same execution environment can take advantage of previous hard parsing work. In this case, 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 leads to high lock rate of Shared pool. There are two rules for optimization.

1. Rule-based optimization rule (RBO)

two。 Cost-based optimization rule (CBO)

Optimization is to generate a variety of execution plans according to the optimization rules, and choose the best one.

A row resource generator is software that converts an execution plan into a data structure that can be used by other parts.

III. Implementation

Use the execution plan structure output by the row resource generator to perform the specific steps.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.