上午10点公司一个集中交易数据库突发大量opr用户的连接，使得超出数据库session数限制，发现已无法登陆，后台也登陆不了，紧急kill部分LOCAL=NO进程登陆到数据库。检查发现两个sql效率不高造成了session等待，以致超出数据库的连接数限制。下面是这两个sql，从sql和执行计划看，都涉及到一张表的全表扫描PAYMENT_ACCEPT_MAIN，在process_date列上建索引，知会开发到开发库做测试ok后下紧急版本搞定。临时规避措施，对该opr用户加profile限制连接数，并监控该用户连接达到一定数量kill掉一些,另外找维护时间加大session数

select 'alter system kill session '||''''||a.SID||','||a.SERIAL#||''';'

from v$session a, v$sql b

where a.SQL_HASH_VALUE = b.HASH_VALUE

and b.HASH_VALUE in (2458842900, 402313782);

SQL> @hashvalue_sqlplan 402313782

Optimizer Plan:

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

| SELECT STATEMENT |------------- | | | 6933 |

| SORT ORDER BY | | 1 | 220 | 6933 |

| NESTED LOOPS | | 1 | 220 | 6932 |

| NESTED LOOPS | | 1 | 199 | 6931 |

| TABLE ACCESS FULL |PAYMENT_ACCEPT_MAIN | 1 | 57 | 6930 |

| TABLE ACCESS BY INDEX ROWID |PAYMENT_BILL_MAIN | 1 | 142 | 1 |

| INDEX UNIQUE SCAN |PK_PM_BILL_MN_ACCEPT_NO | 1 | | 1 |

| INDEX UNIQUE SCAN |PK_PM_TX_LOG_ACCEPT_NO | 1 | 21 | 1 |

10 rows selected

sqltext：

select t1.accept_no,
      t1.payer_name,
       t1.payer_account_no,
       t1.payee_name,
       t1.payee_account_no,
       t1.amount,
       t1.payee_bank_name,
       t1.payee_bank_no,
       t2.process_date,
       t2.processor_id,
       t2.processor_name
from payment_bill_main t1, payment_accept_main t2, payment_tx_log t3
where t1.accept_no = t3.accept_no
   and t2.accept_no = t1.accept_no
   and t1.CHARGE_TYPE_CODE = '01'
   and t1.POUNDAGE > 0
   and t2.PROCESSOR_ID = :1
   and t2.SITE_CODE = :2
   and t2.process_date >= trunc(sysdate)
   and t2.process_date < trunc(sysdate) + 1
   and t2.biz_status_code <> '210'

SQL> @hashvalue_sqlplan 2458842900

Optimizer Plan:

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

| SELECT STATEMENT |------------- | | | 6934 |

| SORT ORDER BY | | 3 | 648 | 6934 |

| NESTED LOOPS OUTER | | 3 | 648 | 6933 |

| NESTED LOOPS | | 3 | 489 | 6932 |

| NESTED LOOPS | | 3 | 285 | 6931 |

| TABLE ACCESS FULL |PAYMENT_ACCEPT_MAIN | 3 | 213 | 6930 |

| TABLE ACCESS BY INDEX ROWID |PAYMENT_BILL_EXT | 1 | 24 | 1 |

| INDEX UNIQUE SCAN |PK_PM_BILL_EXT_ACCEPTNO | 1 | | 1 |

| TABLE ACCESS BY INDEX ROWID |PAYMENT_BILL_MAIN | 1 | 68 | 1 |

| INDEX UNIQUE SCAN |PK_PM_BILL_MN_ACCEPT_NO | 1 | | 1 |

| TABLE ACCESS BY INDEX ROWID |PAYMENT_TX_LOG | 1 | 53 | 1 |

| INDEX UNIQUE SCAN |PK_PM_TX_LOG_ACCEPT_NO | 1 | | 1 |

13 rows selected

SELECT b.ACCEPT_NO as ACCEPT_NO,
       b.BIZ_TYPE_CODEas BIZ_TYPE_CODE,
       b.BAR_CODE as BAR_CODE,
       b.BIZ_STATUS_CODE as STATUS,
       b.PROCESS_DATE as SGN_DATE_STR,
       b.PROCESSOR_ID as PROCESSOR_ID,
       a.TRXS_NO as TRXS_NO,
       a.OPERATOR as OPERATOR,
       a.SGN_DATE as TX_DATE,
       c.PAYER_ACCOUNT_NO as PAYER_ACCOUNT_NO,
       c.PAYER_NAME as PAYER_NAME,
       c.AMOUNT as AMOUNT,
       nvl(a.tx_path, c.SEND_PATH_CODE) as TX_PATH,
       f.FLEX_VALUE10
FROM PAYMENT_TX_LOG      a,
       PAYMENT_ACCEPT_MAIN b,
       PAYMENT_BILL_MAIN   c,
       PAYMENT_BILL_EXT    f
WHERE 1 = 1
   and a.ACCEPT_NO(+) = b.ACCEPT_NO
   and b.BIZ_TYPE_CODE != '2011'
   and b.ACCEPT_NO = f.ACCEPT_NO
   AND b.BIZ_STATUS_CODE is not null
   AND b.ACCEPT_NO = c.ACCEPT_NO
   AND b.PROCESSOR_ID = :1
   AND b.process_date >= :2
   AND b.process_date < :3 + 1
   AND c.currency_code = :4
   AND b.SITE_CODE in (:5, :6)
order by b.ACCEPT_NO

ITPub博客

4月7日故障处理

最新文章