糖尿病康复 > 使用DBMS_STATS来收集统计信息

使用DBMS_STATS来收集统计信息

时间：2023-07-23 02:00:44

相关推荐

使用DBMS_STATS来收集统计信息

数据库|mysql教程

使用,DBMS,STATS,收集,统计,信息,overvie

数据库-mysql教程

会员积分兑换系统源码下载,vscode一键抽取函数,ubuntu 终端教程,查看tomcat访问端口,金刚爬虫图片,php文件加密工具,迎泽区seo优化多少天lzw

overview Oracles cost-based optimizer (COB) uses statistics to calculate the selectivity (the fraction of rows in a table that the SQL statements predicate chooses) of predicates and to estimate the cost of each execution plan. The COB wil

杰奇2.2系统源码,ubuntu不进入桌面,tomcat无法上传数据库,音乐爬虫api接口,php环境为什么这么复杂,长尾seo广告lzw

小蚂蚁门户7.3源码,codeimg vscode,ubuntu ntp,tomcat配置ssl,sqlite 查询时间,摄影网页设计的概述,laravel 数据库优化,网站选服务器文件,页面懒加载插件,前端框架,爬虫新浪微博,php 爬虫,seo关键词价格,消息通知springboot,html 非常用标签,网站日期选择器,匿名留言网页源码,织梦网页模板,公司网站后台,html5手机页面标题栏,樱桃企业网站管理系统,占卜程序源代码lzw

overview

Oracle’s cost-based optimizer (COB) uses statistics to calculate the selectivity (the fraction of rows in a table that the SQL statement’s predicate chooses) of predicates and to estimate the “cost” of each execution plan. The COB will use the selectivity of a predicate to estimate the cost of a particular access method and to determin the optimal join order

ORACLE COB使用统计信息来计算查询谓词的选择性，并借此评估执行计划的成本。然后COB会使用谓词的选择性来评估特定的访问路径的成本并确定最优的连接顺序。

statistics are used to quantify the data distribution and storage characteristics of tables, columns, indexes and partitions. The COB uses these statistics to estimate how much I/O and memory are required to execute a SQL statement using a particular execution plan. Statistics are stored in the data dictionary, and they can be exported from one database and imported into another. Situations in where you would want to perform this, might be to transfer production statistics to a test system to simulate the real environment, even though the test system may only have small samples of the data。

统计信息被用来量化表、列、索引和分区的数据分布特征和存储特征。COB使用统计信息来评估SQL语句采用某特定执行计划时的内存和输入输出量。统计信息存储在数据字典视图中，它们可以被导出和导入，例如，我们可以将生产环境的统计信息导入到测试环境中以便模拟真环境，即使测试环境具有较小的数据采样。

In order to give the Oracle cost-based optimizer the most up-to-date information about schema objects (and the best chance for choosing a good execution plan) all application tables and indexes to be accessed must be analyzed. New statistics should be gathered on schema objects that are out of date. After loading or deleting large amounts of data would obviously change the number of rows. Other changes like updating a large amount of rows would not effect the number of rows, but may effect the average row length.

为了给ORACLE COB提供最新的关于模式对象的信息（从而可以选择最优执行计划），所有被访问的应用表和索引都需要被分析。如果对象的统计信息已经过时，我们需要更新统计信息，例如，在进行大量的装载或者删除数据后，或者对表数据进行了大量的更新操作。

Statistics can be generated with the ANALYZE statement or with the package DBMS_STATS (introduced in Oracle8i). The DBMS_STATS package is great for DBA’s in managing database statistics only for use by the COB. The package itself allows the DBA to create, modify, view and delete statistics from a standard, well-defined set of package procedures. The statistics can be gathered on tables, indexes, columns, partitions and schemas, but note that it does not generate statistics for clusters.

统计信息可以通过ANALYZE命令或者DBMS_STATS包来收集。在COB模式下，DBMS_STATS包是DBA管理统计信息的有力工具。DBMS_STATS包允许管理员以调用过程的方式创建，编辑，查看和删除统计信息。它可以收集表、索引、列、分区和模式的统计信息，但是它不可以生成cluster的统计信息；

DBMS_STATS provides a mechanism for you to view and modify optimizer statistics gathered for database objects.The statistics can reside in two different locations:

The dictionary.A table created in the user’s schema for this purpose

dbms_stats包为我们提供了查看和编辑统计信息的机制。统计信息可以存储在2个不同的位置：数据字典视图和用户自定义的表中。

Only statistics stored in the dictionary itself have an impact on the cost-based optimizer.

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. Oracle also invalidates any currently parsed SQL statements that access the object.

The next time such a statement executes, the optimizer automatically chooses a new execution plan based on the new statistics. Distributed statements issued on remote databases that access the analyzed objects use the new statistics the next time Oracle parses them.

When you associate a statistics type with a column or domain index, Oracle calls the statistics collection method in the statistics type if you analyze the column or domain index.

只有存储在字典视图中的统计信息，才会被优化器使用。

当我们收集表、列或者索引的统计信息时，如果数据字典中已经包含有统计信息，oracle会将已有信息进行更新。同时oracle也会使当前解析的与更新对象相关的sql语句无效，以便可以使用信息的统计信息。在远程主机中执行的分布式语句，则在oracle下次解析时才会使用心得统计信息。

当我们将某列或者域索引与某一统计类别管理时，oracle会在分析该列或者域索引是调用该统计类别下的统计收集方法。

missing statistics

When statistics do not exist on schema objects, the optimizer uses the following default values.

当统计信息不存在是，oracle会使用如下的默认统计信息。

Analyze vs DBMS_STATS

The following is a quick overview of the two.

Analyze The only method available for collecting statistics in Oracle 8.0 and lower.(ORACLE 8之前仅有的统计信息收集方式)ANALYZE can only run serially（只可以串行执行）.ANALYZE cannot overwrite or delete certain types of statistics that where generated by DBMS_STATS（不可以覆盖DBMS_STATS生成的部分统计信息）.ANALYZE calculates global statistics for partitioned tables and indexes instead of gathering them directly. This can lead to inaccuracies for some statistics, such as the number of distinct values.（ANALYZE针对分区表和索引计算全局统计信息，而不是直接针对整张表进行统计分析，这可能造成不正确的统计信息，例如distinct value的取值） For partitioned tables and indexes, ANALYZE gathers statistics for the individual partitions and then calculates the global statistics from the partition statistics.（对于分区表，ANALYZE收集每个分区的统计信息，然后根据各个分区的信息计算出全局统计信息）For composite partitioning, ANALYZE gathers statistics for the subpartitions and then calculates the partition statistics and global statistics from the subpartition statistics.（对于组合分区表，ANALYZE收集每个子分区的统计信息，然后据此计算各个分区和全局的统计信息） ANALYZE can gather additional information that is not used by the optimizer, such as information about chained rows and the structural integrity of indexes, tables, and clusters. DBMS_STATS does not gather this information.（ANALYZE 会收集某些与优化器无关的信息，例如chainrow，索引、表和cluster的结构完整性，DBMS_STATS不会收集这些信息）No easy way of knowing which tables or how much data within the tables have changed. The DBA would generally re-analyze all of their tables on a semi-regular basis.（没有办法知道哪些表或者表中的哪些数据发生了变化，dba通常会依据一定的规则重新收集所有标的统计信息）

DBMS_STATS Only available for Oracle 8i and higher.(在oracle8之后才可用)Statistics can be generated to a statistics table and can then be imported or exported between databases and re-loaded into the data dictionary at any time. This allows the DBA to experiment with various statistics.（统计信息可以被导出导入，方便了DBA的使用）DBMS_STATS routines have the option to run via parallel query or operate serially（可以并行或者串行执行）.Can gather statistics for sub-partitions or partitions.（可以收集分区和子分区的统计信息）Certain DDL commands (ie. create index) automatically generate statistics, therefore eliminating the need to generate statistics explicitly after DDL command.（某些DDL语句可以自动收集统计信息）DBMS_STATS does not generate information about chained rows and the structural integrity of segments.（不会收集chainrow和段结构有效性的统计信息）The DBA can set a particular table, a whole schema or the entire database to be automatically monitored when a modification occurs. When enabled, any change (insert, update, delete, direct load, truncate, etc.) that occurs on a table will be tracked in the SGA. This information is incorporated into the data dictionary by the SMON process at a pre-set interval (every 3 hours in Oracle 8.1.x, and every 15 minutes in Oracle 9i). The information collected by this monitoring can be seen in the DBA_TAB_MODIFICATIONS view. Oracle 9i introduced a new function in the DBMS_STATS package called: FLUSH_DATABASE_MONITORING_INFO. The DBA can make use of this function to flush the monitored table data more frequently. Oracle 9i will also automatically call this procedure prior to executing DBMS_STATS for statistics gathering purposes. Note that this function is not included with Oracle 8i.（使用DBMS_STATS，DBA可以指定某张表，或者整个用户，或者这个数据库自动监视数据的变化。当发生任何变化时（增删改查，装载，truncate等），oracle会在sga中自动记录数据的变化，随后SMON进程会将这些变化与已有的统计信息进行合并（oracle8每3个小时合并一次，oracle9之后没15分钟合并一次）。我们可以通过DBA_TAB_MODIFICATIONS视图来查看已经发生的变化。我们也可以直接使用9i引入的新函数FLUSH_DATABASE_MONITORING_INFO来将信息手动合并到已有统计信息中。在9i中，oracle会在每次调用DBMS_STATS时，首先调用FLASH_DATABASE_MONITORING_INFO函数。）DBMS_STATS provides a more efficient, scalable solution for statistics gathering and should be used over the traditional ANALYZE command which does not support features such as parallelism and stale statistics collection.（DBMS_STAS提供了一种更高效，可伸缩的信息统计方式，我们优先使用DBMS_STATS,而不使用ANNLYZE）Use of table monitoring in conjunction with DBMS_STATS stale object statistics generation is highly recommended for environments with large, random and/or sporadic data changes. These features allow the database to more efficiently determine which tables should be re-analyzed versus the DBA having to force statistics collection for all tables. Including those that have not changed enough to merit a re-scan)（优先使用dbms_stats）