oradebug short_stack

本是古典何须时尚 2022-08-20 09:18 137阅读 0赞

SQL如果消耗在CPU上，等待事件很难看出问题，非等待事件主要是IO，锁。往往通过oradebug short\_stack可以看出端倪。

**session1:**

create table test1 as select \* from dba\_objects;

create table test2 as select \* from dba\_objects;  
exec dbms\_stats.gather\_table\_stats(user,'test1',cascade => true);  
exec dbms\_stats.gather\_table\_stats(user,'test2',cascade => true);

select  pid  
   from v$Process  
  where addr =  
        (select paddr  
           from v$session  
          where sid = (select sid from v$mystat where rownum = 1));

PID  
\----------  
        18

\--开始执行SQL，此sql运行要几分钟

select count(1) from test1 t1,test2 t2 where t1.object\_type = t2.object\_type   
and t1.object\_type='SYNONYM';  
  
  
**session2：**  
SQL> oradebug setorapid 18  
Oracle pid: 18, Unix process pid: 27004, image: oracle@oracle (S000)  
SQL> oradebug short\_stack  
ksedsts()+461<-ksdxfstk()+32<-ksdxcb()+1876<-sspuser()+112<-\_\_sighandler()<-qerstRowP()+415<-qerhjWalkHashBucket()+397<-qerhjInnerProbeHashTable()+700<-qerstRowP()+465<-qerstRowP()+465<-kdstf11001010000km()+617<-kdsttgr()+103623<-qertbFetch()+2346<-qerstFetch()+321<-rwsfcd()+103<-qerstFetch()+321<-qerhjFetch()+593<-qerstFetch()+321<-qergsFetch()+505<-qerstFetch()+321<-opifch2()+2995<-opifch()+64<-opiodr()+916<-ttcpip()+2242<-opitsk()+1673<-opiino()+966<-opiodr()+916<-opirip()+1458<-opidrv()+603<-sou2o()+103<-opimai\_real()+266<-ssthrdmain()+252<-main()+201<-\_\_libc\_start\_main()+244<-\_start()+36  
SQL> oradebug short\_stack  
ksedsts()+461<-ksdxfstk()+32<-ksdxcb()+1876<-sspuser()+112<-\_\_sighandler()<-\_intel\_fast\_memcmp()+60<-qerhjWalkHashBucket()+214<-qerhjInnerProbeHashTable()+700<-qerstRowP()+465<-qerstRowP()+465<-kdstf11001010000km()+617<-kdsttgr()+103623<-qertbFetch()+2346<-qerstFetch()+321<-rwsfcd()+103<-qerstFetch()+321<-qerhjFetch()+593<-qerstFetch()+321<-qergsFetch()+505<-qerstFetch()+321<-opifch2()+2995<-opifch()+64<-opiodr()+916<-ttcpip()+2242<-opitsk()+1673<-opiino()+966<-opiodr()+916<-opirip()+1458<-opidrv()+603<-sou2o()+103<-opimai\_real()+266<-ssthrdmain()+252<-main()+201<-\_\_libc\_start\_main()+244<-\_start()+36  
SQL> oradebug short\_stack  
ksedsts()+461<-ksdxfstk()+32<-ksdxcb()+1876<-sspuser()+112<-\_\_sighandler()<-kxhrPUcompare()+121<-qerhjWalkHashBucket()+214<-qerhjInnerProbeHashTable()+700<-qerstRowP()+465<-qerstRowP()+465<-kdstf11001010000km()+617<-kdsttgr()+103623<-qertbFetch()+2346<-qerstFetch()+321<-rwsfcd()+103<-qerstFetch()+321<-qerhjFetch()+593<-qerstFetch()+321<-qergsFetch()+505<-qerstFetch()+321<-opifch2()+2995<-opifch()+64<-opiodr()+916<-ttcpip()+2242<-opitsk()+1673<-opiino()+966<-opiodr()+916<-opirip()+1458<-opidrv()+603<-sou2o()+103<-opimai\_real()+266<-ssthrdmain()+252<-main()+201<-\_\_libc\_start\_main()+244<-\_start()+36  
SQL> oradebug short\_stack  
ksedsts()+461<-ksdxfstk()+32<-ksdxcb()+1876<-sspuser()+112<-\_\_sighandler()<-qerstSnapStats()+8<-qerstRowP()+499<-qerhjWalkHashBucket()+397<-qerhjInnerProbeHashTable()+700<-qerstRowP()+465<-qerstRowP()+465<-kdstf11001010000km()+617<-kdsttgr()+103623<-qertbFetch()+2346<-qerstFetch()+321<-rwsfcd()+103<-qerstFetch()+321<-qerhjFetch()+593<-qerstFetch()+321<-qergsFetch()+505<-qerstFetch()+321<-opifch2()+2995<-opifch()+64<-opiodr()+916<-ttcpip()+2242<-opitsk()+1673<-opiino()+966<-opiodr()+916<-opirip()+1458<-opidrv()+603<-sou2o()+103<-opimai\_real()+266<-ssthrdmain()+252<-main()+201<-\_\_libc\_start\_main()+244<-\_start()+36  
SQL> oradebug short\_stack  
ksedsts()+461<-ksdxfstk()+32<-ksdxcb()+1876<-sspuser()+112<-\_\_sighandler()<-kxhrPUcompare()+201<-qerhjWalkHashBucket()+214<-qerhjInnerProbeHashTable()+700<-qerstRowP()+465<-qerstRowP()+465<-kdstf11001010000km()+617<-kdsttgr()+103623<-qertbFetch()+2346<-qerstFetch()+321<-rwsfcd()+103<-qerstFetch()+321<-qerhjFetch()+593<-qerstFetch()+321<-qergsFetch()+505<-qerstFetch()+321<-opifch2()+2995<-opifch()+64<-opiodr()+916<-ttcpip()+2242<-opitsk()+1673<-opiino()+966<-opiodr()+916<-opirip()+1458<-opidrv()+603<-sou2o()+103<-opimai\_real()+266<-ssthrdmain()+252<-main()+201<-\_\_libc\_start\_main()+244<-\_start()+36  
SQL> oradebug short\_stack  
ksedsts()+461<-ksdxfstk()+32<-ksdxcb()+1876<-sspuser()+112<-\_\_sighandler()<-kxhrUnpack()+37<-qerhjWalkHashBucket()+302<-qerhjInnerProbeHashTable()+700<-qerstRowP()+465<-qerstRowP()+465<-kdstf11001010000km()+617<-kdsttgr()+103623<-qertbFetch()+2346<-qerstFetch()+321<-rwsfcd()+103<-qerstFetch()+321<-qerhjFetch()+593<-qerstFetch()+321<-qergsFetch()+505<-qerstFetch()+321<-opifch2()+2995<-opifch()+64<-opiodr()+916<-ttcpip()+2242<-opitsk()+1673<-opiino()+966<-opiodr()+916<-opirip()+1458<-opidrv()+603<-sou2o()+103<-opimai\_real()+266<-ssthrdmain()+252<-main()+201<-\_\_libc\_start\_main()+244<-\_start()+36  
SQL> oradebug short\_stack  
ksedsts()+461<-ksdxfstk()+32<-ksdxcb()+1876<-sspuser()+112<-\_\_sighandler()<-qerstRowP()+8<-qerhjWalkHashBucket()+397<-qerhjInnerProbeHashTable()+700<-qerstRowP()+465<-qerstRowP()+465<-kdstf11001010000km()+617<-kdsttgr()+103623<-qertbFetch()+2346<-qerstFetch()+321<-rwsfcd()+103<-qerstFetch()+321<-qerhjFetch()+593<-qerstFetch()+321<-qergsFetch()+505<-qerstFetch()+321<-opifch2()+2995<-opifch()+64<-opiodr()+916<-ttcpip()+2242<-opitsk()+1673<-opiino()+966<-opiodr()+916<-opirip()+1458<-opidrv()+603<-sou2o()+103<-opimai\_real()+266<-ssthrdmain()+252<-main()+201<-\_\_libc\_start\_main()+244<-\_start()+36  
SQL> oradebug short\_stack  
ksedsts()+461<-ksdxfstk()+32<-ksdxcb()+1876<-sspuser()+112<-\_\_sighandler()<-qerhjWalkHashBucket()+164<-qerhjInnerProbeHashTable()+700<-qerstRowP()+465<-qerstRowP()+465<-kdstf11001010000km()+617<-kdsttgr()+103623<-qertbFetch()+2346<-qerstFetch()+321<-rwsfcd()+103<-qerstFetch()+321<-qerhjFetch()+593<-qerstFetch()+321<-qergsFetch()+505<-qerstFetch()+321<-opifch2()+2995<-opifch()+64<-opiodr()+916<-ttcpip()+2242<-opitsk()+1673<-opiino()+966<-opiodr()+916<-opirip()+1458<-opidrv()+603<-sou2o()+103<-opimai\_real()+266<-ssthrdmain()+252<-main()+201<-\_\_libc\_start\_main()+244<-\_start()+36  
SQL> oradebug short\_stack  
ksedsts()+461<-ksdxfstk()+32<-ksdxcb()+1876<-sspuser()+112<-\_\_sighandler()<-qerstRowP()+74<-qerhjWalkHashBucket()+397<-qerhjInnerProbeHashTable()+700<-qerstRowP()+465<-qerstRowP()+465<-kdstf11001010000km()+617<-kdsttgr()+103623<-qertbFetch()+2346<-qerstFetch()+321<-rwsfcd()+103<-qerstFetch()+321<-qerhjFetch()+593<-qerstFetch()+321<-qergsFetch()+505<-qerstFetch()+321<-opifch2()+2995<-opifch()+64<-opiodr()+916<-ttcpip()+2242<-opitsk()+1673<-opiino()+966<-opiodr()+916<-opirip()+1458<-opidrv()+603<-sou2o()+103<-opimai\_real()+266<-ssthrdmain()+252<-main()+201<-\_\_libc\_start\_main()+244<-\_start()+36  
SQL> oradebug short\_stack  
ksedsts()+461<-ksdxfstk()+32<-ksdxcb()+1876<-sspuser()+112<-\_\_sighandler()<-qerstUpdateStats()+166<-qerstRowP()+85<-qerhjWalkHashBucket()+397<-qerhjInnerProbeHashTable()+700<-qerstRowP()+465<-qerstRowP()+465<-kdstf11001010000km()+617<-kdsttgr()+103623<-qertbFetch()+2346<-qerstFetch()+321<-rwsfcd()+103<-qerstFetch()+321<-qerhjFetch()+593<-qerstFetch()+321<-qergsFetch()+505<-qerstFetch()+321<-opifch2()+2995<-opifch()+64<-opiodr()+916<-ttcpip()+2242<-opitsk()+1673<-opiino()+966<-opiodr()+916<-opirip()+1458<-opidrv()+603<-sou2o()+103<-opimai\_real()+266<-ssthrdmain()+252<-main()+201<-\_\_libc\_start\_main()+244<-\_start()+36  
SQL> oradebug short\_stack  
ksedsts()+461<-ksdxfstk()+32<-ksdxcb()+1876<-sspuser()+112<-\_\_sighandler()<-qerstUpdateStats()+166<-qerstRowP()+85<-qerhjWalkHashBucket()+397<-qerhjInnerProbeHashTable()+700<-qerstRowP()+465<-qerstRowP()+465<-kdstf11001010000km()+617<-kdsttgr()+103623<-qertbFetch()+2346<-qerstFetch()+321<-rwsfcd()+103<-qerstFetch()+321<-qerhjFetch()+593<-qerstFetch()+321<-qergsFetch()+505<-qerstFetch()+321<-opifch2()+2995<-opifch()+64<-opiodr()+916<-ttcpip()+2242<-opitsk()+1673<-opiino()+966<-opiodr()+916<-opirip()+1458<-opidrv()+603<-sou2o()+103<-opimai\_real()+266<-ssthrdmain()+252<-main()+201<-\_\_libc\_start\_main()+244<-\_start()+36  
SQL> oradebug short\_stack  
ksedsts()+461<-ksdxfstk()+32<-ksdxcb()+1876<-sspuser()+112<-\_\_sighandler()<-kxhrUnpack()+123<-qerhjWalkHashBucket()+302<-qerhjInnerProbeHashTable()+700<-qerstRowP()+465<-qerstRowP()+465<-kdstf11001010000km()+617<-kdsttgr()+103623<-qertbFetch()+2346<-qerstFetch()+321<-rwsfcd()+103<-qerstFetch()+321<-qerhjFetch()+593<-qerstFetch()+321<-qergsFetch()+505<-qerstFetch()+321<-opifch2()+2995<-opifch()+64<-opiodr()+916<-ttcpip()+2242<-opitsk()+1673<-opiino()+966<-opiodr()+916<-opirip()+1458<-opidrv()+603<-sou2o()+103<-opimai\_real()+266<-ssthrdmain()+252<-main()+201<-\_\_libc\_start\_main()+244<-\_start()+36  
SQL> oradebug short\_stack  
ksedsts()+461<-ksdxfstk()+32<-ksdxcb()+1876<-sspuser()+112<-\_\_sighandler()<-qerstSnapStats()+170<-qerstRowP()+499<-qerhjWalkHashBucket()+397<-qerhjInnerProbeHashTable()+700<-qerstRowP()+465<-qerstRowP()+465<-kdstf11001010000km()+617<-kdsttgr()+103623<-qertbFetch()+2346<-qerstFetch()+321<-rwsfcd()+103<-qerstFetch()+321<-qerhjFetch()+593<-qerstFetch()+321<-qergsFetch()+505<-qerstFetch()+321<-opifch2()+2995<-opifch()+64<-opiodr()+916<-ttcpip()+2242<-opitsk()+1673<-opiino()+966<-opiodr()+916<-opirip()+1458<-opidrv()+603<-sou2o()+103<-opimai\_real()+266<-ssthrdmain()+252<-main()+201<-\_\_libc\_start\_main()+244<-\_start()+36  
SQL> oradebug short\_stack  
ksedsts()+461<-ksdxfstk()+32<-ksdxcb()+1876<-sspuser()+112<-\_\_sighandler()<-kxhrPUcompare()+240<-qerhjWalkHashBucket()+214<-qerhjInnerProbeHashTable()+700<-qerstRowP()+465<-qerstRowP()+465<-kdstf11001010000km()+617<-kdsttgr()+103623<-qertbFetch()+2346<-qerstFetch()+321<-rwsfcd()+103<-qerstFetch()+321<-qerhjFetch()+593<-qerstFetch()+321<-qergsFetch()+505<-qerstFetch()+321<-opifch2()+2995<-opifch()+64<-opiodr()+916<-ttcpip()+2242<-opitsk()+1673<-opiino()+966<-opiodr()+916<-opirip()+1458<-opidrv()+603<-sou2o()+103<-opimai\_real()+266<-ssthrdmain()+252<-main()+201<-\_\_libc\_start\_main()+244<-\_start()+36  
SQL> oradebug short\_stack  
ksedsts()+461<-ksdxfstk()+32<-ksdxcb()+1876<-sspuser()+112<-\_\_sighandler()<-qerhjWalkHashBucket()+221<-qerhjInnerProbeHashTable()+700<-qerstRowP()+465<-qerstRowP()+465<-kdstf11001010000km()+617<-kdsttgr()+103623<-qertbFetch()+2346<-qerstFetch()+321<-rwsfcd()+103<-qerstFetch()+321<-qerhjFetch()+593<-qerstFetch()+321<-qergsFetch()+505<-qerstFetch()+321<-opifch2()+2995<-opifch()+64<-opiodr()+916<-ttcpip()+2242<-opitsk()+1673<-opiino()+966<-opiodr()+916<-opirip()+1458<-opidrv()+603<-sou2o()+103<-opimai\_real()+266<-ssthrdmain()+252<-main()+201<-\_\_libc\_start\_main()+244<-\_start()+36  
SQL> oradebug short\_stack  
ksedsts()+461<-ksdxfstk()+32<-ksdxcb()+1876<-sspuser()+112<-\_\_sighandler()<-kxhrPUcompare()+240<-qerhjWalkHashBucket()+214<-qerhjInnerProbeHashTable()+700<-qerstRowP()+465<-qerstRowP()+465<-kdstf11001010000km()+617<-kdsttgr()+103623<-qertbFetch()+2346<-qerstFetch()+321<-rwsfcd()+103<-qerstFetch()+321<-qerhjFetch()+593<-qerstFetch()+321<-qergsFetch()+505<-qerstFetch()+321<-opifch2()+2995<-opifch()+64<-opiodr()+916<-ttcpip()+2242<-opitsk()+1673<-opiino()+966<-opiodr()+916<-opirip()+1458<-opidrv()+603<-sou2o()+103<-opimai\_real()+266<-ssthrdmain()+252<-main()+201<-\_\_libc\_start\_main()+244<-\_start()+36  
SQL> oradebug short\_stack  
ksedsts()+461<-ksdxfstk()+32<-ksdxcb()+1876<-sspuser()+112<-\_\_sighandler()<-qerstSnapStats()+274<-qerstRowP()+499<-qerhjWalkHashBucket()+397<-qerhjInnerProbeHashTable()+700<-qerstRowP()+465<-qerstRowP()+465<-kdstf11001010000km()+617<-kdsttgr()+103623<-qertbFetch()+2346<-qerstFetch()+321<-rwsfcd()+103<-qerstFetch()+321<-qerhjFetch()+593<-qerstFetch()+321<-qergsFetch()+505<-qerstFetch()+321<-opifch2()+2995<-opifch()+64<-opiodr()+916<-ttcpip()+2242<-opitsk()+1673<-opiino()+966<-opiodr()+916<-opirip()+1458<-opidrv()+603<-sou2o()+103<-opimai\_real()+266<-ssthrdmain()+252<-main()+201<-\_\_libc\_start\_main()+244<-\_start()+36