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

The analysis of sql syntax is from right to left

First, the execution steps of the sql statement:

1) Syntax analysis, analyze whether the syntax of the sentence conforms to the specification, and measure the meaning of each expression in the sentence.

2) semantic analysis to check whether all the database objects involved in the statement exist, and the user has the corresponding permissions.

3) View transformation, which converts the query statements related to the view into the corresponding query statements against the base table.

4) expression conversion, which converts complex SQL expressions into simpler equivalent join expressions.

5) choose optimizer, different optimizer generally produce different "execution plan"

6) choose the connection method. ORACLE has three connection methods, and you can choose the appropriate connection method for multi-table connection ORACLE.

7) Select the join order, for multi-table join ORACLE, choose which pair of tables to join first, and select which of the two tables as the source data table.

8) choose the search path of the data, and choose the appropriate data search path according to the above conditions, such as full table search or index or other methods.

9) run the execution Plan

Second, oracle sharing principle:

ORACLE stores the executed SQL statements in a shared pool (shared buffer pool) in memory, which can be shared by all database users.

When you execute a SQL statement (sometimes called a cursor), if it is exactly the same as the previously executed statement, ORACLE can quickly get the parsed statement and the best execution path. This feature greatly improves the execution performance of SQL and saves memory usage.

Third, the method of oracle statement to improve the query efficiency:

1:where column in (select * from... Where.)

2:... Where exists (select'X' from... where...)

The second format is far more efficient than the first.

In Oracle, almost all IN operator subqueries can be rewritten into subqueries that use EXISTS.

Using the EXIST,Oracle system first checks the main query and then runs the subquery until it finds the first match

This saves time when the Oracle system executes the in subquery, it first executes the subquery and stores the list of results in an indexed temporary table.

Avoid using the having clause. HAVING filters the result set only after all records have been retrieved.

This process requires sorting, totals, and so on. If you can limit the number of records through the WHERE clause, you can reduce the overhead.

Fourth, the complete execution order of SQL Select statements:

1. From clause assembles data from different data sources

2. The where clause filters record rows based on the specified criteria

3. The group by clause divides the data into multiple groups

4. Use the aggregation function to calculate

5. Use the having clause to filter the grouping

6. Evaluate all expressions

7. Fields of select

Use order by to sort the result set.

The most obvious feature that distinguishes the SQL language from other programming languages is the order in which the code is processed. In most database languages, the code is processed in coding order. But in the SQL statement, the first clause pattern to be processed is FROM, not the first occurrence of SELECT. Step number of SQL query processing:

1 (8) SELECT (9) DISTINCT (11)

2 (1) FROM

3 (3) JOIN

4 (2) ON

5 (4) WHERE

6 (5) GROUP BY

7 (6) WITH {CUBE | ROLLUP}

8 (7) HAVING

9 (10) ORDER BY

Each of the above steps produces a virtual table that is used as input for the next step. These virtual tables are not available to callers (client applications or external queries). Only the tables generated in the last step will be given to the caller. If a clause is not specified in the query, the corresponding step is skipped.

Brief introduction of logical query processing phase:

1. FROM: perform Cartesian product (cross join) on the first two tables in the FROM clause to generate the virtual table VT1.

2. ON: apply ON filters to the VT1, and only those that make it true are inserted into the TV2.

3. OUTER (JOIN): if OUTER JOIN is specified (as opposed to CROSS JOIN or INNER JOIN), rows that are not found in the reserved table will be added to VT2 as external rows to generate TV3. If the FROM clause contains more than two tables, repeat steps 1 through 3 for the result table generated by the previous join and the next table until all table locations have been processed.

4. WHERE: apply a WHERE filter to the TV3, and only rows that are true are inserted into the TV4.

5. GROUP BY: the rows in TV4 are grouped according to the column list in the GROUP BY clause to generate TV5.

6. CUTE | ROLLUP: insert the supergroup into VT5 to generate VT6.

7. HAVING: apply the HAVING filter to the VT6 so that only groups that are true are inserted into the VT7.

8. SELECT: processes the SELECT list to generate VT8.

9. DISTINCT: remove duplicate lines from VT8, product VT9.

10. ORDER BY: VC10 the rows in VT9 in the order of the column list in the ORDER BY clause.

11. TOP: select a specified number or proportion of rows from the beginning of the VC10, generate the table TV11, and return it to the caller.

During the execution of the SQL statement in Oracle, the internal parsing principle of Oracle is as follows:

1. When a user submits an SQL expression for the first time, Oracle will Hard parse the SQL. This process is a bit like program compilation, checking syntax, table name, field name and other related information (such as the following figure). This process will take a long time because it needs to analyze the syntax and semantics of the statement. Then the optimized execution plan (sql plan) is obtained, and some space is allocated in memory to save the statement and the corresponding execution plan and other information.

2. When the user requests for the second time or multiple requests, Oracle will automatically find the previous statement and execution plan, instead of Hard parse, but directly Soft parse (call up the corresponding execution plan of the statement, and then execute it), thus reducing the analysis time of the database.

Note that only identical statements in Oracle, package case, spaces, and line breaks are required to be the same before the previous analysis results and execution plan are reused.

The analysis process is shown in the following figure:

For a large number of frequently accessed SQL statements, if we do not use Bind variables, which Oracle will spend a lot of Shared latch and CPU on Hard parse processing, so we should try our best to improve the reuse rate of statements and reduce the analysis time of sentences. By understanding the analysis process of Oracle SQL statements, we can understand the internal processing logic of Oracle and avoid it in design and implementation.

When operating with JDBC or other persistent data (such as Hibernate,JDO, etc.), try to use placeholders (? )

The process of ORACLE sql is roughly as follows:

1. Using the HASH algorithm, we get a hash value, which can be viewed through V$SQLAREA.HASH_VALUE.

two。 Check the library cache in shared pool to see if there is the same hash value. If so, soft parsing is performed without hard parsing.

3. If this hash value does not exist in shared pool, a syntax check is performed to see if there are any syntax errors

4. If there are no syntax errors, a semantic check is performed to check whether the object referenced by the SQL exists and whether the user has access to the object.

5. If there are no semantic errors, parse the SQL, generate the parsing tree, and execute the plan

6. Generate the binary code that ORACLE can run, run the code and return the result to the user

Both hard and soft parsing are carried out in step 5.

Hard parsing is usually an expensive operation, accounting for about 70% of the time performed by the entire SQL, which generates execution trees, execution plans, and so on.

When the same SQL statement is executed again, because it is found that there is the same hash value in library cache, there will be no hard parsing, but soft parsing.

So what on earth does soft parsing do? In fact, soft parsing skips the operation of generating parsing tree and generating execution plan, which is time-consuming and CPU-consuming, and runs directly using the generated execution plan.

The SQL statement.

The following excerpt from eygle deeply parses the description of the execution process of SQL in ORACLE.

1. First, get the library cache latch, according to the HASH_VALUE of the SQL, find out whether the HASH_VALUE exists in the library cache. If you find the HASH_VALUE, call it soft parsing, and the Server gets changed to the SQL execution plan to move to step 4. If the shared code cannot be found, the hard parsing is performed.

two。 Release library pool cache, get shared pool latch, find and lock free space (find chunk in bucket). If it cannot be found, report an ORA-04031 error.

3. Release the shared pool latch, retrieve the library cache latch, and put the SQL execution plan into the library cache.

4. Release library cache latch to keep the library cache pin/lock in null mode.

5. Start the execution.

Library cache latch can be understood as occurs during hard / soft parsing, because library cache is searched during parsing, so library cache latch is generated.

Library cache pin occurs during the execution phase.

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.