When you use the SQL language, Oracle will accompany the "execution plan" to the database, that is, the statement will execute through the full table scan, or otherwise by indexing search . The search scheme is closely related to Oracle's optimizer.
The execution step of the SQL statement.
1 Syntax Analysis The syntax of the analysis statement is in line with the norm, measuring the meaning of each expression in the statement.
2 Semantic analysis checks if all database objects involved in statements have existence, and users have corresponding permissions.
3 View conversion will convert the query statement involved to the corresponding to the base table query statement.
4 Expression Conversion converts complex SQL expressions to a simpler equivalent connection expression.
5 Select different optimers from optimizers to generally produce different "implementation plans"
6 Selecting the connection method Oracle has three connection methods, and the multi-table connection Oracle can select the appropriate connection.
7 Select which pair of consecutive connections to multiple tables to connect Oracle, select which table in both tables as the source data sheet.
8 Select the search path of the data Select the appropriate data search path according to the above conditions, such as using full table search or use indexes or other ways.
9 Run "Executive Plan"
Oracle optimizer
Oracle has two optimizers: based on rules (RBO, Rule Based Optimizer), and cost-based optimer (CBO, COST based Optimizer).
RBO has been adopted since Oracle 6, has a strict use rule, as long as you write SQL statements according to it, no matter what the content in the data table, you will not affect your "execution plan", that is, Data is not "sensitive", Oracle has no longer developing this technology.
CBO is introduced from Oracle 7, many new technologies used in Oracle Since 7th editions are based on CBO, such as star connection arrangement, hash connection query, and parallel queries. CBO calculates various "considerations" that may "implement plan", that is, COST, which uses COST's lowest solution as the actual operation. The calculation according to the COST of the "Executive Plan", depending on the statistical distribution of data in the data sheet, the Oracle database itself is unclear, and must analyze the table and related index to collect the data required to CBO.
In general, the "implementation plan" selected by CBO will not be poor than RBO, and relatively, CBO's request for programmers is so demanding, saving programmers in order to be from multiple possible Select a debugging time that is taken in the Executive Plan, but there is a problem in some occasions.
More typical issues are: Sometimes, it indicates that there is an index, but the inquiry process is clearly not used in the relevant index, resulting in a long time, which is huge, where is it? Find in the following order and basically discover the reason.
Find the cause of the step
First, we have to determine what is optimized in the optimization mode of the database, and the corresponding parameters are: Optimizer_Mode. "Show Parameter Optimizer_Mode" can be run in SVRMGRL to view. Since Oracle V7, the default setting should be "choose", that is, if the CBO is selected for the analyzed table query, select RBO. If the parameter is set to "Rule", an overwhelming RBO is selected regardless of the analysis, unless Hint is enforced in the statement.
Second, check whether the first column of the indexed column or the combined index appears in the WHERE clause of the PL / SQL statement, this is the necessary conditions that "execute plan" can be used to associate indexes. Third, see which type of connection is used. There is a total of Oracle Sort Merge Join (SMJ), Hash Join (HJ), and NESTED LOOIN (NL). In two tables, only NESTED LOOP can effectively utilize the index when there is an index on the target column of the inner table. SMJ can only avoid data sorting procedures due to the existence of index even if they have an index on the relevant column. HJ must have little effect on data query speeds due to Hash operations.
Fourth, see if the connection order allows the use of related indexes. Assume that there is an index on the deptno column of the table EMP, and the list DEPT's column DEPTNO has no index. The where statement has EMP.DEPTNO = Dept.deptno. When doing NL connections, EMP is made as an appearance, first visited, due to connection mechanism, the data access method of the appearance is full table scan, the index on Emp.Deptno is clearly not used, up to it, index full scan Or index rapidly scanning.
Fifth, whether to use the system data dictionary table or view. Since the system data dictionary table is not analyzed, it may result in a very poor "execution plan". But don't analyze the data dictionary table without authorization, otherwise it may cause a deadlock, or the system performance is lowered.
Sixth, there is a potential data type conversion. If the characteristic data is compared to the numerical data, Oracle automatically converts the character type with the to_number () function, resulting in the occurrence of the sixth phenomenon. Seventh, whether to collect enough statistics for the table and related indexes. The table is often increasing, deleted, and changing tables, and the table and index are analyzed regularly, and the SQL statement "Analyze Table XXXX Compute Statistics for All Indexes" can be used. Oracle mastered the full reflection of actual statistics or make the right choice. Eighth, the selectivity of the index column is not high. We assume typical situations, with table EMP, a total of one million lines, but the EMP.DEPTNO column, data is only 4 different values, such as 10, 20, 30, 40. Although there are many EMP data, the value in Oracle Default Table is evenly distributed in all data lines, that is, each DEPTNO value has 250,000 data rows. Suppose SQL search criteria deptno = 10, using indexes on the DEPTNO column for data search efficiency, often more than full measuring scan, Oracle is grateful to index "vision", it is considered that the selectivity of the index is not high. But we consider another situation, if one million data is actually not allocated between 4 DEPTNO values, 990,000 rows correspond to value 10, 5000 rows of corresponding value 20, 3000 rows, corresponding value 30, 2000 lines Value 40. When searching for other DEPTNO values outside of this data distribution, there is no doubt that if the index can be applied, the efficiency will be much higher. We can use the index column separate analysis, or use the Analyze statement to establish a histogram, collect enough statistics to the column, so that Oracle can use the value of the search selective value. Ninth, whether the index column value can be empty (NULL). If the index column value can be a null value, those operations in the SQL statement need to return NULL values will not be used, such as count (*), but scan with full mete. This is because the stored value in the index cannot be full space. Eleventh, see if it is useful to query (PQO). Parallel queries will not be indexed. If we want to use the Ind_col1 index of the table A, the following methods can be used: "SELECT / * INDEX (A IndEx (A Ind_col1) * / * from a where col1 = xxx;" Note that the comment must be followed after SELECT, and The " " in the comment is followed by the comment starter "/ *" or "-", otherwise Hint is considered to be a general comment, and there is no impact on the execution of the PL / SQL statement. One is an Explain Table mode. The user must first establish a Plan_Table table, each step of the execution plan will be recorded in the table, and the SQL script is built in $ {oracle_home} / rdbms / admin / UTLXPLAN.SQL . Open SQL * Plus, enter "Set Autotrace On" and run the SQL statement to be debugged.
