求助，请问除了count(*)和查看user_tables等系统表，还有没有其他办法获取到数据库里每张表的记录数？因为数据量太大所以count太慢。。

linsuren 2017-09-14 04:30:25
我需要维护大概二十多个数据库，每个数据库表的记录都很多，大概几千万的样子，而且不断地会有新数据插入进来，我的工作就是监控每个数据库，获取到每张表每次增加的数据，存到另外一张单独的表中。
我的思路是隔几小时遍历一次每个库的每张表，对其count然后存到另外一张专门用于维护这些信息的表，然而有的表数据量太大，count需要很长很长时间，二三十分钟的样子，目前一共1000多张表，程序运行了一夜也没有把所有表统计完。所以请教大家还有没有其他比较快点的替代方法？
...全文
1041 17 打赏 收藏 转发到动态 举报
写回复
用AI写文章
17 条回复
切换为时间正序
请发表友善的回复…
发表回复
linsuren 2017-09-20
  • 打赏
  • 举报
 回复
引用 9 楼 minsic78 的回复:
[quote=引用 7 楼 linsuren 的回复:] 还有朋友有好办法吗
负责任地告诉你：没了 实际上在监控表上创建物化视图日志应该是最方便的方法了——除非应用做出配合，插入两份数据，但是我想想，连物化视图日志也无法配合创建，那么这就属于馊主意了。 其他方法就更加麻烦咯，比如logminer解析redo日志，动作更大，更需要源数据库端的配合，维护成本更高～[/quote] 谢谢你，我问了我们这边的DBA，也是这样说，确实没有更好的方法。。。
linsuren 2017-09-20
  • 打赏
  • 举报
 回复
引用 8 楼 qq646748739 的回复:
user_tables里面有个字段： num_rows （表记录数统计） 根据你的实际情况，完全可以参考下。
这个表的num_rows不是实时更新的，必须分析一下表才行，然而分析表耗时可能更长。。。不过还是谢谢你
minsic78 2017-09-19
  • 打赏
  • 举报
 回复
顺便问个问题：这里不能自己编辑自己的帖子？需要什么权限才可以？
minsic78 2017-09-19
  • 打赏
  • 举报
 回复
不好意思，上贴的总结部分逻辑IO算的有问题，少数了个0 导致结论部分有点问题，这里纠正下： 1）若没有主键 1a）直接count花费时间为：00:00:00.78，执行计划中，有对原表一次全表扫描，逻辑读为：158696； 1b）若是创建临时表，总时间为：00:00:03.14 + 00:00:00.44，大概为4秒，select子句包含一次全表扫描是一目了然的事情，就是说，总的逻辑IO为：150000（建临时表对原表扫描） + 8056（对临时表的扫描） = 158000多 2）若是有主键 2a）直接count花费时间为：00:00:00.43，执行计划中对原表的全表扫描消失，只对主键索引来了一次INDEX FAST FULL SCAN，逻辑读为：12061 2b）若是创建临时表，总时间为：00:00:02.40 + 00:00:00.46，大概是3秒半不到点，原来没主键情况下的表扫描被优化成了主键索引的INDEX FAST FULL SCAN，总的逻辑IO：12000 + 8056，大概为 20000稍多 结论：无论是执行时间还是IO，当原表上有主键时，达到最优（2a），执行时间为：00:00:00.43，逻辑IO为：12061。
minsic78 2017-09-19
  • 打赏
  • 举报
 回复
引用 12 楼 wmxcn2000 的回复:
[quote=引用 11 楼 minsic78的回复:][quote=引用 10 楼 wmxcn2000 的回复:] 楼主试一下 create table tmp as select 主键列 from t 再去count 一下 tmp表。
如果原表有主键的话，没必要啊，直接就是主键索引的INDEX FAST FULL SCAN解决问题，这个操作的IO与全表扫描建出来的临时表的IO基本相当，但前面还有一次建临时表的操作，意味着又需要快速全扫一次主键索引，总IO是直接count的两倍。如果没有主键，那么建临时表一次原表全表扫描，count一次全表扫描临时表，而直接count就是一次原表全表扫描，主键列相当于还是扫描了两次。 不知道建临时表再count的出发点是什么 [/quote] ctas 的速度会超出想象。[/quote] 来看下这个测试，也就是我上面提到的有无主键下两种情况：
引用
--创建测试“大表”，总记录数500万+ SQL> create table test_count as select * from dba_objects; Table created. Elapsed: 00:00:01.31 SQL> select count(*) from test_count; COUNT(*) ---------- 163942 Elapsed: 00:00:00.15 SQL> insert into test_count select * from test_count; 163942 rows created. Elapsed: 00:00:00.53 SQL> / 327884 rows created. Elapsed: 00:00:01.55 SQL> / 655768 rows created. Elapsed: 00:00:02.91 SQL> / 1311536 rows created. Elapsed: 00:00:36.90 SQL> / 2623072 rows created. Elapsed: 00:01:12.19 SQL> commit; Commit complete. Elapsed: 00:00:00.01 --添加一列作为主键候选 SQL> alter table test_count add pk number; Table altered. Elapsed: 00:00:00.10 SQL> update test_count set pk=rownum; 5246144 rows updated. Elapsed: 00:08:08.96 SQL> commit; Commit complete. Elapsed: 00:00:00.01 SQL> select count(*) from test_count; COUNT(*) ---------- 5246144 Elapsed: 00:00:01.07 SSQL> select min(pk),max(pk) from test_count; MIN(PK) MAX(PK) ---------- ---------- 1 5246144 Elapsed: 00:00:01.56 --没有主键 SQL> set autotrace trace SQL> select count(*) from test_count; Elapsed: 00:00:00.78 Execution Plan ---------------------------------------------------------- Plan hash value: 1844084873 ------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 21630 (1)| 00:00:05 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| TEST_COUNT | 5392K| 21630 (1)| 00:00:05 | ------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 1 db block gets 158696 consistent gets 79221 physical reads 0 redo size 529 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> create table tt as select pk from test_count; Table created. Elapsed: 00:00:03.14 SQL> select count(*) from tt; Elapsed: 00:00:00.44 Execution Plan ---------------------------------------------------------- Plan hash value: 3133740314 ------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2264 (3)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| TT | 5588K| 2264 (3)| 00:00:01 | ------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 9 recursive calls 0 db block gets 8068 consistent gets 8210 physical reads 0 redo size 529 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed --添加主键 SQL> alter table test_count add constraint pk_constraint primary key (pk); Table altered. Elapsed: 00:01:30.37 SQL> select count(*) from test_count; Elapsed: 00:00:00.43 Execution Plan ---------------------------------------------------------- Plan hash value: 2318785894 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3224 (2)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FAST FULL SCAN| PK_CONSTRAINT | 5392K| 3224 (2)| 00:00:01 | ------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 46 recursive calls 1 db block gets 12061 consistent gets 11607 physical reads 0 redo size 529 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 6 sorts (memory) 0 sorts (disk) 1 rows processed SQL> drop table tt; Table dropped. Elapsed: 00:00:00.05 SQL> create table tt as select pk from test_count; Table created. Elapsed: 00:00:02.40 SQL> select count(*) from tt; Elapsed: 00:00:00.46 Execution Plan ---------------------------------------------------------- Plan hash value: 3133740314 ------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2253 (3)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| TT | 4572K| 2253 (3)| 00:00:01 | ------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 9 recursive calls 0 db block gets 8056 consistent gets 8149 physical reads 0 redo size 529 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
可以看到： 1）若没有主键 1a）直接count花费时间为：00:00:00.78，执行计划中，对原表一次全表扫描，逻辑读为：158696； 1b）若是创建临时表，总时间为：00:00:03.14 + 00:00:00.44，大概为4秒，select子句包含一次全表扫描是一目了然的事情，也 就是说，总的逻辑IO为：15000（建临时表对原表扫描） + 8056（对临时表的扫描） = 23000多 2）若是有主键 2a）直接count花费时间为：00:00:00.43，执行计划中对原表的全表扫描消失，只对主键索引来了一次INDEX FAST FULL SCA N，逻辑读为：12061 2b）若是创建临时表，总时间为：00:00:02.40 + 00:00:00.46，大概是3秒半不到点，原来没主键情况下的表扫描被优化成了主 键索引的INDEX FAST FULL SCAN，总的逻辑IO：12000 + 8056，大概为 20000稍多 结论：无论是执行时间还是IO，当原表上有主键时，达到最优（2a），执行时间为：00:00:00.43，逻辑IO为：12061；而若是使用临时表，甚至不如直接count全表（1a）。 这个测试里也可以看到：500万级的统计，即使直接count全表，也不会慢到哪去，前提当然是列并不多，dba_objects这样的表的行长度应该说还是比较有代表性的，楼主有些千万级别的表，但他的问题不在于count了全表，而是先一步试图收集那些表的统计信息，想通过user_tables里保存的统计信息来记录统计数据条数，收集统计信息会对原表做复杂的统计，可能包含更多次的表扫描，这样的处理，将问题大大复杂化了。
碧水幽幽泉 2017-09-18
  • 打赏
  • 举报
 回复
user_tables里面有个字段： num_rows （表记录数统计）
根据你的实际情况，完全可以参考下。
卖水果的net 2017-09-18
  • 打赏
  • 举报
 回复
引用 11 楼 minsic78的回复:
[quote=引用 10 楼 wmxcn2000 的回复:] 楼主试一下 create table tmp as select 主键列 from t 再去count 一下 tmp表。
如果原表有主键的话，没必要啊，直接就是主键索引的INDEX FAST FULL SCAN解决问题，这个操作的IO与全表扫描建出来的临时表的IO基本相当，但前面还有一次建临时表的操作，意味着又需要快速全扫一次主键索引，总IO是直接count的两倍。如果没有主键，那么建临时表一次原表全表扫描，count一次全表扫描临时表，而直接count就是一次原表全表扫描，主键列相当于还是扫描了两次。 不知道建临时表再count的出发点是什么 [/quote] ctas 的速度会超出想象。
minsic78 2017-09-18
  • 打赏
  • 举报
 回复
引用 10 楼 wmxcn2000 的回复:
楼主试一下 create table tmp as select 主键列 from t 再去count 一下 tmp表。
如果原表有主键的话，没必要啊，直接就是主键索引的INDEX FAST FULL SCAN解决问题，这个操作的IO与全表扫描建出来的临时表的IO基本相当，但前面还有一次建临时表的操作，意味着又需要快速全扫一次主键索引，总IO是直接count的两倍。如果没有主键，那么建临时表一次原表全表扫描，count一次全表扫描临时表，而直接count就是一次原表全表扫描，主键列相当于还是扫描了两次。 不知道建临时表再count的出发点是什么
卖水果的net 2017-09-18
  • 打赏
  • 举报
 回复
楼主试一下 create table tmp as select 主键列 from t 再去count 一下 tmp表。
minsic78 2017-09-18
  • 打赏
  • 举报
 回复
引用 7 楼 linsuren 的回复:
还有朋友有好办法吗
负责任地告诉你：没了 实际上在监控表上创建物化视图日志应该是最方便的方法了——除非应用做出配合，插入两份数据，但是我想想，连物化视图日志也无法配合创建，那么这就属于馊主意了。 其他方法就更加麻烦咯，比如logminer解析redo日志，动作更大，更需要源数据库端的配合，维护成本更高～
linsuren 2017-09-15
  • 打赏
  • 举报
 回复
还有朋友有好办法吗
linsuren 2017-09-14
  • 打赏
  • 举报
 回复
引用 5 楼 minsic78 的回复:
[quote=引用 4 楼 linsuren 的回复:] [quote=引用 2 楼 minsic78 的回复:] 另外，如果非想用count，也不要查user_tables，这张表里的num_rows基本不准，因为它是对表收集统计信息的时候才更新的。 非想用count，可以考虑在表的非空字段，比如主键字段上建条索引，count的时候，CBO会将整表扫描转移到这条索引的扫描上来，从而节省你count的IO和时间。
嗯嗯，我都是先执行一下 analyze table tablename compute statistics for table，然后再查user_tables，但是后来发现 analyze table 也需要很长的时间。然后这些数据库我只有读的权限，所以加字段、触发器之类的也就行不通了。。。[/quote] analyze本质上就是在表上做各种统计操作，扫描整表是逃不掉的，表记录数一多，自然就完蛋了。 如果权限有限，恐怕很难搞了。[/quote] 确实很难，困扰好多天了。。
minsic78 2017-09-14
  • 打赏
  • 举报
 回复
引用 4 楼 linsuren 的回复:
[quote=引用 2 楼 minsic78 的回复:] 另外，如果非想用count，也不要查user_tables，这张表里的num_rows基本不准，因为它是对表收集统计信息的时候才更新的。 非想用count，可以考虑在表的非空字段，比如主键字段上建条索引，count的时候，CBO会将整表扫描转移到这条索引的扫描上来，从而节省你count的IO和时间。
嗯嗯，我都是先执行一下 analyze table tablename compute statistics for table，然后再查user_tables，但是后来发现 analyze table 也需要很长的时间。然后这些数据库我只有读的权限，所以加字段、触发器之类的也就行不通了。。。[/quote] analyze本质上就是在表上做各种统计操作，扫描整表是逃不掉的，表记录数一多，自然就完蛋了。 如果权限有限，恐怕很难搞了。
linsuren 2017-09-14
  • 打赏
  • 举报
 回复
引用 2 楼 minsic78 的回复:
另外，如果非想用count，也不要查user_tables，这张表里的num_rows基本不准，因为它是对表收集统计信息的时候才更新的。 非想用count，可以考虑在表的非空字段，比如主键字段上建条索引，count的时候，CBO会将整表扫描转移到这条索引的扫描上来，从而节省你count的IO和时间。
嗯嗯，我都是先执行一下 analyze table tablename compute statistics for table，然后再查user_tables，但是后来发现 analyze table 也需要很长的时间。然后这些数据库我只有读的权限，所以加字段、触发器之类的也就行不通了。。。
minsic78 2017-09-14
  • 打赏
  • 举报
 回复
引用 2 楼 minsic78 的回复:
另外，如果非想用count，也不要查user_tables，这张表里的num_rows基本不准，因为它是对表收集统计信息的时候才更新的。 非想用count，可以考虑在表的非空字段，比如主键字段上建条索引，count的时候，CBO会将整表扫描转移到这条索引的扫描上来，从而节省你count的IO和时间。
对了，如果已经有主键，那么索引就不需要建了，因为主键创建的时候一条唯一索引就已经被隐含创建了。
minsic78 2017-09-14
  • 打赏
  • 举报
 回复
另外，如果非想用count，也不要查user_tables，这张表里的num_rows基本不准，因为它是对表收集统计信息的时候才更新的。 非想用count，可以考虑在表的非空字段，比如主键字段上建条索引，count的时候，CBO会将整表扫描转移到这条索引的扫描上来，从而节省你count的IO和时间。
minsic78 2017-09-14
  • 打赏
  • 举报
 回复
物化视图日志可以借用下，如果不想用这个，或者可以给原表加个时间戳之类的标识字段
JAVAWEB-NOTE01：查询正则表达式网址
连接mysql服务器数据库及表操作创建、删除、查看数据库(注意删数据库不要把它系统自带的数据库删除 mysql,information_schema, performance_schema)创建、删除、查看表 (对表进行操作需要先进入到数据库)新增、更新...
数据库系统原理 笔记整理
数据库系统原理 ╭ 第一章 数据库系统概述 │ 第二章 关系数据库 │ 第三章 数据库设计 │ 第四章 SQL与关系数据库基本操作 数据库系统原理< │ 第五章 数据库编程 │ 第六章 数据库安全与保护 │ 第七章...
JAVA2.1
连接mysql服务器数据库及表操作创建、删除、查看数据库创建、删除、查看表新增、更新、删除表记录查询表记录基础查询WHERE子句查询模糊查询多行函数查询分组查询排序查询分页查询其他函数mysql的数据类型数值类型...
速达5000进销存结账数据转换出错解决方案及替换文件处理
htmltable {th, td {th {pre {简介：速达5000是...本文针对该问题深入分析可能的成因，包括pk_bill.bpl文件损坏、数据库错误、版本不兼容等，并提供完整的解决路径，如备份恢复、软件修复、数据库检查和技术支持求助。
Oracle性能查看
6、根据v.sid查看对应连接的资源占用等情况 SELECT n.NAME, v.VALUE, n.CLASS, n.Statistic# FROM V$statname n, V$sesstat v WHERE v.Sid = &sid AND v.Statistic# = n.Statistic# ORDER BY n.CLASS, n....
Oracle

17,137

社区成员

55,257

社区内容

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章