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_typeand 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