一條insert語句導致的效能問題分析(一)
今天早上開發找我看一個問題,說他們透過程式連線去查一個表的資料的時候,只查到了8條記錄,這個情況著實比較反常,因為從業務上的資料情況來說,不可能只有8條。
但是開發沒有太多的許可權做線上環境的資料檢查,就讓我幫忙看一下。
語句大概是下面這樣的形式。
select count(*) from TEST_VIP_LOG t where t.flag in(2,3) and insert_time >= to_date('2016-03-10','YYYY-MM-DD') and insert_time< to_date('2016-03-17','YYYY-MM-DD')
簡單執行之後,發現返回的結果是2萬多條記錄。
當然我這邊查詢的結果還是有一定的可靠性的。所以開發的這個問題就自然落到了我的頭上,為什麼他們檢視的資料只有8條,而我這邊的資料卻有2萬多條,這個問題聽起來確實有些蹊蹺,但是都是事出有因,簡單瞭解了一下事情的來龍去脈之後,原來他們是在早上八點程式自動連線去做的查詢,我查詢的時候已經到了快10點,這個時間點裡,一切皆有可能,但是為什麼短時間內會有這麼大的資料變化呢,於是我檢視了資料庫的負載情況,發現在八點左右確實有一些DB time的提升,檢視sql方面的變化,也確實發現有一個job在執行,而執行的過程中會涉及這個表TEST_VIP_LOG的資料變更。看起來問題似乎是有了一些眉目。但是當我檢視鎖的情況時,整個人都不好了。
$ sh showlock.sh
Current Locks
-------------
SID_SERIAL ORACLE_USERN OBJECT_NAME LOGON_TIME SEC_WAIT OSUSER MACHINE PROGRAM STAT STATUS LOCK_ MODE_HELD
------------ ------------ ------------------------- ------------------- --------- ---------- ------------ -------------------- ---------- ---------- ----- ----------
2655,14247 SYS TEST_VIP_LOG 2016-03-16 01:03:25 0 oracle statg2.cyou. oracle@statg2.cyou.c WAITING ACTIVE DML Row-X (SX)
可以看到有一個session還在active狀態,而且相關的表正是test_vip_log,而且這個session是在凌晨1點登陸的,一直到了早上十點多還在執行。也就間接意味著執行了近10個小時。
關聯了一下對應的session執行的語句,發現是一條insert語句,竟然執行了近10個小時。
$ sh showsessql.sh 2655,14247
SQL_ID SQL_TEXT
------------------------------ ------------------------------------------------------------
d1zs82wnrs52u INSERT INTO TEST_VIP_LOG(CN,GRADE,RANK,SCORE,FLAG,INSERT_TIM
E,OLD_RANK,SIGN,STATUS,TAG,OLD_SCORE) SELECT A.CN,A.GRADE,A.
RANK,A.SCORE,DECODE(SIGN(A.RANK-(NVL(B.RANK,-1))),1,2,-1,3,0
,1), SYSDATE,(NVL(B.RANK,-1)),B.SIGN,B.FLAG,B.TAG,B.SCORE FR
OM ( SELECT * FROM TEST_VIP_NEW MINUS SELECT * FROM TEST_VIP_NEW_BAK
) A LEFT JOIN TEST_VIP_NEW_BAK B ON A.CN=B.CN
然後就開始想這個語句是在幾個月以前有一個需求變更,裡面有兩個表TEST_VIP_NEW和TEST_VIP_NEW_BAK做一些關聯,然後把資料插入TEST_VIP_LOG,這個關聯看起來還是比較奇怪的。
我們來簡單看一看。
insert into TEST_vip_log(CN,GRADE,RANK,SCORE,FLAG,INSERT_TIME,OLD_RANK,sign,stat
us,TAG,OLD_SCORE)
select a.cn,a.GRADE,a.RANK,a.SCORE,DECODE(sign(a.rank-(NVL(b.rank,-1))),1,2,-1
,3,0,1),
sysdate,(NVL(b.rank,-1)),b.sign,b.flag,b.tag,b.score
from
(
select * from TEST_vip_new minus select * from TEST_vip_new_bak
) a left join TEST_vip_new_bak b
on a.cn=b.cn ;
首先test_vip_new會和test_vip_new_bak做一個minus操作,會以test_vip_new為基準匹配,然後得到的結果集再和test_vip_new_bak繼續匹配,左連線匹配。
總體來看這個對映關係沒有任何意義啊。可以做一個簡單的測試來說明。兩個表存在一個欄位id,然後做匹配
SQL> create table a (id number);
Table created.
SQL> create table b (id number);
Table created.
SQL> insert into a values(1);
1 row created.
SQL> insert into a values(2);
1 row created.
SQL> insert into b values(1);
1 row created.
SQL> select * from a minus select * from b;
ID
----------
2
minus之後得到的結果是id=2的記錄,然後再和表b對映,那麼這種對映關係得到的結果是下面的形式。
SQL> select *from (select * from a minus select * from b) a left join b on a.id=b.id;
ID ID
---------- ----------
2
感覺這種表連線方式就是多餘的,因為minus之後的結果,表b中肯定是沒有匹配的值,再一次關聯也實在是浪費。
然後回到原本的sql語句。
xxxx (select * from TEST_vip_new minus select * from TEST_vip_new_bak
) a left join TEST_vip_new_bak b
on a.cn=b.cn
這個表test_vip_new_bak反覆關聯,這個表的資料是怎麼得來的呢,原來在job開始執行的時候就會重新初始化這個表的資料
execute immediate 'truncate table TEST_vip_new_bak';
insert /*+ append*/ into TEST_vip_new_bak select * from TEST_vip_new;
COMMIT;
按照目前的分析思路,可見test_vip_new裡面的資料和test_vip_new_bak中的資料差別很小,為什麼不直接去增量的資料呢。帶著疑問感覺好像找到了問題的關鍵,然後把開發的同學叫上來一起討論一番,其實對於我來說是比較好奇為什麼會寫出那樣的表關聯,當時是出於什麼特別的考慮。
但是開發沒有太多的許可權做線上環境的資料檢查,就讓我幫忙看一下。
語句大概是下面這樣的形式。
select count(*) from TEST_VIP_LOG t where t.flag in(2,3) and insert_time >= to_date('2016-03-10','YYYY-MM-DD') and insert_time< to_date('2016-03-17','YYYY-MM-DD')
簡單執行之後,發現返回的結果是2萬多條記錄。
當然我這邊查詢的結果還是有一定的可靠性的。所以開發的這個問題就自然落到了我的頭上,為什麼他們檢視的資料只有8條,而我這邊的資料卻有2萬多條,這個問題聽起來確實有些蹊蹺,但是都是事出有因,簡單瞭解了一下事情的來龍去脈之後,原來他們是在早上八點程式自動連線去做的查詢,我查詢的時候已經到了快10點,這個時間點裡,一切皆有可能,但是為什麼短時間內會有這麼大的資料變化呢,於是我檢視了資料庫的負載情況,發現在八點左右確實有一些DB time的提升,檢視sql方面的變化,也確實發現有一個job在執行,而執行的過程中會涉及這個表TEST_VIP_LOG的資料變更。看起來問題似乎是有了一些眉目。但是當我檢視鎖的情況時,整個人都不好了。
$ sh showlock.sh
Current Locks
-------------
SID_SERIAL ORACLE_USERN OBJECT_NAME LOGON_TIME SEC_WAIT OSUSER MACHINE PROGRAM STAT STATUS LOCK_ MODE_HELD
------------ ------------ ------------------------- ------------------- --------- ---------- ------------ -------------------- ---------- ---------- ----- ----------
2655,14247 SYS TEST_VIP_LOG 2016-03-16 01:03:25 0 oracle statg2.cyou. oracle@statg2.cyou.c WAITING ACTIVE DML Row-X (SX)
可以看到有一個session還在active狀態,而且相關的表正是test_vip_log,而且這個session是在凌晨1點登陸的,一直到了早上十點多還在執行。也就間接意味著執行了近10個小時。
關聯了一下對應的session執行的語句,發現是一條insert語句,竟然執行了近10個小時。
$ sh showsessql.sh 2655,14247
SQL_ID SQL_TEXT
------------------------------ ------------------------------------------------------------
d1zs82wnrs52u INSERT INTO TEST_VIP_LOG(CN,GRADE,RANK,SCORE,FLAG,INSERT_TIM
E,OLD_RANK,SIGN,STATUS,TAG,OLD_SCORE) SELECT A.CN,A.GRADE,A.
RANK,A.SCORE,DECODE(SIGN(A.RANK-(NVL(B.RANK,-1))),1,2,-1,3,0
,1), SYSDATE,(NVL(B.RANK,-1)),B.SIGN,B.FLAG,B.TAG,B.SCORE FR
OM ( SELECT * FROM TEST_VIP_NEW MINUS SELECT * FROM TEST_VIP_NEW_BAK
) A LEFT JOIN TEST_VIP_NEW_BAK B ON A.CN=B.CN
然後就開始想這個語句是在幾個月以前有一個需求變更,裡面有兩個表TEST_VIP_NEW和TEST_VIP_NEW_BAK做一些關聯,然後把資料插入TEST_VIP_LOG,這個關聯看起來還是比較奇怪的。
我們來簡單看一看。
insert into TEST_vip_log(CN,GRADE,RANK,SCORE,FLAG,INSERT_TIME,OLD_RANK,sign,stat
us,TAG,OLD_SCORE)
select a.cn,a.GRADE,a.RANK,a.SCORE,DECODE(sign(a.rank-(NVL(b.rank,-1))),1,2,-1
,3,0,1),
sysdate,(NVL(b.rank,-1)),b.sign,b.flag,b.tag,b.score
from
(
select * from TEST_vip_new minus select * from TEST_vip_new_bak
) a left join TEST_vip_new_bak b
on a.cn=b.cn ;
首先test_vip_new會和test_vip_new_bak做一個minus操作,會以test_vip_new為基準匹配,然後得到的結果集再和test_vip_new_bak繼續匹配,左連線匹配。
總體來看這個對映關係沒有任何意義啊。可以做一個簡單的測試來說明。兩個表存在一個欄位id,然後做匹配
SQL> create table a (id number);
Table created.
SQL> create table b (id number);
Table created.
SQL> insert into a values(1);
1 row created.
SQL> insert into a values(2);
1 row created.
SQL> insert into b values(1);
1 row created.
SQL> select * from a minus select * from b;
ID
----------
2
minus之後得到的結果是id=2的記錄,然後再和表b對映,那麼這種對映關係得到的結果是下面的形式。
SQL> select *from (select * from a minus select * from b) a left join b on a.id=b.id;
ID ID
---------- ----------
2
感覺這種表連線方式就是多餘的,因為minus之後的結果,表b中肯定是沒有匹配的值,再一次關聯也實在是浪費。
然後回到原本的sql語句。
xxxx (select * from TEST_vip_new minus select * from TEST_vip_new_bak
) a left join TEST_vip_new_bak b
on a.cn=b.cn
這個表test_vip_new_bak反覆關聯,這個表的資料是怎麼得來的呢,原來在job開始執行的時候就會重新初始化這個表的資料
execute immediate 'truncate table TEST_vip_new_bak';
insert /*+ append*/ into TEST_vip_new_bak select * from TEST_vip_new;
COMMIT;
按照目前的分析思路,可見test_vip_new裡面的資料和test_vip_new_bak中的資料差別很小,為什麼不直接去增量的資料呢。帶著疑問感覺好像找到了問題的關鍵,然後把開發的同學叫上來一起討論一番,其實對於我來說是比較好奇為什麼會寫出那樣的表關聯,當時是出於什麼特別的考慮。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/23718752/viewspace-2058515/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 一條insert語句導致的效能問題分析(二)
- 一條sql語句導致的資料庫當機問題及分析SQL資料庫
- 一條sql語句“導致”的資料庫當機問題及分析SQL資料庫
- 由一條sql語句導致的系統IO問題SQL
- 一條簡單的sql語句導致的系統問題SQL
- 一條執行4秒的sql語句導致的系統問題SQL
- merge語句導致的效能問題緊急優化優化
- Oracle 使用一條insert語句完成多表插入Oracle
- 【SQL】使用一條INSERT語句完成多表插入SQL
- merge語句導致的ORA錯誤分析
- MySQL 中 一條 order by index limit 語句的分析MySqlIndexMIT
- 一條全表掃描sql語句的分析SQL
- 一條sql語句的建議調優分析SQL
- C 語言宣告與定義不一致導致的問題
- ANALYZE導致的阻塞問題分析
- insert導致的效能問題大排查(r11筆記第26天)筆記
- 如此大的一條sql語句在30個左右的併發訪問系統當中的效能問題?SQL
- mysql insert語句錯誤問題解決MySql
- MySQL8.0 view導致的效能問題MySqlView
- 一個insert插入語句很慢的優化優化
- 一條SQL語句的書寫SQL
- 一條很 巧妙的 SQL 語句SQL
- 一條sql語句的優化SQL優化
- 一條SQL語句的旅行之路SQL
- SCHEDULER呼叫XDB程式導致效能問題
- 【Mysql】兩條insert 語句產生的死鎖MySql
- 如何分析一條sql的效能SQL
- 一個CRM OData的效能問題分析
- MySQL:一個簡單insert語句的大概流程MySql
- mysql同一個事務中update,insert導致死鎖問題分析解決MySql
- 一條更新語句的執行流程
- 一條update語句的優化探索優化
- 一條sql語句的改進探索SQL
- 一條簡單SQL語句的構成及語句解析SQL
- 執行計劃的偏差導致的效能問題
- 一個JTextPane寫SQL語句的問題SQL
- 一次oracle行級鎖導致的問題Oracle
- 一條簡單的sql語句執行15天的原因分析SQL