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

Oracle statistics collection

1: collect statistics 2: lock statistics 3: delete statistics 4: export / import statistics 5: query statistics

Six: official documents of statistical information

One: collect statistical information

(1) user level

Example 1:

Begin

Dbms_stats.gather_schema_stats (ownname = > UPPER (OWNER_NAME)

Estimate_percent = > 100

Method_opt = > 'FOR ALL INDEXED COLUMNS'

Cascade = > TRUE)

End

Example 2:

Exec dbms_stats.gather_schema_stats ('HR')

(2) Table level

EXEC DBMS_STATS.GATHER_TABLE_STATS ('HR',' T1')

EXEC DBMS_STATS.GATHER_TABLE_STATS ('OWNER','TABLE',estimate_percent= > 30 cacade = > TRUE)

EXEC DBMS_STATS.GATHER_TABLE_STATS ('SH',' SALES', method_opt = > 'FOR COLUMNS (empno, deptno)')

EXEC DBMS_STATS.GATHER_TABLE_STATS ('SH',' SALES', method_opt = > 'FOR COLUMNS (sal+comm)')

Analyze table abc compute statistics

Analyze table abc estimate statistics sample 20 percent

Analyze table tablename compute statistics for all indexes

-analyze table compute statistics = analyze table compute statistics for table for all indexes for all columns

-histogram

Exec dbms_stats.gather_table_stats ('chen','test1',method_opt= >' for columns col1 size AUTO')

Exec dbms_stats.gather_table_stats ('chen','test1',method_opt= >' for columns (ACCYEAR,ACCMONTH) size AUTO')

Analyze table gl_detail compute statistics for columns prepareddatev size 254

Two: lock statistics

Exec dbms_stats.lock_table_stats (ownname = > user,tabname = > table_name)

Three: delete statistics

Begin

Dbms_stats.delete_table_stats (ownname = > 'CHEN', tabname = >' TEST1')

End

Or

ANALYZE TABLE TEST1 DELETE STATISTICS

-delete and lock

BEGIN

DBMS_STATS.DELETE_TABLE_STATS ('HR','ORDERS')

DBMS_STATS.LOCK_TABLE_STATS ('HR','ORDERS')

END

Four: export / import statistics

(1) first create an analysis table, which is used to hold the previous analysis values:

Begin

Dbms_stats.create_stat_table (ownname = > 'CHEN', stattab = >' STAT_TABLE',cascade = > true)

End

(2) Export table analysis information to stat_table

Begin

Dbms_stats.export_table_stats (ownname = > 'CHEN'

Tabname = >'T1'

Stattab = > 'STAT_TABLE')

End

(3) start updating T1 table statistics

Begin

Dbms_stats.gather_table_stats (ownname = > 'CHEN'

Tabname = >'T1'

Cascade = > true)

End

(4) Delete analysis information

Begin

Dbms_stats.delete_table_stats (ownname = > 'CHEN', tabname = >' T1')

End

(5) Import analysis information

Begin

Dbms_stats.import_table_stats (ownname = > 'CHEN'

Tabname = >'T1'

Stattab = > 'STAT_TABLE')

End

Five: query statistical information

(1) View table statistics

DBA_TABLES

DBA_TAB_STATISTICS

(2) View column statistics

DBA_TAB_COL_STATISTICS

DBA_TAB_COLUMNS

(3) View index statistics

DBA_IND_STATISTICS

Six: official documents of statistical information

Http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/toc.htm http://docs.oracle.com/cd/E11882_01/server.112/e41573/stats.htm#PFGRF003

Optimizer statistics include the following:

1:Table statistics

(1) Number of rows

(2) Number of blocks

(3) Average row length

2:Column statistics

(1) Number of distinct values (NDV) in column

(2) Number of nulls in column

(3) Data distribution (histogram)

(4) Extended statistics

3:Index statistics

(1) Number of leaf blocks

(2) Levels

(3) Clustering factor

4:System statistics

(1) I am O performance and utilization

(2) CPU performance and utilization

Two: Managing Automatic Optimizer Statistics Collection

This section contains the following topics:

1:Enabling and Disabling Automatic Optimizer Statistics Collection

2:Considerations When Gathering Statistics

1:Enabling and Disabling Automatic Optimizer Statistics Collection

If for some reason automatic optimizer statistics collection is disabled, then you can enable it using the ENABLE procedure in the DBMS_AUTO_TASK_ADMIN package:

BEGIN

DBMS_AUTO_TASK_ADMIN.ENABLE (

Client_name = > 'auto optimizer stats collection'

, operation = > NULL

, window_name = > NULL

);

END

/

When you want to disable automatic optimizer statistics collection, you can disable it using the DISABLE procedure in the DBMS_AUTO_TASK_ADMIN package:

BEGIN

DBMS_AUTO_TASK_ADMIN.DISABLE (

Client_name = > 'auto optimizer stats collection'

, operation = > NULL

, window_name = > NULL

);

END

/

GATHER_SCHEMA_STATS Procedures

This procedure gathers statistics for all objects in a schema.

Syntax

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)

Parameters

Table 142-43 GATHER_SCHEMA_STATS Procedure Parameters

(1) ownname

Schema to analyze (NULL means current schema)

(2) 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.

(3) 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.

(4) method_opt

Accepts:

FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]

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

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

Column is defined as column: = column_name | extension name | extension

-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 on which to collect histograms based on data distribution and the workload of the columns.

-SKEWONLY: Oracle determines the columns on which 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.

(5) 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_DEGREE value determines the degree of parallelism automatically.

This is between 1 (serial execution) and DEFAULT_DEGREE (the system default value based on number of CPUs and initialization parameters) according to the size of the object.

When using DEGREE= > NULL, DEGREE= > n, or DEGREE= > DBMS_STATS.DEFAULT_DEGREE, the current implementation of DBMS_STATS may use serial execution if the size of the object does not warrant parallel execution.

(6) 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.

(7) 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.

(8) stattab

User statistics table identifier describing where to save the current statistics.

(9) statid

User statistics table identifier describing where to save the current statistics.

(10) statid

Identifier (optional) to associate with these statistics within stattab.

(11) options

Further specification of which objects to gather statistics for:

GATHER: Gathers statistics on all objects in the schema.

GATHER AUTO: 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.

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

GATHER EMPTY: 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.

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

LIST EMPTY: Returns list of objects which currently have no statistics.

(12) objlist

List of objects found to be stale or empty

(13) statown

Schema containing stattab (if different than ownname)

(14) 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.

(15) force

Gather statistics on objects even if they are locked

(16) 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.

Welcome to follow my Wechat official account "IT Little Chen" and learn and grow together!

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.