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

Original text link http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-bp-for-stats-gather-12c-1967354.pdf

Translator Liu Jinlong

Introduction language

The Oracle optimizer generates all possible access paths (execution plans) for the SQL statement, and then selects an execution path with the lowest cost value, which refers to oracle's estimate of the resources consumed to execute SQL. In order for the optimizer to accurately calculate the COST value of each execution plan, this requires all the objects (tables, indexes, etc.) to be accessed by the execution of the SQL statement and the necessary description information of the system.

This necessary information is often referred to as optimizer statistics (optimizer statistics). Understanding and managing optimizer statistics is the key to optimizing SQL execution. Knowing when, how, and how to collect optimizer statistics is critical to maintaining good system performance. This article will discuss in detail when and how to collect statistics in common Oracle scenarios. The article is roughly divided into the following parts:

How to collect statistics

When to collect statistics

Improve the quality of statistical information

Quickly collect statistics

When do not need to collect statistics

Collect other types of statistics

How to collect statistics

The preferred method in Oracle is the automatic collection of statistics. If the system already has a complete manual statistics collection program, then manual statistics collection can be preferred. No matter which collection method you choose, the first thing to consider is whether the default global parameter settings meet your needs.

In most cases, these default parameters can be satisfied, but if we want to modify them according to the actual situation of our system, we can set SET_GLOBAL_PREFS. Parameter value. Once we choose to do this, we can override the default settings by using the DBMS_STATS "setpreference" tool. For example, use the SET_TABLE_PREFS parameter to set table statistics to be collected using incremental or to collect histogram information. In this way, we will specify which specified statistics are collected by default, without the need to adjust the parameters when collecting statistics. We are free to use the default parameters to collect statistics at the table / user / database level and to make sure that these statistics collection strategies have been used. More importantly, we are free to switch between automatic and manual statistics collection.

Automatic statistical information collection

Oracle databases need to collect object statistics that are missing or have been "stale" out-of-date statistics. This is done by automated tasks performed in a predefined maintenance window. For objects with high priority within oracle, the statistics of these objects need to be collected and updated first.

The automatic statistics collection job uses the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC procedure, which uses the same default parameter settings as the DBMS_STATS.GATHER_*_STATS procedure. These default settings are sufficient in most scenarios. However, in some scenarios, one or more of the default parameter values need to be changed, and we can use the DBMS_STATS.GATHER_*_STATS procedure to complete the setting. Parameter values should be changed as little as possible, preferably on a per-object basis. For example, if we want to modify the statistics expiration threshold of the specified table, and we want the threshold to be changed from the original 10% to 5%, we can use the DBMS_STATS.SET_TABLE_PREFS process to change the STALE_PERCENT property of the specified table.

Execdbms_stats.set_table_prefs (user,'SALES','STALE_PERCENT','5')

After the modification is complete, we can use DBMS_STATS.GET_PREFS to view the property value modification. Three options are required, parameter name, user name, and table name:

Selectdbms_stats.get_prefs ('STALE_PERCENT',user,'SALES') stale_percent from dual;STALE_PERCENT

-

five

Setting DBMS_STATS Preferences

As mentioned above, we may need to set the collection policy for specified objects and tables during automatic statistics collection through the DBMS_STAT process. We can customize the collection policy through the DBMS_STATS.GATHER_*_STATS process, but the recommended method for oracle is to use the DBMS_STATS.SET_*_PREFS process to set it.

Parameters can be modified at the table, object, database, or global level (AUTOSTATS_TARGET and CONCURRENT can only be changed at the global level):

SET_TABLE_PREFS

SET_SCHEMA_PREFS

SET_DATABASE_PREFS

SET_GLOBAL_PREFS

In general, the parameters we most often modify are ESTIMATE_PERCENT (control sampling percentage) and METHOD_OPT (control histogram information creation), but the estimated percentage is now better than the default value, leaving its default value for the reasons described later in this section

When collecting statistics for a table, the DBMS_STATS.GATHER_*_STATS procedure is allowed to modify the default values of the parameters specified by the SET_TABLE_PREFS procedure.

When using the DBMS_STATS.GATHER_*_STATS procedure to collect statistics for all existing tables of the specified object, we can use the SET_SCHEMA_PREFS procedure to modify the default parameter configuration. This procedure actually calls the SET_TABLE_PREFS procedure to set default parameters for all tables of the specified object. So when we are done using this process, the parameters used by the user's newly created table to collect statistics are the parameters specified according to the GLOBAL configuration.

Similarly, the SET_DATABASE_PREFS procedure can modify the default parameters when collecting user-defined object statistics using the DBMS_STATS.GATHER_*_STATS procedure. In fact, this procedure also calls the SET_TABLE_PREFS procedure to set default parameters for all tables of the specified object. For objects created after the default parameters are modified, he chooses the default parameter configuration specified by the GLOBAL procedure. If you set the ADD_SYS parameter to TRUE, then Oracle's own users (SYS,SYSTEM, etc.) can also be included.

The SET_GLOBAL_PREFS procedure can specify the default parameters for all statistics collection processes that do not set table priority objects. After modifying the default parameters using the SET_GLOBAL_PREFS procedure, all newly created objects will use the modified default collection parameters, unless the parameters are explicitly specified by the GATHER_*_STATS procedure or the table priority is set.

The above process parameter settings are given priority when using DBMS_STATS.GATHER_*_STATS to collect statistics.

Oracle 12CR2 introduces new parameters that affect priority

REFERENCE_OVERRIDES_PARAMETER. When this parameter is set to TRUE, the order of priority changes. This is shown in the following figure.

VPSOXyDaejfFxiaictxSZUoT8g/0?wx_fmt=png ">

ESTIMATE_PERCENT

During the collection of statistics, you can use the ESTIMATE_PERCENT parameter to control the percentage of statistics rows. When all rows in the table are counted (that is, 100% sampling), we will get the most accurate statistics. Oracle database introduces a new sampling algorithm in 11g, hash-based algorithm to achieve row information statistics, using 10% sampling frequency to collect information accuracy close to 100% sampling frequency. The new algorithm is started when you specify the estimate_percent setting auto_sample_size (default) using the dbms_stats gather_ * _ stats procedure. Before Oracle database 11g, database administrators often set the estimate_precent parameter to a very low value to ensure that statistics can be collected quickly. Oracle strongly recommends that you keep the default parameter auto_sample_size starting at 11g. This is particularly important because 12C began to introduce new histogram types, mixing and Top-Frequency, which can only be collected if the parameters remain at the default auto_sample_size.

Many systems now retain the old statistics collection script (set the percentage manually). So when the database is upgraded to 12CR2, consider using the preference_overrides_parameter parameter to override the default parameter used for manual statistics collection. Or modify the statistics collection script directly.

METHOD_OPT

The METHOD_OPT parameter controls whether the bar chart is created during the collection process. Column chart is a special type of column statistics in oracle database. Users provide detailed information about the distribution of column data in the table. By default, the METHOD_OPT parameter is' FOR ALL COLUMNS SIZE AUTO', 'when the columns in the table are used in equivalent or range where conditions such as WHERE col1=' X' or WHERE col1 BETWEEN'A' and 'Borders, and the column data is skewed. Then oracle collects histogram information on these columns. The optimizer knows which column user query predicates because the information is stored in the data dictionary table SYS.COL_USAGE$.

Some DBA prefer to control the creation of the histogram themselves. The recommended way for Oracle to use is to set it through set_table_prefs. For example, you can specify that you create a histogram for only two columns of the SALES table, COL1 and COL2.

Begin

Dbms_stats.set_table_prefs (

User

'SALES'

'method_opt'

'for all columns size 1 for columns size 254 col1col2')

End

/

You can also specify that columns must have histograms (COL1 and COL2), and allow the optimizer to decide whether to create additional histograms on other columns:

Begin

Dbms_stats.set_table_prefs (

User

'SALES'

'method_opt'

'for all columns size auto for columns size 254col1col2')

End

/

If you set the METHOD_OPT property to 'FOR ALL COLUMNS SIZE 1'. Then histogram creation will be prohibited. For example, you can modify the METHOD_OPT in the global properties of DBMS_STATS so that histogram information is not created.

Begin

Dbms_stats.set_global_prefs (

'method_opt'

'for all columns size 1')

End

/

We can also delete histogram information that is not needed on some columns. Using the following method, DBMS_STATS.DELETE_COLUMN_STATS then specifies col_stat_type as' HISTOGRAM'.

Manual statistics collection

If there is already a well-established statistics collection process or for some reason you want to disable automatic statistics collection for a specific user scheme and keep only the statistics of the collected data dictionary. You can use the dbms_stats.set_global_prefs procedure to change the autostats_target parameter to oracle instead of auto.

Execdbms_stats.set_global_prefs ('autostats_target','oracle')

The dbms_stats package should be used to find outdated analyze commands. The DBMS _ stats package provides multiple dbms_stats.gather_*_stats procedures to collect statistics for user schema objects, data dictionaries, and fixed objects. Ideally, with the exception of schema and object names, all parameters of these procedures should be defaulted to default values. In most cases, default and adaptive parameter settings are sufficient:

Exec dbms_stats.gather_table_stats ('sh','sales')

As mentioned above, if you have to change the default values of statistical parameters, use the DBMS_STATS.SET_*_PREF procedure to do so with minimum impact.

Pending Statistics

When we decide to change the parameter defaults of the dbms_stats_gather_*_stats process, oracle strongly recommends that we verify these changes before modifying them in the production system. If you don't have a complete test environment, you should use pending statistics. Pending statistics is used instead of the commonly used data dictionary table, and the statistical information is stored in the pending table so that it can be enabled and tested in a controlled way before the system is released and used. To activate the collection of pending statistics, you need to use the dbms_stats.set_*_prefs procedure to change the parameter publish from the default true to false for objects that want to create pending statistics. The following example enables pending statistics for the sales table under the sh user and collects statistics for the sales table.

Execdbms_stats.set_table_prefs ('sh','sales','publish','false')

Enable pending statistics by setting publish to false.

Collect object statistics normally

Exec dbms_stats.gather_table_stats ('sh','sales')

The statistics collected for these objects can be displayed by querying the * _ tab_pending_stats view:

You can use pending statistics by setting the initialization parameter optimizer_use_pending_stats to true through an alter session command. Any sql running in this session will use these new unpublished statistics after pending statistics are enabled. The optimizer uses the current statistics in the standard data dictionary table when there are no pending statistics for tables accessed in other sessions. After validating these pending statistics, you can use the

Dbms_stats.publish_pending_stats process to release.

Exec dbms_stats.publish_pending_stats ('sh','sales')

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.