「Hard Parseに時間が掛かる手軽なSQLを作りたい。彡(ﾟ)(ﾟ)」と呟いた
(※下記twitterモーメント参照)ところ、色々とアイデアを頂いたので、
全部ではないのですがお試し＆まとめてみましたやで彡(ﾟ)(ﾟ)
Hard Parseに時間が掛かるSQL ※twitterモーメント
https://twitter.com/i/moments/1001431132234072064
Case1. IN句リテラルを仕様上限(999個)まで
こちらの結果は下記の通り、ぼちぼち彡(ﾟ)(ﾟ)
SELECT A.ITEM_NAME
, COUNT(*)
FROM ITEM_TBL A
WHERE ITEM_NO IN (
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,
:
975,976,977,978,979,980,981,982,983,984,985,986,987,988,989,990,991,992,993,994,995,996,997,998,999)
GROUP BY ITEM_NAME
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.08 0.09 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 2 0 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.08 0.09 0 2 0 10
Case2. 結合表8個以上
こちらの結果は下記の通り、Case1. よりは増えました彡(ﾟ)(ﾟ)
SELECT A.ITEM_NAME
, COUNT(*)
FROM ITEM_TBL A
, ITEM_TBL B
, ITEM_TBL C
, ITEM_TBL D
, ITEM_TBL E
, ITEM_TBL F
, ITEM_TBL G
, ITEM_TBL H
, ITEM_TBL I
, ITEM_TBL J
WHERE J.REGIST_DATE BETWEEN TO_DATE('2012/08/01', 'YYYY/MM/DD')
AND TO_DATE('2012/08/02', 'YYYY/MM/DD')
AND A.ITEM_NO = B.ITEM_NO
AND B.ITEM_NO = C.ITEM_NO
AND C.ITEM_NO = D.ITEM_NO
AND D.ITEM_NO = E.ITEM_NO
AND E.ITEM_NO = F.ITEM_NO
AND F.ITEM_NO = G.ITEM_NO
AND G.ITEM_NO = H.ITEM_NO
AND H.ITEM_NO = I.ITEM_NO
AND I.ITEM_NO = J.ITEM_NO
GROUP BY A.ITEM_NAME
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.48 0.48 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 10 0 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.48 0.48 0 10 0 10
Case3. 結合表8個以上 + permutation増強
こちらの結果は下記の通り、Case2. より更に増加彡(ﾟ)(ﾟ)
ALTER SESSION SET "_optimizer_max_permutations"=80000;
SELECT A.ITEM_NAME
, COUNT(*)
FROM ITEM_TBL A
, ITEM_TBL B
, ITEM_TBL C
, ITEM_TBL D
, ITEM_TBL E
, ITEM_TBL F
, ITEM_TBL G
, ITEM_TBL H
, ITEM_TBL I
, ITEM_TBL J
WHERE J.REGIST_DATE BETWEEN TO_DATE('2012/08/01', 'YYYY/MM/DD')
AND TO_DATE('2012/08/02', 'YYYY/MM/DD')
AND A.ITEM_NO = B.ITEM_NO
AND B.ITEM_NO = C.ITEM_NO
AND C.ITEM_NO = D.ITEM_NO
AND D.ITEM_NO = E.ITEM_NO
AND E.ITEM_NO = F.ITEM_NO
AND F.ITEM_NO = G.ITEM_NO
AND G.ITEM_NO = H.ITEM_NO
AND H.ITEM_NO = I.ITEM_NO
AND I.ITEM_NO = J.ITEM_NO
GROUP BY A.ITEM_NAME
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.66 0.68 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 10 0 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.67 0.69 0 10 0 10
Case4. UNION500行
こちらの結果は下記の通り、エエ感じやで！彡(^)(^)
SELECT ITEM_NO FROM ITEM_TBL WHERE ITEM_NO BETWEEN 1 AND 100 UNION
SELECT ITEM_NO FROM ITEM_TBL WHERE ITEM_NO BETWEEN 1 AND 100 UNION
SELECT ITEM_NO FROM ITEM_TBL WHERE ITEM_NO BETWEEN 1 AND 100 UNION
SELECT ITEM_NO FROM ITEM_TBL WHERE ITEM_NO BETWEEN 1 AND 100 UNION
SELECT ITEM_NO FROM ITEM_TBL WHERE ITEM_NO BETWEEN 1 AND 100 UNION
:
SELECT ITEM_NO FROM ITEM_TBL WHERE ITEM_NO BETWEEN 1 AND 100
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 1.04 1.05 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.01 0 500 0 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 1.06 1.06 0 500 0 10
Case5. DUAL表を200個結合
Kazumasa Obaraさんネタ。元ネタの500個だと
終わらなかったので200個で計測。ぶっちぎりで草ｗｗｗｗｗ
SELECT A.* FROM DUAL A,
DUAL,
:
DUAL,
DUAL,
DUAL,
DUAL,
DUAL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 48.86 49.96 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 48.86 49.96 0 3 0 1
やっぱり結合順序の探索には時間が掛かるんやなぁ……彡(ﾟ)(ﾟ)
アイデアを頂いた皆さん、ありがとうございます！(｀･ω･)ゞ