by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

콜 트리 분석 예제들




콜 트리 분석 예제들

하드 파스 시간이 매우 긴 경우의 콜 트리 분석

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 경합에 의한 세션 행 분석

--> 재현 않됨... ㅜㅜ

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.