数据异常使执行计划没有使用期望索引导致查询效率降低

系统割接之后，用户资料表A_S表产生很多account级别的数据。程序在查询此表时(下表第一个SQL)，根据AWR report，消耗资源严重。

SQL ordered by Gets

• Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
• %Total - Buffer Gets as a percentage of Total Buffer Gets
• %CPU - CPU Time as a percentage of Elapsed Time
• %IO - User I/O Time as a percentage of Elapsed Time
• Total Buffer Gets: 503,703,670
• Captured SQL account for 97.7% of Total

Buffer Gets	Executions	Gets per Exec	%Total	Elapsed Time (s)	%CPU	%IO	SQL Id	SQL Module	SQL Text
481,852,220	1,544	312,080.45	95.66	4,883.83	55.9	0	7mn0s0rj9rcaq		Select /*+ USE_NL(a b c) */ a....
3,184,950	8,646	368.37	0.63	36.79	49.5	.3	6hwxvxahyup08	URE_U9	begin PUBLIC_PKG_NOTIF_CIRQ . ...
2,513,179	233	10,786.18	0.50	170.38	58.4	0	060zmjty9da91	URE_U9	begin PUBLIC_PKG_NOTIF_CIRQ . ...
2,277,956	9	253,106.22	0.45	23.97	53.8	0	c4qkh3s9p2384	oracle@sdp1 (TNS V1-V3)	SELECT /*+ USE_NL ("A2") USE_N...
1,545,893	386,472	4.00	0.31	7.47	50.8	.6	dtkk2mrmqpj34	URE_U9	SELECT SUB_MSISDN, NVL(IMSI, '...
1,539,048	384,761	4.00	0.31	7.57	50.8	.7	75v522q8bhcyv	URE_U9	SELECT SUB_MSISDN, NVL(IMSI, '...
550,432	668	824.00	0.11	8.12	55.5	0	9ngq9b1zmyu0k	LTP@sdp1 (TNS V1-V3)	SELECT EAC.TABLE_NAME, EAC.BIL...
420,264	234	1,796.00	0.08	28.20	59	0	3bn7wp5vypt1g	URE_U9	select count(*) from NOTIF_QUE...
418,468	233	1,796.00	0.08	28.76	57.5	0	18c1z8x947qr8	URE_U9	SELECT COUNT(1) FROM NOTIF_QUE...
418,468	234	1,788.32	0.08	28.09	58.9	0	2k4rfb2dhbad7	URE_U9	select count(*) from NOTIF_QUE...

分析得知，在A_S表的3620724条记录中, 3620017条记录的subscr_no和subscr_no_resets为0.

select current_state, count(*) from account_subscriber group by current_state order by 1;

CURRENT_STATE   COUNT(*)

------------- ----------

            0    3620017

            1          1

            2        543

           50         30

           60        113

select count(*) from account_subscriber where subscr_no=0 and subscr_no_resets=0 and account_no!=0;

  COUNT(*)

----------

   3620017

因为大部分的subscr_no和subscr_no_resets为0，oracle认为原有主键("SUBSCR_NO", "SUBSCR_NO_RESETS", "ACCOUNT_NO")的索引并不需要，使用全表扫描的策略要优于使用索引。全表扫描使查询变慢，出现ORA-01013的原因(ORA-01013原因暂时不明，不确定是oracle机制还是程序有查询时间控制，一旦超时，就取消查询)。

---------------------------------------------------------------------------------------------------------------

| Id  | Operation                      | Name                 | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT               |                      |       |       |       |  5591K(100)|          |

|   1 |  SORT ORDER BY                 |                      |  4712K|  4678M|  5259M|  5591K  (1)| 21:44:43 |

|   2 |   NESTED LOOPS OUTER           |                      |  4712K|  4678M|       |  4948K  (1)| 19:14:33 |

|   3 |    NESTED LOOPS OUTER          |                      |  4344K|  3500M|       |  2770K  (2)| 10:46:25 |

|   4 |     NESTED LOOPS OUTER         |                      |  4344K|  1831M|       |   592K  (4)| 02:18:16 |

|   5 |      TABLE ACCESS FULL         | BULK_SUBSCRIBER_JOIN |     6 |   234 |       |     2   (0)| 00:00:01 |

|*  6 |      TABLE ACCESS FULL         | ACCOUNT_SUBSCRIBER   |   724K|   278M|       | 98761   (4)| 00:23:03 |

|   7 |     TABLE ACCESS BY INDEX ROWID| ACCOUNT_SUBSCRIBER   |     1 |   403 |       |     1   (0)| 00:00:01 |

|*  8 |      INDEX RANGE SCAN          | I_ACCOUNT_NO         |     1 |       |       |     1   (0)| 00:00:01 |

|   9 |    TABLE ACCESS BY INDEX ROWID | ACCOUNT_SUBSCRIBER   |     1 |   196 |       |     1   (0)| 00:00:01 |

|* 10 |     INDEX RANGE SCAN           | I_ACCOUNT_NO         |     1 |       |       |     1   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   6 - filter(("D"."SUBSCR_NO"="A"."SUBSCR_NO" AND "D"."SUBSCR_NO_RESETS"="A"."SUBSCR_NO_RESETS"))

   8 - access("C"."ACCOUNT_NO"="A"."TARGET_ACCOUNT_NO")

  10 - access("B"."ACCOUNT_NO"=DECODE("A"."ACCOUNT_NO",0,"A"."PARENT_ACCOUNT_NO","A"."ACCOUNT_NO"))

联系DBA，强制使oracle执行下列执行计划

--------------------------------------------------------------------------------------------------------

| Id  | Operation                       | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                |                      |     1 |  1248 |    12   (9)| 00:00:01 |

|   1 |  SORT ORDER BY                  |                      |     1 |  1248 |    12   (9)| 00:00:01 |

|   2 |   NESTED LOOPS OUTER            |                      |     1 |  1248 |    11   (0)| 00:00:01 |

|   3 |    NESTED LOOPS OUTER           |                      |     1 |   845 |    10   (0)| 00:00:01 |

|   4 |     NESTED LOOPS OUTER          |                      |     1 |   442 |     9   (0)| 00:00:01 |

|   5 |      TABLE ACCESS FULL          | BULK_SUBSCRIBER_JOIN |     1 |    39 |     2   (0)| 00:00:01 |

|   6 |      TABLE ACCESS BY INDEX ROWID| ACCOUNT_SUBSCRIBER   |   724K|   278M|     7   (0)| 00:00:01 |

|*  7 |       INDEX RANGE SCAN          | ASR_PK               |    14 |       |     1   (0)| 00:00:01 |

|   8 |     TABLE ACCESS BY INDEX ROWID | ACCOUNT_SUBSCRIBER   |     1 |   403 |     1   (0)| 00:00:01 |

|*  9 |      INDEX RANGE SCAN           | I_ACCOUNT_NO         |     1 |       |     1   (0)| 00:00:01 |

|  10 |    TABLE ACCESS BY INDEX ROWID  | ACCOUNT_SUBSCRIBER   |     1 |   403 |     1   (0)| 00:00:01 |

|* 11 |     INDEX RANGE SCAN            | I_ACCOUNT_NO         |     1 |       |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   7 - access("D"."SUBSCR_NO"="A"."SUBSCR_NO"(+) AND "D"."SUBSCR_NO_RESETS"="A"."SUBSCR_NO_RESETS"(+))

   9 - access("B"."ACCOUNT_NO"(+)=DECODE("A"."ACCOUNT_NO",0,"A"."PARENT_ACCOUNT_NO","A"."ACCOUNT_NO"))

  11 - access("C"."ACCOUNT_NO"(+)="A"."TARGET_ACCOUNT_NO")