|
经开发人员反应，有3张空表的查询非常满，至少是1分钟，我这里就列举这3张表的其中一个作为例子。
set autotrace on
select * from mpac.j_t_detect_met_rslt
2 /
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1056217659
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 33M| 11G| 383K (2)| 01:16:45 |
| 1| TABLE ACCESS FULL| J_T_DETECT_MET_RSLT| 33M| 11G| 383K (2)| 01:16:45 |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1543023 consistent gets
637269 physical reads
0 redo size
13350 bytes sent via SQL*Net toclient
481 bytes received via SQL*Netfrom client
1 SQL*Net roundtrips to/fromclient
0 sorts (memory)
0 sorts (disk)
0 rows processed
表内没有数据，但是逻辑读1543023，物理读637269，很明显，这是一个高HWM表，查询慢是肯定的。从业务上分析，它肯定是之前进行过大量dml操作的表，例如一个insert语句，很有可能在表所占用的块的下一个块已经被其他表占用了，块不具有连续性，所以数据被零星的分配到了整个表空间，如果仅仅是delete行，表所标记占用的块还是那么多，只是块内没有数据。
SQL> truncate table mpac.j_t_detect_met_rslt
2 /
Table truncated.
SQL> select * from mpac.j_t_detect_met_rslt;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1056217659
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 33M| 11G| 383K (2)| 01:16:45 |
| 1| TABLE ACCESS FULL| J_T_DETECT_MET_RSLT| 33M| 11G| 383K (2)| 01:16:45 |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
1 db block gets
5 consistent gets
0 physical reads
96 redo size
13350 bytes sent via SQL*Net toclient
481 bytes received via SQL*Netfrom client
1 SQL*Net roundtrips to/fromclient
0 sorts (memory)
0 sorts (disk)
0 rows processed
truncate表后再次查看执行计划，逻辑读物理读已经非常低了，查询表时间在ms级。但是从Rows ，Bytes这2列看出，读取行33M，总数据量是11G，表明这个表的统计信息仍然是不恰当的
重新收集此表的统计信息：
exec dbms_stats.gather_table_stats('mpac',' j_t_detect_met_rslt ');
再次查看执行计划
select * frommpac.j_t_detect_met_rslt;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value:1056217659
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3536 | 2857 (1)| 00:00:35 |
| 1 | TABLE ACCESS FULL| J_T_DETECT_MET_RSLT | 1 | 3536 | 2857 (1)| 00:00:35 |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
1 db block gets
5 consistent gets
0 physical reads
96 redo size
13350 bytes sent via SQL*Net to client
481 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/fromclient
0 sorts (memory)
0 sorts (disk)
0 rows processed
0 rows processed
Rows为1行（是存储的此表的数据字典，还是为行记录预留的1行还需要进一步证实），Bytes 3536。此时表的统计信息正常
|
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31461640/viewspace-2140319/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
