Introduction It’s common to have multi-terabyte partitioned tables in an Oracle. Statistics Maintenance for Partitioned Tables. To update statistics. Home » Articles » 11g » Here Statistics Collection Enhancements in Oracle Database 11g Release 1. Pending Statistics; Extended Statistics. Multi-Column (Column. Analyzing only partition of table. (insert/update) the LATEST partition. You can enable it to let Oracle collect statistics on big tables when the change is.

1. Update Oracle Table

14.1 Understanding Statistics Optimizer statistics are a collection of data that describe more details about the database and the objects in the database. These statistics are used by the query optimizer to choose the best execution plan for each SQL statement. Optimizer statistics include the following:. Table statistics. Number of rows.

Number of blocks. Average row length. Column statistics. Number of distinct values (NDV) in column.

Number of nulls in column. Data distribution (histogram). Index statistics. Number of leaf blocks. Levels. Clustering factor.

System statistics. I/O performance and utilization. CPU performance and utilization.

Note: The statistics mentioned in this section are optimizer statistics, which are created for the purposes of query optimization and are stored in the data dictionary. These statistics should not be confused with performance statistics visible through V$ views. The optimizer statistics are stored in the data dictionary. They can be viewed using data dictionary views. Because the objects in a database can be constantly changing, statistics must be regularly updated so that they accurately describe these database objects. Statistics are maintained automatically by Oracle or you can maintain the optimizer statistics manually using the DBMSSTATS package. For a description of the automatic and manual processes, see.

The DBMSSTATS package also provides procedures for managing statistics. You can save and restore copies of statistics. You can export statistics from one system and import those statistics into another system.

For example, you could export statistics from a production system to a test system. In addition, you can lock statistics to prevent those statistics from changing. The lock methods are described in.

14.2 Automatic Statistics Gathering The recommended approach to gathering statistics is to allow Oracle to automatically gather the statistics. Oracle gathers statistics on all database objects automatically and maintains those statistics in a regularly-scheduled maintenance job. Automated statistics collection eliminates many of the manual tasks associated with managing the query optimizer, and significantly reduces the chances of getting poor execution plans because of missing or stale statistics. 14.2.1 GATHERSTATSJOB Optimizer statistics are automatically gathered with the job GATHERSTATSJOB. This job gathers statistics on all objects in the database which have:. Missing statistics. Stale statistics This job is created automatically at database creation time and is managed by the Scheduler.

The Scheduler runs this job when the maintenance window is opened. By default, the maintenance window opens every night from 10 P.M. And all day on weekends. The stoponwindowclose attribute controls whether the GATHERSTATSJOB continues when the maintenance window closes. The default setting for the stoponwindowclose attribute is TRUE, causing Scheduler to terminate GATHERSTATSJOB when the maintenance window closes.

The remaining objects are then processed in the next maintenance window. See Also: for information on the Scheduler and maintenance windows tasks The GATHERSTATSJOB job gathers optimizer statistics by calling the DBMSSTATS.GATHERDATABASESTATSJOBPROC procedure. The GATHERDATABASESTATSJOBPROC procedure collects statistics on database objects when the object has no previously gathered statistics or the existing statistics are stale because the underlying object has been modified significantly (more than 10% of the rows).The DBMSSTATS.GATHERDATABASESTATSJOBPROC is an internal procedure, but its operates in a very similar fashion to the DBMSSTATS.GATHERDATABASESTATS procedure using the GATHER AUTO option. The primary difference is that the DBMSSTATS.GATHERDATABASESTATSJOBPROC procedure prioritizes the database objects that require statistics, so that those objects which most need updated statistics are processed first. This ensures that the most-needed statistics are gathered before the maintenance window closes. 14.2.2 Enabling Automatic Statistics Gathering Automatic statistics gathering is enabled by default when a database is created, or when a database is upgraded from an earlier database release. You can verify that the job exists by viewing the DBASCHEDULERJOBS view: SELECT.

FROM DBASCHEDULERJOBS WHERE JOBNAME = 'GATHERSTATSJOB'; In situations when you want to disable automatic statistics gathering, the most direct approach is to disable the GATHERSTATSJOB as follows: BEGIN DBMSSCHEDULER.DISABLE('GATHERSTATSJOB'); END; / Automatic statistics gathering relies on the modification monitoring feature, described in. If this feature is disabled, then the automatic statistics gathering job is not able to detect stale statistics.

This feature is enabled when the STATISTICSLEVEL parameter is set to TYPICAL or ALL. TYPICAL is the default value. 14.2.3.1 When to Use Manual Statistics Automatic statistics gathering should be sufficient for most database objects which are being modified at a moderate speed. However, there are cases where automatic statistics gathering may not be adequate.

Because the automatic statistics gathering runs during an overnight batch window, the statistics on tables which are significantly modified during the day may become stale. There are typically two types of such objects:. Volatile tables that are being deleted or truncated and rebuilt during the course of the day. Objects which are the target of large bulk loads which add 10% or more to the object's total size. For highly volatile tables, there are two approaches:.

The statistics on these tables can be set to NULL. When Oracle encounters a table with no statistics, Oracle dynamically gathers the necessary statistics as part of query optimization.

This dynamic sampling feature is controlled by the OPTIMIZERDYNAMICSAMPLING parameter, and this parameter should be set to a value of 2 or higher. The default value is 2.

The statistics can set to NULL by deleting and then locking the statistics: BEGIN DBMSSTATS.DELETETABLESTATS('OE','ORDERS'); DBMSSTATS.LOCKTABLESTATS('OE','ORDERS'); END; / See for information about the sampling levels that can be set. The statistics on these tables can be set to values that represent the typical state of the table.

You should gather statistics on the table when the tables has a representative number of rows, and then lock the statistics. This is more effective than the GATHERSTATSJOB, because any statistics generated on the table during the overnight batch window may not be the most appropriate statistics for the daytime workload. For tables which are being bulk-loaded, the statistics-gathering procedures should be run on those tables immediately following the load process, preferably as part of the same script or job that is running the bulk load. For external tables, statistics are not collected during GATHERSCHEMASTATS, GATHERDATABASESTATS, and automatic statistics gathering processing. However, you can collect statistics on an individual external table using GATHERTABLESTATS. Sampling on external tables is not supported so the ESTIMATEPERCENT option should be explicitly set to NULL.

Because data manipulation is not allowed against external tables, it is sufficient to analyze external tables when the corresponding file changes. If the monitoring feature is disabled by setting STATISTICSLEVEL to BASIC, automatic statistics gathering cannot detect stale statistics. In this case statistics need to be manually gathered. See for information on the automatic monitoring facility. Another area in which statistics need to be manually gathered are the system statistics. These statistics are not automatically gathered.

See for more information. Statistics on fixed objects, such as the dynamic performance tables, need to be manually collected using GATHERFIXEDOBJECTSSTATS procedure. Fixed objects record current database activity; statistics gathering should be done when database has representative activity. Note: Do not use the COMPUTE and ESTIMATE clauses of ANALYZE statement to collect optimizer statistics. These clauses are supported solely for backward compatibility and may be removed in a future release.

The DBMSSTATS package collects a broader, more accurate set of statistics, and gathers statistics more efficiently. You may continue to use ANALYZE statement to for other purposes not related to optimizer statistics collection:. To use the VALIDATE or LIST CHAINED ROWS clauses. To collect information on free list blocks The DBMSSTATS package can gather statistics on table and indexes, and well as individual columns and partitions of tables.

It does not gather cluster statistics; however, you can use DBMSSTATS to gather statistics on the individual tables instead of the whole cluster. When you generate statistics for a table, column, or index, if the data dictionary already contains statistics for the object, then Oracle updates the existing statistics. The older statistics are saved and can be restored later if necessary. When gathering statistics on system schemas, you can use the procedure DBMSSTATS.GATHERDICTIONARYSTATS. This procedure gather statistics for all system schemas, including SYS and SYSTEM, and other optional schemas, such as CTXSYS and DRSYS.

When statistics are updated for a database object, Oracle invalidates any currently parsed SQL statements that access the object. The next time such a statement executes, the statement is re-parsed and the optimizer automatically chooses a new execution plan based on the new statistics. Distributed statements accessing objects with new statistics on remote databases are not invalidated. The new statistics take effect the next time the SQL statement is parsed. Lists the procedures in the DBMSSTATS package for gathering statistics on database objects. 14.3.1.1 Statistics Gathering Using Sampling The statistics-gathering operations can utilize sampling to estimate statistics.

Sampling is an important technique for gathering statistics. Gathering statistics without sampling requires full table scans and sorts of entire tables. Sampling minimizes the resources necessary to gather statistics.

Sampling is specified using the ESTIMATEPERCENT argument to the DBMSSTATS procedures. While the sampling percentage can be set to any value, Oracle Corporation recommends setting the ESTIMATEPERCENT parameter of the DBMSSTATS gathering procedures to DBMSSTATS. AUTOSAMPLESIZE to maximize performance gains while achieving necessary statistical accuracy. AUTOSAMPLESIZE lets Oracle determine the best sample size necessary for good statistics, based on the statistical property of the object. Because each type of statistics has different requirements, the size of the actual sample taken may not be the same across the table, columns, or indexes. For example, to collect table and column statistics for all tables in the OE schema with auto-sampling, you could use: EXECUTE DBMSSTATS.GATHERSCHEMASTATS('OE',DBMSSTATS.AUTOSAMPLESIZE); When the ESTIMATEPERCENT parameter is manually specified, the DBMSSTATS gathering procedures may automatically increase the sampling percentage if the specified percentage did not produce a large enough sample.

This ensures the stability of the estimated values by reducing fluctuations. 14.3.1.2 Parallel Statistics Gathering The statistics-gathering operations can run either serially or in parallel. The degree of parallelism can be specified with the DEGREE argument to the DBMSSTATS gathering procedures.

Parallel statistics gathering can be used in conjunction with sampling. Oracle Corporation recommends setting the DEGREE parameter to DBMSSTATS.AUTODEGREE. This setting allows Oracle to choose an appropriate degree of parallelism based on the size of the object and the settings for the parallel-related init.ora parameters. Note that certain types of index statistics are not gathered in parallel, including cluster indexes, domain indexes, and bitmap join indexes.

14.3.1.3 Statistics on Partitioned Objects For partitioned tables and indexes, DBMSSTATS can gather separate statistics for each partition, as well as global statistics for the entire table or index. Similarly, for composite partitioning, DBMSSTATS can gather separate statistics for subpartitions, partitions, and the entire table or index.The type of partitioning statistics to be gathered is specified in the GRANULARITY argument to the DBMSSTATS gathering procedures. Depending on the SQL statement being optimized, the optimizer can choose to use either the partition (or subpartition) statistics or the global statistics.

Both types of statistics are important for most applications, and Oracle Corporation recommends setting the GRANULARITY parameter to AUTO to gather both types of partition statistics. 14.3.1.4 Column Statistics and Histograms When gathering statistics on a table, DBMSSTATS gathers information about the data distribution of the columns within the table. The most basic information about the data distribution is the maximum value and minimum value of the column. However, this level of statistics may be insufficient for the optimizer's needs if the data within the column is skewed. For skewed data distributions, histograms can also be created as part of the column statistics to describe the data distribution of a given column.

Histograms are described in more details in. Histograms are specified using the METHODOPT argument of the DBMSSTATS gathering procedures. Oracle Corporation recommends setting the METHODOPT to FOR ALL COLUMNS SIZE AUTO. With this setting, Oracle automatically determines which columns require histograms and the number of buckets (size) of each histogram.

You can also manually specify which columns should have histograms and the size of each histogram. 14.3.1.5 Determining Stale Statistics Statistics must be regularly gathered on database objects as those database objects are modified over time.

In order to determine whether or not a given database object needs new database statistics, Oracle provides a table monitoring facility. This monitoring is enabled by default when STATISTICSLEVEL is set to TYPICAL or ALL. Monitoring tracks the approximate number of INSERTs, UPDATEs, and DELETEs for that table, as well as whether the table has been truncated, since the last time statistics were gathered. The information about changes of tables can be viewed in the USERTABMODIFICATIONS view. Following a data-modification, there may be a few minutes delay while Oracle propagates the information to this view. Use the DBMSSTATS.FLUSHDATABASEMONITORINGINFO procedure to immediately reflect the outstanding monitored information kept in the memory. The GATHERDATABASESTATS or GATHERSCHEMASTATS procedures gather new statistics for tables with stale statistics when the OPTIONS parameter is set to GATHER STALE or GATHER AUTO.

If a monitored table has been modified more than 10%, then these statistics are considered stale and gathered again. 14.3.1.6 User-defined Statistics You can create user-defined optimizer statistics to support user-defined indexes and functions. When you associate a statistics type with a column or domain index, Oracle calls the statistics collection method in the statistics type whenever statistics are gathered for database objects. You should gather new column statistics on a table after creating a function-based index, to allow Oracle to collect column statistics equivalent information for the expression.

This is done by calling the statistics-gathering procedure with the METHODOPT argument set to FOR ALL HIDDEN COLUMNS. 14.3.2 When to Gather Statistics When gathering statistics manually, you not only need to determine how to gather statistics, but also when and how often to gather new statistics. For an application in which tables are being incrementally modified, you may only need to gather new statistics every week or every month.

The simplest way to gather statistics in these environment is to use a script or job scheduling tool to regularly run the GATHERSCHEMASTATS and GATHERDATABASESTATS procedures. The frequency of collection intervals should balance the task of providing accurate statistics for the optimizer against the processing overhead incurred by the statistics collection process. For tables which are being substantially modified in batch operations, such as with bulk loads, statistics should be gathered on those tables as part of the batch operation. The DBMSSTATS procedure should be called as soon as the load operation completes.

For partitioned tables, there are often cases in which only a single partition is modified. In those cases, statistics can be gathered only on those partitions rather than gathering statistics for the entire table. However, gathering global statistics for the partitioned table may still be necessary. 14.4 System Statistics System statistics describe the system's hardware characteristics, such as I/O and CPU performance and utilization, to the query optimizer. When choosing an execution plan, the optimizer estimates the I/O and CPU resources required for each query. System statistics enable the query optimizer to more accurately estimate I/O and CPU costs, enabling the query optimizer to choose a better execution plan.

When Oracle gathers system statistics, it analyzes system activity in a specified time period (workload statistics) or simulates a workload (noworkload statistics). The statistics are collected using the DBMSSTATS.GATHERSYSTEMSTATS procedure.

Oracle Corporation highly recommends that you gather system statistics. Table 14-2 Optimizer System Statistics in the DBMSSTAT Package Parameter Name Description Initialization Options for Gathering or Setting Statistics Unit cpuspeedNW Represents noworkload CPU speed. CPU speed is the average number of CPU cycles in each second. At system startup Set gatheringmode = NOWORKLOAD or set statistics manually. Ioseektim I/O seek time equals seek time + latency time + operating system overhead time. At system startup 10 (default) Set gatheringmode = NOWORKLOAD or set statistics manually. Ms iotfrspeed I/O transfer speed is the rate at which an Oracle database can read data in the single read request.

At system startup 4096 (default) Set gatheringmode = NOWORKLOAD or set statistics manually. Bytes/ms cpuspeed Represents workload CPU speed. CPU speed is the average number of CPU cycles in each second. None Set gatheringmode = NOWORKLOAD, INTERVAL, or START STOP, or set statistics manually. Maxthr Maximum I/O throughput is the maximum throughput that the I/O subsystem can deliver. None Set gatheringmode = NOWORKLOAD, INTERVAL, or START STOP, or set statistics manually.

Slavethr Slave I/O throughput is the average parallel slave I/O throughput. None Set gatheringmode = INTERVAL or START STOP, or set statistics manually. Sreadtim Single block read time is the average time to read a single block randomly. None Set gatheringmode = INTERVAL or START STOP, or set statistics manually. Ms mreadtim Multiblock read is the average time to read a multiblock sequentially. None Set gatheringmode = INTERVAL or START STOP, or set statistics manually.

Ms mbrc Multiblock count is the average multiblock read count sequentially. None Set gatheringmode = INTERVAL or START STOP, or set statistics manually. Blocks Unlike table, index, or column statistics, Oracle does not invalidate already parsed SQL statements when system statistics get updated. All new SQL statements are parsed using new statistics.

Oracle offers two options for gathering system statistics:. These options better facilitate the gathering process to the physical database and workload: when workload system statistics are gathered, noworkload system statistics will be ignored. Noworkload system statistics are initialized to default values at the first database startup. 14.4.1 Workload Statistics Workload statistics, introduced in Oracle 9 i, gather single and multiblock read times, mbrc, CPU speed ( cpuspeed), maximum system throughput, and average slave throughput. The sreadtim, mreadtim, and mbrc are computed by comparing the number of physical sequential and random reads between two points in time from the beginning to the end of a workload. These values are implemented through counters that change when the buffer cache completes synchronous read requests. Since the counters are in the buffer cache, they include not only I/O delays, but also waits related to latch contention and task switching.

Workload statistics thus depend on the activity the system had during the workload window. If system is I/O bound—both latch contention and I/O throughput—it will be reflected in the statistics and will therefore promote a less I/O intensive plan after the statistics are used. Furthermore, workload statistics gathering does not generate additional overhead. In release 9.2, maximum I/O throughput and average slave throughput were added to set a lower limit for a full table scan (FTS). 14.4.1.1 Gathering Workload Statistics To gather workload statistics, either:.

Run the dbmsstats.gathersystemstats('start') procedure at the beginning of the workload window, then the dbmsstats.gathersystemstats('stop') procedure at the end of the workload window. Run dbmsstats.gathersystemstats('interval', interval=N) where N is the number of minutes when statistics gathering will be stopped automatically. To delete system statistics, run dbmsstats.deletesystemstats. Workload statistics will be deleted and reset to the default noworkload statistics. 14.4.1.2 Multiblock Read Count In release 10.2, the optimizer uses the value of mbrc when performing full table scans (FTS). The value of dbfilemultiblockreadcount is set to the maximum allowed by the operating system by default.

However, the optimizer uses mbrc=8 for costing. The 'real' mbrc is actually somewhere in between since serial multiblock read requests are processed by the buffer cache and split in two or more requests if some blocks are already pinned in the buffer cache, or when the segment size is smaller than the read size. The mbrc value gathered as part of workload statistics is thus useful for FTS estimation. During the gathering process of workload statistics, it is possible that mbrc and mreadtim will not be gathered if no table scans are performed during serial workloads, as is often the case with OLTP systems. On the other hand, FTS occur frequently on DSS systems but may run parallel and bypass the buffer cache.

In such cases, sreadtim will still be gathered since index lookup are performed using the buffer cache. If Oracle cannot gather or validate gathered mbrc or mreadtim, but has gathered sreadtim and cpuspeed, then only sreadtim and cpuspeed will be used for costing. FTS cost will be computed using analytical algorithm implemented in previous releases. Another alternative to computing mbrc and mreadtim is to force FTS in serial mode to allow the optimizer to gather the data. 14.4.2 Noworkload Statistics Noworkload statistics consist of I/O transfer speed, I/O seek time, and CPU speed ( cpuspeednw). The major difference between workload statistics and noworkload statistics lies in the gathering method. Noworkload statistics gather data by submitting random reads against all data files, while workload statistics uses counters updated when database activity occurs.

Isseektim represents the time it takes to position the disk head to read data. Its value usually varies from 5 ms to 15 ms, depending on disk rotation speed and the disk or RAID specification. The I/O transfer speed represents the speed at which one operating system process can read data from the I/O subsystem. Its value varies greatly, from a few MBs per second to hundreds of MBs per second. Oracle uses relatively conservative default settings for I/O transfer speed. In Oracle 10 g, Oracle uses noworkload statistics and the CPU cost model by default.

The values of noworkload statistics are initialized to defaults at the first instance startup: ioseektim = 10ms iotrfspeed = 4096 bytes/ms cpuspeednw = gathered value, varies based on system If workload statistics are gathered, noworkload statistics will be ignored and Oracle will use workload statistics instead. 14.4.2.1 Gathering Noworkload Statistics To gather noworkload statistics, run dbmsstats.gathersystemstats with no arguments. There will be an overhead on the I/O system during the gathering process of noworkload statistics. The gathering process may take from a few seconds to several minutes, depending on I/O performance and database size.

The information is analyzed and verified for consistency. In some cases, the value of noworkload statistics may remain its default value. In such cases, repeat the statistics gathering process or set the value manually to values that the I/O system has according to its specifications by using the dbmsstats.setsystemstats procedure. 14.5.1 Restoring Previous Versions of Statistics Whenever statistics in dictionary are modified, old versions of statistics are saved automatically for future restoring.

Statistics can be restored using RESTORE procedures of DBMSSTATS package. These procedures use a time stamp as an argument and restore statistics as of that time stamp. This is useful in case newly collected statistics leads to some sub-optimal execution plans and the administrator wants to revert to the previous set of statistics. There are dictionary views that display the time of statistics modifications.

These views are useful in determining the time stamp to be used for statistics restoration. Catalog view DBAOPTSTATOPERATIONS contain history of statistics operations performed at schema and database level using DBMSSTATS. The views.TABSTATSHISTORY views ( ALL, DBA, or USER) contain a history of table statistics modifications. The old statistics are purged automatically at regular intervals based on the statistics history retention setting and the time of the recent analysis of the system. Retention is configurable using the ALTERSTATSHISTORYRETENTION procedure of DBMSSTATS.

The default value is 31 days, which means that you would be able to restore the optimizer statistics to any time in last 31 days. Automatic purging is enabled when STATISTICSLEVEL parameter is set to TYPICAL or ALL. If automatic purging is disabled, the old versions of statistics need to be purged manually using the PURGESTATS procedure. The other DBMSSTATS procedures related to restoring and purging statistics include:. PURGESTATS: This procedure can be used to manually purge old versions beyond a time stamp. GETSTATSHISTORYRENTENTION: This function can be used to get the current statistics history retention value. GETSTATSHISTORYAVAILABILITY: This function gets the oldest time stamp where statistics history is available.

Users cannot restore statistics to a time stamp older than the oldest time stamp. When restoring previous versions of statistics, the following limitations apply:. RESTORE procedures cannot restore user-defined statistics. Old versions of statistics are not stored when the ANALYZE command has been used for collecting statistics.

Note: Exporting and importing statistics is a distinct concept from the EXP and IMP utilities of the database. The DBMSSTATS export and import packages do utilize IMP and EXP dump files. Before exporting statistics, you first need to create a table for holding the statistics. This statistics table is created using the procedure DBMSSTATS.CREATESTATTABLE. After this table is created, then you can export statistics from the data dictionary into your statistics table using the DBMSSTATS.EXPORT.STATS procedures. The statistics can then be imported using the DBMSSTATS.IMPORT.STATS procedures. Note that the optimizer does not use statistics stored in a user-owned table.

The only statistics used by the optimizer are the statistics stored in the data dictionary. In order to have the optimizer use the statistics in a user-owned tables, you must import those statistics into the data dictionary using the statistics import procedures. In order to move statistics from one database to another, you must first export the statistics on the first database, then copy the statistics table to the second database, using the EXP and IMP utilities or other mechanisms, and finally import the statistics into the second database. 14.5.3 Restoring Statistics Versus Importing or Exporting Statistics The functionality for restoring statistics is similar in some respects to the functionality of importing and exporting statistics. In general, you should use the restore capability when:. You want to recover older versions of the statistics.

For example, to restore the optimizer behavior to an earlier date. You want the database to manage the retention and purging of statistics histories. You should use EXPORT/IMPORT.STATS procedures when:. You want to experiment with multiple sets of statistics and change the values back and forth. You want to move the statistics from one database to another database.

For example, moving statistics from a production system to a test system. You want to preserve a known set of statistics for a longer period of time than the desired retention date for restoring statistics. 14.5.4 Locking Statistics for a Table or Schema Statistics for a table or schema can be locked. Once statistics are locked, no modifications can be made to those statistics until the statistics have been unlocked. These locking procedures are useful in a static environment in which you want to guarantee that the statistics never change. The DBMSSTATS package provides two procedures for locking and two procedures for unlocking statistics:.

LOCKSCHEMASTATS. LOCKTABLESTATS.

UNLOCKSCHEMASTATS. UNLOCKTABLESTATS. 14.5.6 Estimating Statistics with Dynamic Sampling The purpose of dynamic sampling is to improve server performance by determining more accurate estimates for predicate selectivity and statistics for tables and indexes. The statistics for tables and indexes include table block counts, applicable index block counts, table cardinalities, and relevant join column statistics. These more accurate estimates allow the optimizer to produce better performing plans. You can use dynamic sampling to:. Estimate single-table predicate selectivities when collected statistics cannot be used or are likely to lead to significant errors in estimation.

Estimate statistics for tables and relevant indexes without statistics. Estimate statistics for tables and relevant indexes whose statistics are too out of date to trust. This dynamic sampling feature is controlled by the OPTIMIZERDYNAMICSAMPLING parameter. For dynamic sampling to automatically gather the necessary statistics, this parameter should be set to a value of 2 or higher. The default value is 2. See for information about the sampling levels that can be set. 14.5.6.1 How Dynamic Sampling Works The primary performance attribute is compile time.

Oracle determines at compile time whether a query would benefit from dynamic sampling. If so, a recursive SQL statement is issued to scan a small random sample of the table's blocks, and to apply the relevant single table predicates to estimate predicate selectivities. The sample cardinality can also be used, in some cases, to estimate table cardinality. Any relevant column and index statistics are also collected.

Depending on the value of the OPTIMIZERDYNAMICSAMPLING initialization parameter, a certain number of blocks are read by the dynamic sampling query. 14.5.6.2 When to Use Dynamic Sampling For a query that normally completes quickly (in less than a few seconds), you will not want to incur the cost of dynamic sampling. However, dynamic sampling can be beneficial under any of the following conditions:. A better plan can be found using dynamic sampling. The sampling time is a small fraction of total execution time for the query. The query will be executed many times. Dynamic sampling can be applied to a subset of a single table's predicates and combined with standard selectivity estimates of predicates for which dynamic sampling is not done.

14.5.6.3 How to Use Dynamic Sampling to Improve Performance You control dynamic sampling with the OPTIMIZERDYNAMICSAMPLING parameter, which can be set to a value from 0 to 10. The default is 2. A value of 0 means dynamic sampling will not be done. Increasing the value of the parameter results in more aggressive application of dynamic sampling, in terms of both the type of tables sampled (analyzed or unanalyzed) and the amount of I/O spent on sampling. Dynamic sampling is repeatable if no rows have been inserted, deleted, or updated in the table being sampled. The parameter OPTIMIZERFEATURESENABLE turns off dynamic sampling if set to a version prior to 9.2.0.

14.5.6.4 Dynamic Sampling Levels The sampling levels are as follows if the dynamic sampling level used is from a cursor hint or from the OPTIMIZERDYNAMICSAMPLING initialization parameter:. Level 0: Do not use dynamic sampling. Level 1: Sample all tables that have not been analyzed if the following criteria are met: (1) there is at least 1 unanalyzed table in the query; (2) this unanalyzed table is joined to another table or appears in a subquery or non-mergeable view; (3) this unanalyzed table has no indexes; (4) this unanalyzed table has more blocks than the number of blocks that would be used for dynamic sampling of this table. The number of blocks sampled is the default number of dynamic sampling blocks (32). Level 2: Apply dynamic sampling to all unanalyzed tables.

The number of blocks sampled is two times the default number of dynamic sampling blocks. Level 3: Apply dynamic sampling to all tables that meet Level 2 criteria, plus all tables for which standard selectivity estimation used a guess for some predicate that is a potential dynamic sampling predicate. The number of blocks sampled is the default number of dynamic sampling blocks. For unanalyzed tables, the number of blocks sampled is two times the default number of dynamic sampling blocks.

Level 4: Apply dynamic sampling to all tables that meet Level 3 criteria, plus all tables that have single-table predicates that reference 2 or more columns. The number of blocks sampled is the default number of dynamic sampling blocks. For unanalyzed tables, the number of blocks sampled is two times the default number of dynamic sampling blocks. Levels 5, 6, 7, 8, and 9: Apply dynamic sampling to all tables that meet the previous level criteria using 2, 4, 8, 32, or 128 times the default number of dynamic sampling blocks respectively. Level 10: Apply dynamic sampling to all tables that meet the Level 9 criteria using all blocks in the table. The sampling levels are as follows if the dynamic sampling level for a table is set using the optimizer hint:. Level 0: Do not use dynamic sampling.

Level 1: The number of blocks sampled is the default number of dynamic sampling blocks (32). Levels 2, 3, 4, 5, 6, 7, 8, and 9: The number of blocks sampled is 2, 4, 8, 16, 32, 64, 128, or 256 times the default number of dynamic sampling blocks respectively. Level 10: Read all blocks in the table. 14.6.1 Statistics on Tables, Indexes and Columns Statistics on tables, indexes, and columns are stored in the data dictionary. To view statistics in the data dictionary, query the appropriate data dictionary view ( USER, ALL, or DBA).

These DBA. views include the following:. DBATABLES.

DBAOBJECTTABLES. DBATABSTATISTICS. DBATABCOLSTATISTICS.

DBATABHISTOGRAMS. DBAINDEXES. DBAINDSTATISTICS.

DBACLUSTERS. DBATABPARTITIONS.

DBATABSUBPARTITIONS. DBAINDPARTITIONS. DBAINDSUBPARTITIONS.

DBAPARTCOLSTATISTICS. DBAPARTHISTOGRAMS.

DBASUBPARTCOLSTATISTICS. DBASUBPARTHISTOGRAMS.

14.6.2 Viewing Histograms Column statistics may be stored as histograms. These histograms provide accurate estimates of the distribution of column data. Histograms provide improved selectivity estimates in the presence of data skew, resulting in optimal execution plans with nonuniform data distributions.

Update Oracle Table

Oracle uses two types of histograms for column statistics: height-balanced histograms and frequency histograms. The type of histogram is stored in the HISTOGRAM column of the.TABCOLSTATISTICS views ( USER and DBA). This column can have values of HEIGHT BALANCED, FREQUENCY, or NONE.