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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- sql語句效能優化SQL優化
- SQL語句優化SQL優化
- [20211229]toad下優化sql語句注意的問題.txt優化SQL
- MySQL 52個SQL效能優化策略SQL語句彙總MySql優化
- MYSQL SQL語句優化MySql優化
- sql語句執行順序與效能優化(1)SQL優化
- Mysql 52條SQL語句效能優化策略彙總MySql優化
- Oracle效能優化-SQL優化(案例一)Oracle優化SQL
- Oracle效能優化-SQL優化(案例二)Oracle優化SQL
- Oracle效能優化-SQL優化(案例三)Oracle優化SQL
- Oracle效能優化-SQL優化(案例四)Oracle優化SQL
- MySQL之SQL語句優化MySql優化
- [20201210]sql語句優化.txtSQL優化
- 優化 SQL 語句的步驟優化SQL
- Oracle基本SQL語句OracleSQL
- [20200320]SQL語句優化的困惑.txtSQL優化
- SQL查詢語句 (Oracle)SQLOracle
- Oracle SQL精妙SQL語句講解OracleSQL
- Oracle優化案例-join列索引缺失導致的sql效能問題(二十六)Oracle優化索引SQL
- ORACLE中sql語句----運算子的優先順序OracleSQL
- [20181114]一條sql語句的優化.txtSQL優化
- SQL優化案例-單表分頁語句的優化(八)SQL優化
- 《MySQL慢查詢優化》之SQL語句及索引優化MySql優化索引
- Java中如何解析SQL語句、格式化SQL語句、生成SQL語句?JavaSQL
- Oracle優化案例-單表分頁語句的優化(八)Oracle優化
- 列出oracle dbtime得sql語句OracleSQL
- Oracle SQL效能優化的40條軍規OracleSQL優化
- 50個SQL語句(MySQL版) 問題十四MySql
- [20211221]分析sql語句遇到的問題.txtSQL
- Sql語句本身的優化-定位慢查詢SQL優化
- [20200324]SQL語句優化的困惑2.txtSQL優化
- SQL語句優化的原則與方法QOSQL優化
- 20180417PLSQL中sql語句格式化與註解問題SQL
- oracle 效能優化Oracle優化
- 後臺執行SQL語句(oracle)SQLOracle
- Oracle優化案例-從執行計劃定位SQL問題(三)Oracle優化SQL
- SQL語句最佳化SQL
- MySql常用30種SQL查詢語句優化方法MySql優化
- Oracle 高效能SQL引擎剖析--SQL優化與調優機制詳解OracleSQL優化