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

linsuren 2017-09-14 04:30:25

我需要维护大概二十多个数据库，每个数据库表的记录都很多，大概几千万的样子，而且不断地会有新数据插入进来，我的工作就是监控每个数据库，获取到每张表每次增加的数据，存到另外一张单独的表中。
我的思路是隔几小时遍历一次每个库的每张表，对其count然后存到另外一张专门用于维护这些信息的表，然而有的表数据量太大，count需要很长很长时间，二三十分钟的样子，目前一共1000多张表，程序运行了一夜也没有把所有表统计完。所以请教大家还有没有其他比较快点的替代方法？

...全文

1002 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表。

[/quote] ctas 的速度会超出想象。

minsic78 2017-09-18

打赏
举报

引用 10 楼 wmxcn2000 的回复:

楼主试一下 create table tmp as select 主键列 from t 再去count 一下 tmp表。

卖水果的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和时间。

linsuren 2017-09-14