系统割接之后,用户资料表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 |
| Select /*+ USE_NL(a b c) */ a.... | |
3,184,950 | 8,646 | 368.37 | 0.63 | 36.79 | 49.5 | .3 | URE_U9 | begin PUBLIC_PKG_NOTIF_CIRQ . ... | |
2,513,179 | 233 | 10,786.18 | 0.50 | 170.38 | 58.4 | 0 | URE_U9 | begin PUBLIC_PKG_NOTIF_CIRQ . ... | |
2,277,956 | 9 | 253,106.22 | 0.45 | 23.97 | 53.8 | 0 | 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 | URE_U9 | SELECT SUB_MSISDN, NVL(IMSI, '... | |
1,539,048 | 384,761 | 4.00 | 0.31 | 7.57 | 50.8 | .7 | URE_U9 | SELECT SUB_MSISDN, NVL(IMSI, '... | |
550,432 | 668 | 824.00 | 0.11 | 8.12 | 55.5 | 0 | LTP@sdp1 (TNS V1-V3) | SELECT EAC.TABLE_NAME, EAC.BIL... | |
420,264 | 234 | 1,796.00 | 0.08 | 28.20 | 59 | 0 | URE_U9 | select count(*) from NOTIF_QUE... | |
418,468 | 233 | 1,796.00 | 0.08 | 28.76 | 57.5 | 0 | URE_U9 | SELECT COUNT(1) FROM NOTIF_QUE... | |
418,468 | 234 | 1,788.32 | 0.08 | 28.09 | 58.9 | 0 | 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")