Shulou(Shulou.com)05/31 Report--

This article mainly introduces the example analysis of Oracle optimizer, which is very detailed and has certain reference value. Friends who are interested must finish it!

I. the mode of the optimizer

The optimizer's pattern is used to determine the type of optimizer to use when parsing the target SQL in Oracle, and to determine the focus of calculating the cost value when using CBO. The "focus" here means that when using CBO to calculate the cost value of each execution path of the target SQL, the method of calculating the cost value will vary depending on the optimization mode.

In the Oracle database, the pattern of the optimizer is determined by the value of the parameter OPTIMIZER_MODE, and the value of OPTIMIZER_MODE may be RULE, CHOOSE, FIRST_ROWS_n, FIRST_ROWS, or ALL_ROWS.

The possible values for OPTIMIZER_MODE are as follows:

1.RULE

RULE indicates that Oracle will use RBO to parse the target SQL, and the statistics of the various objects involved in the target SQL have no effect on RBO.

2.CHOOSE

CHOOSE is the default value for OPTIMIZER_MODE in Oracle9i, which means that whether Oracle uses RBO or CBO when parsing the target SQL depends on whether the table objects involved in the SQL have statistics. Specifically, as long as one of the table objects involved in the SQL has statistics, Oracle will use CBO; when parsing the SQL. If none of the table objects involved in the SQL have statistics, then Oracle will use RBO.

3.FIRST_ROWS_n (nasty 1, 10, 10, 100, 1, 000)

It means that when the value of OPTIMIZER_MODE is first _ ROWS_n, Oracle will use CBO to parse the target SQL, and the focus of CBO in calculating the cost value of each execution path of the SQL is to return n records with the fastest response speed. Oracle will change the cost of the execution step corresponding to the record to a very small value (far less than the cost calculated by CBO for the same execution step by default). In this way, Oracle does not violate the general principle of CBO's choice of execution plan (minimum cost value), but also takes into account the meaning of FIRST_ROWS_n (nasty 1, 10, 10, 10, 10 and 1000).

4.FIRST_ROWS

FIRST_ROWS is an obsolete parameter in Oracle9i that indicates that Oracle will use CBO and RBO in parsing the target SQL. The joint use of CBO and RBO here means that in most cases, FIRST_ROWS still uses CBO to parse the target SQL, and at this time CBO focuses on returning the first few records with the fastest response speed (similar to FIRST_ROWS_n) when calculating the cost values of each execution path of the SQL However, when certain situations occur, FIRST_ROWS instead uses some of the built-in rules in RBO to select the execution plan without the cost of the exam. For example, when the value of OPTIMIZER_MODE is first _ ROWS, there is a built-in rule that if Oracle finds that the relevant index can be used to avoid sorting, then Oracle will choose the execution path corresponding to the index without considering the cost, which is obviously unreasonable. Correspondingly, when the value of OPTIMIZER_MODE is first _ ROWS, you will find that the probability of full index scan is higher than before, because walking the index full scan can avoid sorting.

5.ALL_ROWS

ALL_ROWS is the default value for OPTIMIZER_MODE in Oracle 10g and subsequent Oracle database versions, which means that Oracle will use CBO to resolve the target SQL, and at this time CBO focuses on optimal throughput (that is, minimum system Imax O and CPU resource consumption) when calculating the cost values for each execution path of the SQL.

In fact, the cost calculation method varies with the optimizer model, which is mainly reflected in the influence of ALL_ROWS and FIRST_ROWS_n on the cost calculation method. When the optimizer mode is ALL_ROWS, the focus of CBO cost calculation is on the optimal throughput, while when the optimizer mode is FIRST_ROWS_n, the focus of CBO cost calculation becomes to return n records with the fastest response speed. This means that for the same execution steps, the costs calculated by CBO will vary greatly in different optimizer modes, which means that the optimizer has a decisive influence on CBO calculation costs (and thus on CBO's choice of execution plan).

II. Result set

A result set (Row Source) is a collection that contains a specified execution result. For the optimizer (whether RBO or CBO), the result set corresponds to the execution step of the target SQL execution plan, and the execution result of an execution step is the output result set corresponding to that execution step.

For the execution plan of the target SQL, the output result of one execution step is the output result set corresponding to the execution step, and the output result set corresponding to the execution step may be the input result set of the next execution step. Such a step-by-step execution is accompanied by the transfer of the result set between the various execution steps. After all the execution steps of the target SQL execution plan are completed, the final output result set is the final execution result of the SQL.

As far as RBO is concerned, I don't see a description of the result set corresponding to the relevant execution steps in the corresponding execution plan, although the concept of result set also applies to RBO.

For CBO, the Rows column in the corresponding execution reflects CBO's estimate of the number of records (that is, Cardinality) of the output result set corresponding to the relevant execution step.

Third, the method of accessing data

For the optimizer, it is very important for the optimizer to decide how to access the data when parsing the target SQL and getting its execution plan, that is, the optimizer decides what way and method to access the data stored in the Oracle database that the target SQL needs to access.

The data that the target SQL needs to access is generally stored in the table, and there are two ways for Oracle to access the data in the table: one is to access the table directly; the other is to access the index first and then go back to the table (of course, if the data accessed by the target SQL can only be accessed by accessing the relevant index, then there is no need to go back to the table at this time).

3.1 methods of accessing tables

There are two ways to directly access data in a table in an Oracle database: one is a full table scan, and the other is a ROWID scan.

3.1.1 full table scan

Full table scan means that when Oracle accesses the data in the target table, it starts scanning from the first block (BLOCK) of the first EXTENT occupied by the table, and scans directly to the high water level line (HWM,High Water Mark) of the table. All data blocks Oracle in this range must be read. Of course, Oracle applies the filter conditions specified in the target SQL's where condition on all data read during this period, and finally returns only those data that meet the filter criteria.

This is not to say that full table scanning is not good. In fact, Oracle will use multiple reads when doing full table scanning, which is very efficient when the data of the target table is small. But the biggest problem of full table scanning is that the target SQL execution time of full table scanning will be unstable and uncontrollable, and this execution time will certainly increase with the increase of the target table data. Because the high water level of the target table will rise continuously with the increase of the amount of data in the target table, the data of data blocks that need to be read during the full table scan will continue to increase.

If you constantly insert data into the target table in Oracle, the high watermark moves up when the existing space allocated to the table is insufficient, but if you delete data from the table with the DELETE statement, the high watermark does not move down with it. The side effect of high water mark is that even if all the data in the target table is deleted with DELETE, the high water mark will still be in its original position, which means that Oacle still needs to scan all data blocks under the high water mark of the table when scanning the table. At this time, the time consumed by the full table scan operation of the table will not be significantly improved compared with before.

3.1.2 ROWID scan

ROWID scanning means that when Oracle accesses the data in the target table, it locates and accesses the data directly through the ROWID where the data is located. ROWID represents the physical storage address where the data row records in the Oracle are located, which means that the ROWID actually corresponds to the row records in the Oracle data block.

Since ROWID represents the physical storage address where the data rows of the table are located, when Oracle knows the ROWID of the data rows to be accessed, it can naturally access the relevant data rows of the corresponding table directly according to the RWOID, which is the meaning of ROWID scanning.

Strictly speaking, ROWID scanning in Oracle has two meanings: one is to directly access the corresponding data row records according to the value of ROWID entered by the user in the SQL statement; the other is to access the relevant index first, and then return to the table to visit the corresponding data row according to the ROWID obtained after accessing the index.

For the heap table in Oracle, we can get the ROWID value of the corresponding row record through the Oracle built-in ROWID pseudo-column, and then we can translate the ROWID pseudo-column value into the actual physical storage address of the corresponding data row through the relevant methods in the DBMS_ROWID package.

3.2 methods of accessing the index

The index mentioned here refers to the most commonly used B*Tree index.

The B*Tree index of the Oracle database is like an inverted tree. It contains two types of data blocks, one is the index branch block, the other is the index leaf block.

The index branch block contains a pointer to the corresponding index branch block / leaf block and a column of index key values (here the pointer refers to the block address RDBA of the relevant branch block / leaf block. Each index branch block has two types of pointers, one is lmc, and the other is the pointer recorded by the index row record of the index branch block. Lmc is the abbreviation of Left Most Child. Each index branch block has only one lmc. The maximum value in all index key value columns in the branch block / leaf block pointed to by this lmc must be less than the minimum value in all index key value columns of the index branch block in which the lmc is located. The minimum value of all index key value columns of the branch block / leaf block recorded by the index row record of the index branch block must be greater than or equal to the value of the index key value column of the row record). The index column value is not necessarily the complete indexed key value, it may just be the prefix of the indexed key value, as long as Oracle can distinguish the corresponding index branch block / leaf block through these prefixes, so that Oracle can not only save the storage space of the branch block, but also quickly locate the index branch block / leaf block below it. The top block of the index branch block is the so-called index root node. The operation of accessing the B*Tree index in Oracle must start from the root node, that is, it will go through a process from the root node to the branch block and then to the leaf block.

The index leaf block contains the indexed key value and the ROWID used to locate the actual physical storage location of the data row where the index key value is located in the table. For a unique B*Tree index, the ROWID is the header stored in the index row, so the Oracle does not need to add the length of the ROWID at this time. For the non-unique B*Tree index, the ROWID is stored as an additional column with the indexed key column, so the Oracle should store both the ROWID and its length, which means that under the same conditions, the unique B*Tree index saves the storage space of the index leaf block than the non-unique B*Tree index. For non-unique indexes, the ordering of B*Tree indexes is reflected in that Oralce will jointly sort according to the key value of the index and the corresponding ROWID. The index leaf blocks in Oralce are interconnected left and right, which means that there is a two-way pointer linked list that connects these index leaf blocks to each other.

3.2.1 Index uniqueness scan

Index uniqueness scan (INDEX UNIQUE SCAN) is a scan for a unique index (UNIQUE INDEX), which only applies to the target SQL of an equivalent query in the where condition. Because the scanned object is a unique index, the result of the index uniqueness scan will return at most one record.

3.2.2 Index range scan

Index range scan (INDEX RANGE SCAN) is suitable for all types of B*Tree indexes. When the scanned object is a unique index, the where condition of the target SQL must be a range query (predicate condition is BETWEEN, etc.); when the scanned object is a non-unique index, there is no restriction on the where condition of the target SQL (either equivalent query or range query). The result of an index range scan may return multiple records, which is the essential meaning of the word "range" in the index range scan.

Under the same conditions, when the number of index rows of the target index is greater than 1, the logical read consumed by the index scan range is at least 1 more than that of the businessman's index uniqueness scan. Because the scan result may return multiple records, and because the number of index rows of the target index is greater than 1, Oracle has to visit the relevant leaf blocks multiple times in order to determine the scanning end point of the index range scan.

3.2.3 Index full scan

Index full scan (INDEX FULL SCAN) applies to all types of B*Tree indexes (including unique and non-unique indexes). The so-called "index full scan" means to scan all index rows of all leaf blocks of the target index. It is important to note that an index full scan requires scanning all leaf blocks of the target index, but it does not mean that all branch blocks of the index need to be scanned. By default, when Oracle does a full scan of the index, it only needs to access the necessary branch blocks to locate the first row of the leftmost leaf block of the index, and then it can use the bi-directional pointer chain table between the leaf blocks of the index to scan all the index rows of all the leaf blocks of the index sequentially from left to right. Because the index is ordered, the execution result of the index full scan is also ordered, and it is sorted according to the index key value column of the index, which also means that walking the index full scan can not only achieve the effect of sorting, but also avoid the real sorting operation of the index key value column of the index.

By default, the order of the scan results of the index full scan determines that the index full scan cannot be performed in parallel, and usually the index full scan uses a single block read.

In general, index full scan does not need to return to the table, so index full scan is suitable for cases where the query of the target SQL is all the index key value columns of the target index. We know that for the B*Tree index of Oracle database, the index is not entered when all index key columns are null, which means that the prerequisite for full index scanning in Oracle is that the attribute of at least one index key column in the target index is NOT NULL. Obviously, if the attributes of all index key columns of the target index are allowed null values, if you still take the index full scan, you will miss the records in the target table whose index value columns are all NULL, that is, the result of the index full scan will not be allowed at this time! Oracle won't allow that to happen.

3.2.4 Fast full scan of index

Index Fast full scan (INDEX FAST FULL SCAN) is very similar to index full scan, and it also applies to all types of B*Tree indexes. Like index full scan, index fast full scan also needs to scan all index rows of all leaf blocks of the target index.

There are three differences between index fast full scan and index full scan:

1) Fast full scan of the index is only applicable to CBO.

2) the fast full scan of the index can use multi-block reads or can be performed in parallel.

3) the execution result of the fast full scan of the index is not necessarily orderly. This is because during the fast full scan of the index, Orace scans according to the physical storage order of the index rows on disk, rather than the logical order of the index rows, so the scan results are not necessarily orderly (for the index rows of a single index leaf block, the physical storage order is the same as the logical storage order. However, for index leaf blocks adjacent to physical storage locations, the physical storage order of index rows between blocks is not necessarily logically ordered).

3.2.5 Index skip scan

Index skip scan (INDEX SKIP SCAN) is suitable for all types of composite B*Tree indexes. It makes the target SQL that does not specify query conditions on the leading column of the target index in the where condition, but also specifies query conditions on the non-leading column of the index, can still use the index, which is like skipping its leading column when scanning the index. Directly from the non-leading of the index to the start of the scan (which is not the case in the actual execution process), this is the meaning of the word "SKIP" in the index skip scan.

Index skip scanning in Oracle is only suitable for situations where the number of distinct values of the leading column of the target index is small, and the selectivity of subsequent non-leading columns is very good, because the execution efficiency of index skip scanning must decrease with the increase of the number of distinct values of the leading column of the target index.

The above is all the content of the article "sample Analysis of Oracle Optimizer". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.