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

Today, I will talk to you about several common functions of the DBMS_STATS package, which may not be well understood by many people. in order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

3.1 several common functions of the DBMS_STATS package

The collection of performance data consists of several stored procedures:

GATHER_DATABASE_STATS Procedures

GATHER_DICTIONARY_STATS Procedure

GATHER_FIXED_OBJECTS_STATS Procedure

GATHER_INDEX_STATS Procedure

GATHER_SCHEMA_STATS Procedures

GATHER_SYSTEM_STATS Procedure

GATHER_TABLE_STATS Procedure

The respective functions can also be seen from the names. These stored procedures are used to collect the performance data of objects at different levels of the database, including: database, data dictionary, table, index, SCHEMA performance and so on.

3.1.1 GATHER_TABLE_STATS Procedure stored procedure

In 10g, the parameters of GATHER_TABLE_STATS are as follows:

DBMS_STATS.GATHER_TABLE_STATS (

Ownname VARCHAR2

Tabname VARCHAR2

Partname VARCHAR2 DEFAULT NULL

Estimate_percent NUMBER DEFAULT to_estimate_percent_type

(get_param ('ESTIMATE_PERCENT'))

Block_sample BOOLEAN DEFAULT FALSE

Method_opt VARCHAR2 DEFAULT get_param ('METHOD_OPT')

Degree NUMBER DEFAULT to_degree_type (get_param ('DEGREE'))

Granularity VARCHAR2 DEFAULT GET_PARAM ('GRANULARITY')

Cascade BOOLEAN DEFAULT to_cascade_type (get_param ('CASCADE'))

Stattab VARCHAR2 DEFAULT NULL

Statid VARCHAR2 DEFAULT NULL

Statown VARCHAR2 DEFAULT NULL

No_invalidate BOOLEAN DEFAULT to_no_invalidate_type (

Get_param ('NO_INVALIDATE'))

Force BOOLEAN DEFAULT FALSE)

At 11g, the parameters are adjusted:

DBMS_STATS.GATHER_TABLE_STATS (

Ownname VARCHAR2

Tabname VARCHAR2

Partname VARCHAR2 DEFAULT NULL

Estimate_percent NUMBER DEFAULT to_estimate_percent_type

(get_param ('ESTIMATE_PERCENT'))

Block_sample BOOLEAN DEFAULT FALSE

Method_opt VARCHAR2 DEFAULT get_param ('METHOD_OPT')

Degree NUMBER DEFAULT to_degree_type (get_param ('DEGREE'))

Granularity VARCHAR2 DEFAULT GET_PARAM ('GRANULARITY')

Cascade BOOLEAN DEFAULT to_cascade_type (get_param ('CASCADE'))

Stattab VARCHAR2 DEFAULT NULL

Statid VARCHAR2 DEFAULT NULL

Statown VARCHAR2 DEFAULT NULL

No_invalidate BOOLEAN DEFAULT to_no_invalidate_type (

Get_param ('NO_INVALIDATE'))

Force BOOLEAN DEFAULT FALSE)

Description of the parameters:

Parameter

Description

Ownname

Schema of table to analyze

Tabname

Name of table

Partname

Name of partition

Estimate_percent

Percentage of rows to estimate (NULL means compute) The valid range is [0.000001100]. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the appropriate sample size for good statistics. This is the default.The default value can be changed using the SET_PARAM Procedure.

Block_sample

Whether or not to use random block sampling instead of random row sampling. Random block sampling is more efficient, but if the data is not randomly distributed on disk, then the sample values may be somewhat correlated. Only pertinent when doing an estimate statistics.

Method_opt

Accepts:

FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]

FOR COLUMNS [size clause] column | attribute [size_clause] [, column | attribute [size_clause].]

Size_clause is defined as size_clause: = SIZE {integer | REPEAT | AUTO | SKEWONLY}

-integer: Number of histogram buckets. Must be in the range [1254].

-REPEAT: Collects histograms only on the columns that already have histograms.

-AUTO: Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.

-SKEWONLY: Oracle determines the columns to collect histograms based on the data distribution of the columns.

The default is FOR ALL COLUMNS SIZE AUTO.The default value can be changed using the SET_PARAM Procedure.

Degree

Degree of parallelism. The default for degree is NULL. The default value can be changed using the SET_PARAM Procedure NULL means use the table default value specified by the DEGREE clause in the CREATE TABLE or ALTER TABLE statement. Use the constant DBMS_STATS.DEFAULT_DEGREE to specify the default value based on the initialization parameters. The AUTO_DEGREE value determines the degree of parallelism automatically. This is either 1 (serial execution) or DEFAULT_DEGREE (the system default value based on number of CPUs and initialization parameters) according to size of the object.

Granularity

Granularity of statistics to collect (only pertinent if the table is partitioned).

'ALL'-gathers all (subpartition, partition, and global) statistics

'AUTO'- determines the granularity based on the partitioning type. This is the default value.

'DEFAULT'-gathers global and partition-level statistics. This option is obsolete, and while currently supported, it is included in the documentation for legacy reasons only. You should use the 'GLOBAL AND PARTITION' for this functionality. Note that the default value is now 'AUTO'.

'GLOBAL'-gathers global statistics

'GLOBAL AND PARTITION'-gathers the global and partition level statistics. No subpartition level statistics are gathered even if it is a composite partitioned object.

'PARTITION'- gathers partition-level statistics

'SUBPARTITION'-gathers subpartition-level statistics.

Cascade

Gather statistics on the indexes for this table. Index statistics gathering is not parallelized. Using this option is equivalent to running the GATHER_INDEX_STATS Procedure on each of the table's indexes. Use the constant DBMS_STATS.AUTO_CASCADE to have Oracle determine whether index statistics to be collected or not. This is the default. The default value can be changed using theSET_PARAM Procedure.

Stattab

User statistics table identifier describing where to save the current statistics

Statid

Identifier (optional) to associate with these statistics within stattab

Statown

Schema containing stattab (if different than ownname)

No_invalidate

Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE. To have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_PARAM Procedure.

Force

Gather statistics of table even if it is locked

Of all the parameters of the gather_table_stats stored procedure, except for ownname and tabname, the other parameters have default values. So when we call this stored procedure, Oracle parses the table with the default values of the parameters. Such as:

SQL > exec dbms_stats.gather_table_STATS ('SYS','T')

The PL/SQL process completed successfully.

If you want to see the current default value, you can use the dbms_stats.get_param function to get:

SQL > select dbms_stats.get_param ('method_opt') from dual

DBMS_STATS.GET_PARAM ('METHOD_OPT')

FOR ALL COLUMNS SIZE AUTO

Combined with the description of the parameters above:

-AUTO: Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.

As we can see, all the columns are analyzed by histogram, and the value of Oracle set by the histogram is determined by itself.

3.1.1.1 estimate_percent parameters

This parameter is a percentage value that tells the analysis package what percentage of the data in the table to use for analysis.

Theoretically, the more data sampled, the closer the information is to the reality, and the more optimized the execution plan made by CBO. However, the more samples are taken, the more system resources must be consumed. The greater the impact on the system. So the setting of this value should be based on the business situation. If the histogram of the data is evenly distributed, you can use the default value: AUTO_SAMPLE_SIZE, that is, let Oracle determine the sampling ratio itself. Sometimes, especially for bulk-loaded tables, we can estimate the amount of data in the table and manually set a reasonable value. In general, for a table partition with 10 million data, you can set this parameter to 0.000001.

3.1. 1.2 Method_option parameters

This parameter is used to define some values for histogram analysis.

FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]

FOR COLUMNS [size clause] column | attribute [size_clause] [, column | attribute [size_clause].]

Here are four ways to specify which columns to analyze:

(1) all columns: for all column

(2) Index columns: only columns with indexes are analyzed, for all indexed columns

(3) Shadow columns: only analyze the columns of Shadow collections, for all hidden columns

(4) display specified columns: display specified columns for analysis, for columns columns_name

The default value of this parameter is for all columns size auto.

3.1. 1.3 degree parameters

Used to specify the degree of parallelism used in analysis. There are the following settings:

(1) Null: if set to null,Oracle, the parallelism of the properties of the parsed table is used, such as the parallelism specified when the table was created, or the parallelism that the latter uses alter table to reset.

(2) A numeric value: you can explicitly specify the degree of parallelism used in the analysis.

(3) Default_degree: if set to default,Oracle, the degree of parallelism used will be determined according to the setting of the relevant parameters in the initialization parameters.

The default value for this parameter is Null, which determines the parallelism used by the parallelism attribute on the table. When the table or table partition that needs to be analyzed is very large and the system resources are sufficient, we can consider using a parallel way to do the analysis, which will greatly improve the speed of the analysis. Conversely, if your system resources are tight, enabling parallelism may be counterproductive.

3.1. 1.4 Granularity

The granularity of the analysis can be configured as follows:

(1) ALL: the data of global, partition and sub-partition of the table will be analyzed.

(2) AUTO: Oracle automatically decides which granularity to analyze according to the type of partition.

(3) GLOBAL: only do analysis at the global level.

(4) GLOBAL AND PARTITION: only analyze the global and partition levels, but not the sub-partitions, which is different from ALL.

(5) PARTITION: do analysis only at the partition level.

(6) SUBPARTITION: do analysis only in sub-zones.

In production environments, especially in OLAP or data warehouse environments, the setting of this parameter will directly affect the choice of CBO execution plan.

In OLAP or data warehouse systems, it is common to create a new partition, load bulk data (usually large data) into the partition, analyze the partition, and then do reports or data mining. Ideally, analyze the overall situation and partition of the table in order to get the most sufficient data, but usually such tables are very large, and if you need to do a global analysis for each additional partition, it will consume a lot of system resources. However, if only the newly added partition is partitioned without global analysis, the information of oracle in the global scope will be inaccurate.

By default, the DBMS_STATS package analyzes both the table level (global) and the partition level (corresponding parameter partition). If cascade is set to true, the global and partition levels of the corresponding index are also analyzed. If only the partition level is analyzed, but the global information is not analyzed, then the global information is not updated, which will still cause CBO to make an incorrect execution plan.

Therefore, when some new data is inserted into the table, it is a very important problem to analyze the new data. General reference to the following principles:

(1) take a look at the proportion of the newly inserted data in the whole table. If the proportion is not very large, you can consider not doing a global analysis, otherwise you need to consider, one sentence is the actual operation of the business.

(2) sampling ratio. If the amount of data loaded is very large, such as tens of millions or more, the sampling ratio should be reduced as small as possible, but the bottom line cannot affect CBO to make a correct execution plan. The online sampling ratio cannot consume too many resources and affect the normal operation of the business.

(3) the newly loaded data should be analyzed at the partition level. As to whether histogram analysis is needed and how many buckets (size parameter is specified) need to be considered by the distribution of DBA data, the key depends on the tilt degree of the data.

3.1.2 GATHER_SCHEMA_STATS stored procedure

This stored procedure is used to analyze all objects under a user. If you have a large number of data user objects, it is very inconvenient to analyze and set each object individually, and this stored procedure is very convenient. Its advantage is that if there are a large number of objects to be analyzed, it will greatly reduce the workload of DBA. The disadvantage is that all analyses use the same analysis strategy, which may cause the analysis to be not optimal. Therefore, it should be decided according to the actual situation.

The parameters of the stored procedure are as follows:

DBMS_STATS.GATHER_SCHEMA_STATS (

Ownname VARCHAR2

Estimate_percent NUMBER DEFAULT to_estimate_percent_type

(get_param ('ESTIMATE_PERCENT'))

Block_sample BOOLEAN DEFAULT FALSE

Method_opt VARCHAR2 DEFAULT get_param ('METHOD_OPT')

Degree NUMBER DEFAULT to_degree_type (get_param ('DEGREE'))

Granularity VARCHAR2 DEFAULT GET_PARAM ('GRANULARITY')

Cascade BOOLEAN DEFAULT to_cascade_type (get_param ('CASCADE'))

Stattab VARCHAR2 DEFAULT NULL

Statid VARCHAR2 DEFAULT NULL

Options VARCHAR2 DEFAULT 'GATHER'

Objlist OUT ObjectTab

Statown VARCHAR2 DEFAULT NULL

No_invalidate BOOLEAN DEFAULT to_no_invalidate_type (

Get_param ('NO_INVALIDATE'))

Force BOOLEAN DEFAULT FALSE

Obj_filter_list ObjectTab DEFAULT NULL)

The parameters are described as follows:

Parameter

Description

Ownname

Schema to analyze (NULL means current schema)

Estimate_percent

Percentage of rows to estimate (NULL means compute): The valid range is [0.000001100]. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the appropriate sample size for good statistics. This is the default.The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.

Block_sample

Whether or not to use random block sampling instead of random row sampling. Random block sampling is more efficient, but if the data is not randomly distributed on disk, then the sample values may be somewhat correlated. Only pertinent when doing an estimate statistics.

Method_opt

Accepts:

FOR ALL [INDEXED | HIDDEN] Columns [size _ clause]

Size_clause is defined as size_clause: = SIZE {integer | REPEAT | AUTO | SKEWONLY}

-integer: Number of histogram buckets. Must be in the range [1254].

-REPEAT: Collects histograms only on the columns that already have histograms.

-AUTO: Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.

-SKEWONLY: Oracle determines the columns to collect histograms based on the data distribution of the columns.

The default is FOR ALL COLUMNS SIZE AUTO.The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.

Degree

Degree of parallelism. The default for degree is NULL. The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure. NULL means use the table default value specified by the DEGREE clause in the CREATE TABLE or ALTER TABLE statement. Use the constant DBMS_STATS.DEFAULT_DEGREE to specify the default value based on the initialization parameters.The AUTO_DEGREEvalue determines the degree of parallelism automatically. This is either 1 (serial execution) or DEFAULT_DEGREE (the system default value based on number of CPUs and initialization parameters) according to size of the object.

Granularity

Granularity of statistics to collect (only pertinent if the table is partitioned).

'ALL'-gathers all (subpartition, partition, and global) statistics

'AUTO'- determines the granularity based on the partitioning type. This is the default value.

'DEFAULT'-gathers global and partition-level statistics. This option is obsolete, and while currently supported, it is included in the documentation for legacy reasons only. You should use the 'GLOBAL AND PARTITION' for this functionality. Note that the default value is now 'AUTO'.

'GLOBAL'-gathers global statistics

'GLOBAL AND PARTITION'-gathers the global and partition level statistics. No subpartition level statistics are gathered even if it is a composite partitioned object.

'PARTITION'- gathers partition-level statistics

'SUBPARTITION'-gathers subpartition-level statistics.

Cascade

Gather statistics on the indexes as well. Using this option is equivalent to running the GATHER_INDEX_STATS Procedure on each of the indexes in the schema in addition to gathering table and column statistics. Use the constant DBMS_STATS.AUTO_CASCADE to have Oracle determine whether index statistics to be collected or not. This is the default. The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.

Stattab

User statistics table identifier describing where to save the current statistics

Statid

Identifier (optional) to associate with these statistics within stattab

Options

Further specification of which objects to gather statistics for:

GATHER: Gathers statistics on all objects in the schema.

GATHERAUTO: Gathers all necessary statistics automatically. Oracle implicitly determines which objects need new statistics, and determines how to gather those statistics. When GATHER AUTO is specified, the only additional valid parameters are ownname, stattab, statid, objlist and statown; all other parameter settings are ignored. Returns a list of processed objects.

GATHERSTALE: Gathers statistics on stale objects as determined by looking at the * _ tab_modifications views. Also, return a list of objects found to be stale.

GATHEREMPTY: Gathers statistics on objects which currently have no statistics. Also, return a list of objects found to have no statistics.

LIST AUTO: Returns a list of objects to be processed with GATHER AUTO.

LISTSTALE: Returns list of stale objects as determined by looking at the * _ tab_modifications views.

LISTEMPTY: Returns list of objects which currently have no statistics.

Objlist

List of objects found to be stale or empty

Statown

Schema containing stattab (if different than ownname)

No_invalidate

Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE. To have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.

Force

Gather statistics on objects even if they are locked

Obj_filter_list

A list of object filters. When provided, GATHER_SCHEMA_STATS will gather statistics only on objects which satisfy at least one object filter in the list as needed. In a single object filter, we can specify the constraints on the object attributes. The attribute values specified in the object filter are case- insensitive unless double-quoted. Wildcard is allowed in the attribute values. Suppose non-NULL values s1, s2,... Are specified for attributes a1, a2,... In one object filter. An object o is said to satisfy this object filter if (o.a1 like S1) and (o.a2 like S2) and... Is true. See Applying an Object Filter List.

3.1.3 DBMS_STATS.GATHER_INDEX_STATS stored procedures

This stored procedure is used to analyze the index if we set the parameter cascade= > true when using DBMS_STATS.GATHER_TABLES_STATS 's analysis. Then Oracle executes the stored procedure at the same time to analyze the index.

Stored procedure parameters:

DBMS_STATS.GATHER_INDEX_STATS (

Ownname VARCHAR2

Indname VARCHAR2

Partname VARCHAR2 DEFAULT NULL

Estimate_percent NUMBER DEFAULT to_estimate_percent_type

(GET_PARAM ('ESTIMATE_PERCENT'))

Stattab VARCHAR2 DEFAULT NULL

Statid VARCHAR2 DEFAULT NULL

Statown VARCHAR2 DEFAULT NULL

Degree NUMBER DEFAULT to_degree_type (get_param ('DEGREE'))

Granularity VARCHAR2 DEFAULT GET_PARAM ('GRANULARITY')

No_invalidate BOOLEAN DEFAULT to_no_invalidate_type

(GET_PARAM ('NO_INVALIDATE'))

Force BOOLEAN DEFAULT FALSE)

Parameter

Description

Ownname

Schema of index to analyze

Indname

Name of index

Partname

Name of partition

Estimate_percent

Percentage of rows to estimate (NULL means compute). The valid range is [0.000001100]. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the appropriate sample size for good statistics. This is the default.The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.

Stattab

User statistics table identifier describing where to save the current statistics

Statid

Identifier (optional) to associate with these statistics within stattab

Statown

Schema containing stattab (if different than ownname)

Degree

Degree of parallelism. The default for degree is NULL. The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure. NULL means use of table default value that was specified by the DEGREE clause in the CREATE/ALTER INDEX statement. Use the constant DBMS_STATS.DEFAULT_DEGREE for the default value based on the initialization parameters. The AUTO_DEGREE value determines the degree of parallelism automatically. This is either 1 (serial execution) or DEFAULT_DEGREE (the system default value based on number of CPUs and initialization parameters) according to size of the object.

Granularity

Granularity of statistics to collect (only pertinent if the table is partitioned).

'ALL'-gathers all (subpartition, partition, and global) statistics

'AUTO'- determines the granularity based on the partitioning type. This is the default value.

'DEFAULT'-gathers global and partition-level statistics. This option is obsolete, and while currently supported, it is included in the documentation for legacy reasons only. You should use the 'GLOBAL AND PARTITION' for this functionality. Note that the default value is now 'AUTO'.

'GLOBAL'-gathers global statistics

'GLOBAL AND PARTITION'-gathers the global and partition level statistics. No subpartition level statistics are gathered even if it is a composite partitioned object.

'PARTITION'- gathers partition-level statistics

'SUBPARTITION'-gathers subpartition-level statistics.

No_invalidate

Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE. To have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.

Force

Gather statistics on object even if it is locked

Three commonly used stored procedures are discussed above. Analysis is very important for CBO. Failure to specify a realistic data analysis scheme according to your own system may lead to the following problems:

The main results are as follows: (1) insufficient analysis information leads to wrong execution plan of CBO and inefficiency of SQL execution.

(2) too many analysis tools bring about a serious decline in system performance.

3.2 DBMS_STATS package management features

3.2.1 obtaining analysis data

GET_COLUMN_STATS Procedures

GET_INDEX_STATS Procedures

GET_SYSTEM_STATS Procedure

GET_TABLE_STATS Procedure

These four stored procedures obtain field, index, table and system statistics for the user, respectively. Its use is to first define the variable to get the performance metric, then use the stored procedure to assign the value of the performance metric to the variable, and finally output the value of the variable. Such as:

SQL > set serveroutput on

SQL > declare

2 dist number

3 dens number

4 ncnt number

5 orec dbms_stats.statrec

6 avgc number

7 begin

8 dbms_stats.get_column_stats ('SYS','T','object_ID',distcnt= > dist,density= > dens,nullcnt= > ncnt,srec= > orec,avgclen= > avgc)

9 dbms_output.put_line ('the distcnt is:' | | to_char (dist))

10 dbms_output.put_line ('the density is:' | | to_char (dens))

11 dbms_output.put_line ('the nullcnt is:' | | to_char (ncnt))

12 dbms_output.put_line ('the srec is:' | | to_char (ncnt))

13 dbms_output.put_line ('the avgclen is:' | | to_char (avgc))

14 end

15 /

The distcnt is:72926

The density is:.0000137125305103804

The nullcnt is:0

The srec is:0

The avgclen is:5

The PL/SQL process completed successfully.

3.2.2 set up analysis data

SET_COLUMN_STATS Procedures

SET_INDEX_STATS Procedures

SET_SYSTEM_STATS Procedure

SET_TABLE_STATS Procedure

These stored procedures allow us to manually assign values to fields, indexes, tables, and system performance data. One of its uses is that when the corresponding metrics are inaccurate and the execution plan fails, you can use this method to manually assign values to these performance data. In extreme cases, this is also a solution to the problem.

3.2.3 deleting analysis data

DELETE_COLUMN_STATS Procedure

DELETE_DATABASE_STATS Procedure

DELETE_DICTIONARY_STATS Procedure

DELETE_FIXED_OBJECTS_STATS Procedure

DELETE_INDEX_STATS Procedure

DELETE_SCHEMA_STATS Procedure

DELETE_SYSTEM_STATS Procedure

DELETE_TABLE_STATS Procedure

When the abnormal performance data leads to the CBO judgment error, in order to correct this error immediately, deleting the performance data is also a remedial method, such as deleting the data of the table and letting CBO do dynamic sampling and analysis of the table to get a correct result.

It can delete performance data at fields, databases, data dictionaries, base tables, indexes, tables, and so on.

3.2.4 Save analysis data

CREATE_STAT_TABLE Procedure

DROP_STAT_TABLE Procedure

You can use these two stored procedures to create a table to hold the performance data, which facilitates the management of the performance data, or you can delete the table.

3.2.5 Import and export analysis data

EXPORT_COLUMN_STATS Procedure

EXPORT_DATABASE_STATS Procedure

EXPORT_DICTIONARY_STATS Procedure

EXPORT_FIXED_OBJECTS_STATS Procedure

EXPORT_INDEX_STATS Procedure

EXPORT_SCHEMA_STATS Procedure

EXPORT_SYSTEM_STATS Procedure

EXPORT_TABLE_STATS Procedure

IMPORT_COLUMN_STATS Procedure

IMPORT_DATABASE_STATS Procedure

IMPORT_DICTIONARY_STATS Procedure

IMPORT_FIXED_OBJECTS_STATS Procedure

IMPORT_INDEX_STATS Procedure

IMPORT_SCHEMA_STATS Procedure

IMPORT_SYSTEM_STATS Procedure

IMPORT_TABLE_STATS Procedure

These stored procedures can import existing performance metrics into a user-created table for storage and can be reversed from the table when needed.

3.2.6 Lock analysis data

LOCK_SCHEMA_STATS Procedure

LOCK_TABLE_STATS Procedure

UNLOCK_SCHEMA_STATS Procedure

UNLOCK_TABLE_STATS Procedure

The LOCK_* procedures either freeze the current set of the statistics or to keep the statistics empty (uncollected). When statistics on a table are locked, all the statistics depending on the table, including table statistics, column statistics, histograms and statistics on all dependent indexes, are considered to be locked.

Maybe at some point, when we think that the current statistics are very good, the execution plan is accurate, and the data in the table is almost unchanged, then we can use LOCK_TABLE_STATS Procedure to lock the statistics of the table, and it is not allowed to analyze the table or set the analysis data. When the analysis data of the table is locked, all the relevant analysis data, including table-level, column-level, histogram, index analysis data will be locked, not allowed to be updated.

After reading the above, do you have any further understanding of the common functions of the DBMS_STATS package? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.