콜 트리 분석 예제들

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

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

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