Oracle SQL语句为什么不走索引-场景二

来源：这里教程网时间：2026-03-03 20:51:33 作者：

理解“为什么我的索引没有被使用?”这是一个相对广泛的话题，其中从简单的无索引到SQL执行计划及数据发布等原因导致，因此不使用索引有很多不同的原因，下面我们主要介绍通过追踪SQL10053及DBA手动评估相结合说明为什么没有执行计划：当一个有效的SQL语句第一次发送到服务器时，Oracle生成一个执行计划，描述如何检索必要的数据。在旧版本的数据库中，此执行计划可以使用以下两种优化器之一生成：基于规则的优化器（Rule-Based Optimizer， RBO）——这是最初的优化方法，顾名思义，本质上是Oracle应该遵循的一系列规则来生成执行计划。即使在引入了基于成本的优化器之后，如果服务器没有与语句引用的对象相关的内部统计信息，或者通过提示或实例/会话参数显式请求，也会使用此方法。这个优化器目前已经不是Oracle默认选项，在数据库的后续版本中被弃用。基于成本的优化器（CBO）——CBO使用数据库统计信息生成多个执行计划，选择成本最低的执行计划，其中成本与完成操作所需的系统资源相关。在较新的数据库版本中，基于成本的优化器是唯一可用的选项。如果创建了新对象，数据库中的数据量或数据分布发生了变化，统计数据将不再代表数据库的真实状态，因此CBO的决策过程可能会受到严重损害。本文将重点讨论使用DBMS_STATS包管理统计信息，不过也会提到一些遗留方法。使用GATHER_INDEX_STATS显式地收集索引统计信息。

EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK');
EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK', estimate_percent => 15);

获取统计信息可以从特定对象（DBA、ALL和USER视图）的数据字典视图中获得。其中一些视图是在后来的版本中添加的。

DBA_TABLES DBA_TAB_STATISTICS DBA_TAB_PARTITIONS DBA_TAB_SUB_PARTITIONS DBA_TAB_COLUMNS DBA_TAB_COL_STATISTICS DBA_PART_COL_STATISTICS DBA_SUBPART_COL_STATISTICS DBA_INDEXES DBA_IND_STATISTICS DBA_IND_PARTITIONS DBA_IND_SUBPARTIONS

直方图信息可从以下视图获得：

DBA_TAB_HISTOGRAMS DBA_PART_HISTOGRAMS DBA_SUBPART_HISTOGRAMS

表、列和索引统计信息可以使用相关的删除程序删除相关统计信息。

EXEC DBMS_STATS.delete_database_stats;
EXEC DBMS_STATS.delete_schema_stats('SCOTT');
EXEC DBMS_STATS.delete_table_stats('SCOTT', 'EMP');
EXEC DBMS_STATS.delete_column_stats('SCOTT', 'EMP', 'EMPNO');
EXEC DBMS_STATS.delete_index_stats('SCOTT', 'EMP_PK');
EXEC DBMS_STATS.delete_dictionary_stats;

从Oracle 9i R1中引入的GATHER_SYSTEM_STATS过程收集与系统I/O和CPU性能相关的统计信息。向优化器提供这些信息可以使其选择执行计划更加准确，因为它能够同时使用系统的CPU和I/O配置文件来权衡操作的相对成本。有两种可能的系统统计类型：

Noworkload：所有数据库都捆绑了一组默认的Noworkload统计数据，但是可以用更准确的信息替换它们。在收集无工作负载状态时，数据库发出一系列随机I/ o并测试CPU的速度。可以想象，这会在收集阶段给系统带来负载。

EXEC DBMS_STATS.gather_system_stats;

工作负载：当使用start/stop或interval参数启动时，数据库使用计数器跟踪所有系统操作，使其准确了解系统的性能。如果存在工作负载统计信息，将优先使用它们而不是无工作负载统计信息。

-- 手动启动和停止采样系统活动的代表性时间（几个小时）。 EXEC DBMS_STATS.gather_system_stats('start'); EXEC DBMS_STATS.gather_system_stats('stop'); -- 从现在开始采样，直到特定的分钟数。 DBMS_STATS.gather_system_stats('interval', interval => 180);

当前系统统计信息最终会存储在aux_stats$数据基表中的，下面看一下该表的结构：系统表sys.aux_stats$ 介绍：SQL> desc Sys.aux_stats$ Name Null? Type ------------------- -------------------------------------- SNAME NOT NULL VARCHAR2(30) PNAME NOT NULL VARCHAR2(30) PVAL1 NUMBER PVAL2 VARCHAR2(255) SQL> select distinct PNAME from sys.aux_stats$ PNAME ------------------------------ FLAGS CPUSPEEDNW IOSEEKTIM SREADTIM DSTOP STATUS IOTFRSPEED SLAVETHR MREADTIM DSTART MAXTHR CPUSPEED MBRC 13 rows selected.这些值是临时值SYSSTATS_TEMP，可以在设备上的统计信息之前看到这些值。下面我们介绍几个平时关注的值： The Definitions for below SBLKRDS SBLKRDTIM MBLKRDS MBLKRDTIM CPUCYCLES CPUTIM JOB CACHE_JOB MBRTOTAL ########################################## SBLKRDS: Small Block Reads - The number of small-sized data blocks read from storage devices or databases. SBLKRDTIM: Small Block Read Time - The time taken to perform small block reads. MBLKRDS: Medium Block Reads - The count of medium-sized data blocks read from storage devices or databases. MBLKRDTIM: Medium Block Read Time - The time taken to perform medium block reads. CPUCYCLES: CPU Cycles - The total number of clock cycles executed by the CPU during a specific process or task. CPUTIM: CPU Time - The amount of time the CPU spends processing a specific task or job. JOB: A Job - Typically refers to a specific task, process, or unit of work assigned to an individual or a system. CACHE_JOB: Cache Job - A job or task that involves cache memory operations within the database. MBRTOTAL: Memory Buffer Read Total - The total number of read operations from memory buffers.

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. CPU速率	At system startup	Set gathering_mode = NOWORKLOAD or set statistics manually.	Millions/sec.
ioseektim	I/O seek time equals seek time + latency time + operating system overhead time. 寻道时间	At system startup 10 (default)	Set gathering_mode = 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 gathering_mode = NOWORKLOAD or set statistics manually.	Bytes/ms
cpuspeed	Represents workload CPU speed. CPU speed is the average number of CPU cycles in each second. CPU速率	None	Set gathering_mode = NOWORKLOAD, INTERVAL, orSTART\|STOP, or set statistics manually.	Millions/sec.
maxthr	Maximum I/O throughput is the maximum throughput that the I/O subsystem can deliver. 输入输出最大吞吐量	None	Set gathering_mode = NOWORKLOAD, INTERVAL, orSTART\|STOP, or set statistics manually.	Bytes/sec.
slavethr	Slave I/O throughput is the average parallel slave I/O throughput. 从属进程的平均吞吐量	None	Set gathering_mode = INTERVAL or START\|STOP, or set statistics manually.	Bytes/sec.
sreadtim	Single block read time is the average time to read a single block randomly. 单块读时间	None	Set gathering_mode = INTERVAL or START\|STOP, or set statistics manually.	ms
mreadtim	Multiblock read is the average time to read a multiblock sequentially. 多块读时间	None	Set gathering_mode = INTERVAL or START\|STOP, or set statistics manually.	ms
mbrc	Multiblock count is the average multiblock read count sequentially. 一次多块读的数据库块数量	None	Set gathering_mode = INTERVAL or START\|STOP, or set statistics manually.	blocks

This are SYSSTATS_TEMP in the sys.aux_stats$ table before gathering statistics might indicate that the system or certain processes are already collecting and populating temporary statistics. These temporary statistics are often used by the Oracle optimizer to make educated decisions about query execution plans even before official statistics are gathered for tables and indexes. Oracle can use temporary statistics in various scenarios, such as when a new table or index is created, or when the statistics for a table/index are stale or missing. Temporary statistics can help the optimizer make better choices during query optimization, resulting in more efficient query plans. It's important to note that while temporary statistics can be useful, they are not a replacement for regular statistics gathering using the DBMS_STATS package or other methods. 参考文档： Sys.aux_stats$ 表结构介绍基于成本的优化器（CBO）和数据库统计