四川同事反映,前台业务处理任务排队严重,数据插入和查询反应时间太长。大约影响时间一个小时左右。现已恢复正常,请协助查询问题原因。发生时间上午10点-11点之间。
问题点应该在数据库端了,由于是前天的事情,考虑执行一个awr报告。查看一下数据库当时的性能情况。
注意到Top 10 SQL 报告里一条语句明显异常,根据SQL Id链接到完整的语句项上,看到delete from t_check_retail_bill_record where operator_id='dfhq_n' and type='01' and checkdate='20121226';这是业务对账功能的语句,正常情况不应该在这个时段出现该功能语句,而且是delete功能,初步判断是维护人员操作失误,要清除掉数据。这一时间段共执行了16次同样类型的SQL,每次执行都用耗费64秒的时间,共耗费约17分钟的时间。select t1.sql_text
from DBA_HIST_SQLTEXT t1,dba_hist_sqlstat t2
where t1.sql_text like 'delete from t_check_retail_bill_record%'
and t2.sql_id=t1.sql_id and t2.snap_id=33091;
很明显以上语句出现了锁等待以及性能下降,经初步分析此时间段内的性能数据, 发现类似以下的SQL耗用了大量的时间, 对系统造成了很大的压力。
原因找到了,如何避免悲剧重演?
SQL> select num_rows from dba_tables where table_name='T_CHECK_RETAIL_BILL_RECORD' and wner='HQCHECK';
NUM_ROWS
----------
14309740
这个表有14309740条记录,占用了1576MB的空间,算是个巨大表;
看看索引情况:
SQL> select OWNER,INDEX_NAME,TABLE_NAME from dba_indexes where WNER='HQCHECK' and TABLE_NAME='T_CHECK_RETAIL_BILL_RECORD';
没有建立index,因此上面的SQL每次都会全表扫描;
SQL> select 1 from HQCHECK.t_check_retail_bill_record t where t.operator_id='B00A' and t.type='03' and t.checkdate='20121226';
no rows selected
Elapsed: 00:00:25.37
Execution Plan
----------------------------------------------------------
Plan hash value: 3096934412
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 44395 (1)| 00:08:53 |
|* 1 | TABLE ACCESS FULL| T_CHECK_RETAIL_BILL_RECORD | 1 | 20 | 44395 (1)| 00:08:53 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."OPERATOR_ID"='B00A' AND "T"."CHECKDATE"='20121226' AND
"T"."TYPE"='03')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
194848 consistent gets
194769 physical reads
0 redo size
270 bytes sent via SQL*Net to client
389 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
创建索引:create index HQCHECK.chk_bill_idx on HQCHECK.t_check_retail_bill_record(checkdate,operator_id,type) nologging;
SQL> select 1 from HQCHECK.t_check_retail_bill_record where operator_id='B00A' and type='03' and checkdate='20121226';
no rows selected
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 3950944362
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 3 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| CHK_BILL_IDX | 1 | 20 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("CHECKDATE"='20121226' AND "OPERATOR_ID"='B00A' AND
"TYPE"='03')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
270 bytes sent via SQL*Net to client
389 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
问题得以解决。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28985005/viewspace-764371/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28985005/viewspace-764371/