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

p as(

select np.nodecode , np.nodename, d.deviceid, d.devicename, d.loopaddress, p.respara, p.rxcrcerror, p.txcrcerror

from perf_t_ponport p,device d, node c,node np

where p.resid = d.deviceid

and d.nodecode = c.nodecode

and c.citynodecode = np.nodecode

and c.nodecode in (SELECT S.NODECODE FROM SINGLEUSERNODEAUTH S WHERE S.NETUSERID = ‘admin‘ AND S.AUTHTYPE = ‘VIEW‘)

and d.changetype = 0

and p.coltime between trunc(sysdate-1,‘dd‘) and trunc(sysdate,‘dd‘)

and p.rxcrcerror is not null

and p.rxcrcerror >0

order by p.rxcrcerror desc

)

select *

from p

where rownum <=10

这个SQL执行花费了1分钟24秒，需要优化。

查看执行计划：

SQL> explain plan for with p as(
  2       select np.nodecode , np.nodename, d.deviceid, d.devicename, d.loopaddress, p.respara, p.rx
crcerror, 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 = ‘adm
in‘ 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
 17  ;
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 733587010
----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                |    10 |  3430 |  1125K (19)| 03:45:09 |       |       |
|*  1 |  COUNT STOPKEY                      |                |       |       |            |          |       |       |
|   2 |   VIEW                              |                |   133 | 45619 |  1125K (19)| 03:45:09 |       |       |
|*  3 |    SORT ORDER BY STOPKEY            |                |   133 | 22743 |  1125K (19)| 03:45:09 |       |       |
|*  4 |     FILTER                          |                |       |       |            |          |       |       |
|*  5 |      HASH JOIN                      |                |   133 | 22743 |  1125K (19)| 03:45:09 |       |       |
|*  6 |       HASH JOIN SEMI                |                |   133 | 19950 |  1125K (19)| 03:45:08 |       |       |
|*  7 |        HASH JOIN                    |                |   219 | 28251 |  1125K (19)| 03:45:08 |       |       |
|   8 |         NESTED LOOPS                |                |   219 | 25185 |  1125K (19)| 03:45:08 |       |       |
|   9 |          PARTITION RANGE ITERATOR   |                |   219 | 10074 |  1125K (19)| 03:45:03 |   KEY |   KEY |
|* 10 |           TABLE ACCESS FULL         | PERF_T_PONPORT |   219 | 10074 |  1125K (19)| 03:45:03 |   KEY |   KE
|  11 |          TABLE ACCESS BY INDEX ROWID| DEVICE         |     1 |    69 |     2   (0)| 00:00:01 |       |     
|* 12 |           INDEX UNIQUE SCAN         | PK_DEVICE      |     1 |       |     1   (0)| 00:00:01 |       |      
|  13 |         TABLE ACCESS FULL           | NODE           |  1214 | 16996 |     8   (0)| 00:00:01 |       |       |
|* 14 |        INDEX RANGE SCAN             | SYS_C00543203  |  1167 | 24507 |    10   (0)| 00:00:01 |       |
|  15 |       TABLE ACCESS FULL             | NODE           |  1214 | 25494 |     8   (0)| 00:00:01 |       |      
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=10)
   3 - filter(ROWNUM<=10)
   4 - filter(TRUNC(SYSDATE@!-1,‘fmdd‘)<=TRUNC(SYSDATE@!,‘fmdd‘))
   5 - access("C"."CITYNODECODE"="NP"."NODECODE")
   6 - access("C"."NODECODE"="S"."NODECODE")
   7 - access("D"."NODECODE"="C"."NODECODE")
  10 - filter("P"."RXCRCERROR" IS NOT NULL AND "P"."RXCRCERROR">0 AND
              "P"."COLTIME">=TRUNC(SYSDATE@!-1,‘fmdd‘) AND "P"."COLTIME"<=TRUNC(SYSDATE@!,‘fmdd‘))
  12 - access("P"."RESID"="D"."DEVICEID" AND "D"."CHANGETYPE"=0)
  14 - access("S"."NETUSERID"=‘admin‘ AND "S"."AUTHTYPE"=‘VIEW‘)
已选择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‘) )。
改写后的语句：

with p as(
     select  np.nodecode , np.nodename, d.deviceid, d.devicename, d.loopaddress, p.respara, p.rxcrcerror, p.txcrcerror
     from   perf_t_ponport p,device d, node c,node np
     where  p.resid = d.deviceid
     and    d.nodecode = c.nodecode 
     and    c.citynodecode = np.nodecode
     and    d.changetype = 0
     and  c.nodecode in (SELECT S.NODECODE FROM SINGLEUSERNODEAUTH S WHERE S.NETUSERID = ‘admin‘ AND S.AUTHTYPE = ‘VIEW‘)
     and    (p.coltime =trunc(sysdate-1,‘dd‘) or p.coltime =trunc(sysdate,‘dd‘) )
     and    p.rxcrcerror is not null
     and    p.rxcrcerror >0 
    order by   p.rxcrcerror desc
)
select *
from   p
where rownum <=10;

改写后的执行计划：

SQL> explain plan for with p as(
  2       select  np.nodecode , np.nodename, d.deviceid, d.devicename, d.loopaddress, p.respara, p.r
xcrcerror, 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;
已解释。
SQL> 
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2287749996
----------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                |    10 |  3430 |   915   (2)| 00:00:11 |       |       |
|*  1 |  COUNT STOPKEY                     |                |       |       |            |          |       |       |
|   2 |   VIEW                             |                |   266 | 91238 |   915   (2)| 00:00:11 |       |       |
|*  3 |    SORT ORDER BY STOPKEY           |                |   266 | 45486 |   915   (2)| 00:00:11 |       |       |
|*  4 |     HASH JOIN                      |                |   266 | 45486 |   915   (2)| 00:00:11 |       |       |
|   5 |      TABLE ACCESS FULL             | NODE           |  1214 | 25494 |     8   (0)| 00:00:01 |       |       |
|*  6 |      HASH JOIN RIGHT SEMI          |                |   267 | 40050 |   905   (2)| 00:00:11 |       |       |
|*  7 |       INDEX RANGE SCAN             | SYS_C00543203  |  1167 | 24507 |    10   (0)| 00:00:01 |       |     
|*  8 |       HASH JOIN                    |                |   437 | 56373 |   895   (2)| 00:00:11 |       |       |
|   9 |        TABLE ACCESS FULL           | NODE           |  1214 | 16996 |     8   (0)| 00:00:01 |       |       |
|  10 |        NESTED LOOPS