首页 > 代码库 > 甘肃PON数据库分区列范围查询优化
甘肃PON数据库分区列范围查询优化
查询慢的SQL:
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 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 | | 437 | 50255 | 885 (2)| 00:00:11 | | |
| 11 | PARTITION RANGE INLIST | | 437 | 20102 | 2 (0)| 00:00:01 |KEY(I) |KEY(I) |
|* 12 | TABLE ACCESS FULL | PERF_T_PONPORT | 437 | 20102 | 2 (0)| 00:00:01 |KEY(I) |KEY(I)
| 13 | TABLE ACCESS BY INDEX ROWID| DEVICE | 1 | 69 | 2 (0)| 00:00:01 | | |
|* 14 | INDEX UNIQUE SCAN | PK_DEVICE | 1 | | 1 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10)
3 - filter(ROWNUM<=10)
4 - access("C"."CITYNODECODE"="NP"."NODECODE")
6 - access("C"."NODECODE"="S"."NODECODE")
7 - access("S"."NETUSERID"=‘admin‘ AND "S"."AUTHTYPE"=‘VIEW‘)
8 - access("D"."NODECODE"="C"."NODECODE")
12 - filter("P"."RXCRCERROR" IS NOT NULL AND "P"."RXCRCERROR">0 AND
("P"."COLTIME"=TRUNC(SYSDATE@!-1,‘fmdd‘) OR "P"."COLTIME"=TRUNC(SYSDATE@!,‘fmdd‘)))
14 - access("P"."RESID"="D"."DEVICEID" AND "D"."CHANGETYPE"=0)
已选择34行。
改写后, SQL执行花了9秒钟, 性能提升了9倍。改写后的SQL消除了filter, 访问分区的方式为 PARTITION RANGE INLIST 。
关于几种分区访问的方式:
范围分区:
1、PARTITION RANGE ITERATOR
SQL> explain plan for select * from perf_t_ponport p where p.coltime <trunc(sysdate,‘dd‘);
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 4015217925
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 4600 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ITERATOR| | 100 | 4600 | 2 (0)| 00:00:01 | 1 | KEY |
|* 2 | TABLE ACCESS FULL | PERF_T_PONPORT | 100 | 4600 | 2 (0)| 00:00:01 | 1 | KEY |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("P"."COLTIME"<TRUNC(SYSDATE@!,‘fmdd‘))
已选择14行。
SQL> explain plan for select * from perf_t_ponport p where p.coltime > trunc(sysdate-1,‘dd‘) and p.
coltime <trunc(sysdate,‘dd‘);
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1162550374
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 4600 | 1637 (37)| 00:00:20 | | |
|* 1 | FILTER | | | | | | | |
| 2 | PARTITION RANGE ITERATOR| | 100 | 4600 | 1637 (37)| 00:00:20 | KEY | KEY |
|* 3 | TABLE ACCESS FULL | PERF_T_PONPORT | 100 | 4600 | 1637 (37)| 00:00:20 | KEY |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TRUNC(SYSDATE@!-1,‘fmdd‘)<TRUNC(SYSDATE@!,‘fmdd‘))
3 - filter("P"."COLTIME">TRUNC(SYSDATE@!-1,‘fmdd‘) AND "P"."COLTIME"<TRUNC(SYSDATE@!,‘fmdd‘))
已选择16行。
PARTITION RANGE ITERATOR是通过连续的分区迭代来获取数据。
2、PARTITION RANGE SINGLE
SQL> explain plan for select * from perf_t_ponport partition(P_20170307) p;
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1021280532
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 4600 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 100 | 4600 | 2 (0)| 00:00:01 | 85 | 85 |
| 2 | TABLE ACCESS FULL | PERF_T_PONPORT | 100 | 4600 | 2 (0)| 00:00:01 | 85 |
----------------------------------------------------------------------------------------------------
已选择9行。
SQL> explain plan for select * from perf_t_ponport p where p.coltime = trunc(sysdate-1,‘dd‘) and p.
coltime <trunc(sysdate,‘dd‘);
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 506920385
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 4600 | 8 (25)| 00:00:01 | | |
|* 1 | FILTER | | | | | | | |
| 2 | PARTITION RANGE SINGLE| | 100 | 4600 | 8 (25)| 00:00:01 | KEY | KEY |
|* 3 | TABLE ACCESS FULL | PERF_T_PONPORT | 100 | 4600 | 8 (25)| 00:00:01 | KEY | KE
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TRUNC(SYSDATE@!-1,‘fmdd‘)<TRUNC(SYSDATE@!,‘fmdd‘))
3 - filter("P"."COLTIME"=TRUNC(SYSDATE@!-1,‘fmdd‘) AND "P"."COLTIME"<TRUNC(SYSDATE@!,‘fmdd‘))
已选择16行。
PARTITION RANGE SINGLE只扫描一个分区。
3、PARTITION RANGE INLIST
SQL> explain plan for select * from perf_t_ponport p where p.coltime = trunc(sysdate-1,‘dd‘) or p.c
oltime = trunc(sysdate,‘dd‘);
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2473962793
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 4600 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE INLIST| | 100 | 4600 | 2 (0)| 00:00:01 |KEY(I) |KEY(I) |
|* 2 | TABLE ACCESS FULL | PERF_T_PONPORT | 100 | 4600 | 2 (0)| 00:00:01 |KEY(I) |KEY
(I)----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("P"."COLTIME"=TRUNC(SYSDATE@!-1,‘fmdd‘) OR "P"."COLTIME"=TRUNC(SYSDATE@!,‘fmdd‘))
已选择14行。
上面的KEY(I)就是KEY(INLIST)的意思。PARTITION RANGE INLIST无法通过Pstart和Pstop看出分区的起始和结束位置 。
4、ARTITION RANGE ALL和PARTITION RANGE OR
SQL> explain plan for select * from perf_t_ponport p where p.coltime >trunc(sysdate-1,‘dd‘) or p.c
oltime < trunc(sysdate-5,‘dd‘);
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3647984539
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 4600 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL| | 100 | 4600 | 2 (0)| 00:00:01 | 1 | 101 |
|* 2 | TABLE ACCESS FULL | PERF_T_PONPORT | 100 | 4600 | 2 (0)| 00:00:01 | 1 | 101 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("P"."COLTIME"<TRUNC(SYSDATE@!-5,‘fmdd‘) OR
"P"."COLTIME">TRUNC(SYSDATE@!-1,‘fmdd‘))
已选择15行。
ARTITION RANGE ALL是全分区扫描, 上面是在oracle 10g环境测试, 在Oracle 11g环境下,分区扫描方式为:PARTITION RANGE OR, 可以看出11g对这种分区查询条件扫描方式做了优化。
SQL> explain plan for select * from perf_t_ponport p where p.coltime >trunc(sysdate-1,‘dd‘) or p.c
oltime < trunc(sysdate-5,‘dd‘);
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 470656424
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1512M| 67G| 3783K (3)| 12:36:37 | | |
| 1 | PARTITION RANGE OR| | 1512M| 67G| 3783K (3)| 12:36:37 |KEY(OR)|KEY(OR)|
|* 2 | TABLE ACCESS FULL| PERF_T_PONPORT | 1512M| 67G| 3783K (3)| 12:36:37 |KEY(OR)|KEY(OR)|
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("P"."COLTIME"<TRUNC(SYSDATE@!-5,‘fmdd‘) OR
"P"."COLTIME">TRUNC(SYSDATE@!-1,‘fmdd‘))
Note
-----
- ‘PLAN_TABLE‘ is old version
已选择19行。
甘肃PON数据库分区列范围查询优化
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。