经开发人员反应,有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。此时表的统计信息正常