oracle性能问题：sql语句优化

最新推荐文章于 2024-09-24 10:50:41 发布

cixie24082

最新推荐文章于 2024-09-24 10:50:41 发布

阅读量75

点赞数

文章标签：数据库

四川同事反映，前台业务处理任务排队严重，数据插入和查询反应时间太长。大约影响时间一个小时左右。现已恢复正常，请协助查询问题原因。发生时间上午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/