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

The main content of this article is "how to collect oracle statistics". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to collect oracle statistics.

Collect oracle statistics

Optimizer statistical range:

Table statistics;-- number of rows, number of blocks, average row length; all_tables:NUM_ROWS,BLOCKS,AVG_ROW_LEN

Column statistics;-- number of unique values in the column (NDV), number of null values, data distribution

-- DBA_TAB_COLUMNS:NUM_DISTINCT,NUM_NULLS,HISTOGRAM

Index statistics;-- number of leaf blocks, grade, clustering factor

-- DBA_INDEXES:LEAF_BLOCKS,CLUSTERING_FACTOR,BLEVEL

System statistics;-- performance and utilization of Istroke O

-- CPU performance and utilization

-- stored in aux_stats$, which needs to be collected using dbms_stats, and Imax O statistics in X$KCFIO.

-

Analyze

-

Statistics that need to be counted using ANALYZE:

Use the LIST CHAINED ROWS and VALIDATE clauses

Collect statistics for free list blocks

Analyze table tablename compute statistics

Analyze index | cluster indexname estimate statistics

ANALYZE TABLE tablename COMPUTE STATISTICS

FOR TABLE

FOR ALL [LOCAL] INDEXES

FOR ALL [INDEXED] COLUMNS

ANALYZE TABLE tablename DELETE STATISTICS

ANALYZE TABLE tablename VALIDATE REF UPDATE

ANALYZE TABLE tablename VALIDATE STRUCTURE [CASCADE] | [INTO TableName]

ANALYZE TABLE tablename LIST CHAINED ROWS [INTO TableName]

ANALYZE is not suitable for partition table analysis.

--

Dbms_stats

--

Dbms_stats can well estimate statistics (especially for larger partition tables) and obtain better statistical results, resulting in a faster SQL execution plan.

The following four stored procedures in this package collect statistics for index, table, schema, and database, respectively:

Dbms_stats.gather_table_stats collects statistics for tables, columns, and indexes

Dbms_stats.gather_schema_stats collects statistics for all objects under SCHEMA

Dbms_stats.gather_index_stats collects statistics for the index

Dbms_stats.gather_system_stats collects system statistics

Dbms_stats.GATHER_DICTIONARY_STATS: statistics of all dictionary objects

DBMS_STATS.GATHER_DICTIONARY_STATS collects statistics for all system patterns

Dbms_stats.delete_table_stats delete table statistics

Statistics for dbms_stats.delete_index_stats delete index

Statistics for the dbms_stats.export_table_stats output table

Dbms_stats.create_state_table

Statistics of dbms_stats.set_table_stats settings table

Dbms_stats.auto_sample_size

Permissions for statistical collection

= =

Ordinary users must be granted permissions

Sys@ORADB > grant execute_catalog_role to hr

Sys@ORADB > grant connect,resource,analyze any to hr

Consideration of the time of statistical collection

= =

When the parameter STATISTICS_LEVEL is set to TYPICAL or ALL, the system automatically collects statistics at night.

View the job where the system automatically collects statistics:

SELECT * FROM dba_scheduler_jobs WHERE job_name = 'GATHER_STATS_JOB'

You can also collect statistics automatically by disable:

BEGIN

Dbms_scheduler.disable ('GATHER_STATS_JOB')

END

Use manual statistics

Automatic statistics for all objects with moderate change activity should be sufficient, and because automatic statistics collection takes place at night, statistics may be out of date for some objects that are updated frequently. Two typical objects:

Highly variable watches are TRUNCATE/DROP and rebuilt during daytime activities

Block loads objects that exceed 10% of its total size

You can use the following two methods for the first object:

1 set the statistics on these tables to NULL. When Oracle encounters tables that do not have statistics, it will dynamically collect necessary statistics as part of query optimization.

The dynamic collection feature is controlled by OPTIMIZER_DYNAMIC_SAMPLING, and this parameter should be set to greater than or equal to 2, and the default is 2. You can set statistics to NULL by deleting and locking statistics:

DBMS_STATS.DELETE_TABLE_STATS ('SCHEMA','TABLE')

DBMS_STATS.LOCK_TABLE_STATS ('SCHEMA','TABLE')

2 set the statistics on these tables to values that represent the typical state of the table. Collect statistics when the table has a representative value, and then lock the statistics

Since statistics collected at night may not be suitable for daytime loads, manual collection is more effective than GATHER_STATS_JOB in these cases.

For block loading, statistics should be collected immediately after loading, usually merged after the load statement to prevent forgetting.

For external tables, statistics cannot be collected through GATHER_DATABASE_STATS,GATHER_SCHEMA_STATS or automatic statistics collection. So you need to use GATHER_TABLE_STATS to collect statistics on a single table, and sampling is not supported on external tables, and ESTIMATE_PERCENT should be set to NULL.

If STATISTICS_LEVEL is set to BASIC and monitoring features are disabled, automatic statistics collection will not detect out-of-date statistics and need to be collected manually.

3 another place that needs to be collected manually is system statistics, which will not be collected automatically.

For fixed tables, such as dynamic performance tables, you need to use GATHER_FIXED_OBJECTS_STATS collection, and the statistics on these tables should be collected after a representative activity in the database.

Statistical collection consideration

= =

1 Statistical collection using sampling

Statistical collection that does not use sampling requires a full table scan and sorting of the entire table, and sampling minimizes the necessary resources for collecting statistics.

Oracle recommends setting the ESTIMATE_PERCENT parameter of DBMS_STATS to DBMS_STATS.AUTO_SAMPLE_SIZE to maximize performance while achieving the necessary statistical accuracy.

2 parallel statistical collection

Oracle recommends setting the DEGREE parameter of DBMS_STATS to DBMS_STATS.AUTO_DEGREE, which allows Oracle to choose the appropriate degree of parallelism based on the size of the object and the setting of the parallelism initialization parameter.

Clustered indexes, domain indexes, bitmap join indexes cannot be collected in parallel.

3. Statistical collection of partitioned objects

For partitioned tables and indexes, DBMS_STATS can collect statistics for individual partitions and global partitions. For combined partitions, you can collect statistics on subpartitions, partitions, tables / indexes, and partition statistics can be collected by declaring the parameter GRANULARITY. Depending on the SQL statement to be optimized, the optimizer can choose to use partition statistics or global statistics, both of which are important for most systems, and Oracle recommends that GRANULARITY be set to AUTO to collect all the information at the same time.

4 columns of statistics and histogram

When collecting statistics on the table, DBMS_STATS collects information about the data distribution of the columns in the table. The most basic information of the data distribution is the maximum and minimum values, but if the data distribution is skewed, this level of statistics is not enough for the optimizer. For skewed data distribution, histograms are usually used as part of column statistics.

The histogram is declared through the METHOD_OPT parameter, and Oracle recommends setting METHOD_OPT to FOR ALL COLUMNS SIZE AUTO. When using this value, Oracle automatically determines the columns that need the histogram and the number of barrels per histogram. You can also manually set the columns that require a histogram and the number of buckets.

If you need to delete all rows in the table when using DBMS_STATS, you need to use TRUNCATE instead of drop/create, otherwise the load information used by the automatic statistical collection feature and the saved statistical history used by RESTORE_*_STATS will be lost. These features will not function properly.

5 Statistics to determine expiration date

Statistics must be collected periodically for objects that change over time, and to determine out-of-date statistics, Oracle provides a table to monitor these changes, which is enabled by default when STATISTICS_LEVEL is TYPICAL/ all, and the table is USER_TAB_MODIFICATIONS. Using DBMS_STATS.FLUSH_DATABASE _ MONITORING_INFO can immediately reflect the information in memory that exceeds monitoring. When the OPTIONS parameter is set to GATHER STALE or GATHER AUTO, DBMS_STATS collects statistics for objects that have expired statistics.

6 user-defined statistics

After you have created index-based statistics, you should collect new column statistics on the table, which can be set to the FOR ALL HIDDEN COLUMNS of METHOD_OPT by calling the procedure.

7 when to collect statistics

For incrementally changed tables, it may only need to be collected once a month / week, while for post-loaded tables, scripts for collecting statistics are usually added to the load script. For partitioned tables, if only one partition has a major change, only one partition statistics need to be collected, but it is also necessary to collect the partition of the entire table.

System statistics

= =

System statistics describe the characteristics of the hardware of the system, including Icano and CPU. When choosing an execution plan, the optimizer takes into account the CPU and Icano costs required for the query. System statistics allow the optimizer to evaluate CPU and IO costs more accurately and choose a better query plan.

Use DBMS_STATS.GATHER_SYSTEM_STATS to collect system statistics, which Oracle recommends. DBA permission is required to collect system statistics.

The optimizer system statistics collected include:

CpuspeedNW: represents the unloaded CPU speed, and the CPU speed is the number of CPU cycles per second; set gathering_mode = NOWORKLOAD or manually set statistics; unit Millions/sec.

Ioseektim:I/O lookup time = lookup time + delay time + OS load time; set gathering_mode = NOWORKLOAD or manually set statistics; unit is ms.

Iotfrspeed:I/O transmission speed; by setting gathering_mode = NOWORKLOAD or manually setting statistics; unit is Bytes/ms.

Cpuspeed: represents the loaded CPU speed, and the CPU speed is the number of CPU cycles per second. Set gathering_mode = NOWORKLOAD,INTERVAL, START | STOP or manually set statistics (in Millions/sec).

Maxthr: maximum NOWORKLOAD,INTERVAL O throughput; set gathering_mode = NOWORKLOAD,INTERVAL, START | STOP or manually set statistics; unit Bytes/sec.

Slavethr: service INTERVAL,START O throughput is the average parallel service Imax O throughput; by setting gathering_mode = INTERVAL,START | STOP or manually setting statistics; Bytes/sec.

Sreadtim: the aPCge time to read a single block randomly; set gathering_mode = INTERVAL,START | STOP or manually set statistics (in ms).

Mreadtim: the aPCge time to read multiple chunks sequentially, set by setting gathering_mode = INTERVAL,START | STOP or manually setting statistics (in ms).

Mbrc: the aPCge number of chunks per read for multiple chunks; set by setting gathering_mode = INTERVAL,START | STOP or manually set statistics (in blocks).

The re-collection of system statistics does not invalidate the current SQL, but all new SQL statements use the new statistics.

Oracle provides two options for collecting statistics: load statistics and non-load statistics.

Load statistics

= =

Run dbms_stats.gather_system_stats ('start') at the beginning of the load window, and then run dbms_stats.gather_system_stats (' stop') to end the load window.

Run dbms_stats.gather_system_stats ('interval', interval= > N), where N means the system statistics collection ends after N minutes.

Run dbms_stats.delete_system_stats () to delete the load statistics.

Non-load statistics

= =

Run dbms_stats.gather_system_stats () with no parameters to collect non-load statistics, and there will be a certain amount of load when running non-load statistics. In some cases, the value of non-payload statistics may remain the default, and the dbms_stats.set_system_stats setting is required.

Management statistics

= =

Dump statistics for previous versions

Use the RESTORE procedure to dump previous versions of statistics, which use a timestamp as a parameter, and the views that contain the statistical time include:

1 DBA_OPTSTAT_OPERATIONS: this includes statistical operations performed at the mode / system level using DBMS_STATS

2 * _ TAB_STATS_HISTORY: contains the history of table statistics changes.

The old statistics are refreshed periodically, depending on the ALTER_STATS_HISTORY_RETENTION process setting of DBMS_STATS, with a default of 31 days.

By default, automatic refresh is enabled if STATISTICS_LEVEL is TYPICAL/ALL; otherwise, manual refresh is required using PURGE_STAT.

Other information related to the dump refresh includes:

PURGE_STATS: manually refresh old statistics that exceed a certain timestamp

GET_STATS_HISTORY_RENTENTION: get the current historical statistics retention value

GET_STATS_HISTORY_AVAILABILTY: gets the timestamp of the oldest statistics available.

Restrictions on dumps:

1 cannot dump user-defined statistics

2 if ANALYZE collection is used, the old statistics cannot be dumped.

Import / Export Statistics

= =

Before exporting statistics, you need to use DBMS_STATS.CREATE_STAT_TABLE to create a statistical table to retain statistics. After the table is created, you can use DBMS_STATS.EXPORT_*_STATS to export statistics to a custom table, and these statistics can be re-imported using DBMS_STATS.IMPORT_*_STATS.

You can also use IMP/ exp to import to other databases.

Dump statistics and import and export statistics

Use of dumps:

1 restore the statistics of the old version

2 want to retain and refresh the statistical history of database management

When using EXPORT/IMPORT_*_STATS:

1 experiment on different situations of various values

2 move statistics to different databases

(3) retain the statistical data for a longer time.

Lock statistics for tables and schemas

= =

Once the statistics are locked, they cannot be changed until they are unlocked. DBMS_STAT provides two procedures for unlocking and two for locking:

1 LOCK_SCHEMA_STATS; LOCK_TABLE_STATS

2 UNLOCK_SCHEMA_STATS; UNLOCK_TABLE_STATS

Set up statistics

= =

You can use SET_*_STATISTICS to set tables, indexes, columns, and system statistics.

Using dynamic sampling to evaluate statistics

= =

The purpose of dynamic sampling is to improve server performance by determining more accurate estimates for predicate selectivity and table / index statistics.

Situations where dynamic sampling can be used:

1 estimate the predicate selectivity of a single table when the collected statistics cannot be used or will lead to serious estimation errors

2 estimate the statistics of tables / indexes without statistics

3 Statistics of expired tables and indexes

The dynamic sampling feature is controlled by the parameter OPTIMIZER_DYNAMIC_SAMPLING, and the default level is 2.

Working mechanism of dynamic sampling

The main performance feature is that at compile time, Oracle determines whether a query can benefit from sampling, and if so, it randomly scans a small number of table blocks with recursive SQL, and then applies related single-table predicates to evaluate predicate selectivity.

Time to use dynamic sampling

Benefits from the use of dynamic sampling:

1 you can find a better implementation plan

2 sampling time is only a small part of the total time

3 query will be executed multiple times

Sampling level

= =

The range is from 1. 10

Missing statistical processing

= =

When Oracle encounters missing statistics, the optimizer dynamically makes the necessary statistics. In some cases, Oracle cannot perform dynamic sampling, including remote / external tables, where default statistics are used.

Table defaults when statistics are missing:

1 Cardinality:num_of_blocks * (block_size-cache_layer) / avg_row_len

2 Average row length:100 byt

3 actual value of Number of blocks:100 or partition-based mapping

4 Remote cardinality:2000 Lin

5 Remote average row length:100 byt

Index default value when statistics are missing:

Levels:1

Leaf blocks:25

Leaf blocks/key:1

Data blocks/key:1

Distinct keys:100

Clustering factor:800

Gather_schema_stats

= =

Begin

Dbms_stats.gather_schema_stats (wnname = > 'SCOTT'

Ptions = > 'GATHER AUTO'

Estimate_percent = > dbms_stats.auto_sample_size

Method_opt = > 'for all columns size repeat'

Degree = > 15)

End

The options parameter uses four preset methods:

Gather-- reanalyzes the entire architecture (Schema).

Gather empty-- only analyzes tables that are not yet available.

Gather stale-- only reanalyzes tables with more than 10% modifications (these changes include inserts, updates, and deletions).

Gather auto-- reanalyzes objects that do not currently have statistics, as well as objects whose statistics are out of date (dirty). Similar to using a combination of gather stale and gather empty.

Note that monitoring is required for both gather stale and gather auto.

If you execute an alter table xxx monitoring command, Oracle uses the dba_tab_modifications view to track the table that has changed.

In this way, you know exactly how many inserts, updates, and deletions have occurred since the last time the statistics were analyzed.

SELECT * FROM Sys.Dba_Tab_Modifications WHERE Table_Owner = 'SCOTT'

When using the alter table xxx monitoring command to implement Oracle table monitoring, you need to use the auto option in dbms_stats.

The auto option is based on data distribution and how the application accesses the column (for example, the amount of work of a column determined by monitoring)

To create a histogram. Using method_opt= > 'auto' is similar to using gather auto in the option parameter of dbms_stats.

Begin

Dbms_stats.gather_schema_stats (ownname = > 'SCOTT'

Estimate_percent = > dbms_stats.auto_sample_size

Method_opt = > 'for all columns size auto'

Degree = > 7)

End

Estimate_percent option

The following estimate_percent parameters are a relatively new design that allows Oracle's dbms_stats to automatically estimate the optimal percentage of a segment to sample when collecting statistics:

Estimate_percent = > dbms_stats.auto_sample_size

To verify the accuracy of automatic statistical sampling, you can view the dba_tables sample_size column. One interesting thing is that when using automatic sampling, Oracle chooses a percentage of 5 to 20 for a sample size. Remember, the better the quality of the statistics, the better the decisions CBO makes.

Method_opt option

The method_opt parameter of dbms_stats is particularly useful for refreshing statistics when table and index data changes. The method_opt parameter is also suitable for determining which columns require a histograms.

In some cases, the distribution of values within an index affects CBO's decision to use an index or perform a full table scan. For example, if the number of values specified in the where clause is asymmetrical, a full table scan appears more economical than index access.

If you have a highly skewed index (the number of rows of some values is asymmetric), you can create Oracle histogram statistics. But in the real world, the chances of this happening are quite small. One of the most common mistakes when using CBO is to introduce histograms unnecessarily in CBO statistics. As a rule of thumb, histograms should be used only if the column values require that the execution plan must be modified.

In order to generate histograms intelligently, Oracle prepares the method_opt parameter for dbms_stats. There are also some important new options in the method_opt clause, including skewonly,repeat and auto:method_opt= > 'for all columns size skewonly'

Method_opt= > 'for all columns size repeat'

Method_opt= > 'for all columns size auto'

The skewonly option takes a lot of processing time because it checks the distribution of values for each column in each index.

If dbms_stat finds that the columns of an index are unevenly distributed, it creates a histogram for that index to help the cost-based SQL optimizer decide whether to do index access or full table scan access. For example, in an index, assuming that there is a column in 50% of the rows, the full table scan is faster than the index scan in order to retrieve those rows.

-- *

-- SKEWONLY option-Detailed analysis

--

-- Use this method for a first-time analysis for skewed indexes

-- This runs a long time because all indexes are examined

-- *

Begin

Dbms_stats.gather_schema_stats (ownname = > 'SCOTT'

Estimate_percent = > dbms_stats.auto_sample_size

Method_opt = > 'for all columns size skewonly'

Degree = > 7)

End

When reanalyzing statistics, using the repeat option, the reanalysis task consumes less resources. When you use the repeat option, only the existing histogram is re-indexed and no other histogram opportunities are searched. You should take this approach when reanalyzing statistics on a regular basis.

-- *

-- REPEAT OPTION-Only reanalyze histograms for indexes

-- that have histograms

--

-- Following the initial analysis, the weekly analysis

Job will use the "repeat" option. The repeat option

-- tells dbms_stats that no indexes have changed, and

-- it will only reanalyze histograms for

-- indexes that have histograms.

-- *

Begin

Dbms_stats.gather_schema_stats (ownname = > 'SCOTT'

Estimate_percent = > dbms_stats.auto_sample_size

Method_opt = > 'for all columns size repeat'

Degree = > 7)

End

The statistics about the tables in Oracle are in the data dictionary, which can be queried by the following SQL:

SELECT Table_Name,Num_Rows,Blocks,Empty_Blocks,Avg_Space,Chain_Cnt,Avg_Row_Len,Sample_Size,Last_Analyzed

FROM Dba_Tables WHERE wner = 'SCOTT'

This is a summary of commands and toolkits.

1. For partitioned tables, it is recommended to use DBMS_STATS instead of the analyze statement.

A) can be carried out in parallel, for multiple users, multiple Table

B) you can get the data of the entire partition table and the data of a single partition.

C) Compute Statistics at different levels: single partition, sub-partition, full table, all partitions, but no clustering statistics are collected

D) Statistics can be poured out

E) users can collect statistics automatically

2. Shortcomings of DBMS_STATS

A) cannot Validate Structure

B) cannot collect CHAINED ROWS, cannot collect information about CLUSTER TABLE, these two still need to use the analyze statement.

C) DBMS_STATS does not Analyze the index by default, because the default Cascade is False, which needs to be manually specified as True

3. External Table,Analyze cannot be used, only DBMS_STATS can be used to collect information.

GATHER_TABLE_STATS

= =

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'))

Stattype varchar2 default 'DATA'

Force boolean default FALSE)

Parameter description:

Ownname: the owner of the table to be analyzed

Tabname: the name of the table to analyze.

Partname: the name of the partition, useful only for partitioned tables or partitioned indexes.

Estimate_percent: percentage of sampling rows, value range [0.000001100], null is all analysis, no sampling. Constant: DBMS_STATS.AUTO_SAMPLE_SIZE is the default value, and oracle determines the best sampling value.

Block_sapmple: whether to use block sampling instead of row sampling.

Method_opt: determines how histograms information is counted. The values of method_opt are as follows:

For all columns: count the histograms of all columns.

For all indexed columns: count the histograms of all indexed columns.

For all hidden columns: count the histograms of the column you can't see.

For columns SIZE | REPEAT | AUTO | SKEWONLY:

Statistics the value range of histograms.N of specified column [1254]; R

Histograms last counted by EPEAT

AUTO the size of N is determined by oracle.

SKEWONLY multiple end-points with the same value which is what we define by "there is skew in the data

Degree: sets the parallelism of collecting statistics. The default value is null.

Granularity:Granularity of statistics to collect, only pertinent if the table is partitioned.

Cascade: collects information about the index. The default is falase.

Stattab specifies the table to store statistics, statid if the statistics of multiple tables are stored in the same stattab to distinguish. Statown stores the owner of the statistics table. If the above three parameters are not specified, the statistics will be updated directly to the data dictionary.

No_invalidate: Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE.

Force: collect statistics even if the table is locked

Example:

Execute dbms_stats.gather_table_stats (ownname = > 'owner'

Tabname = > 'table_name'

Estimate_percent = > null

Method_opt = > 'for all indexed columns'

Cascade = > true)

GATHER_INDEX_STATS

= =

BEGIN

SYS.DBMS_STATS.GATHER_INDEX_STATS (OwnName = > 'ABC'

IndName = > 'IDX_FUNC_ABC'

Estimate_Percent = > 10

Degree = > SYS.DBMS_STATS.DEFAULT_DEGREE

No_Invalidate = > FALSE)

END

-

10g automatic collection of statistics

-

Starting from 10g, Oracle creates a scheduled task called GATHER_STATS_JOB by default after building the library, which is used to automatically collect statistics for CBO.

This automated task starts by default from 10:00 to 6:00 on weekdays and throughout the day on weekends.

Call DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC to collect statistics. The process first detects missing statistics and obsolete objects. Then prioritize and start the statistics.

You can query the operation of this JOB by:

SELECT * FROM Dba_Scheduler_Jobs WHERE Job_Name = 'GATHER_STATS_JOB'

Actually, Job, which also runs at 10:00, has an AUTO_SPACE_ADVISOR_JOB:

SELECT Job_Name, Last_Start_Date FROM Dba_Scheduler_Jobs

JOB_NAME LAST_START_DATE

AUTO_SPACE_ADVISOR_JOB 30-OCT-08 10.00.01.463000 PM + 08:00

GATHER_STATS_JOB 30-OCT-08 10.00.01.463000 PM + 08:00

However, this automation function has affected the normal operation of many systems, and 10:00 in the evening is not an idle time for most production systems.

Automatic analysis can lead to extremely serious latch competition, which in turn can lead to database Hang or Crash.

Therefore, it is recommended that you turn off this automatic statistics collection feature:

There are two ways to turn off and turn on the automatic collection function, as follows:

Method 1:

Exec dbms_scheduler.disable ('SYS.GATHER_STATS_JOB')

Exec dbms_scheduler.enable ('SYS.GATHER_STATS_JOB')

Method 2:

Alter system set "_ optimizer_autostats_job" = false scope=spfile

Alter system set "_ optimizer_autostats_job" = true scope=spfile

-

View statistics

-

Statistics on tables / indexes / columns

DBA_TABLES

DBA_OBJECT_TABLES

DBA_TAB_STATISTICS

DBA_TAB_COL_STATISTICS

DBA_TAB_HISTOGRAMS

DBA_INDEXES

DBA_IND_STATISTICS

DBA_CLUSTERS

DBA_TAB_PARTITIONS

DBA_TAB_SUBPARTITIONS

DBA_IND_PARTITIONS

DBA_IND_SUBPARTITIONS

DBA_PART_COL_STATISTICS

DBA_PART_HISTOGRAMS

DBA_SUBPART_COL_STATISTICS

DBA_SUBPART_HISTOGRAMS

-

Histogram statistics

-

The type of the histogram is stored on the HISTOGRAM column of the * TAB_COL_STATISTICS view.

Bde_last_analyzed.sql-Verifies CBO Statistics

Bde_last_analyzed.sql verifies the CBO statistics in the data dictionary for all tables, indexes, and partitions. It also validates the statistics on tables and indexes owned by 'SYS'.

The 5 generated reports bde_last_analyzed_xxx.html, present the total of tables and indexes analyzed per module and per date.

Script. Bde_last_analyzed.sql provided in this Note can be used on any 8i, 9i, 10g, 11g or higher database, including Oracle Apps 11i and R12 instances

If it is an ERP database, use APPS to connect, otherwise you can connect with any other SYS user

# sqlplus /

SQL > START bde_last_analyzed.sql

Review spool output files bde_last_analyzed_xxx.html files. Spool files get created on same directory from which this script. Is executed. On NT, files may get created under $ORACLE_HOME/bin.

If some modules have not been analyzed, or they have but not recently, these Apps objects must be analyzed using FND_STATS or coe_stats.sql if belonging to Oracle Apps. Otherwise use DBMS_STATS.

If Oracle Apps, use corresponding concurrent program with an estimate of 10%, or execute equivalent FND_STATS procedure from SQL*Plus:

SQL > exec fnd_stats.gather_schema_statistics ('APPLSYS'); Where' APPLSYS' is the module (schema) that requires new statistics.

If only a few tables require to have their statistics gathered, use the corresponding concurrent program to gather stats by table, or execute equivalent FND_STATS procedure from SQL*Plus:

SQL > exec fnd_stats.gather_table_stats ('MRP','MRP_FORECAST_DATES')

Where 'MRP' is the schema owner, and' MRP_FORECAST_DATES' is the table name. This syntax is only for non-partitioned Tables.

If any Partitioned Table requires its Global Stats being rebuilt, it is because at some point you gathered Stats on the table using a granularity of PARTITION. See second method below:

Begin

Dbms_stats.delete_table_stats (ownname = > 'APPLSYS', tabname = >' WF_ITEM_ACTIVITY_STATUSES')

Fnd_stats.gather_table_stats (ownname = > 'APPLSYS', tabname = >' WF_ITEM_ACTIVITY_STATUSES'

Granularity = > 'DEFAULT')

End

/

Once you fix your stats, be sure to ALWAYS use the granularity of DEFAULT for partitioned tables.

If you want to execute this bde_last_analyzed.sql script. Against only one schema, modify DEF SCHEMA code line.

-

Statistics instance of partition table

-

ORATEA ORACLE statistics play a very important role in the implementation of SQL, and ORACLE has different statistical information at all levels of the table, through which various statistical information of the table and columns can be described. Here are some common and common statistics through a composite partition table.

SQL >

Create table test

Partition by range (object_id)

Subpartition by hash (object_type) subpartitions 4

(partition p1 values less than (10000)

Partition p2 values less than (20000)

Partition p3 values less than (30000)

Partition p4 values less than (maxvalue))

As

Select * from dba_objects

The table has been created.

Sql >

BEGIN

Dbms_stats.gather_table_stats (ownname = > 'SCOTT'

Tabname = > 'TEST'

Estimate_percent = > 100

Block_sample = > FALSE

Method_opt = > 'FOR ALL COLUMNS SIZE 10'

Granularity = > 'ALL'

Cascade = > TRUE)

END

1, table-level statistics

SQL > select table_name,num_rows,blocks,empty_blocks,avg_space from user_tables where table_name = 'TEST'

TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE

TEST 50705 788 0 0

2, statistics of the columns on the table

SQL > select table_name,column_name,num_distinct,density from user_tab_columns where table_name = 'TEST'

TABLE_NAME COLUMN_NAME NUM_DISTINCT DENSITY

-

TEST OWNER 25. 365014295

TEST OBJECT_NAME 30275. 000039205

TEST SUBOBJECT_NAME 191. 015657993

TEST OBJECT_ID 50705. 000019722

TEST DATA_OBJECT_ID 4334. 000248075

TEST OBJECT_TYPE 42. 271207855

TEST CREATED 2305. 001608457

TEST LAST_DDL_TIME 2369. 001566737

TEST TIMESTAMP 2412. 001610251

TEST STATUS 2. 000009861

TEST TEMPORARY 2. 000009861

TEST GENERATED 2. 000009861

TEST SECONDARY 2. 000009861

13 rows selected.

3, histogram information of the columns on the table

SQL >

Select table_name,column_name,endpoint_number,endpoint_value

From user_tab_histograms

Where table_name = 'TEST'

And column_name = 'OBJECT_ID'

TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE

--

TEST OBJECT_ID 0 2

TEST OBJECT_ID 1 5160

TEST OBJECT_ID 2 10587

TEST OBJECT_ID 3 15658

TEST OBJECT_ID 4 20729

TEST OBJECT_ID 5 25800

TEST OBJECT_ID 6 30870

TEST OBJECT_ID 7 35940

TEST OBJECT_ID 8 41089

TEST OBJECT_ID 9 46821

TEST OBJECT_ID 10 53497

4, statistics of the partition

SQL >

Select partition_name,num_rows,blocks,empty_blocks,avg_space

From user_tab_partitions

Where table_name = 'TEST'

PARTITION_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE

--

P1 9581 140 0 0

P2 9973 164 0 0

P3 10000 158 00

P4 21151 326 0 0

5, the statistics listed in the partition

SQL > select column_name,num_distinct,density,num_nulls

From user_part_col_statistics

Where table_name = 'TEST'

And partition_name = 'P1'

COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS

--

OWNER 7. 000052187 0

OBJECT_NAME 7412. 000156925 0

SUBOBJECT_NAME 26. 47017301 9496

OBJECT_ID 9581. 000104373 0

DATA_OBJECT_ID 1765. 000664385 7780

OBJECT_TYPE 34. 18494854 0

CREATED 913. 001977449 0

LAST_DDL_TIME 994. 001882695 0

TIMESTAMP 982. 001928775 0

STATUS 2. 000052187 0

TEMPORARY 2. 000052187 0

GENERATED 2. 000052187 0

SECONDARY 1. 000052187 0

6. Histogram information listed in the partition

SQL > select column_name,bucket_number,endpoint_value

From user_part_histograms

Where table_name = 'TEST'

And partition_name = 'P1'

And column_name = 'OBJECT_ID'

COLUMN_NAME BUCKET_NUMBER ENDPOINT_VALUE

OBJECT_ID 0 2

OBJECT_ID 1 1005

OBJECT_ID 2 1963

OBJECT_ID 3 2921

OBJECT_ID 4 3888

OBJECT_ID 5 4859

OBJECT_ID 6 5941

OBJECT_ID 7 6899

OBJECT_ID 8 7885

OBJECT_ID 9 8864

OBJECT_ID 10 9999

7, statistics of subzones

SQL > select subpartition_name,num_rows,blocks,empty_blocks

From user_tab_subpartitions

Where table_name = 'TEST'

And partition_name = 'P1'

SUBPARTITION_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS

SYS_SUBP21 3597 50 0

SYS_SUBP22 3566 52 0

SYS_SUBP23 637 11 0

SYS_SUBP24 1781 27 0

8, statistics of the columns on the subpartition

SQL > select column_name,num_distinct,density

From user_subpart_col_statistics

Where table_name = 'TEST'

And subpartition_name = 'SYS_SUBP21'

COLUMN_NAME NUM_DISTINCT DENSITY

OWNER 6. 000139005

OBJECT_NAME 3595. 000278319

SUBOBJECT_NAME 4. 014285714

OBJECT_ID 3597. 000278009

DATA_OBJECT_ID 155. 006451613

OBJECT_TYPE 8. 000139005

CREATED 751. 002392334

LAST_DDL_TIME 784. 002302524

TIMESTAMP 768. 00235539

STATUS 1. 000139005

TEMPORARY 2. 000139005

GENERATED 2. 000139005

SECONDARY 1. 000139005

9, histogram information of the columns on the subpartition

SQL > select column_name,bucket_number,endpoint_value

From user_subpart_histograms

Where table_name = 'TEST'

And subpartition_name = 'SYS_SUBP21'

And column_name = 'OBJECT_ID'

COLUMN_NAME BUCKET_NUMBER ENDPOINT_VALUE

OBJECT_ID 0 208

OBJECT_ID 1 1525

OBJECT_ID 2 2244

OBJECT_ID 3 2892

OBJECT_ID 4 3252

OBJECT_ID 5 4047

OBJECT_ID 6 5238

OBJECT_ID 7 6531

OBJECT_ID 8 7661

OBJECT_ID 9 8474

OBJECT_ID 10 9998

After analyzing this composite partition, we produced the above nine different levels of statistics. CBO needs so much statistics to have an efficient execution plan.

At this point, I believe you have a deeper understanding of "how to collect oracle statistics". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.