oracle查询优化

Question

表APPLICATION_DATA_BAK 记录条数为500万，PROTOCOL_GROUP_INFO的记录条数为75
表APPLICATION_DATA_BAKk的字段APPLICATION_PROTOCOL有75种取值，表APPLICATION_DATA_BAKk的字段link_id的有6种取值，字段RECORD_TIME有2880中取值，针对每一种取值，数据分布相对均匀。
现有查询语句：
select to_char(record_time,'yyyy-mm-dd hh24:mi:ss'),SUM(TCP4_BYTE_IN),SUM(TCP4_BYTE_OUT) ,SUM(TCP4_PKTNUM_IN),SUM(TCP4_PKTNUM_OUT) 
from APPLICATION_DATA_BAK A join PROTOCOL_GROUP_INFO B on A.APPLICATION_PROTOCOL = B.PROTOCOL_ID 
where LINK_ID= 342 and GROUP_ID = 1 group by RECORD_TIME order by RECORD_TIME不加任何索引的执行计划为：
2773 rows selected.Elapsed: 00:00:04.86Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=13985 Card=34247 B
          ytes=1267139)   1    0   SORT (GROUP BY) (Cost=13985 Card=34247 Bytes=1267139)
   2    1     HASH JOIN (Cost=13649 Card=34247 Bytes=1267139)
   3    2       TABLE ACCESS (FULL) OF 'PROTOCOL_GROUP_INFO' (TABLE) (
          Cost=3 Card=11 Bytes=88)   4    2       TABLE ACCESS (FULL) OF 'APPLICATION_DATA_BAK' (TABLE)
          (Cost=13643 Card=239728 Bytes=6952112)Statistics
----------------------------------------------------------
        237  recursive calls
          0  db block gets
      61426  consistent gets
      61349  physical reads
          0  redo size
     116706  bytes sent via SQL*Net to client
       2536  bytes received via SQL*Net from client
        186  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
       2773  rows processed对字段APPLICATION_PROTOCOL加bitmap索引后的执行计划为：
2773 rows selected.
Elapsed: 00:00:04.84
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=13985 Card=34247 B
          ytes=1267139)   1    0   SORT (GROUP BY) (Cost=13985 Card=34247 Bytes=1267139)
   2    1     HASH JOIN (Cost=13649 Card=34247 Bytes=1267139)
   3    2       TABLE ACCESS (FULL) OF 'PROTOCOL_GROUP_INFO' (TABLE) (
          Cost=3 Card=11 Bytes=88)   4    2       TABLE ACCESS (FULL) OF 'APPLICATION_DATA_BAK' (TABLE)
          (Cost=13643 Card=239728 Bytes=6952112)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      61372  consistent gets
      61317  physical reads
          0  redo size
     116706  bytes sent via SQL*Net to client
       2536  bytes received via SQL*Net from client
        186  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       2773  rows processed继续在LINK_ID上加bitmap索引后的执行计划为：
Elapsed: 00:01:08.06
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=8098 Card=34247 By
          tes=1267139)   1    0   SORT (GROUP BY) (Cost=8098 Card=34247 Bytes=1267139)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'APPLICATION_DATA_BAK'
          (TABLE) (Cost=7762 Card=3196 Bytes=92684)   3    2       NESTED LOOPS (Cost=7762 Card=34247 Bytes=1267139)
   4    3         TABLE ACCESS (FULL) OF 'PROTOCOL_GROUP_INFO' (TABLE)
           (Cost=3 Card=11 Bytes=88)   5    3         BITMAP CONVERSION (TO ROWIDS)
   6    5           BITMAP AND
   7    6             BITMAP INDEX (SINGLE VALUE) OF 'LINKID_INDEX' (I
          NDEX (BITMAP))   8    6             BITMAP INDEX (SINGLE VALUE) OF 'APPLICATION_PROT
          OCOL_INDEX' (INDEX (BITMAP))
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     115182  consistent gets
     115050  physical reads
          0  redo size
     116706  bytes sent via SQL*Net to client
       2536  bytes received via SQL*Net from client
        186  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       2773  rows processed
从上可知，对字段APPLICATION_PROTOCOL加索引后，性能改善不明显，但对LINK_ID字段加bitmap索引后，性能恶化严重，为了提高该查询语句的性能，应该如何优化，谢谢！

Answer 1

LINK_ID= 342 有多少记录？
GROUP_ID=1 有多少记录？

Answer 2

APPLICATION_DATA_BAK表中LINK_ID= 342的记录条数为238683
GROUP_ID字段属于PROTOCOL_GROUP_INFO表，通过联合查询APPLICATION_DATA_BAK表中GROUP_ID=1的记录条数为400万

Answer 3

1 APPLICATION_DATA_BAK表建立联合索引：(APPLICATION_PROTOCOL,LINK_ID,RECORD_TIME) 
2.exec dbms_stats.gather_table_stats('大写表拥有者','大写表名',cascade=>true) 
3.执行： 
select /*+ ordered use_nl(B,A)*/ 
to_char(A.record_time,'yyyy-mm-dd hh24:mi:ss'), 
SUM(A.TCP4_BYTE_IN), 
SUM(A.TCP4_BYTE_OUT), 
SUM(A.TCP4_PKTNUM_IN), 
SUM(A.TCP4_PKTNUM_OUT) 
from PROTOCOL_GROUP_INFO B,APPLICATION_DATA_BAK A 
where A.APPLICATION_PROTOCOL = B.PROTOCOL_ID 
and A.LINK_ID= 342 
and A.GROUP_ID = 1 
group by A.RECORD_TIME 
order by A.RECORD_TIME2773 rows selected.Elapsed: 00:00:07.25Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=288234 Card=35185
          Bytes=1301845)   1    0   SORT (GROUP BY) (Cost=288234 Card=35185 Bytes=1301845)
   2    1     NESTED LOOPS (Cost=287890 Card=35185 Bytes=1301845)
   3    2       TABLE ACCESS (FULL) OF 'APPLICATION_DATA_BAK' (TABLE)
          (Cost=13644 Card=249580 Bytes=7237820)   4    2       TABLE ACCESS (FULL) OF 'PROTOCOL_GROUP_INFO' (TABLE) (
          Cost=1 Card=1 Bytes=8)Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
    1732146  consistent gets
      61345  physical reads
          0  redo size
     116706  bytes sent via SQL*Net to client
       2536  bytes received via SQL*Net from client
        186  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       2773  rows processed

Answer 4

怎么还走fts？统计信息收集了吗？把其他索引删掉。select /*+ ordered use_nl(B,A) index (a)*/ 
...

Answer 5

Elapsed: 00:00:04.86 
这样的速度,你优化的目标是什么? 如果要速度要快,另外建立一个汇总表,通过TRIGGER把数据汇总过去.

Answer 6

字段link_id的有6种,如果比较少而范围确定的化,依照此栏位进行PARTITION设计,也会改善性能. 当数据量比较大的时候,
取得好的性能,不仅仅从SQL上着手.