数据库CPU被耗光了,前台操作有点偏慢,查看了一下会话情况,数据库压力基本都在一条SQL上。
SQL> select * from v$version ;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
SQL> EXPLAIN PLAN FOR
2 SELECT count(*) FROM T_SR_SERVICEREQUEST T
3 WHERE :"SYS_B_0"= :"SYS_B_1"
4 AND T.SUBMITTIME >= SYSDATE - :1/:"SYS_B_2"
5 AND T.SRTYPEID = :2
6 AND T.SUBSNUMBER = :3
7 and (t.SERVICEID like to_char(sysdate,:"SYS_B_3")||:"SYS_B_4"
8 or t.SERVICEID like to_char(sysdate-:"SYS_B_5",:"SYS_B_6")||:"SYS_B_7") ;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 100 | 13 | | |
| 1 | SORT AGGREGATE | | 1 | 100 | | | |
| 2 | CONCATENATION | | | | | | |
|* 3 | FILTER | | | | | | |
| 4 | PARTITION RANGE ITERATOR | | | | | KEY | KEY |
|* 5 | TABLE ACCESS BY LOCAL INDEX ROWID| T_SR_SERVICEREQUEST | 1 | 100 | 3 | KEY | KEY |
|* 6 | INDEX RANGE SCAN | PK_SR_SERVICEREQUEST | 3214 | | 2 | KEY | KEY |
|* 7 | FILTER | | | | | | |
| 8 | PARTITION RANGE ITERATOR | | | | | KEY | KEY |
|* 9 | TABLE ACCESS BY LOCAL INDEX ROWID| T_SR_SERVICEREQUEST | 1 | 100 | 3 | KEY | KEY |
|* 10 | INDEX RANGE SCAN | PK_SR_SERVICEREQUEST | 3214 | | 2 | KEY | KEY |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(:Z=:Z)
5 - filter("T"."SRTYPEID"=:Z AND "T"."SUBSNUMBER"=:Z AND
"T"."SUBMITTIME">=SYSDATE@!-TO_NUMBER(:Z)/TO_NUMBER(:Z))
6 - access("T"."SERVICEID" LIKE TO_CHAR(SYSDATE@!-TO_NUMBER(:Z),:Z)||:Z)
filter("T"."SERVICEID" LIKE TO_CHAR(SYSDATE@!-TO_NUMBER(:Z),:Z)||:Z)
7 - filter(:Z=:Z)
9 - filter("T"."SUBSNUMBER"=:Z AND "T"."SRTYPEID"=:Z AND
"T"."SUBMITTIME">=SYSDATE@!-TO_NUMBER(:Z)/TO_NUMBER(:Z))
10 - access("T"."SERVICEID" LIKE TO_CHAR(SYSDATE@!,:Z)||:Z)
filter("T"."SERVICEID" LIKE TO_CHAR(SYSDATE@!,:Z)||:Z AND LNNVL("T"."SERVICEID" LIKE
TO_CHAR(SYSDATE@!-TO_NUMBER(:Z),:Z)||:Z))
Note: cpu costing is off
33 rows selected.
从这个执行计划上看,使用了PK进行INDEX RANGE SCAN,然后在FILTER,效率并不一定有多高
使用10046的trace来去得绑定变量的值,全部替换之后,语句如下:
SELECT count(*) FROM T_SR_SERVICEREQUEST T
WHERE 1= 1
AND T.SUBMITTIME >= SYSDATE - 2/24
AND T.SRTYPEID = '004002007001'
AND T.SUBSNUMBER = '020815520617299'
AND (t.SERVICEID like to_char(sysdate,'yyMMdd')||'%'
OR t.SERVICEID like to_char(sysdate-1,'yyMMdd')||'%');
现在sqlplus下直接用set autotrace on来测试语句效果
SQL> SELECT count(*) FROM T_SR_SERVICEREQUEST T
2 WHERE 1= 1
AND T.SUBMITTIME >= SYSDATE - 2/24
AND T.SRTYPEID = '004002007001'
AND T.SUBSNUMBER = '020815520617299'
AND (t.SERVICEID like to_char(sysdate,'yyMMdd')||'%'
OR t.SERVICEID like to_char(sysdate-1,'yyMMdd')||'%'); 3 4 5 6 7
COUNT(*)
----------
1
Elapsed: 00:00:01.17
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=13 Card=1 Bytes=100)
1 0 SORT (AGGREGATE)
2 1 CONCATENATION
3 2 PARTITION RANGE (ITERATOR)
4 3 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'T_SR_SERVICE
REQUEST' (Cost=3 Card=1 Bytes=100)
5 4 INDEX (RANGE SCAN) OF 'PK_SR_SERVICEREQUEST' (UNIQ
UE) (Cost=2 Card=3214)
6 2 PARTITION RANGE (ITERATOR)
7 6 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'T_SR_SERVICE
REQUEST' (Cost=3 Card=1 Bytes=100)
8 7 INDEX (RANGE SCAN) OF 'PK_SR_SERVICEREQUEST' (UNIQ
UE) (Cost=2 Card=3214)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
96775 consistent gets
0 physical reads
0 redo size
518 bytes sent via SQL*Net to client
656 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
放弃使用PK,强制一下时间索引,也许效果更理想
SQL> SELECT /*+index(t IX_SERVICEREQUEST_SUBMITTIME)*/ count(*) FROM T_SR_SERVICEREQUEST T
2 WHERE 1= 1
3 AND T.SUBMITTIME >= SYSDATE - 2/24
4 AND T.SRTYPEID = '004002007001'
5 AND T.SUBSNUMBER = '020815520617299'
6 AND (t.SERVICEID like to_char(sysdate,'yyMMdd')||'%'
7 OR t.SERVICEID like to_char(sysdate-1,'yyMMdd')||'%');
COUNT(*)
----------
1
Elapsed: 00:00:00.09
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=271 Card=1 Bytes=100
)
1 0 SORT (AGGREGATE)
2 1 PARTITION RANGE (ALL)
3 2 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'T_SR_SERVICERE
QUEST' (Cost=271 Card=3 Bytes=300)
4 3 INDEX (RANGE SCAN) OF 'IX_SERVICEREQUEST_SUBMITTIME'
(NON-UNIQUE) (Cost=263 Card=64274)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7128 consistent gets
7 physical reads
4540 redo size
518 bytes sent via SQL*Net to client
656 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
上面的语句效率提高了10几倍,consistent gets从96775降到了7128,这可以极大的缓解CPU压力了
但存在一个风险,PARTITION RANGE (ALL),当前按时间的分区数据量还不大,当分区充盈又未及时清退的时候,灾难也许也就来了
所以考虑更换一下语句的写法,能使用上分区
这个分区指定在serviceid上,表设计不合理,这里就先不管这一茬了
SQL> SELECT count(*) FROM T_SR_SERVICEREQUEST T
2 WHERE 1= 1
3 AND T.SUBMITTIME >= SYSDATE - 2/24
4 AND T.SRTYPEID = '004002007001'
5 AND T.SUBSNUMBER = '020815520617299'
6 AND t.SERVICEID between to_char(sysdate-1,'yyMMdd') and to_char(sysdate+1,'yyMMdd');
COUNT(*)
----------
1
Elapsed: 00:00:00.07
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=1 Bytes=100)
1 0 SORT (AGGREGATE)
2 1 FILTER
3 2 PARTITION RANGE (ITERATOR)
4 3 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'T_SR_SERVICE
REQUEST' (Cost=1 Card=1 Bytes=100)
5 4 INDEX (RANGE SCAN) OF 'IX_SERVICEREQUEST_SUBMITTIM
E' (NON-UNIQUE) (Cost=263 Card=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5300 consistent gets
0 physical reads
0 redo size
518 bytes sent via SQL*Net to client
656 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10867315/viewspace-714334/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10867315/viewspace-714334/