- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=23789601&
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.
콜 트리 분석 예제들
하드 파스 시간이 매우 긴 경우의 콜 트리 분석
ed make_long.sql
set heading off
set timing off
set feedback off
set serveroutput on size 100000
var v_sql clob;
begin
:v_sql := 'select count(*) from ';
for r in (select t1.table_name from user_tables t1, user_tables t2 where rownum <= 500) loop
:v_sql := :v_sql || r.table_name || ', ';
end loop;
:v_sql := :v_sql || ' dual where 1 = 0;';
end;
/
spool long_parse.sql
exec dbms_output.put_line(:v_sql);
spool off
set heading on
set timing on
set feedback on
set serveroutput off
@make_long
-- session #1
@long_parse
-- session #2
set long 100000
select * from table(tpack.callstack_prof_report(session_id=>142);
STACK_TRACE HIT_CNT HIT_PCT
-------------------------------------------------------------------------------- ---------- ----------
0000000076F4C521 5 50
->0000000076E1652D
->OracleThreadStart()
->opimai()
->opimai_real()
->sou2o()
->opidrv()
->opiodr()
->opiino()
->opitsk()
->ttcpip()
->opiodr()
->kpoal8()
->kpooprx()
->opiosq0()
->kksParseCursor() <---------------------
->kkspsc0()
->kksfbc()
->kxsGetRuntimeLock()
->kksLoadChild() <---------------------
->rpiswu2()
->__PGOSF525_kksFullTypeCheck() <---------------------
->opitca()
->apadrv()
->apaqbd()
->apaqbdList()
->apaqbdDescendents()
->apakkoqb()
->kkoqbc()
->kkooqb()
->kkorrio()
->apafdr()
->apafjo()
->apafbp()
->0000000076F4C521
->0000000076E1652D
->OracleOradebugThreadStart()
->ssthreadsrgruncallback()
->ksdxcb()
->ksdxfdmp()
->ksedst_tracecb()
->ksedst1()
0000000076F4C521 1 10
->0000000076E1652D
->OracleThreadStart()
->opimai()
->opimai_real()
->sou2o()
->opidrv()
->opiodr()
->opiino()
->opitsk()
->ttcpip()
->opiodr()
->kpoal8()
->kpooprx()
->opiosq0()
->kksParseCursor()
->kkspsc0()
->kksfbc()
->kxsGetRuntimeLock()
->kksLoadChild()
->rpiswu2()
->__PGOSF525_kksFullTypeCheck()
->opitca()
->apadrv()
->apaqbd()
->apaqbdList()
->apaqbdDescendents()
->apakkoqb()
->kkoqbc()
->kkooqb()
->kkorrio()
->apafdr()
->apafjo()
->apafbp()
->kkoCheckIdxUnusable()
->kkpoxguii_get_unusable_index_info()
->qksmaIdxHighMatFragNum()
->0000000076F4C521
->0000000076E1652D
->OracleOradebugThreadStart()
->ssthreadsrgruncallback()
->ksdxcb()
->ksdxfdmp()
->ksedst_tracecb()
->ksedst1()
0000000076F4C521 1 10
->0000000076E1652D
->OracleThreadStart()
->opimai()
->opimai_real()
->sou2o()
->opidrv()
->opiodr()
->opiino()
->opitsk()
->ttcpip()
->opiodr()
->kpoal8()
->kpooprx()
->opiosq0()
->kksParseCursor()
->kkspsc0()
->kksfbc()
->kxsGetRuntimeLock()
->kksLoadChild()
->rpiswu2()
->__PGOSF525_kksFullTypeCheck()
->opitca()
->apadrv()
->apaqbd()
->apaqbdList()
->apaqbdDescendents()
->apakkoqb()
->kkoqbc()
->kkooqb()
->kkorrio()
->apafdr()
->apafjo()
->apafbp()
->apacjp()
->appget()
->appopd()
->0000000076F4C521
->0000000076E1652D
->OracleOradebugThreadStart()
->ssthreadsrgruncallback()
->ksdxcb()
->ksdxfdmp()
->ksedst_tracecb()
->ksedst1()
0000000076F4C521 1 10
->0000000076E1652D
->OracleThreadStart()
->opimai()
->opimai_real()
->sou2o()
->opidrv()
->opiodr()
->opiino()
->opitsk()
->ttcpip()
->opiodr()
->kpoal8()
->kpooprx()
->opiosq0()
->kksParseCursor()
->kkspsc0()
->kksfbc()
->kxsGetRuntimeLock()
->kksLoadChild()
->rpiswu2()
->__PGOSF525_kksFullTypeCheck()
->opitca()
->apadrv()
->apaqbd()
->apaqbdList()
->apaqbdDescendents()
->apakkoqb()
->kkoqbc()
->kkooqb()
->kkorrio()
->apafdr()
->apafjo()
->qksfroChild()
->0000000076F4C521
->0000000076E1652D
->OracleOradebugThreadStart()
->ssthreadsrgruncallback()
->ksdxcb()
->ksdxfdmp()
->ksedst_tracecb()
->ksedst1()
0000000076F4C521 1 10
->0000000076E1652D
->OracleThreadStart()
->opimai()
->opimai_real()
->sou2o()
->opidrv()
->opiodr()
->opiino()
->opitsk()
->ttcpip()
->opiodr()
->kpoal8()
->kpooprx()
->opiosq0()
->kksParseCursor()
->kkspsc0()
->kksfbc()
->kxsGetRuntimeLock()
->kksLoadChild()
->rpiswu2()
->__PGOSF525_kksFullTypeCheck()
->opitca()
->apadrv()
->apaqbd()
->apaqbdList()
->apaqbdDescendents()
->apakkoqb()
->kkoqbc()
->kkooqb()
->kkorrio()
->apafdr()
->apafjo()
->apafbp()
->apafb3()
->appget()
->0000000076F4C521
->0000000076E1652D
->OracleOradebugThreadStart()
->ssthreadsrgruncallback()
->ksdxcb()
->ksdxfdmp()
->ksedst_tracecb()
->ksedst1()
0000000076F4C521 1 10
->0000000076E1652D
->OracleThreadStart()
->opimai()
->opimai_real()
->sou2o()
->opidrv()
->opiodr()
->opiino()
->opitsk()
->ttcpip()
->opiodr()
->kpoal8()
->kpooprx()
->opiosq0()
->kksParseCursor()
->kkspsc0()
->kksfbc()
->kxsGetRuntimeLock()
->kksLoadChild()
->rpiswu2()
->__PGOSF525_kksFullTypeCheck()
->opitca()
->apadrv()
->apaqbd()
->apaqbdList()
->apaqbdDescendents()
->apakkoqb()
->kkoqbc()
->kkooqb()
->kkorrio()
->apafdr()
->apafjo()
->apafbp()
->apacjp()
->0000000076F4C521
->0000000076E1652D
->OracleOradebugThreadStart()
->ssthreadsrgruncallback()
->ksdxcb()
->ksdxfdmp()
->ksedst_tracecb()
->ksedst1()
-- session #2
oradebug setospid 3628
oradebug short_stack
ksedsts()+585<-ksdxfstk()+44<-ksdxcb()+2261<-ssthreadsrgruncallback()+632<-OracleOradebugThreadStart()+820<-0000000076E1652D<-0000000076F4C521<-kghalf()+1198<-kghalp()+97<-kksalx()+103<-audRegFro()+38<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59
<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59
<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59
<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59
<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-audRegFro()+59<-__PGOSF164_kkmpfcbk()+1816<-qcsprfro()+599<-qcsprfro_tree()+292<-qcsprfro_tree()+373<-qcspafq()+96<-qcspqbDescendents()+227<-qcspqb()+154
<-kkmdrv()+200<-opiSem()+2560<-opiDeferredSem()+565<-opitca()+391<-__PGOSF525_kksFullTypeCheck()+27<-rpiswu2()+2757<-kksLoadChild()+9357<-kxsGetRuntimeLock()+2320<-kksfbc()+15225<-kkspsc0()+2117<-kksParseCursor()+181<-opiosq0()+2538<-kpooprx()+357<-kpoal8()+940<-opiodr()+1662<-ttcpip()+1325<-opitsk()+2040
<-opiino()+1258<-opiodr()+1662<-opidrv()+864<-sou2o()+98<-opimai_real()+158<-opimai()+191<-OracleThreadStart()+724<-0000000076E1652D<-0000000076F4C521
-- 여러 차례 반복적으로 호출함으로써 콜 트리 상에 어떤변화가 있는지 분석하는 것이 중요.
PGA메모리가 점진적으로 커지는 경우의 콜 트리 분석
- 힙 메모리 덤프로 분석부분은 4장 참고
create table t1(c1 int); create table t2(c1 int); create or replace trigger t1_trg1 after insert on t1 for each row begin insert into t2 values(:new.c1); end; / create or replace trigger t2_trg1 after insert on t2 for each row begin declare numrows number; begin numrows := 1; end; end; / -- session #1 declare v_start_time number := dbms_utility.get_time; begin for idx in 1 .. 100000 loop insert into t1 values(idx); if mod(idx, 1000) = 0 then dbms_output.put_line(idx || 'th exe = ' || (dbms_utility.get_time - v_start_time)/100); v_start_time := dbms_utility.get_time; end if; end loop; end; / -- session #2 set long 10000 select * from table(tpack.callstack_prof_report(147)); STACK_TRACE HIT_CNT HIT_PCT -------------------------------------------------------------------------------- ---------- ---------- 0000000076F4C521 1 10 ->0000000076E1652D ->OracleThreadStart() ->opimai() ->opimai_real() ->sou2o() ->opidrv() ->opiodr() ->opiino() ->opitsk() ->ttcpip() ->opiodr() ->kpoal8() ->opiexe() ->kkxexe() ->peicnt() ->plsql_run() ->pfrrun() ->pfrrun_no_tool() ->pfrinstr_EXECC() ->pevm_EXECC() ->psdnal() ->psddr0() ->rpidrv() ->rpiswu2() ->rpidru() ->rpidrus() ->opiodr() ->opipls() ->opiexe() ->insexe() ->qerltcFetch() ->qerltcSingleRowLoad() ->__PGOSF662_qerltcNoKdtBufferedInsRowCBK() ->qesltcAfterRowProcessing() ->qesltcExecuteAfterRowTriggers() ->kxtexe() ->kxtExecuteTriggerReal() ->rpiswu2() ->kxtExecuteTriggerRecursive() ->kkxtexe() ->peiet_execute_trigger() ->peicnt() ->plsql_run() ->pfrrun() ->pfrrun_no_tool() ->pfrinstr_EXECC() ->pevm_EXECC() ->psdnal() ->psddr0() ->rpidrv() ->rpiswu2() ->rpidru() ->rpidrus() ->opiodr() ->opipls() ->opiexe() ->insexe() ->qerltcFetch() ->qerltcSingleRowLoad() ->__PGOSF662_qerltcNoKdtBufferedInsRowCBK() ->kdtInsRow() ->kdtwrp() ->kdtchg() ->ktbchg2() ->ktuchg() ->ktuchg2() ->kcbchg1() ->kcbchg1_main() ->kcrfw_redo_gen() ->kcbklbc() ->0000000076F4C521 ->0000000076E1652D ->OracleOradebugThreadStart() ->ssthreadsrgruncallback() ->ksdxcb() ->ksdxfdmp() ->ksedst_tracecb() ->ksedst1()
비정상적인 Library Cache Pin 경합에 의한 세션 행 분석
--> 재현 않됨... ㅜㅜ
문서정보
- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=23789601&
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.