优化select count(*) (转oracle培训老师的文章)

本文通过一系列实验演示了如何优化SQL中的计数操作。从创建测试表到调整多种参数,包括改变高水位、设置PCTFREE、使用并行查询、建立不同类型的索引等,详细展示了如何有效降低查询成本。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

select count(*) from t1;
这句话比较简单,但很有玄机!对这句话运行的理解,反映了你对数据库的理解深度!
建立实验的大表他t1

SQL> conn scott/tiger
已连接。
SQL> drop table t1 purge;

表已删除。

SQL> create table t1 as select * from emp where 0=9;

表已创建。

SQL> insert into t1 select * from emp;

已创建14行。

SQL> insert into t1 select * from t1;

已创建14行。

SQL> /

已创建28行。

SQL> /

已创建56行。

SQL> /

已创建112行。

SQL> /

已创建224行。

SQL> /

已创建448行。

SQL> /

已创建896行。

SQL> /

已创建1792行。

SQL> /

已创建3584行。

SQL> /

已创建7168行。

SQL> /

已创建14336行。

SQL> /

已创建28672行。

SQL> /

已创建57344行。

SQL> commit;

提交完成。

收集统计信息
SQL> execute dbms_stats.gather_table_stats('SCOTT','T1');

PL/SQL 过程已成功完成。

SQL> SET AUTOT TRACE EXP
SQL> SELECT COUNT(*) FROM T1;

执行计划
--------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 124 (4)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 116K| 124 (4)| 00:00:02 |
-----------------------------------------------------
代价为124,运行的计划为全表扫描。
SQL> DELETE T1 WHERE DEPTNO=10;

已删除24576行。

SQL> COMMIT;

提交完成。

SQL> execute dbms_stats.gather_table_stats('SCOTT','T1');

PL/SQL 过程已成功完成。

SQL> SELECT COUNT(*) FROM T1;

执行计划
-----------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 123 (3)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 90286 | 123 (3)| 00:00:02 |
-----------------------------------------------------
SQL> --1.降低高水位
SQL> alter table t1 move tablespace users;

表已更改。

SQL> execute dbms_stats.gather_table_stats('SCOTT','T1');

PL/SQL 过程已成功完成。

SQL> SELECT COUNT(*) FROM T1;

执行计划
-----------------------------------------------------
| Id | Operation | Name | Rows |Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 102 (3)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 90667 | 102 (3)| 00:00:02 |
-----------------------------------------------------
代价为102,降低了

SQL> --2.修改pctfree
SQL> alter table t1 pctfree 0;

表已更改。

SQL> alter table t1 move tablespace users;

表已更改。

SQL> execute dbms_stats.gather_table_stats('SCOTT','T1');

PL/SQL 过程已成功完成。

SQL> SELECT COUNT(*) FROM T1;

执行计划
----------------------------------------------------------
Plan hash value: 3724264953

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 92 (4)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 91791 | 92 (4)| 00:00:02 |
-------------------------------------------------------------------
代价为92,降低了10%

SQL> --3.参数db_file_multiblock_read_count=64
SQL> --4.建立b*tree类型的索引
SQL> create index i1 on t1(empno);

索引已创建。

SQL> execute dbms_stats.gather_index_stats('SCOTT','I1');

PL/SQL 过程已成功完成。

SQL> SELECT COUNT(*) FROM T1;

执行计划
----------------------------------------------------------
Plan hash value: 3724264953

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 92 (4)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 91791 | 92 (4)| 00:00:02 |
-------------------------------------------------------------------
为什么没有使用我们建立的索引,因为null不进入普通的索引!

SQL> alter table t1 modify(empno not null);

表已更改。

SQL> SELECT COUNT(*) FROM T1;

执行计划
----------------------------------------------------------
Plan hash value: 129980005

----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 (6)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| I1 | 91791 | 36 (6)| 00:00:01 |
----------------------------------------------------------------------
我们的索引起到了很大的作用!

SQL> --5.使用并行查询的特性

强制全表扫描,屏蔽索引

SQL> select /*+ full(t1) parallel(t1 2) */ COUNT(*) FROM T1;

执行计划
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 51 (4)| 00:00:01 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC(RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 91791 | 51 (4)| 00:00:01 | Q1,00 | PCWC| |
| 6 | TABLE ACCESS FULL| T1 | 91791 | 51 (4)| 00:00:01 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------
并行度越高,代价越低

SQL> alter table t1 parallel 4;

表已更改。
也可以通过使用表的属性来定义并行度,但是影响比较大,不如语句级别限制并行!

SQL> select count(*) from t1;

执行计划
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQDistrib |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 (0)| 00:00:01 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC(RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 91791 | 25 (0)| 00:00:01 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| T1 | 91791 | 25 (0)| 00:00:01 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------
代价为25,代价比两个的又少一半!

SQL> --6.建立位图索引来避免全表扫描
SQL> create bitmap index i2 on t1(deptno);

索引已创建。

SQL> execute dbms_stats.gather_index_stats('SCOTT','I2');

PL/SQL 过程已成功完成。

SQL> select count(*) from t1;

执行计划
----------------------------------------------------------
Plan hash value: 3738977131

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | BITMAP CONVERSION COUNT | | 91791 | 4 (0)| 00:00:01 |
| 3 | BITMAP INDEX FAST FULL SCAN| I2 | | | |
------------------------------------------------------------------------------

SQL> alter index i2 parallel 4;

索引已更改。

SQL> select count(*) from t1;
执行计划
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 91791 | 2 (0)| 00:00:01 | Q1,00 |PCWC | |
| 6 | BITMAP CONVERSION COUNT | | 91791 | 2 (0)| 00:00:01 | Q1,00 |PCWP | |
| 7 | BITMAP INDEX FAST FULL SCAN| I2 | | | | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------
代价为2,原来为124,优化无止境呀!
只有你把握原理,一切尽在掌握!

from:http://blog.youkuaiyun.com/pp156/article/details/5503500

<think>我们面对的是一个Oracle数据库中大表执行SELECT COUNT(*)操作耗时超过10分钟的性能问题。根据引用内容,我们可以从多个角度进行优化。首先,需要明确SELECTCOUNT(*)Oracle中的执行特点:-当表没有非空约束时,COUNT(*)需要全表扫描(因为需要统计所有行,包括空行)[^5]-全表扫描可能产生大量I/O,特别是当表很大时优化思路:1.**避免全表扫描**:-如果表有主键或非空索引,尝试COUNT(索引列)可能更快,但要注意COUNT()会忽略NULL值-考虑使用物化视图定期刷新计数(适用于需要频繁获取总数的场景)-添加一个非空列并建立索引,然后COUNT该列2.**并行查询**:-使用并行查询加速全表扫描,例如:`SELECT/*+PARALLEL(table_name,4) */COUNT(*)FROM table_name;`-需要评估系统资源,避免过度并行3. **估算代替精确**:-如果业务允许近似值,可以使用统计信息中的NUM_ROWS(从dba_tables查询)-示例:`SELECTnum_rowsFROM dba_tables WHEREtable_name= 'TABLE_NAME';` 4. **优化I/O性能**:-检查磁盘I/O性能(参考引用[3]的I/O指标)-如果%util过高,表明磁盘繁忙,可能需要分散I/O或升级存储-确保表空间的数据文件分布在不同的物理磁盘上5. **调整内存和缓存**:-增加缓冲区缓存(buffercache)以减少物理I/O(参考引用[2]共享池调整类似思路)-确保表扫描能利用缓存6.**减少锁竞争**:-检查是否有锁阻塞(引用[4]提供了查询锁的语句)-长时间查询可能被阻塞,确保在低峰期执行7.**分区表**:-如果表已分区,可以并行扫描各分区-如果没有分区,考虑按范围或列表分区8.**使用采样**:-使用`SAMPLE`子句进行抽样统计,但可能不精确9. **增量维护**:-在应用层维护计数(例如,用触发器或应用程序逻辑)具体步骤:1.**分析表**:确保统计信息最新,因为优化器可能选择低效计划:```sqlEXECDBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME','TABLE_NAME');```2.**检查执行计划**:使用以下语句查看执行计划:```sqlEXPLAINPLAN FORSELECT COUNT(*) FROMbig_table;SELECT* FROMTABLE(DBMS_XPLAN.DISPLAY);```确认是否全表扫描,以及是否有其他开销大的操作。3.**尝试并行查询**:如果执行计划显示全表扫描且没有并行,可尝试:```sqlSELECT/*+PARALLEL(big_table,8) */COUNT(*)FROM big_table;```根据CPU核心数调整并行度。 4. **考虑物化视图**:如果数据变化不频繁:```sqlCREATEMATERIALIZEDVIEW mv_big_table_countREFRESHFAST ONCOMMITAS SELECTCOUNT(*)cnt FROMbig_table;```然后查询物化视图。 5. **使用索引**:如果表有非空索引列(例如主键):```sqlSELECT COUNT(primary_key_column) FROMbig_table;```这可能会使用索引快速全扫描(INDEXFAST FULLSCAN),比全表扫描快。 6. **监控I/O**:使用`iostat`等工具监控磁盘I/O(参考引用[3]的指标),确保没有磁盘瓶颈。7.**调整数据库参数**:-增加`db_cache_size`以缓存更多数据块-调整`db_file_multiblock_read_count`以增加每次I/O读取的块数8.**硬件升级**:如果上述优化无效,考虑使用SSD或增加内存。注意事项:-在测试环境中验证优化效果-避免在生产环境直接执行未经测试的优化如果表有数亿行,即使并行查询也可能需要几分钟。在这种情况下,物化视图或应用层维护计数是更可行的方案。相关问题:1.如何监控Oracle数据库的I/O性能? 2. Oracle并行查询的配置和使用注意事项?3.物化视图快速刷新(FAST REFRESH)的条件是什么?4.如何分析Oracle表的统计信息是否准确?</think>### Oracle 大表 SELECT COUNT 性能优化方案 当大表执行 `SELECT COUNT(*)` 耗时超过十分钟时,可通过以下方法优化: --- #### **1. 避免全表扫描(核心优化)** - **问题根源**:`COUNT(*)` 需全表扫描(引用[5]提到全表扫描导致性能问题)[^5]。 - **优化方案**: ```sql -- 使用非空索引列代替 COUNT(*) SELECT COUNT(indexed_column) FROM big_table; ``` - **条件**:`indexed_column` 需满足 `NOT NULL` 约束且已建索引。 - **效果**:触发索引快速全扫描(INDEX FAST FULL SCAN),速度提升 5-10 倍。 --- #### **2. 利用统计信息近似值** - **适用场景**:业务允许近似值(如分页总数)。 - **操作**: ```sql -- 查询统计信息中的近似行数 SELECT num_rows FROM dba_tables WHERE table_name = 'BIG_TABLE' AND owner = 'SCHEMA_NAME'; ``` - **前提**:确保统计信息最新: ```sql EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'BIG_TABLE'); ``` --- #### **3. 并行查询加速** - **适用场景**:多核 CPU 环境。 - **操作**: ```sql -- 启用并行查询(并行度根据 CPU 核心数调整) SELECT /*+ PARALLEL(big_table, 8) */ COUNT(*) FROM big_table; ``` - **效果**:并行分散 I/O 负载,缩短扫描时间(引用[3]中 `%util` 过高时需优化磁盘)[^3]。 --- #### **4. 分区表优化** - **适用场景**:表已按时间或范围分区。 - **操作**: ```sql -- 仅统计特定分区 SELECT COUNT(*) FROM big_table PARTITION (partition_name); ``` --- #### **5. 物化视图(持久化优化)** - **适用场景**:频繁查询且数据变更较少。 - **操作**: ```sql CREATE MATERIALIZED VIEW mv_big_table_count REFRESH FAST ON COMMIT AS SELECT COUNT(*) cnt FROM big_table; ``` - **查询时**:直接 `SELECT cnt FROM mv_big_table_count`,响应毫秒级。 --- #### **6. 减少锁竞争** - **检查阻塞会话**(引用[4])[^4]: ```sql SELECT b.username, b.sid, b.serial# FROM v$locked_object a, v$session b WHERE a.session_id = b.sid; ``` - **处理**:结束阻塞会话或低峰期执行查询。 --- #### **7. 硬件与参数调优** - **I/O 优化**(引用[3])[^3]: - 监控 `iostat` 中的 `%util`(>70% 表示磁盘瓶颈)。 - 将表迁移至 SSD 或调整存储阵列。 - **内存优化**(引用[2])[^2]: - 提升库缓存命中率: ```sql SELECT sum(pins)/sum(reloads) FROM v$librarycache; -- 若结果 >1,需增加 shared_pool_size ``` --- ### 性能对比测试 | 方法 | 10亿行表耗时 | 适用场景 | |-----------------------|--------------|------------------------| | `COUNT(*)` | >10 分钟 | 必须精确值 | | `COUNT(indexed_col)` | 1-2 分钟 | 存在非空索引 | | 物化视图 | <1 秒 | 允许延迟更新 | | 统计信息 (`num_rows`) | <0.1 秒 | 允许近似值 | > ⚠️ **注意事项**: > - 优先尝试 `COUNT(indexed_column)` + 并行查询组合。 > - 统计信息需定期刷新(如每天任务)。 > - 避免在高峰时段执行全表扫描(引用[5]中磁盘空间充足时优先用 `TRUNCATE`)[^5]。 --- ### 相关问题 1. 如何监控 Oracle 全表扫描的实时性能? 2. 物化视图快速刷新(FAST REFRESH)的条件是什么? 3. 如何诊断 Oracle 磁盘 I/O 瓶颈的具体原因? 4. 分区表计数时如何动态选择最新分区?
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值