oracle效能問題：sql語句優化

四川同事反映，前臺業務處理任務排隊嚴重，資料插入和查詢反應時間太長。大約影響時間一個小時左右。現已恢復正常，請協助查詢問題原因。發生時間上午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/，如需轉載，請註明出處，否則將追究法律責任。