优化器收集有关不同类型的数据库对象和数据库环境特征的统计信息。
10.2.1表统计
表统计信息包含优化器在制定执行计划时使用的元数据。
10.2.1.1永久表统计
在Oracle数据库中，表统计信息包括有关行和块的信息。
优化器使用这些统计信息来确定表扫描和表连接的成本。该数据库跟踪有关永久表的所有相关统计信息。例如，存储在表统计信息中的DBA_TAB_STATISTICS跟踪以下内容：
- 行数: 在
DBA_TAB_STATISTICS确定基数时，数据库使用存储在其中的行计数。 - 平均行长
- 数据块数 : 优化器使用带有
DB_FILE_MULTIBLOCK_READ_COUNT初始化参数的数据块数来确定基表访问成本。 - 空数据块数 :
DBMS_STATS.GATHER_TABLE_STATS在收集永久表的统计信息之前提交。
示例10-1表统计
本示例查询表的统计信息sh.customers。
SELECT NUM_ROWS, AVG_ROW_LEN, BLOCKS,
EMPTY_BLOCKS, LAST_ANALYZED
FROM DBA_TAB_STATISTICS
WHERE OWNER='SH'
AND TABLE_NAME='CUSTOMERS';
输出示例如下：
NUM_ROWS AVG_ROW_LEN BLOCKS EMPTY_BLOCKS LAST_ANAL
---------- ----------- ---------- ------------ ---------
55500 189 1517 0 25-MAY-17
10.2.1.2 临时表统计
DBMS_STATS 可以同时收集永久和全局临时表的统计信息，但其他注意事项也适用于后者。
- 临时表的类型临时表 : 分为全局，私有或游标持续时间。
- 全局临时表 :
DBMS_STATS的统计信息收集与临时表相同类型的全局临时表统计信息。 - 全局临时表的共享和特定于会话的统计信息 : 从Oracle Database 12c开始，您可以设置表级别的首选项
GLOBAL_TEMP_TABLE_STATS，以对共享（SHARED）或特定于会话的全局临时表进行统计SESSION。
10.2.1.2.1临时表的类型
临时表分为全局，私有或游标持续时间。
在所有类型的临时表中，数据仅对插入它的会话可见。这些表的区别如下：
- 一个全局临时表是明确创建持久化对象，对于一个特定的时间卖场中间会话的私有数据。
该表是全局的，因为该定义对于所有会话都是可见的。的
ON COMMIT子句CREATE GLOBAL TEMPORARY TABLE指示该表是特定于事务的（DELETE ROWS）还是特定于会话的（PRESERVE ROWS）。全局临时表的优化器统计信息可以共享或特定于会话。 私人临时表是一个显式地创建对象，由私人仅存储器的元数据定义的，即针对特定的持续时间存储中间会话专用数据。
- 该表是私有的，因为该定义仅对创建该表的会话可见。的
ON COMMIT子句CREATE PRIVATE TEMPORARY TABLE指示该表是特定于事务的（DROP DEFINITION）还是特定于会话的（PRESERVE DEFINITION）。 - 游标持续时间临时表是与游标相关联的隐式创建只读存储器对象。
与全局和专用临时表不同，
DBMS_STATS不能收集游标持续时间临时表的统计信息。
这些表的区别在于它们存储数据的位置，创建和删除它们的方式以及元数据的持续时间和可见性。请注意，当会话首先将数据插入全局临时表时，而不是在创建表时，数据库会分配存储空间。
表10-1临时表的重要特征
| 特性 | 全球临时表 | 私人临时餐桌 | 游标持续时间临时表 |
|---|---|---|---|
| 数据可见性 | 会话插入数据 | 会话插入数据 | 会话插入数据 |
| 资料储存 | 持久的 | 内存或临时文件，但仅在会话或事务期间 | 仅在内存中 |
| 元数据的可见性 | 所有会议 | 创建表的会话（在USER_PRIVATE_TEMP_TABLES视图中，基于V$视图） | 会话执行游标 |
| 元数据的持续时间 | 直到表被明确删除 | 直到明确删除表，或者会话（PRESERVE DEFINITION）或事务（DROP DEFINITION）结束 | 直到游标从共享池中消失 |
| 表格创建 | CREATE GLOBAL TEMPORARY TABLE（supports AS SELECT） | CREATE PRIVATE TEMPORARY TABLE（supports AS SELECT） | 在优化程序认为有用时隐式创建 |
| 创建对现有交易的影响 | 没有隐式提交 | 没有隐式提交 | 没有隐式提交 |
| 命名规则 | 与永久表相同 | 必须以 ORA$PTT_ | 内部生成的唯一名称 |
| 桌子掉落 | DROP GLOBAL TEMPORARY TABLE | DROP PRIVATE TEMPORARY TABLE，或在会话（PRESERVE DEFINITION）或事务（DROP DEFINITION） 结束时隐式删除 | 会话结束时隐式掉线 |
也可以看看：
- “ 游标持续时间临时表 ”
10.2.1.2.2全局临时表的统计信息
DBMS_STATS 为全局临时表收集与永久表相同类型的统计信息。
注意： 您无法收集私有临时表的统计信息。
下表显示了全局临时表在收集和存储优化器统计信息方面的不同之处，具体取决于这些表是作用于事务还是会话。
表10-2全局临时表的优化器统计信息
| 特性 | 特定交易 | 特定于会话 |
|---|---|---|
DBMS_STATS征收的 影响 | 不提交 | 提交 |
| 统计存储 | 仅内存 | 字典表 |
| 直方图创建 | 不支持 | 支持的 |
以下过程不会提交特定于事务的临时表，因此不会删除这些表中的行：
GATHER_TABLE_STATSDELETE_obj_STATS，其中obj是TABLE，COLUMN，或INDEXSET_obj_STATS，其中obj是TABLE，COLUMN，或INDEXGET_obj_STATS，其中obj是TABLE，COLUMN，或INDEX
前面的程序单元遵守GLOBAL_TEMP_TABLE_STATS统计信息偏好。例如，如果表首选项设置为SESSION，则SET_TABLE_STATS设置会话统计信息，并将所有行GATHER_TABLE_STATS 保留在特定于事务的临时表中。SHARED但是，如果表首选项设置为，则SET_TABLE_STATS设置共享统计信息，并从特定于事务的临时表中GATHER_TABLE_STATS 删除所有行。
10.2.1.2.3全局临时表的共享和特定于会话的统计信息
从Oracle Database 12c开始，您可以设置表级首选项GLOBAL_TEMP_TABLE_STATS，以对共享（SHARED）或特定于会话（）的全局临时表进行统计SESSION。
如果GLOBAL_TEMP_TABLE_STATS为SESSION，则可以在一个会话中收集全局临时表的优化程序统计信息，然后仅将此统计信息用于此会话。同时，用户可以继续维护统计信息的共享版本。在优化过程中，优化器首先检查全局临时表是否具有特定于会话的统计信息。如果是，那么优化器将使用它们。否则，优化器将使用共享统计信息（如果存在）。
注意：在Oracle Database 12c之前的版本中，数据库不会针对全局临时表和非全局临时表维护优化器统计信息。即使不同会话中的数据可能不同，数据库仍维护所有会话共享的统计信息的一个版本。
特定于会话的优化器统计信息具有以下特征：
- 跟踪统计信息的字典视图同时显示当前会话中的共享统计信息和特定于会话的统计信息。
的意见是
DBA_TAB_STATISTICS，DBA_IND_STATISTICS，DBA_TAB_HISTOGRAMS，和DBA_TAB_COL_STATISTICS（每个视图具有相应的USER_和ALL_版本）。该SCOPE列显示统计信息是特定于会话的还是共享的。特定于会话的统计信息必须存储在数据字典中，以便多个进程可以在Oracle RAC中访问它们。 CREATE ... AS SELECT自动收集优化器统计信息。但是，当GLOBAL_TEMP_TABLE_STATS设置SHARED为时，必须使用手动收集统计信息DBMS_STATS。- 不支持待处理的统计信息。
- 其他会话不共享使用特定于会话的统计信息的游标。
不同的会话可以共享使用共享统计信息的游标，例如在Oracle Database 12c之前的版本中。同一会话可以共享使用特定于会话的统计信息的游标。
- 默认情况下，
GATHER_TABLE_STATS对于临时表，立即使在同一会话中编译的先前游标无效。但是，此过程不会使在其他会话中编译的游标无效。
10.2.2列统计
列统计信息跟踪有关列值和数据分布的信息。
优化器使用列统计信息来生成准确的基数估计，并就索引使用，联接顺序，联接方法等做出更好的决策。例如，统计信息DBA_TAB_COL_STATISTICS跟踪以下内容：
- 不同值的数量
- 空数
- 高低值
- 直方图相关信息
优化器可以使用扩展统计信息，这是列统计信息的一种特殊类型。这些统计信息对于通知优化器各列之间的逻辑关系很有用。
10.2.3索引统计
索引的统计包括关于索引的级别数，索引块的数量，以及所述索引和所述数据块之间的关系的信息。优化器使用这些统计信息来确定索引扫描的成本。
- 索引统计信息 : 类型该
DBA_IND_STATISTICS视图跟踪索引统计信息。 - 索引聚类因子 : 对于B树索引，索引聚类因子度量与索引值（例如姓氏）相关的行的物理分组。
- 索引聚类因子对成本的影响：此示例说明了索引聚类因子如何影响表访问的成本。
10.2.3.1索引统计的类型
该DBA_IND_STATISTICS视图跟踪索引统计信息。
统计信息包括：
- 等级 : 该
BLEVEL列显示从根块到叶块所需的块数。B树索引具有两种类型的块：用于搜索的分支块和用于存储值的叶块。有关B树索引的概念性概述，请参见Oracle数据库概念。 - 不同值 : 此列跟踪不同索引值的数量。如果定义了唯一约束，而没有
NOT NULL定义约束，则此值等于非空值的数量。 - 每个不同索引键的平均叶子块数
- 每个不同的索引键指向的平均数据块数
示例10-2索引统计
本示例查询表的cust_lname_ix和customers_pk索引的一些索引统计信息sh.customers（包括示例输出）：
SELECT INDEX_NAME, BLEVEL, LEAF_BLOCKS AS "LEAFBLK", DISTINCT_KEYS AS "DIST_KEY",
AVG_LEAF_BLOCKS_PER_KEY AS "LEAFBLK_PER_KEY",
AVG_DATA_BLOCKS_PER_KEY AS "DATABLK_PER_KEY"
FROM DBA_IND_STATISTICS
WHERE OWNER = 'SH'
AND INDEX_NAME IN ('CUST_LNAME_IX','CUSTOMERS_PK');
INDEX_NAME BLEVEL LEAFBLK DIST_KEY LEAFBLK_PER_KEY DATABLK_PER_KEY
-------------- ------ ------- -------- --------------- ---------------
CUSTOMERS_PK 1 115 55500 1 1
CUST_LNAME_IX 1 141 908 1 10
10.2.3.2索引聚类因子
对于B树索引，索引聚类因子测量与索引值（例如姓氏）相关的行的物理分组。
索引聚类因子可帮助优化程序确定对于某些查询而言，索引扫描还是全表扫描更有效。低聚类因子表示有效的索引扫描。
接近表中块数的聚类因子表明，索引键在表块中对行进行了物理排序。如果数据库执行全表扫描，则数据库倾向于检索行，因为它们存储在按索引键排序的磁盘上。
接近行数的聚类因子表明，相对于索引键，行在数据库块中随机散布。如果数据库执行全表扫描，则数据库将不会通过此索引键以任何排序的顺序检索行。
聚类因子是特定索引的属性，而不是表。如果一个表上存在多个索引，则一个索引的聚类因子可能较小，而另一索引的聚类因子较大。尝试重组表以改善一个索引的聚类因子可能会降低另一索引的聚类因子。
示例10-3索引聚类因子
本示例说明了优化器如何使用索引聚类因子来确定使用索引是否比全表扫描更有效。
- 启动SQL * Plus并以身份连接到数据库
sh，然后查询sh.customers表中的行数和块数（包括示例输出）：SELECT table_name, num_rows, blocks FROM user_tables WHERE table_name='CUSTOMERS'; TABLE_NAME NUM_ROWS BLOCKS ------------------------------ ---------- ---------- CUSTOMERS 55500 1486 - 在
customers.cust_last_name列上创建索引。例如，执行以下语句：
CREATE INDEX CUSTOMERS_LAST_NAME_IDX ON customers(cust_last_name); - 查询新创建索引的索引聚类因子。
以下查询显示
customers_last_name_idx索引具有较高的聚集因子，因为该聚集因子明显大于表中的块数：SELECT index_name, blevel, leaf_blocks, clustering_factor FROM user_indexes WHERE table_name='CUSTOMERS' AND index_name= 'CUSTOMERS_LAST_NAME_IDX'; INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR ------------------------------ ---------- ----------- ----------------- CUSTOMERS_LAST_NAME_IDX 1 141 9859 - 创建
customers表的新副本，并按行对行进行排序cust_last_name。例如，执行以下语句：
DROP TABLE customers3 PURGE; CREATE TABLE customers3 AS SELECT * FROM customers ORDER BY cust_last_name; - 收集
customers3表格上的统计信息。例如，执行以下
GATHER_TABLE_STATS过程：执行DBMS_STATS.GATHER_TABLE_STATS（null，'CUSTOMERS3'）;
- 查询
customers3表中的行数和块数。例如，输入以下查询（包括示例输出）：
SELECT TABLE_NAME, NUM_ROWS, BLOCKS FROM USER_TABLES WHERE TABLE_NAME='CUSTOMERS3'; TABLE_NAME NUM_ROWS BLOCKS ------------------------------ ---------- ---------- CUSTOMERS3 55500 1485 - 在的
cust_last_name列上创建索引customers3。例如，执行以下语句：
CREATE INDEX CUSTOMERS3_LAST_NAME_IDX ON customers3(cust_last_name); - 查询索引的索引聚类因子
customers3_last_name_idx。以下查询显示
customers3_last_name_idx索引的聚类系数较低：SELECT INDEX_NAME, BLEVEL, LEAF_BLOCKS, CLUSTERING_FACTOR FROM USER_INDEXES WHERE TABLE_NAME = 'CUSTOMERS3' AND INDEX_NAME = 'CUSTOMERS3_LAST_NAME_IDX'; INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR ------------------------------ ---------- ----------- ----------------- CUSTOMERS3_LAST_NAME_IDX 1 141 1455该表
customers3具有与原始customers表相同的数据，但是索引oncustomers3具有较低的聚类因子，因为表中的数据由排序cust_last_name。现在，聚类因子约为块数的10倍，而不是70倍。 - 查询
customers表。例如，执行以下查询（包括示例输出）：
SELECT cust_first_name, cust_last_name FROM customers WHERE cust_last_name BETWEEN 'Puleo' AND 'Quinn'; CUST_FIRST_NAME CUST_LAST_NAME -------------------- ---------------------------------------- Vida Puleo Harriett Quinlan Madeleine Quinn Caresse Puleo - 显示查询的光标。
例如，执行以下查询（包括部分示例输出）：
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR()); ------------------------------------------------------------------------------- | Id | Operation | Name | Rows |Bytes|Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0| SELECT STATEMENT | | | | 405 (100)| | |* 1| TABLE ACCESS STORAGE FULL| CUSTOMERS | 2335|35025| 405 (1)|00:00:01| -------------------------------------------------------------------------------前面的计划表明，优化器未在原始
customers表上使用索引。 - 查询
customers3表。例如，执行以下查询（包括示例输出）：
SELECT cust_first_name, cust_last_name FROM customers3 WHERE cust_last_name BETWEEN 'Puleo' AND 'Quinn'; CUST_FIRST_NAME CUST_LAST_NAME -------------------- ---------------------------------------- Vida Puleo Harriett Quinlan Madeleine Quinn Caresse Puleo - 显示查询的光标。
例如，执行以下查询（包括部分示例输出）：
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR()); --------------------------------------------------------------------------------------- |Id| Operation | Name |Rows|Bytes|Cost(%CPU)| Time| --------------------------------------------------------------------------------------- | 0| SELECT STATEMENT | | | |69(100)| | | 1| TABLE ACCESS BY INDEX ROWID|CUSTOMERS3 |2335|35025|69(0) |00:00:01| |*2| INDEX RANGE SCAN |CUSTOMERS3_LAST_NAME_IDX|2335| |7(0) |00:00:01| ---------------------------------------------------------------------------------------结果集相同，但是优化器选择索引。计划成本远低于原始
customers表上使用的计划成本。 - 查询
customers带有强制优化器使用索引的提示。例如，执行以下查询（包括部分示例输出）：
SELECT /*+ index (Customers CUSTOMERS_LAST_NAME_IDX) */ cust_first_name, cust_last_name FROM customers WHERE cust_last_name BETWEEN 'Puleo' and 'Quinn'; CUST_FIRST_NAME CUST_LAST_NAME -------------------- ---------------------------------------- Vida Puleo Caresse Puleo Harriett Quinlan Madeleine Quinn - 显示查询的光标。
例如，执行以下查询（包括部分示例输出）：
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR()); ----------------------------------------------------------------------------------------- | Id | Operation | Name |Rows|Bytes|Cost(%CPU)| Time | ----------------------------------------------------------------------------------------- | 0| SELECT STATEMENT | | | | 422(100) | | | 1| TABLE ACCESS BY INDEX ROWID|CUSTOMERS |335 |35025| 422(0) |00:00:01| |*2| INDEX RANGE SCAN |CUSTOMERS_LAST_NAME_IDX|2335| | 7(0) |00:00:01| -----------------------------------------------------------------------------------------先前的计划表明，使用索引on
customers的成本高于全表扫描的成本。因此，使用索引不一定能提高性能。索引聚类因子是衡量索引扫描是否比全表扫描更有效的度量。
10.2.3.3索引聚集因子对成本的影响：示例
此示例说明了索引聚类因子如何影响表访问的成本。
请考虑以下情形：
- 一个表包含9行，这些行存储在3个数据块中。
- 该
col1列当前存储的值A，B和C。 - 该表
col1_idx存在一个名为的非唯一索引col1。
示例10-4并置数据
假设这些行存储在数据块中，如下所示：
Block 1 Block 2 Block 3
------- ------- -------
A A A B B B C C C
在此示例中，的索引聚类因子col1_idx很低。具有相同索引列值的行在col1表的相同数据块中。因此，使用索引范围扫描以返回所有带值的行的成本A很低，因为仅需读取表中的一个块。
示例10-5分散的数据
假设相同的行分散在数据块中，如下所示：
Block 1 Block 2 Block 3
------- ------- -------
A B C A C B B A C
在此示例中，的索引聚类因子col1_idx更高。数据库必须读取表中的所有三个块，以检索值为A中的所有行col1。
10.2.4系统统计
该系统统计描述硬件特性，如I / O和CPU性能和利用率。
系统统计信息使查询优化器可以在选择执行计划时更准确地估计I / O和CPU成本。更新系统统计信息时，数据库不会使先前解析的SQL语句无效。数据库使用新的统计信息解析所有新的SQL语句。
10.2.5用户定义的优化器统计信息
在可扩展的优化使用户定义的 函数和索引创建统计信息收集，选择性和成本函数的作者。
优化程序成本模型已扩展为集成用户提供的信息，以评估CPU和I / O成本。统计信息类型充当用户定义函数的接口，这些函数会影响执行计划的选择。但是，要使用统计信息类型，优化器需要一种机制来将该类型绑定到数据库对象，例如列，独立函数，对象类型，索引，索引类型或包。SQL语句ASSOCIATE STATISTICS 允许发生这种绑定。
用户定义统计信息的功能与同时使用标准SQL数据类型和对象类型的列以及域索引相关。将统计信息类型与列或域索引关联时，只要DBMS_STATS收集统计信息，数据库就会在统计信息类型中调用统计信息收集方法。
