生產sql調優之統計資訊分析
首先就是和他們確認最近有什麼改動,他們說這個是用了很久的sql語句了,沒有任何的改動,再聽他們說,之前也沒有任何的問題。
然後就和他們確認之前這個jobl處理大概多長時間,那個哥們說他也是最近才進的這個專案,可能資料量不同,他也不清楚。但是他肯定的說這個job會跑的很快,幾個小時肯定能處理完。
大概瞭解了下,他們也確定具體的sql語句是什麼,沒有得到太多的資訊,首先通過top命令來抓一下目前消耗資源比較多的程式。
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
9543 oradb1 25 0 12.2g 25m 21m R 100.0 0.0 426:03.72 oracleDB01 (LOCAL=NO)
32334 oradb1 25 0 12.2g 26m 22m R 100.0 0.0 36:46.28 oracleDB01 (LOCAL=NO)
通過如下的指令碼可以抓到當前的process正在進行的sql語句。
if [ -z "$1" ]; then
echo "no process has provided!"
exit 0
fi
sh_tmp_process=`sqlplus -silent $DB_CONN_STR@$SH_DB_SID <
select addr from v\\$process where spid=$1;
exit;
END`
if [ -z "$sh_tmp_process" ]; then
echo "no process exists or session is not from a DB account"
echo
echo "####### Process Information from OS level as below ########"
ps -ef|grep $1|grep -v "grep"|grep ora
echo "##############################################"
exit 0
else
echo '*******************************************'
echo "Process has found, pid: $1 , addr: $sh_tmp_process "
echo
echo "####### Process Information from OS level as below ########"
ps -ef|grep $1|grep -v grep|grep ora
echo "##############################################"
sqlplus -s $DB_CONN_STR@$SH_DB_SID <
col terminal format a15
col osuser format a15
col process format a15
col username format a15
set linesize 150
select sid,serial#,username,osuser ,machine,process,terminal,type,to_char(LOGON_TIME,'yyyy-mm-dd hh24:mi:ss')login_time from v\$session
where paddr='$sh_tmp_process';
prompt .
col sql_id format a30
col prev_sql_id format a30
col sql_text format a60
set linesize 150
set pages 50
select sql_id,sql_text from v\$sql where sql_id in (select sql_id from v\$session where paddr='$sh_tmp_process' and sql_id is not null ) and rownum<2;
select sql_id prev_sql_id ,sql_text from v\$sql where sql_id in (select prev_sql_id sql_id from v\$session where paddr='$sh_tmp_process' ) and rownum<2;
EOF
fi
結果看了一下,和他們確認,就是他們的job要用的sql語句。可以看到裡面已經有一些hint,想必是之前就碰到過問題,優化過的。
這個語句也算是挺長的一個sql了,裡面用到了union all來做兩個結果的合併。
SELECT DISTINCT 'K',
AR.RESOURCE_VALUE,
AR.RESOURCE_TYPE,
GREATEST(TO_CHAR(AR.EFFECTIVE_DATE, 'YYYYMMDDHH24MISS'),
TO_CHAR(SB.EFFECTIVE_DATE, 'YYYYMMDDHH24MISS')),
LEAST(NVL(TO_CHAR(AR.EXPIRATION_DATE, 'YYYYMMDDHH24MISS'),
'47001231000000'),
NVL(TO_CHAR(SB.EXPIRATION_DATE, 'YYYYMMDDHH24MISS'),
'47001231000000')),
AR.AGR_NO,
SB.MEDIUM_CUS_ID,
SB.SUB_STATUS,
SB.BUSINESS_ENTITY_ID,
SB.LANGUAGE,
SB.ROUTING_POLICY_ID,
SB.L9_PORT_IND,
SB.L9_SPLIT_PERIOD
FROM HUGE_RESOURCE AR,
MEDIUM_SUB SB,
(select /*+ RESULT_CACHE */
DISTINCT PARAM_NAME as PARAM_NAME
from SMALL_PARAM
where GUIDING_IND = 'Y') OP,
MEDIUM_CUS CS
WHERE AR.AGR_NO = 1056851
AND AR.AGREEMENT_KEY = MOD(1056851, 100)
AND (AR.RESOURCE_STATE != 'F' OR AR.RESOURCE_STATE IS NULL)
AND AR.RANGE_IND = 'N'
AND SB.MEDIUM_SUB_NO = AR.AGR_NO
AND EXISTS
(select /*+ INDEX(OP SMALL_PARAM_1IX) */
1
from SMALL_PARAM OP
where OP.PARAM_NAME in (AR.RESOURCE_PRM_CD, AR.BASE_PARAM_NAME) )
AND SB.MEDIUM_CUS_ID = CS.MEDIUM_CUS_ID
AND (SB.EXPIRATION_DATE IS NULL OR SB.EXPIRATION_DATE >= to_date('19000101000000','YYYYMMDDHH24MISS'))
AND (AR.EXPIRATION_DATE IS NULL OR AR.EXPIRATION_DATE >= to_date('19000101000000','YYYYMMDDHH24MISS'))
AND (AR.EFFECTIVE_DATE < SB.EXPIRATION_DATE OR
SB.EXPIRATION_DATE IS NULL)
AND (AR.EXPIRATION_DATE IS NULL OR
AR.EXPIRATION_DATE > SB.EFFECTIVE_DATE)
AND SB.SUB_STATUS != 'T'
UNION ALL
SELECT DISTINCT 'K',
AR.RESOURCE_VALUE,
AR.RESOURCE_TYPE,
GREATEST(TO_CHAR(AR.EFFECTIVE_DATE, 'YYYYMMDDHH24MISS'),
TO_CHAR(SH.EFFECTIVE_DATE, 'YYYYMMDDHH24MISS')),
LEAST(NVL(TO_CHAR(AR.EXPIRATION_DATE, 'YYYYMMDDHH24MISS'),
'47001231000000'),
NVL(TO_CHAR(SH.EXPIRATION_DATE, 'YYYYMMDDHH24MISS'),
'47001231000000')),
AR.AGR_NO,
SH.MEDIUM_CUS_ID,
SH.SUB_STATUS,
SH.BUSINESS_ENTITY_ID,
SH.LANGUAGE,
SH.ROUTING_POLICY_ID,
SH.L9_PORT_IND,
SH.L9_SPLIT_PERIOD
FROM HUGE_RESOURCE AR,
MEDIUM_SUB_HISTORY SH,
(select /*+ RESULT_CACHE */
DISTINCT PARAM_NAME as PARAM_NAME
from SMALL_PARAM
where GUIDING_IND = 'Y') OP,
MEDIUM_CUS CS
WHERE AR.AGR_NO = 1056851
AND AR.AGREEMENT_KEY = MOD(1056851, 100)
AND (AR.RESOURCE_STATE != 'F' OR AR.RESOURCE_STATE IS NULL)
AND AR.RANGE_IND = 'N'
AND SH.MEDIUM_SUB_NO = AR.AGR_NO
AND EXISTS
(select /*+ INDEX(OP SMALL_PARAM_1IX) */
1
from SMALL_PARAM OP
where OP.PARAM_NAME in (AR.RESOURCE_PRM_CD, AR.BASE_PARAM_NAME))
AND SH.MEDIUM_CUS_ID = CS.MEDIUM_CUS_ID
AND (SH.EXPIRATION_DATE IS NULL OR SH.EXPIRATION_DATE >= to_date('19000101000000','YYYYMMDDHH24MISS'))
AND (AR.EXPIRATION_DATE IS NULL OR AR.EXPIRATION_DATE >= to_date('19000101000000','YYYYMMDDHH24MISS'))
AND (AR.EFFECTIVE_DATE < SH.EXPIRATION_DATE OR
SH.EXPIRATION_DATE IS NULL)
AND (AR.EXPIRATION_DATE IS NULL OR
AR.EXPIRATION_DATE > SH.EFFECTIVE_DATE)
AND SH.SUB_STATUS NOT IN ('C', 'T')
和他們確認了基本的時間後,先抓一個awr看看裡面怎麼說。然後在這個基礎上抓一個awr的sql report。看看執行計劃的情況。
從執行計劃來看,也是一個執行時間比較的sql語句,檢視指標,消耗並不高,大概執行時間為5秒,但是根據他們的反饋,需要跑幾十萬個這樣的查詢。這樣下來。這個語句得跑
8秒*80萬次/60/60=111個小時,這樣確實太長了。得跑將近5天的樣子。
首先既然他們肯定了sql語句沒有做過改動,而且也已經經過調優了,那就先看看為什麼顯示執行計劃消耗沒那麼高,實際上卻差別這麼大,首先想到的就是statistics的問題。
在檢視了關聯的幾個達標之後,發現有一個大表的統計資訊誤差有10%左右,其他的都在1%以內的樣子。
這也是一個可能原因,在檢視索引的使用情況,都走索引了,沒有任何索引失效的情況。
sql語句不能隨便動,只能下手的地方就是統計資訊了,其他的效能引數一直都沒有做過改動了。
和他們確認之後,先做了一個統計資訊收集。
exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=> NULL, TABNAME => 'HUGE_RESOURCE', CASCADE => TRUE,METHOD_OPT =>'FOR ALL INDEXED COLUMNS SIZE 1', ESTIMATE_PERCENT => 40 , DEGREE => 8,GRANULARITY =>'ALL');
執行之後就建立了一個快照,然後過了幾分鐘之後又生成一個快照,發現明顯快了不少,從5秒降低到了2秒。也算是不少的收穫了。2秒*80000次/60/60=44.4小時,還是有一些問題,然後剩下的問題就是看看之前的執行情況了。
下面是我在最近這一個月左右的時間內現有的awr中找到的。時間浮動還是比較小。
|
Elapsed Time (s) |
Executions |
Elapsed Time per Exec (s) |
%Total |
%CPU |
%IO |
SQL Id |
SQL Module |
SQL Text |
31st May |
305.44 |
145 |
2.11 |
25.56 |
99.69 |
0.26 |
gext1GenericEx@ccbdbpr1 (TNS V1-V3) |
/* */ SELECT DISTINCT 'K', AR.... |
|
21st May |
413.88 |
614 |
0.67 |
1.50 |
99.74 |
0.17 |
gext1GenericEx@ccbdbpr1 (TNS V1-V3) |
/* */ SELECT DISTINCT 'K', AR.... |
|
12th May |
100.17 |
63 |
1.59 |
0.80 |
99.68 |
0.25 |
gext1GenericEx@ccbdbpr1 (TNS V1-V3) |
SELECT DISTINCT 'K', AR.RESOUR... |
|
9th April |
818.45 |
793 |
1.03 |
3.73 |
99.86 |
0.09 |
gext1GenericEx@ccbdbpr1 (TNS V1-V3) |
SELECT DISTINCT 'K', AR.RESOUR. |
然後我仔細比較了一下對應的sql語句。發現今天碰到sql語句和之前的有一些不同之處。看來也不能全信
偶有了他們說的。
有了基本的參照,就可以在這個基礎上分析了。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/23718752/viewspace-1173566/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 生產系統pl/sql調優案例SQL
- 【sql調優】系統資訊統計SQL
- 生產系統調優之_敢於質疑
- 生產系統調優之_毫秒級的改進
- SQL優化之統計資訊和索引SQL優化索引
- ORACLE SQL調優之統計資訊缺失導致的邏輯讀暴增OracleSQL
- 生產系統 SQL 執行異常原因分析SQL
- 生產環境sql語句調優實戰第二篇SQL
- 生產環境sql語句調優實戰第三篇SQL
- 生產環境sql語句調優實戰第四篇SQL
- 生產環境sql語句調優實戰第五篇SQL
- 生產環境sql語句調優實戰第六篇SQL
- 生產環境sql語句調優實戰第八篇SQL
- 生產環境sql語句調優實戰第九篇SQL
- oracle之autotrace統計資訊分析Oracle
- 【sql調優之執行計劃】sort operationsSQL
- 【sql調優之執行計劃】estimator iSQL
- 【sql調優之執行計劃】hash joinSQL
- 【調優篇基本原理】系統統計資訊
- SQL調優--表統計資訊未及時更新導致查詢超級慢SQL
- Linux系統效能調優之效能分析Linux
- 生產環境sql語句調優實戰第七篇SQL
- 生產環境sql語句調優實戰第十篇SQL
- 資料庫效能調優之始: analyze統計資訊資料庫
- 【調優篇基本原理】物件統計資訊物件
- 管好統計資訊,開啟SQL優化之門SQL優化
- 【sql調優之執行計劃】query transformerSQLORM
- 【sql調優之執行計劃】temp table transformationSQLORM
- 【sql調優之執行計劃】in相關的operationSQL
- 【sql調優之執行計劃】merge sort joinSQL
- 手工收集統計資訊及立即產生新的執行計劃
- 【sql調優之執行計劃】獲取執行計劃SQL
- Oracle SQL調優系列之SQL Monitor ReportOracleSQL
- 生產環境大型sql語句調優實戰第一篇(一)SQL
- 生產環境大型sql語句調優實戰第一篇(二)SQL
- 【sql調優之執行計劃】使用hint(五)Hint for parallelSQLParallel
- 【sql調優之執行計劃】使用hint(四)Hints for JoinSQL
- SQL調優SQL