Oracle Optimizer: Migrating to using the cost-based optimizer-----series 1.2

oracle| optimization
Oracle Optimizer: Migrating to using the cost-based optimizer-----series 1.2

3.2 Cost-based optimizer (CBO)

Methodologies based on cost optimizer following computational costs. All execution plans are identified with cost, and the optimizer chooses one with the lowest cost. In the implementation plan, higher costs will mean higher resources. The lower the cost, the more efficient the query.
The CBO uses all the statistical information and histogram that is stored in the data dictionary, the user provides hints and parameter settings to achieve the cost of use, the CBO generates all the possible access methods and then chooses the most appropriate. The number of permutations depends on the number of tables appearing in the query, sometimes up to about 80,000 permutations or more, please refer to the second part of the series to set the parameters.
The CBO may also perform operations such as query transformations, view merges, or transformations, adding join predicates, and so on.
This will change the original statement and change the existing or newly added predicate, and all of these goals for the new access plan will be better than the original. Note that the conversion does not affect the returned data, but only the execution path, please refer to the information associated with the second part of the series parameter chapter.

3.2.1 Statistical data


The statistics provide accurate input in order for the CBO to work properly; the resulting data is stored in the object and includes information such as the number of rows in the table, a single value in the column, the number of blocks in the page in the index, and so on. The more accurate the statistics, the more efficient the optimizer provides. See how this information is generated and how best to maintain it in the third section of this series, which generates the statistics section. Statistical data may be accurate or estimated, and it uses the COMPUTE clause to analyze all the data in the object, which will give the optimizer accurate information to work with and achieve a perfect execution plan.
With the estimate clause, the data content of the sample size mentioned in the object is parsed to generate statistics. The sample size is specified as either the number of randomly parsed rows or a percentage of rows to generate statistics, or an optional block sample, which saves time if there are large amounts of table data in the system.
The premise of a good execution plan depends on how close the estimate is to the exact value, and the idea of setting different sample sizes to achieve the right goals or different levels of assessment for different types of tables is quite feasible to achieve close to accurate statistics.
The statistics are stored in the data dictionary of the owner SYS user, and the following view shows the collection statistics for tables, columns, and indexes.
Table:
Dba_tables
Number of num_rows– lines
blocks– number of blocks used
empty_blocks– number of unused empty blocks
avg_space– allocates the average free space (in bytes) to the table, considering all the empty and free blocks.
chain_cnt– the number of rows linked or moved
avg_row_len– the length of the average line in the byte meter
last_analyzed– the date of the last table analysis
sample_size– provides the sample size for estimate statistics, and for compute is equal to the Num_rows column value.
global_stats– for partitioned tables, yes– collection of statistical data will be used as a whole, no– collection of statistical data will estimate the table
user_stats– If the user indicates that statistics are set for the table, yes
For a table of separate partitions the statistics can be found from dba_tab_partitions, and cluster statistics can be found from dba_clusters.
Column
Dba_tab_columns
num_distinct– Number of single values
low_value– Minimum value
high_value– Highest Value
The aggregation degree of the density– column.
num_nulls– the number of records involving null values in columns
The number of columns in the num_buckets– column, the section of the Reference bar chart
Sample_size–estimate the sample size provided, if compute is equal to the total number of rows
Last_analyzed-date of the last table analysis
Dba_tab_col_statistics display similar data-for partitioned table column statistics from Dba_part_col_statistics and dba_subpart_col_statistics can be found
Index
Dba_indexes
blevel– the depth of the index, from the root level to the leaf level
leaf_blocks– the number of page-level blocks.
DISTINCT keys– number of single key values
avg_leaf_blocks_per_key– The average number of occurrences per single key value should be 1 for a unique index
avg_data_blocks_per_key– the average number of blocks in a table that a single key value points to
clustering_factor– determines the total number of rows sorted by index. If the number is near the number of blocks, the table is sorted in index order, that is, all the page levels point to the same row in the table. If the index is close to the number of rows, the index is sorted randomly, that is, All of the page's levels are scattered across rows that point to multiple blocks.
Number of num_rows– index rows
Sample_size-estimate the sample size provided, if compute is equal to the total number of rows
Last_analyzed-date of the last table analysis
Global_stats for partitioned tables, yes– collection of statistical data will be used as a whole, no– collection of statistical data will estimate the table
User_stats If the user indicates that statistics are set for the table, yes
pct_direct_access– index The secondary index of the organization table, which effectively guesses the percentage of rows
Single partitioned index statistics can be found from dba_ind_partitions and dba_ind_subpartitions
Data dictionaries that involve histogram information will be discussed later. adjourned