오라클 성능 트러블슈팅의 기초 (2012년)
콜 트리 분석 예제들
하드 파스 시간이 매우 긴 경우의 콜 트리 분석
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()
"데이터베이스 스터디모임" 에서 2012년에 "오라클 성능 트러블슈팅의 기초
" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/4119
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.