首页 > 代码库 > 甘肃PON数据库分区列范围查询优化

甘肃PON数据库分区列范围查询优化

查询慢的SQL:
  1. with p as(
  2. select np.nodecode , np.nodename, d.deviceid, d.devicename, d.loopaddress, p.respara, p.rxcrcerror, p.txcrcerror
  3. from perf_t_ponport p,device d, node c,node np
  4. where p.resid = d.deviceid
  5. and d.nodecode = c.nodecode
  6. and c.citynodecode = np.nodecode
  7. and c.nodecode in (SELECT S.NODECODE FROM SINGLEUSERNODEAUTH S WHERE S.NETUSERID = ‘admin‘ AND S.AUTHTYPE = ‘VIEW‘)
  8. and d.changetype = 0
  9. and p.coltime between trunc(sysdate-1,‘dd‘) and trunc(sysdate,‘dd‘)
  10. and p.rxcrcerror is not null
  11. and p.rxcrcerror >0
  12. order by p.rxcrcerror desc
  13. )
  14. select *
  15. from p
  16. where rownum <=10
这个SQL执行花费了1分钟24秒,需要优化。


查看执行计划:
  1. SQL> explain plan for with p as(
  2. 2 select np.nodecode , np.nodename, d.deviceid, d.devicename, d.loopaddress, p.respara, p.rx
  3. crcerror, p.txcrcerror
  4. 3 from perf_t_ponport p,device d, node c,node np
  5. 4 where p.resid = d.deviceid
  6. 5 and d.nodecode = c.nodecode
  7. 6 and c.citynodecode = np.nodecode
  8. 7 and c.nodecode in (SELECT S.NODECODE FROM SINGLEUSERNODEAUTH S WHERE S.NETUSERID = ‘adm
  9. in‘ AND S.AUTHTYPE = ‘VIEW‘)
  10. 8 and d.changetype = 0
  11. 9 and p.coltime between trunc(sysdate-1,‘dd‘) and trunc(sysdate,‘dd‘)
  12. 10 and p.rxcrcerror is not null
  13. 11 and p.rxcrcerror >0
  14. 12 order by p.rxcrcerror desc
  15. 13 )
  16. 14 select *
  17. 15 from p
  18. 16 where rownum <=10
  19. 17 ;
  20. 已解释。
  21. SQL> select * from table(dbms_xplan.display);
  22. PLAN_TABLE_OUTPUT
  23. ----------------------------------------------------------------------------------------------------
  24. Plan hash value: 733587010
  25. ----------------------------------------------------------------------------------------------------
  26. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
  27. ----------------------------------------------------------------------------------------------------
  28. | 0 | SELECT STATEMENT | | 10 | 3430 | 1125K (19)| 03:45:09 | | |
  29. |* 1 | COUNT STOPKEY | | | | | | | |
  30. | 2 | VIEW | | 133 | 45619 | 1125K (19)| 03:45:09 | | |
  31. |* 3 | SORT ORDER BY STOPKEY | | 133 | 22743 | 1125K (19)| 03:45:09 | | |
  32. |* 4 | FILTER | | | | | | | |
  33. |* 5 | HASH JOIN | | 133 | 22743 | 1125K (19)| 03:45:09 | | |
  34. |* 6 | HASH JOIN SEMI | | 133 | 19950 | 1125K (19)| 03:45:08 | | |
  35. |* 7 | HASH JOIN | | 219 | 28251 | 1125K (19)| 03:45:08 | | |
  36. | 8 | NESTED LOOPS | | 219 | 25185 | 1125K (19)| 03:45:08 | | |
  37. | 9 | PARTITION RANGE ITERATOR | | 219 | 10074 | 1125K (19)| 03:45:03 | KEY | KEY |
  38. |* 10 | TABLE ACCESS FULL | PERF_T_PONPORT | 219 | 10074 | 1125K (19)| 03:45:03 | KEY | KE
  39. | 11 | TABLE ACCESS BY INDEX ROWID| DEVICE | 1 | 69 | 2 (0)| 00:00:01 | |
  40. |* 12 | INDEX UNIQUE SCAN | PK_DEVICE | 1 | | 1 (0)| 00:00:01 | |
  41. | 13 | TABLE ACCESS FULL | NODE | 1214 | 16996 | 8 (0)| 00:00:01 | | |
  42. |* 14 | INDEX RANGE SCAN | SYS_C00543203 | 1167 | 24507 | 10 (0)| 00:00:01 | |
  43. | 15 | TABLE ACCESS FULL | NODE | 1214 | 25494 | 8 (0)| 00:00:01 | |
  44. ----------------------------------------------------------------------------------------------------
  45. Predicate Information (identified by operation id):
  46. ---------------------------------------------------
  47. 1 - filter(ROWNUM<=10)
  48. 3 - filter(ROWNUM<=10)
  49. 4 - filter(TRUNC(SYSDATE@!-1,‘fmdd‘)<=TRUNC(SYSDATE@!,‘fmdd‘))
  50. 5 - access("C"."CITYNODECODE"="NP"."NODECODE")
  51. 6 - access("C"."NODECODE"="S"."NODECODE")
  52. 7 - access("D"."NODECODE"="C"."NODECODE")
  53. 10 - filter("P"."RXCRCERROR" IS NOT NULL AND "P"."RXCRCERROR">0 AND
  54. "P"."COLTIME">=TRUNC(SYSDATE@!-1,‘fmdd‘) AND "P"."COLTIME"<=TRUNC(SYSDATE@!,‘fmdd‘))
  55. 12 - access("P"."RESID"="D"."DEVICEID" AND "D"."CHANGETYPE"=0)
  56. 14 - access("S"."NETUSERID"=‘admin‘ AND "S"."AUTHTYPE"=‘VIEW‘)
  57. 已选择36行。
查看执行计划:id为4的filter过滤条件为filter(TRUNC(SYSDATE@!-1,‘fmdd‘)<=TRUNC(SYSDATE@!,‘fmdd‘))。 查看id为9的执行计划访问分区的方式为:PARTITION RANGE ITERATOR。 说明分区表PERF_T_PONPORT的分区列的条件导致了性能压力。
coltime的列都是yyyy/mm/dd的格式,因此可以将PERF_T_PONPORT的分区列的条件条件改写为:(p.coltime =trunc(sysdate-1,‘dd‘) or p.coltime =trunc(sysdate,‘dd‘) )。

改写后的语句:
  1. with p as(
  2. select np.nodecode , np.nodename, d.deviceid, d.devicename, d.loopaddress, p.respara, p.rxcrcerror, p.txcrcerror
  3. from perf_t_ponport p,device d, node c,node np
  4. where p.resid = d.deviceid
  5. and d.nodecode = c.nodecode
  6. and c.citynodecode = np.nodecode
  7. and d.changetype = 0
  8. and c.nodecode in (SELECT S.NODECODE FROM SINGLEUSERNODEAUTH S WHERE S.NETUSERID = ‘admin‘ AND S.AUTHTYPE = ‘VIEW‘)
  9. and (p.coltime =trunc(sysdate-1,‘dd‘) or p.coltime =trunc(sysdate,‘dd‘) )
  10. and p.rxcrcerror is not null
  11. and p.rxcrcerror >0
  12. order by p.rxcrcerror desc
  13. )
  14. select *
  15. from p
  16. where rownum <=10;

改写后的执行计划:
  1. SQL> explain plan for with p as(
  2. 2 select np.nodecode , np.nodename, d.deviceid, d.devicename, d.loopaddress, p.respara, p.r
  3. xcrcerror, p.txcrcerror
  4. 3 from perf_t_ponport p,device d, node c,node np
  5. 4 where p.resid = d.deviceid
  6. 5 and d.nodecode = c.nodecode
  7. 6 and c.citynodecode = np.nodecode
  8. 7 and d.changetype = 0
  9. 8 and c.nodecode in (SELECT S.NODECODE FROM SINGLEUSERNODEAUTH S WHERE S.NETUSERID = ‘admin
  10. AND S.AUTHTYPE = ‘VIEW‘)
  11. 9 and (p.coltime =trunc(sysdate-1,‘dd‘) or p.coltime =trunc(sysdate,‘dd‘) )
  12. 10 and p.rxcrcerror is not null
  13. 11 and p.rxcrcerror >0
  14. 12 order by p.rxcrcerror desc
  15. 13 )
  16. 14 select *
  17. 15 from p
  18. 16 where rownum <=10;
  19. 已解释。
  20. SQL>
  21. SQL> select * from table(dbms_xplan.display);
  22. PLAN_TABLE_OUTPUT
  23. ----------------------------------------------------------------------------------------------------
  24. Plan hash value: 2287749996
  25. ----------------------------------------------------------------------------------------------------
  26. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
  27. ----------------------------------------------------------------------------------------------------
  28. | 0 | SELECT STATEMENT | | 10 | 3430 | 915 (2)| 00:00:11 | | |
  29. |* 1 | COUNT STOPKEY | | | | | | | |
  30. | 2 | VIEW | | 266 | 91238 | 915 (2)| 00:00:11 | | |
  31. |* 3 | SORT ORDER BY STOPKEY | | 266 | 45486 | 915 (2)| 00:00:11 | | |
  32. |* 4 | HASH JOIN | | 266 | 45486 | 915 (2)| 00:00:11 | | |
  33. | 5 | TABLE ACCESS FULL | NODE | 1214 | 25494 | 8 (0)| 00:00:01 | | |
  34. |* 6 | HASH JOIN RIGHT SEMI | | 267 | 40050 | 905 (2)| 00:00:11 | | |
  35. |* 7 | INDEX RANGE SCAN | SYS_C00543203 | 1167 | 24507 | 10 (0)| 00:00:01 | |
  36. |* 8 | HASH JOIN | | 437 | 56373 | 895 (2)| 00:00:11 | | |
  37. | 9 | TABLE ACCESS FULL | NODE | 1214 | 16996 | 8 (0)| 00:00:01 | | |
  38. | 10 | NESTED LOOPS | | 437 | 50255 | 885 (2)| 00:00:11 | | |
  39. | 11 | PARTITION RANGE INLIST | | 437 | 20102 | 2 (0)| 00:00:01 |KEY(I) |KEY(I) |
  40. |* 12 | TABLE ACCESS FULL | PERF_T_PONPORT | 437 | 20102 | 2 (0)| 00:00:01 |KEY(I) |KEY(I)
  41. | 13 | TABLE ACCESS BY INDEX ROWID| DEVICE | 1 | 69 | 2 (0)| 00:00:01 | | |
  42. |* 14 | INDEX UNIQUE SCAN | PK_DEVICE | 1 | | 1 (0)| 00:00:01 | | |
  43. ----------------------------------------------------------------------------------------------------
  44. Predicate Information (identified by operation id):
  45. ---------------------------------------------------
  46. 1 - filter(ROWNUM<=10)
  47. 3 - filter(ROWNUM<=10)
  48. 4 - access("C"."CITYNODECODE"="NP"."NODECODE")
  49. 6 - access("C"."NODECODE"="S"."NODECODE")
  50. 7 - access("S"."NETUSERID"=‘admin‘ AND "S"."AUTHTYPE"=‘VIEW‘)
  51. 8 - access("D"."NODECODE"="C"."NODECODE")
  52. 12 - filter("P"."RXCRCERROR" IS NOT NULL AND "P"."RXCRCERROR">0 AND
  53. ("P"."COLTIME"=TRUNC(SYSDATE@!-1,‘fmdd‘) OR "P"."COLTIME"=TRUNC(SYSDATE@!,‘fmdd‘)))
  54. 14 - access("P"."RESID"="D"."DEVICEID" AND "D"."CHANGETYPE"=0)
  55. 已选择34行。

改写后, SQL执行花了9秒钟, 性能提升了9倍。改写后的SQL消除了filter, 访问分区的方式为 PARTITION RANGE INLIST 。

关于几种分区访问的方式:
范围分区:
1、PARTITION RANGE ITERATOR
  1. SQL> explain plan for select * from perf_t_ponport p where p.coltime <trunc(sysdate,‘dd‘);
  2. 已解释。
  3. SQL> select * from table(dbms_xplan.display);
  4. PLAN_TABLE_OUTPUT
  5. ----------------------------------------------------------------------------------------------------
  6. Plan hash value: 4015217925
  7. ----------------------------------------------------------------------------------------------------
  8. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
  9. ----------------------------------------------------------------------------------------------------
  10. | 0 | SELECT STATEMENT | | 100 | 4600 | 2 (0)| 00:00:01 | | |
  11. | 1 | PARTITION RANGE ITERATOR| | 100 | 4600 | 2 (0)| 00:00:01 | 1 | KEY |
  12. |* 2 | TABLE ACCESS FULL | PERF_T_PONPORT | 100 | 4600 | 2 (0)| 00:00:01 | 1 | KEY |
  13. ----------------------------------------------------------------------------------------------------
  14. Predicate Information (identified by operation id):
  15. ---------------------------------------------------
  16. 2 - filter("P"."COLTIME"<TRUNC(SYSDATE@!,‘fmdd‘))
  17. 已选择14行。

  1. SQL> explain plan for select * from perf_t_ponport p where p.coltime > trunc(sysdate-1,‘dd‘) and p.
  2. coltime <trunc(sysdate,‘dd‘);
  3. 已解释。
  4. SQL> select * from table(dbms_xplan.display);
  5. PLAN_TABLE_OUTPUT
  6. ----------------------------------------------------------------------------------------------------
  7. Plan hash value: 1162550374
  8. ----------------------------------------------------------------------------------------------------
  9. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
  10. ----------------------------------------------------------------------------------------------------
  11. | 0 | SELECT STATEMENT | | 100 | 4600 | 1637 (37)| 00:00:20 | | |
  12. |* 1 | FILTER | | | | | | | |
  13. | 2 | PARTITION RANGE ITERATOR| | 100 | 4600 | 1637 (37)| 00:00:20 | KEY | KEY |
  14. |* 3 | TABLE ACCESS FULL | PERF_T_PONPORT | 100 | 4600 | 1637 (37)| 00:00:20 | KEY |
  15. ----------------------------------------------------------------------------------------------------
  16. Predicate Information (identified by operation id):
  17. ---------------------------------------------------
  18. 1 - filter(TRUNC(SYSDATE@!-1,‘fmdd‘)<TRUNC(SYSDATE@!,‘fmdd‘))
  19. 3 - filter("P"."COLTIME">TRUNC(SYSDATE@!-1,‘fmdd‘) AND "P"."COLTIME"<TRUNC(SYSDATE@!,‘fmdd‘))
  20. 已选择16行。

PARTITION RANGE ITERATOR是通过连续的分区迭代来获取数据。


2、PARTITION RANGE SINGLE
  1. SQL> explain plan for select * from perf_t_ponport partition(P_20170307) p;
  2. 已解释。
  3. SQL> select * from table(dbms_xplan.display);
  4. PLAN_TABLE_OUTPUT
  5. ----------------------------------------------------------------------------------------------------
  6. Plan hash value: 1021280532
  7. ----------------------------------------------------------------------------------------------------
  8. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
  9. ----------------------------------------------------------------------------------------------------
  10. | 0 | SELECT STATEMENT | | 100 | 4600 | 2 (0)| 00:00:01 | | |
  11. | 1 | PARTITION RANGE SINGLE| | 100 | 4600 | 2 (0)| 00:00:01 | 85 | 85 |
  12. | 2 | TABLE ACCESS FULL | PERF_T_PONPORT | 100 | 4600 | 2 (0)| 00:00:01 | 85 |
  13. ----------------------------------------------------------------------------------------------------
  14. 已选择9行。


  1. SQL> explain plan for select * from perf_t_ponport p where p.coltime = trunc(sysdate-1,‘dd‘) and p.
  2. coltime <trunc(sysdate,‘dd‘);
  3. 已解释。
  4. SQL> select * from table(dbms_xplan.display);
  5. PLAN_TABLE_OUTPUT
  6. ----------------------------------------------------------------------------------------------------
  7. Plan hash value: 506920385
  8. ----------------------------------------------------------------------------------------------------
  9. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
  10. ----------------------------------------------------------------------------------------------------
  11. | 0 | SELECT STATEMENT | | 100 | 4600 | 8 (25)| 00:00:01 | | |
  12. |* 1 | FILTER | | | | | | | |
  13. | 2 | PARTITION RANGE SINGLE| | 100 | 4600 | 8 (25)| 00:00:01 | KEY | KEY |
  14. |* 3 | TABLE ACCESS FULL | PERF_T_PONPORT | 100 | 4600 | 8 (25)| 00:00:01 | KEY | KE
  15. ----------------------------------------------------------------------------------------------------
  16. Predicate Information (identified by operation id):
  17. ---------------------------------------------------
  18. 1 - filter(TRUNC(SYSDATE@!-1,‘fmdd‘)<TRUNC(SYSDATE@!,‘fmdd‘))
  19. 3 - filter("P"."COLTIME"=TRUNC(SYSDATE@!-1,‘fmdd‘) AND "P"."COLTIME"<TRUNC(SYSDATE@!,‘fmdd‘))
  20. 已选择16行。

PARTITION RANGE SINGLE只扫描一个分区。

3、PARTITION RANGE INLIST

  1. SQL> explain plan for select * from perf_t_ponport p where p.coltime = trunc(sysdate-1,‘dd‘) or p.c
  2. oltime = trunc(sysdate,‘dd‘);
  3. 已解释。
  4. SQL> select * from table(dbms_xplan.display);
  5. PLAN_TABLE_OUTPUT
  6. ----------------------------------------------------------------------------------------------------
  7. Plan hash value: 2473962793
  8. ----------------------------------------------------------------------------------------------------
  9. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
  10. ----------------------------------------------------------------------------------------------------
  11. | 0 | SELECT STATEMENT | | 100 | 4600 | 2 (0)| 00:00:01 | | |
  12. | 1 | PARTITION RANGE INLIST| | 100 | 4600 | 2 (0)| 00:00:01 |KEY(I) |KEY(I) |
  13. |* 2 | TABLE ACCESS FULL | PERF_T_PONPORT | 100 | 4600 | 2 (0)| 00:00:01 |KEY(I) |KEY(I)
  14. ----------------------------------------------------------------------------------------------------
  15. Predicate Information (identified by operation id):
  16. ---------------------------------------------------
  17. 2 - filter("P"."COLTIME"=TRUNC(SYSDATE@!-1,‘fmdd‘) OR "P"."COLTIME"=TRUNC(SYSDATE@!,‘fmdd‘))
  18. 已选择14行。

上面的KEY(I)就是KEY(INLIST)的意思。PARTITION RANGE INLIST无法通过Pstart和Pstop看出分区的起始和结束位置 。


4、ARTITION RANGE ALL和PARTITION RANGE OR
  1. SQL> explain plan for select * from perf_t_ponport p where p.coltime >trunc(sysdate-1,‘dd‘) or p.c
  2. oltime < trunc(sysdate-5,‘dd‘);
  3. 已解释。
  4. SQL> select * from table(dbms_xplan.display);
  5. PLAN_TABLE_OUTPUT
  6. ----------------------------------------------------------------------------------------------------
  7. Plan hash value: 3647984539
  8. ----------------------------------------------------------------------------------------------------
  9. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
  10. ----------------------------------------------------------------------------------------------------
  11. | 0 | SELECT STATEMENT | | 100 | 4600 | 2 (0)| 00:00:01 | | |
  12. | 1 | PARTITION RANGE ALL| | 100 | 4600 | 2 (0)| 00:00:01 | 1 | 101 |
  13. |* 2 | TABLE ACCESS FULL | PERF_T_PONPORT | 100 | 4600 | 2 (0)| 00:00:01 | 1 | 101 |
  14. ----------------------------------------------------------------------------------------------------
  15. Predicate Information (identified by operation id):
  16. ---------------------------------------------------
  17. 2 - filter("P"."COLTIME"<TRUNC(SYSDATE@!-5,‘fmdd‘) OR
  18. "P"."COLTIME">TRUNC(SYSDATE@!-1,‘fmdd‘))
  19. 已选择15行。

ARTITION RANGE ALL是全分区扫描, 上面是在oracle 10g环境测试, 在Oracle 11g环境下,分区扫描方式为:PARTITION RANGE OR, 可以看出11g对这种分区查询条件扫描方式做了优化。
  1. SQL> explain plan for select * from perf_t_ponport p where p.coltime >trunc(sysdate-1,‘dd‘) or p.c
  2. oltime < trunc(sysdate-5,‘dd‘);
  3. 已解释。
  4. SQL> select * from table(dbms_xplan.display);
  5. PLAN_TABLE_OUTPUT
  6. ----------------------------------------------------------------------------------------------------
  7. Plan hash value: 470656424
  8. ----------------------------------------------------------------------------------------------------
  9. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
  10. ----------------------------------------------------------------------------------------------------
  11. | 0 | SELECT STATEMENT | | 1512M| 67G| 3783K (3)| 12:36:37 | | |
  12. | 1 | PARTITION RANGE OR| | 1512M| 67G| 3783K (3)| 12:36:37 |KEY(OR)|KEY(OR)|
  13. |* 2 | TABLE ACCESS FULL| PERF_T_PONPORT | 1512M| 67G| 3783K (3)| 12:36:37 |KEY(OR)|KEY(OR)|
  14. ----------------------------------------------------------------------------------------------------
  15. Predicate Information (identified by operation id):
  16. ---------------------------------------------------
  17. 2 - filter("P"."COLTIME"<TRUNC(SYSDATE@!-5,‘fmdd‘) OR
  18. "P"."COLTIME">TRUNC(SYSDATE@!-1,‘fmdd‘))
  19. Note
  20. -----
  21. - ‘PLAN_TABLE‘ is old version
  22. 已选择19行。






甘肃PON数据库分区列范围查询优化