|
IOTN :: Q/A Book >> ±Ã±ÝÇÑ°Ô ÀÖÀ¸¸é ¿Ã·ÁÁÖ¼¼¿ä. 
 |
connect by bug°°Àºµ¥¿ä..
|
¼Û½ÂÇö
[LIST]
|
2013-04-30 17:24:11, Á¶È¸ : 2,817 |

»çÀå´Ô...Àß Áö³»°í °è½ÃÁö¿ä~~
½ÅÇ׸¸Àº ¾îÂî Àß µÇ¼Ì´ÂÁö¿ä...
´Ù¸§ÀÌ ¾Æ´Ï¶ó ¹¹ Á» ¿©ÂÞ¾î º¼·Á°í ÇÕ´Ï´Ù...
À§ Á¦¸ñó·³ connect by bugÀÎ µí ½ÍÀºµ¥¿ä..
µµÀúÈ÷ ´äÀ» ¸øÃ¡°Ú½À´Ï´Ù...¤Ð¤Ð
¾Æ·¡ÀÇ Äõ¸®¸¦ ½ÇÇàÀ» Çϸé 1058°Ç Á¤µµ°¡ ³ª¿Í¾ß Çϴµ¥ 235°ÇÀÌ ³ª¿É´Ï´Ù.
À̸®Àú¸® ã¾ÆºÁ¼ alter session set optimizer_features_enable='10.2.0.4';·Î ³·Ãß¾î¼ ½ÇÇàÀ» Çϸé 335°ÇÀÌ ³ª¿À±¸¿ä
±×·±µ¥ ´õ Ȳ´çÇÑ(?)°ÍÀº Á¶°ÇÀý¿¡¼ Ä÷³ÀÇ Çʵ尪¸¸ ¹Ù²Ù¾ú´Âµ¥ ´Ù¸¥ Çϳª´Â Á¤»óÀ¸·Î ³ª¿É´Ï´Ù.
2900°ÇÀÌ ³ª¿Í¾ß Çϴµ¥ 2300°ÇÀÌ ³ª¿À´Ù°¡ À§ optimizer¸¦ °Á¦·Î ³·Ãß¾î¼ Å×½ºÆ® ÇØ º¸¸é 2900°ÇÀÌ ³ª¿É´Ï´Ù.
µ¿ÀÏÇÑ Å×À̺íÀä...
Á¦°¡ À߸ø¾Æ´Â °É±î¿ä...
ÇØ´ç Äõ¸® ¿Ã·Áµå¸³´Ï´Ù..
¸Ç ¾Æ·¡ÀÇ connect by start with ÀÇ °èÃþÄõ¸®°¡ ¹®Á¦¸¦ ÀÏÀ¸Åµ´Ï´Ù...¤Ð¤Ð
select count(*) from (
select I.standardCode,
I.parentsCode,
I.korName,
I.engName,
I.memo,
I.representationCode,
I.answersetCode,
I.finalFlag,
level,
rownum as rowNumber,
I.sortOrder
from
(
select
D.standardCode,
D.parentsCode,
D.korName,
D.engName,
D.memo,
D.representationCode,
'' as answersetCode,
'M' as finalFlag,
D.origin_order as sortOrder
from (
SELECT
B.STANDARD_CODE as standardCode,
(SELECT PARENTS_CODE FROM TB_LB_INDEX_DETAIL WHERE STANDARD_CODE = B.standard_code AND INDEX_SEQ = A.SEQ) as parentsCode,
trim(SUBSTR( SP_GET_INDEX_TITLE_RE(A.SEQ) , INSTR(SP_GET_INDEX_TITLE_RE(A.SEQ), '>', -1) + 1 )) as korName,
C.ENG_NAME as engName,
SP_GET_INDEX_MEMO(A.SEQ) as memo,
C.REPRESENTATION_CODE as representationCode,
C.ANSWERSET_CODE as answersetCode,
'M' as finalFlag,
a.origin_order
FROM
TB_LB_INDEX A
JOIN
(
SELECT SEQ,
SUBSTR( SP_GET_INDEX_STANDARD_CODE(SEQ), -6 ) AS STANDARD_CODE
FROM TB_LB_INDEX
ORDER BY DICTIONARY_KIND, ORIGIN_ORDER
) B
ON A.SEQ = B.SEQ
JOIN TB_LB_DICTIONARY C
ON B.STANDARD_CODE = C.STANDARD_CODE
and A.DICTIONARY_KIND = 'DKC003'
ORDER BY A.DICTIONARY_KIND, A.ORIGIN_ORDER
) D
union
SELECT Q.standard_code as standardCode,
F.standard_code as parentsCode,
Q.KOR_NAME as korName,
Q.ENG_NAME as engName,
Q.MEMO as memo,
Q.representation_code as representationCode,
(SELECT SET_CODE FROM TB_LB_QUESTION_INDEX WHERE STANDARD_CODE=Q.standard_code) as answersetCode,
'F' as finalFlag ,
999 as sortOrder
FROM TB_LB_INDEX E,TB_LB_INDEX_DETAIL F,TB_LB_DICTIONARY G ,TB_LB_DICTIONARY Q
where E.DICTIONARY_KIND='DKC003'
AND G.DEL_YN = 'Y'
and E.seq=F.INDEX_SEQ
and F.standard_code=G.standard_code
and F.index_order= (
select max(T.index_order)
from TB_LB_INDEX_DETAIL T
where T.index_seq=F.index_seq
group by T.index_seq
)
and F.index_seq=Q.index_code
) I
connect by prior I.standardCode=I.parentsCode
start with I.parentsCode is null
)
| 216.73.216.139
|
|
 |
|
 |
SQL> alter session set "_optimizer_connect_by_elim_dups" = false;
OR
SQL> alter session set "_optimizer_connect_by_cost_based" = false;
À§ÀÇ µÎ°³ÀÇ Parameter µéÀ» ½áº¸½Ã°í, ÇÑ ¹ø ¼öÇàÇÏ¿© º¸½ÃÁö¿ä ... Á¤È®È÷ DB version ÀÌ ¾î¶»°Ô µÇ½Ã´ÂÁö ? |
2013-05-01 01:07:28
|
|
 |
¾î·Á¿î ¼÷Á¦¸¦ ¿Ã·È³×.. ^^
-- Optimizer Trace
alter session set events '10053 trace name context forever, level 1';
-- ¹®Á¦ SQL ½ÇÇà
select count(*) from ...
alter session set events '10053 trace name context off';
-- SQL Trace
alter session set events '10046 trace name context forever, level 12';
-- ¹®Á¦ SQL ½ÇÇà
select count(*) from ...
alter session set events '10046 trace name context off';
cd user_dump_dest
tkprof file1.trc 10053.txt
tkprof file2.trc 10046.txt
trc, txt ¿Ã·ÁÁà.. µµ¿ò µÉ¸¸ÇÑ°Ô ÀÖÀ»°Å °°Àºµ¥..
´Ù¸¥ bug »ý°¢Çؼ ÃÖ±Ù Patch ÇØº¸´Â°Íµµ ÁÁ°Ú´Ù.. ^^ |
2013-05-01 05:35:39
|
|
 |
±¸Á¤ÀÏ´Ô -> DB versionÀº 10.2.0.5 ÀÔ´Ï´Ù. |
2013-05-02 09:11:57
|
|
 |
´Ü¼øÇÏ°Ô °èÃþÄõ¸®¸¸ ¹®Á¦ÀÎ °Í °°´Ù¶ó´Â »ý°¢Àº µéÁö ¾Ê±¸¿ä...
index³ª ±âŸ unionÀýÀ̳ª ¿©·¯°¡Áö°¡ °É¸®±ä Çϴµ¥ Á¦°¡ ±íÀÌ ¾ËÁö¸¦ ¸øÇÏ´Ï...¤Ð¤Ð
sol x86_64 ¿¡ 10.2.0.5 ÀÔ´Ï´Ù.
workaround µÈ °Í °°±âµµ ÇÏ°í ±×·¯´Âµ¥...
ÀúÈñ ȸ»ç°¡ DB opnÀÌ ¾Æ´Ï´Ù º¸´Ï cpu ÆÐÄ¡µî ±âŸ ´Ù¸¥ patch´Â ¸ø±¸ÇÏ´Â »óÅÂÀÔ´Ï´Ù..(ÁÂÀý) |
2013-05-02 10:02:05
|
|
 |
workaround µÈ °Í °°±âµµ ÇÏ°í ±×·¯´Âµ¥...
µÈ´Ù´Â°Å¾ß ¹¹¾ß....
_optimizer_connect_by_elim_dups, _optimizer_connect_by_cost_based
Àß µÉ°Í °°Àºµ¥... |
2013-05-02 13:58:06
|
Copyright 1999-2025 Zeroboard / skin by ÃÖ±æÈ£(gilho.kr@gmail.com)
|
|
|