使用dbms_stat采集统计信息时estimate_percent和cascade的默认值

本文详细解读了使用dbms_stats采集统计信息时,未指定estimate_percent参数时的默认行为,包括采样比例默认为100%,CASCADE默认值为TRUE,即会对表、索引和列进行分析采集统计信息。通过实验验证,提供了实用的数据库性能优化建议。

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

收集统计信息可以用dbms_stats包,通常用这样的语法:exec dbms_stat.gather_table_stats(ownname=>'xxx', tabname=>'xxx', estimate_percent=>xxx, method_opt=>'xxx', cascade=>xxx);


其中estimate_percent表示选择的采样比例,如果太低,收集速度会快,但可能不会很准确,如果太高,收集速度会慢,但比较准确,各有利弊。但同时也支持默认方式:exec dbms_stat.gather_table_stats(ownname=>'xxx', tabname=>'xxx');,那这里收集的采样比例是多少呢?

做个实验:

SQL> create table t2 as select * from dba_objects;
Table created.

SQL> select count(*) from t2;
  COUNT(*)
----------
     11218

SQL> create index idx_t2 on t2(object_id);
Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>'SYS', tabname=>'T2');
PL/SQL procedure successfully completed.

查询dba_tables表,看到NUM_ROWS值是11218,说明此处采样比例是100%。

再查询dba_indexes表,看到索引IDX_T2的相关统计列已经有值了,说明索引也进行了分析,即CASCADE默认值是TRUE。

再次查询dba_ind_columns表,看到列已经有了值,例如:COLUMN_POSITION、COLUMN_LENGTH等,也证明了CASCADE默认值是TRUE。

总结:

使用dbms_stats.gather_table_stats(ownname=>'SYS', tabname=>'T2');方式采集统计信息,

1、estimate_percent值默认是100%(其实estimate_percent可以设置为NULL,和100%作用相同,我理解NULL和不设这个值也相同,根据三段论传递,那么不设estimate_percent即默认是100%)。

2、CASCADE默认值是TRUE,即会对表、索引和列都会进行分析采集统计信息。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30166976/viewspace-1665927/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/30166976/viewspace-1665927/

<think>嗯,用户现在问的是如何查询Oracle中数据超过1千万的表。之前他们问过关于删除超1亿表产生大量日志的问题,现在可能是在优化或管理数据库遇到了性能或空间的问题。需要确认用户的身份,可能是DBA或者开发人员,负责维护数据库性能,处理大表的问题。 首先,用户需要识别哪些表的数据量超过千万,这可能涉及到空间管理、性能优化或者分区策略的调整。需要给出几种方法,包括数据字典查询、估算行数、使用统计信息等。还要考虑不同方法的优缺点,比如准确性速度之间的权衡。 用户可能希望得到一个直接的查询语句,但也要注意统计信息可能不是实的,所以需要提醒他们及收集统计信息。另外,是否需要考虑分区表的情况?比如,分区表的总行数可能需要聚合各个分区的行数。 还要考虑权限问题,用户是否有访问DBA_TABLES等视图的权限?如果用户没有DBA权限,可能需要使用ALL_TABLES或USER_TABLES,但这样可能无法获取所有表的信息。可能需要提示用户检查权限,或者使用其他方法。 另外,用户可能想知道大表的影响,比如查询性能、维护难度,以及如何处理这些大表,比如分区、归档等。但当前问题主要是识别,所以可能需要在回答中简要提及后续步骤,但重点还是查询方法。 是否需要提供脚本示例?比如使用PL/SQL循环遍历所有表,但这可能会比较慢,特别是当表很多的候。或者使用DBMS_SPACE包来估算行数,但这也需要权限。 还要注意不同的Oracle版本可能有不同的数据字典视图,比如DBA_TABLES中的NUM_ROWS在旧版本中是否存在?可能需要确认兼容性,但通常这是标准字段。 总结下来,回答应该包括几种方法: 1. 使用DBA_TABLES查询统计信息中的NUM_ROWS。 2. 使用估算方法,比如通过块数量行数估算。 3. 使用PL/SQL逐表统计。 4. 使用DBMS_SPACE包估算。 5. 结合分区表的处理。 需要详细说明每种方法的步骤,优缺点,以及注意事项,比如统计信息是否最新,权限要求,执行间等。同提醒用户及收集统计信息,并考虑后续对大表的优化策略。</think>在Oracle数据库中,快速识别数据量超过1000万行的表是性能调优、容量规划或分区设计的重要前提。以下是几种常用的方法及其注意事项: --- ### **1. 通过数据字典查询(依赖统计信息)** #### **方法说明** Oracle的统计信息(`NUM_ROWS`)记录了表的预估行数,但需要确保统计信息已收集且相对准确。 #### **查询语句** ```sql SELECT owner, table_name, num_rows FROM dba_tables WHERE num_rows > 10000000 ORDER BY num_rows DESC; ``` #### **注意事项** - **权限要求**:需要`DBA_TABLES`视图的访问权限(通常为DBA角色)。 - **统计信息效性**:若统计信息未更新,结果可能不准确。可通过以下命令手动收集: ```sql EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA_NAME', estimate_percent => 100, cascade => TRUE); ``` - **分区表处理**:分区表的`NUM_ROWS`通常为0,需查询`DBA_TAB_PARTITIONS`: ```sql SELECT table_owner, table_name, SUM(num_rows) AS total_rows FROM dba_tab_partitions GROUP BY table_owner, table_name HAVING SUM(num_rows) > 10000000; ``` --- ### **2. 通过块数量估算(无需统计信息)** #### **方法说明** 通过数据块数量平均每块行数估算表的总行数,适用于统计信息过期或未收集的场景。 #### **查询语句** ```sql SELECT owner, table_name, blocks, ROUND((blocks * avg_row_len) / 1024) AS "估算大小(MB)", ROUND(blocks * ( SELECT avg_row_cnt FROM dba_tab_statistics WHERE table_name = t.table_name AND owner = t.owner )) AS "估算行数" FROM dba_tables t WHERE tablespace_name IS NOT NULL AND ROUND(blocks * ( SELECT avg_row_cnt FROM dba_tab_statistics WHERE table_name = t.table_name AND owner = t.owner )) > 10000000; ``` #### **注意事项** - **估算误差**:结果仅供参考,实际行数可能与估算值存在较大偏差。 - **性能影响**:复杂查询可能消耗较多资源。 --- ### **3. 通过`COUNT(*)`精确统计(谨慎使用)** #### **方法说明** 直接执行`COUNT(*)`获取精确行数,但可能对性能产生严重影响。 #### **操作示例** ```sql SELECT COUNT(*) FROM schema_name.table_name; ``` #### **优化建议** - **并行查询**: ```sql SELECT /*+ PARALLEL(t, 8) */ COUNT(*) FROM schema_name.table_name t; ``` - **分批统计**: ```sql SELECT SUM(cnt) FROM ( SELECT COUNT(*) cnt FROM schema_name.table_name PARTITION (partition_name1) UNION ALL SELECT COUNT(*) cnt FROM schema_name.table_name PARTITION (partition_name2) ); ``` #### **适用场景** - 数据量较小或允许短间资源占用。 - 需精确行数且统计信息不可信。 --- ### **4. 使用`DBMS_SPACE`包估算** #### **方法说明** 通过存储过程估算表的空间行数,适用于未收集统计信息的大表。 #### **操作示例** ```sql DECLARE l_total_blocks NUMBER; l_total_bytes NUMBER; l_unused_blocks NUMBER; l_unused_bytes NUMBER; l_last_used_extent_file_id NUMBER; l_last_used_extent_block_id NUMBER; l_last_used_block NUMBER; BEGIN DBMS_SPACE.SPACE_USAGE( segment_owner => 'SCHEMA_NAME', segment_name => 'TABLE_NAME', segment_type => 'TABLE', total_blocks => l_total_blocks, total_bytes => l_total_bytes, unused_blocks => l_unused_blocks, unused_bytes => l_unused_bytes, last_used_extent_file_id => l_last_used_extent_file_id, last_used_extent_block_id => l_last_used_extent_block_id, last_used_block => l_last_used_block ); DBMS_OUTPUT.PUT_LINE('有效数据块数: ' || (l_total_blocks - l_unused_blocks)); END; ``` #### **注意事项** - 需结合平均每块行数估算总行数(如方法2)。 - 需要`ANALYZE`权限。 --- ### **5. 通过AWR/ASH报告间接分析** #### **方法说明** 通过历史性能数据(AWR报告)识别高频访问的大表。 ```sql SELECT obj.owner, obj.object_name, SUM(t.logical_reads_delta) AS total_logical_reads FROM dba_hist_seg_stat t JOIN dba_objects obj ON t.obj# = obj.object_id WHERE t.snap_id BETWEEN [start_snap_id] AND [end_snap_id] GROUP BY obj.owner, obj.object_name HAVING SUM(t.logical_reads_delta) > 1e8 -- 根据实际阈值调整 ORDER BY total_logical_reads DESC; ``` --- ### **总结与建议** | **方法** | **优点** | **缺点** | **适用场景** | |------------------------|------------------------------|-----------------------------|---------------------------| | 数据字典查询(`DBA_TABLES`) | 速度快,资源消耗低 | 依赖统计信息效性 | 常规检查,统计信息已更新 | | 块数量估算 | 不依赖统计信息 | 估算误差较大 | 统计信息缺失或过期 | | `COUNT(*)` | 结果精确 | 性能影响大,可能超 | 小表或允许资源占用 | | `DBMS_SPACE` | 直接分析物理存储 | 需要额外计算平均行数 | 未收集统计信息的大表 | | AWR/ASH报告 | 结合性能数据 | 需要历史快照权限 | 性能问题根因分析 | #### **后续优化建议** 1. **分区表设计**:对大表按间或范围分区,提升查询维护效率。 2. **归档策略**:将历史数据迁移至归档表或冷存储。 3. **索引优化**:检查大表的索引是否合理,避免冗余索引。 4. **统计信息维护**:定期收集统计信息,确保查询优化器准确性。 5. **使用外部表或NoSQL**:对日志类数据可考虑外部表或非关系型存储。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值