数据库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