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

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

2024-09-08 14:04:39   219人阅读

查询慢的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  ;
  21. 已解释。
  23. SQL> select * from table(dbms_xplan.display);
  25. PLAN_TABLE_OUTPUT
  26. ----------------------------------------------------------------------------------------------------
  27. Plan hash value: 733587010
  29. ----------------------------------------------------------------------------------------------------
  30. | Id  | Operation                           | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
  31. ----------------------------------------------------------------------------------------------------
  32. |   0 | SELECT STATEMENT                    |                |    10 |  3430 |  1125K (19)| 03:45:09 |       |       |
  33. |*  1 |  COUNT STOPKEY                      |                |       |       |            |          |       |       |
  34. |   2 |   VIEW                              |                |   133 | 45619 |  1125K (19)| 03:45:09 |       |       |
  35. |*  3 |    SORT ORDER BY STOPKEY            |                |   133 | 22743 |  1125K (19)| 03:45:09 |       |       |
  36. |*  4 |     FILTER                          |                |       |       |            |          |       |       |
  37. |*  5 |      HASH JOIN                      |                |   133 | 22743 |  1125K (19)| 03:45:09 |       |       |
  38. |*  6 |       HASH JOIN SEMI                |                |   133 | 19950 |  1125K (19)| 03:45:08 |       |       |
  39. |*  7 |        HASH JOIN                    |                |   219 | 28251 |  1125K (19)| 03:45:08 |       |       |
  40. |   8 |         NESTED LOOPS                |                |   219 | 25185 |  1125K (19)| 03:45:08 |       |       |
  41. |   9 |          PARTITION RANGE ITERATOR   |                |   219 | 10074 |  1125K (19)| 03:45:03 |   KEY |   KEY |
  42. |* 10 |           TABLE ACCESS FULL         | PERF_T_PONPORT |   219 | 10074 |  1125K (19)| 03:45:03 |   KEY |   KE
  43. |  11 |          TABLE ACCESS BY INDEX ROWID| DEVICE         |     1 |    69 |     2   (0)| 00:00:01 |       |     
  44. |* 12 |           INDEX UNIQUE SCAN         | PK_DEVICE      |     1 |       |     1   (0)| 00:00:01 |       |      
  45. |  13 |         TABLE ACCESS FULL           | NODE           |  1214 | 16996 |     8   (0)| 00:00:01 |       |       |
  46. |* 14 |        INDEX RANGE SCAN             | SYS_C00543203  |  1167 | 24507 |    10   (0)| 00:00:01 |       |
  47. |  15 |       TABLE ACCESS FULL             | NODE           |  1214 | 25494 |     8   (0)| 00:00:01 |       |      
  48. ----------------------------------------------------------------------------------------------------
  50. Predicate Information (identified by operation id):
  51. ---------------------------------------------------
  53.    1 - filter(ROWNUM<=10)
  54.    3 - filter(ROWNUM<=10)
  55.    4 - filter(TRUNC(SYSDATE@!-1,‘fmdd‘)<=TRUNC(SYSDATE@!,‘fmdd‘))
  56.    5 - access("C"."CITYNODECODE"="NP"."NODECODE")
  57.    6 - access("C"."NODECODE"="S"."NODECODE")
  58.    7 - access("D"."NODECODE"="C"."NODECODE")
  59.   10 - filter("P"."RXCRCERROR" IS NOT NULL AND "P"."RXCRCERROR">0 AND
  60.               "P"."COLTIME">=TRUNC(SYSDATE@!-1,‘fmdd‘) AND "P"."COLTIME"<=TRUNC(SYSDATE@!,‘fmdd‘))
  61.   12 - access("P"."RESID"="D"."DEVICEID" AND "D"."CHANGETYPE"=0)
  62.   14 - access("S"."NETUSERID"=‘admin‘ AND "S"."AUTHTYPE"=‘VIEW‘)
  64. 已选择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;
  20. 已解释。
  22. SQL> 
  23. SQL> select * from table(dbms_xplan.display);
  25. PLAN_TABLE_OUTPUT
  26. ----------------------------------------------------------------------------------------------------
  27. Plan hash value: 2287749996
  29. ----------------------------------------------------------------------------------------------------
  30. | Id  | Operation                          | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
  31. ----------------------------------------------------------------------------------------------------
  32. |   0 | SELECT STATEMENT                   |                |    10 |  3430 |   915   (2)| 00:00:11 |       |       |
  33. |*  1 |  COUNT STOPKEY                     |                |       |       |            |          |       |       |
  34. |   2 |   VIEW                             |                |   266 | 91238 |   915   (2)| 00:00:11 |       |       |
  35. |*  3 |    SORT ORDER BY STOPKEY           |                |   266 | 45486 |   915   (2)| 00:00:11 |       |       |
  36. |*  4 |     HASH JOIN                      |                |   266 | 45486 |   915   (2)| 00:00:11 |       |       |
  37. |   5 |      TABLE ACCESS FULL             | NODE           |  1214 | 25494 |     8   (0)| 00:00:01 |       |       |
  38. |*  6 |      HASH JOIN RIGHT SEMI          |                |   267 | 40050 |   905   (2)| 00:00:11 |       |       |
  39. |*  7 |       INDEX RANGE SCAN             | SYS_C00543203  |  1167 | 24507 |    10   (0)| 00:00:01 |       |     
  40. |*  8 |       HASH JOIN                    |                |   437 | 56373 |   895   (2)| 00:00:11 |       |       |
  41. |   9 |        TABLE ACCESS FULL           | NODE           |  1214 | 16996 |     8   (0)| 00:00:01 |       |       |
  42. |  10 |        NESTED LOOPS                |                |   437 | 50255 |   885   (2)| 00:00:11 |       |       |
  43. |  11 |         PARTITION RANGE INLIST     |                |   437 | 20102 |     2   (0)| 00:00:01 |KEY(I) |KEY(I) |
  44. |* 12 |          TABLE ACCESS FULL         | PERF_T_PONPORT |   437 | 20102 |     2   (0)| 00:00:01 |KEY(I) |KEY(I) 
  45. |  13 |         TABLE ACCESS BY INDEX ROWID| DEVICE         |     1 |    69 |     2   (0)| 00:00:01 |       |       |
  46. |* 14 |          INDEX UNIQUE SCAN         | PK_DEVICE      |     1 |       |     1   (0)| 00:00:01 |       |       |
  47. ----------------------------------------------------------------------------------------------------
  49. Predicate Information (identified by operation id):
  50. ---------------------------------------------------
  52.    1 - filter(ROWNUM<=10)
  53.    3 - filter(ROWNUM<=10)
  54.    4 - access("C"."CITYNODECODE"="NP"."NODECODE")
  55.    6 - access("C"."NODECODE"="S"."NODECODE")
  56.    7 - access("S"."NETUSERID"=‘admin‘ AND "S"."AUTHTYPE"=‘VIEW‘)
  57.    8 - access("D"."NODECODE"="C"."NODECODE")
  58.   12 - filter("P"."RXCRCERROR" IS NOT NULL AND "P"."RXCRCERROR">0 AND
  59.               ("P"."COLTIME"=TRUNC(SYSDATE@!-1,‘fmdd‘) OR "P"."COLTIME"=TRUNC(SYSDATE@!,‘fmdd‘)))
  60.   14 - access("P"."RESID"="D"."DEVICEID" AND "D"."CHANGETYPE"=0)
  62. 已选择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‘);
  3. 已解释。
  5. SQL> select * from table(dbms_xplan.display);
  7. PLAN_TABLE_OUTPUT
  8. ----------------------------------------------------------------------------------------------------
  9. Plan hash value: 4015217925
  11. ----------------------------------------------------------------------------------------------------
  12. | Id  | Operation                | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
  13. ----------------------------------------------------------------------------------------------------
  14. |   0 | SELECT STATEMENT         |                |   100 |  4600 |     2   (0)| 00:00:01 |       |       |
  15. |   1 |  PARTITION RANGE ITERATOR|                |   100 |  4600 |     2   (0)| 00:00:01 |     1 |   KEY |
  16. |*  2 |   TABLE ACCESS FULL      | PERF_T_PONPORT |   100 |  4600 |     2   (0)| 00:00:01 |     1 |   KEY |
  17. ----------------------------------------------------------------------------------------------------
  19. Predicate Information (identified by operation id):
  20. ---------------------------------------------------
  22.    2 - filter("P"."COLTIME"<TRUNC(SYSDATE@!,‘fmdd‘))
  24. 已选择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‘);
  4. 已解释。
  6. SQL> select * from table(dbms_xplan.display);
  8. PLAN_TABLE_OUTPUT
  9. ----------------------------------------------------------------------------------------------------
  10. Plan hash value: 1162550374
  12. ----------------------------------------------------------------------------------------------------
  13. | Id  | Operation                 | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
  14. ----------------------------------------------------------------------------------------------------
  15. |   0 | SELECT STATEMENT          |                |   100 |  4600 |  1637  (37)| 00:00:20 |       |       |
  16. |*  1 |  FILTER                   |                |       |       |            |          |       |       |
  17. |   2 |   PARTITION RANGE ITERATOR|                |   100 |  4600 |  1637  (37)| 00:00:20 |   KEY |   KEY |
  18. |*  3 |    TABLE ACCESS FULL      | PERF_T_PONPORT |   100 |  4600 |  1637  (37)| 00:00:20 |   KEY |   
  19. ----------------------------------------------------------------------------------------------------
  21. Predicate Information (identified by operation id):
  22. ---------------------------------------------------
  24.    1 - filter(TRUNC(SYSDATE@!-1,‘fmdd‘)<TRUNC(SYSDATE@!,‘fmdd‘))
  25.    3 - filter("P"."COLTIME">TRUNC(SYSDATE@!-1,‘fmdd‘) AND "P"."COLTIME"<TRUNC(SYSDATE@!,‘fmdd‘))
  27. 已选择16行。

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


2、PARTITION RANGE SINGLE
  1. SQL> explain plan for select * from  perf_t_ponport partition(P_20170307) p;
  3. 已解释。
  5. SQL> select * from table(dbms_xplan.display);
  7. PLAN_TABLE_OUTPUT
  8. ----------------------------------------------------------------------------------------------------
  9. Plan hash value: 1021280532
  11. ----------------------------------------------------------------------------------------------------
  12. | Id  | Operation              | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
  13. ----------------------------------------------------------------------------------------------------
  14. |   0 | SELECT STATEMENT       |                |   100 |  4600 |     2   (0)| 00:00:01 |       |       |
  15. |   1 |  PARTITION RANGE SINGLE|                |   100 |  4600 |     2   (0)| 00:00:01 |    85 |    85 |
  16. |   2 |   TABLE ACCESS FULL    | PERF_T_PONPORT |   100 |  4600 |     2   (0)| 00:00:01 |    85 |   
  17. ----------------------------------------------------------------------------------------------------
  19. 已选择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‘);
  4. 已解释。
  6. SQL> select * from table(dbms_xplan.display);
  8. PLAN_TABLE_OUTPUT
  9. ----------------------------------------------------------------------------------------------------
  10. Plan hash value: 506920385
  12. ----------------------------------------------------------------------------------------------------
  13. | Id  | Operation               | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
  14. ----------------------------------------------------------------------------------------------------
  15. |   0 | SELECT STATEMENT        |                |   100 |  4600 |     8  (25)| 00:00:01 |       |       |
  16. |*  1 |  FILTER                 |                |       |       |            |          |       |       |
  17. |   2 |   PARTITION RANGE SINGLE|                |   100 |  4600 |     8  (25)| 00:00:01 |   KEY |   KEY |
  18. |*  3 |    TABLE ACCESS FULL    | PERF_T_PONPORT |   100 |  4600 |     8  (25)| 00:00:01 |   KEY |   KE
  19. ----------------------------------------------------------------------------------------------------
  21. Predicate Information (identified by operation id):
  22. ---------------------------------------------------
  24.    1 - filter(TRUNC(SYSDATE@!-1,‘fmdd‘)<TRUNC(SYSDATE@!,‘fmdd‘))
  25.    3 - filter("P"."COLTIME"=TRUNC(SYSDATE@!-1,‘fmdd‘) AND "P"."COLTIME"<TRUNC(SYSDATE@!,‘fmdd‘))
  27. 已选择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‘);
  4. 已解释。
  6. SQL> select * from table(dbms_xplan.display);
  8. PLAN_TABLE_OUTPUT
  9. ----------------------------------------------------------------------------------------------------
  10. Plan hash value: 2473962793
  12. ----------------------------------------------------------------------------------------------------
  13. | Id  | Operation              | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
  14. ----------------------------------------------------------------------------------------------------
  15. |   0 | SELECT STATEMENT       |                |   100 |  4600 |     2   (0)| 00:00:01 |       |       |
  16. |   1 |  PARTITION RANGE INLIST|                |   100 |  4600 |     2   (0)| 00:00:01 |KEY(I) |KEY(I) |
  17. |*  2 |   TABLE ACCESS FULL    | PERF_T_PONPORT |   100 |  4600 |     2   (0)| 00:00:01 |KEY(I) |KEY(I)
  18. ----------------------------------------------------------------------------------------------------
  20. Predicate Information (identified by operation id):
  21. ---------------------------------------------------
  23.    2 - filter("P"."COLTIME"=TRUNC(SYSDATE@!-1,‘fmdd‘) OR "P"."COLTIME"=TRUNC(SYSDATE@!,‘fmdd‘))
  25. 已选择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‘);
  4. 已解释。
  6. SQL> select * from table(dbms_xplan.display);
  8. PLAN_TABLE_OUTPUT
  9. ----------------------------------------------------------------------------------------------------
  10. Plan hash value: 3647984539
  12. ----------------------------------------------------------------------------------------------------
  13. | Id  | Operation           | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
  14. ----------------------------------------------------------------------------------------------------
  15. |   0 | SELECT STATEMENT    |                |   100 |  4600 |     2   (0)| 00:00:01 |       |       |
  16. |   1 |  PARTITION RANGE ALL|                |   100 |  4600 |     2   (0)| 00:00:01 |     1 |   101 |
  17. |*  2 |   TABLE ACCESS FULL | PERF_T_PONPORT |   100 |  4600 |     2   (0)| 00:00:01 |     1 |   101 |
  18. ----------------------------------------------------------------------------------------------------
  20. Predicate Information (identified by operation id):
  21. ---------------------------------------------------
  23.    2 - filter("P"."COLTIME"<TRUNC(SYSDATE@!-5,‘fmdd‘) OR
  24.               "P"."COLTIME">TRUNC(SYSDATE@!-1,‘fmdd‘))
  26. 已选择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‘);
  4. 已解释。
  6. SQL> select * from table(dbms_xplan.display);
  8. PLAN_TABLE_OUTPUT
  9. ----------------------------------------------------------------------------------------------------
  10. Plan hash value: 470656424
  12. ----------------------------------------------------------------------------------------------------
  13. | Id  | Operation          | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
  14. ----------------------------------------------------------------------------------------------------
  15. |   0 | SELECT STATEMENT   |                |  1512M|    67G|  3783K  (3)| 12:36:37 |       |       |
  16. |   1 |  PARTITION RANGE OR|                |  1512M|    67G|  3783K  (3)| 12:36:37 |KEY(OR)|KEY(OR)|
  17. |*  2 |   TABLE ACCESS FULL| PERF_T_PONPORT |  1512M|    67G|  3783K  (3)| 12:36:37 |KEY(OR)|KEY(OR)|
  18. ----------------------------------------------------------------------------------------------------
  20. Predicate Information (identified by operation id):
  21. ---------------------------------------------------
  23.    2 - filter("P"."COLTIME"<TRUNC(SYSDATE@!-5,‘fmdd‘) OR
  24.               "P"."COLTIME">TRUNC(SYSDATE@!-1,‘fmdd‘))
  26. Note
  27. -----
  28.    - ‘PLAN_TABLE‘ is old version
  30. 已选择19行。






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


联系
我们