On 10/21/05, Christo Kutrovsky <kutrovsky.oracle@xxxxxxxxx> wrote: > I am curious as to how a failed statspack job can cause this ? Christo, you're probably the only person that will be interested in this, but I included the callstack of the first trace file from the j00n processes inline below. > And if not for production monitoring, where would you run statspack :) This is where I insert some attempt at humor mentioning the Heisenberg Uncertainty Principle. This generated 75 trace files since yesterday morning. Preventing any sessions from being created pretty much falls outside of non-destructive testing. > Christo Dump file d:\oracle\admin\test\bdump\test_j002_2256.trc Thu Oct 20 15:00:07 2005 ORACLE V10.1.0.4.0 - Production vsnsta=0 vsnsql=13 vsnxtr=3 Oracle Database 10g Release 10.1.0.4.0 - Production Windows 2000 Version V5.0 Service Pack 4 CPU : 4 - type 586 Process Affinity: 0x00000000 Memory (A/P) : PH:1485M/3583M, PG:8412M/10604M, VA:1053M/3071M Instance name: test Redo thread mounted by this instance: 1 Oracle process number: 27 Windows thread id: 2256, image: ORACLE.EXE (J002) *** SERVICE NAME:(SYS$USERS) 2005-10-20 15:00:07.261 *** SESSION ID:(81.1) 2005-10-20 15:00:07.261 *** 2005-10-20 15:00:07.261 ksedmp: internal or fatal error ORA-00600: internal error code, arguments: [504], [0x55A9A5B0], [32], [4], [row cache objects], [10], [2], [0x0] Current SQL statement for this session: INSERT INTO STATS$SQL_PLAN ( PLAN_HASH_VALUE , ID , OPERATION , OPTIONS , OBJECT_NODE , OBJECT# , OBJECT_OWNER , OBJECT_NAME , OBJECT_ALIAS , OBJECT_TYPE , OPTIMIZER , PARENT_ID , DEPTH , POSITION , SEARCH_COLUMNS , COST , CARDINALITY , BYTES , OTHER_TAG , PARTITION_START , PARTITION_STOP , PARTITION_ID , OTHER , DISTRIBUTION , CPU_COST , IO_COST , TEMP_SPACE , ACCESS_PREDICATES , FILTER_PREDICATES , PROJECTION , TIME , QBLOCK_NAME , REMARKS , SNAP_ID ) SELECT /*+ ordered use_nl(s) use_nl(sp.p) */ NEW_PLAN.PLAN_HASH_VALUE , SP.ID , MAX(SP.OPERATION) , MAX(SP.OPTIONS) , MAX(SP.OBJECT_NODE) , MAX(SP.OBJECT#) , MAX(SP.OBJECT_OWNER) , MAX(SP.OBJECT_NAME) , MAX(SP.OBJECT_ALIAS) , MAX(SP.OBJECT_TYPE) , MAX(SP.OPTIMIZER) , MAX(SP.PARENT_ID) , MAX(SP.DEPTH) , MAX(SP.POSITION) , MAX(SP.SEARCH_COLUMNS) , MAX(SP.COST) , MAX(SP.CARDINALITY) , MAX(SP.BYTES) , MAX(SP.OTHER_TAG) , MAX(SP.PARTITION_START) , MAX(SP.PARTITION_STOP) , MAX(SP.PARTITION_ID) , MAX(SP.OTHER) , MAX(SP.DISTRIBUTION) , MAX(SP.CPU_COST) , MAX(SP.IO_COST) , MAX(SP.TEMP_SPACE) , 0 , 0 , MAX(SP.PROJECTION) , MAX(SP.TIME) , MAX(SP.QBLOCK_NAME) , MAX(SP.REMARKS) , MAX(NEW_PLAN.SNAP_ID) FROM (SELECT /*+ index(spu) */ SPU.PLAN_HASH_VALUE , SPU.HASH_VALUE HASH_VALUE , SPU.ADDRESS ADDRESS , SPU.TEXT_SUBSET TEXT_SUBSET , SPU.SNAP_ID SNAP_ID FROM STATS$SQL_PLAN_USAGE SPU WHERE SPU.SNAP_ID = :B3 AND SPU.DBID = :B2 AND SPU.INSTANCE_NUMBER = :B1 AND NOT EXISTS (SELECT /*+ nl_aj */ * FROM STATS$SQL_PLAN SSP WHERE SSP.PLAN_HASH_VALUE = SPU.PLAN_HASH_VALUE ) ) NEW_PLAN , V$SQL S , V$SQL_PLAN SP WHERE S.ADDRESS = NEW_PLAN.ADDRESS AND S.PLAN_HASH_VALUE = NEW_PLAN.PLAN_HASH_VALUE AND S.HASH_VALUE = NEW_PLAN.HASH_VALUE AND SP.HASH_VALUE = NEW_PLAN.HASH_VALUE AND SP.ADDRESS = NEW_PLAN.ADDRESS AND SP.HASH_VALUE = S.HASH_VALUE AND SP.ADDRESS = S.ADDRESS AND SP.CHILD_NUMBER = S.CHILD_NUMBER GROUP BY NEW_PLAN.PLAN_HASH_VALUE, SP.ID ORDER BY NEW_PLAN.PLAN_HASH_VALUE, SP.ID ----- PL/SQL Call Stack ----- object line object handle number name 5420CD70 3383 package body PERFSTAT.STATSPACK 5420CD70 4512 package body PERFSTAT.STATSPACK 5420CD70 91 package body PERFSTAT.STATSPACK 534CF2A0 1 anonymous block ----- Call Stack Trace ----- calling call entry argument values in hex location type point (? means dubious value) -------------------- -------- -------------------- ---------------------------- _ksedmp+576 CALLrel _ksedst+0 0 _ksfdmp.160+14 CALLrel _ksedmp+0 3 _kgeriv+139 CALLreg 00000000 451F430 3 _kgesiv+78 CALLrel _kgeriv+0 451F430 BEC1004 1F8 7 BE56388 _ksesic7+59 CALLrel _kgesiv+0 451F430 BEC1004 1F8 7 BE56388 1F8 7 BE56388 _ksl_level_check.16 CALLrel _ksesic7+0 1F8 2 55A9A5B0 0 20 0 0 4 0 1 3+361 11 2B4C3C4 0 A 0 0 2 0 2 0 _kslgetl+432 CALLrel _ksl_level_check.16 3+0 _kqrpre1+343 CALLrel _kslgetl+0 55A9A5B0 1 0 791 BE56554 14 0 _kqrpre+28 CALLrel _kqrpre1+0 11 BE56554 BE56574 3 5574B6B8 BE56570 0 0 _kkdogoid+98 CALLrel _kqrpre+0 11 BE56554 BE56574 3 5574B6B8 BE56570 0 _kokacau+637 CALLrel _kkdogoid+0 D01 BE56C04 _kokavpr+453 CALLrel _kokacau+0 BE566C4 _kokageti+924 CALLrel _kokavpr+0 BE56C04 0 0 0 1 FF 0 BE56810 BE5681C _kokdlod+175 CALLrel _kokageti+0 _kodclod+161 CALLreg 00000000 BE56908 BF0DA70 0 _kocobld.8+510 CALLrel _kodclod+0 451F430 0 BE56938 1 _kocgpn2+1621 CALLrel _kocobld.8+0 _kocpin+37 CALLrel _kocgpn2+0 451F430 BE56CBC 0 0 3 2 A C 1 0 1 0 A _kotgtbtv+96 CALLrel _kocpin+0 _qcdotdbiv+68 CALLrel _kotgtbtv+0 _qcdopint+38 CALLrel _qcdotdbiv+0 BE56D20 456CA8E8 BE50001 _kkdopint+142 CALLrel _qcdopint+0 BE56D20 456CA8E8 _kokuatp+11 CALLrel _kkdopint+0 _kokutrt+61 CALLrel _kokuatp+0 _kokuoop+606 CALLrel _kokutrt+0 _k2copnws+510 CALL??? 00000000 BE5736C 0 7584630 0 _msqopnws+2847 CALLrel _k2copnws+0 BE5736C 0 2594380 0 _msqfun+5044 CALLrel _msqopnws+0 0 0 7584630 0 0 _kokucon+476 CALLrel _msqfun+0 _kokuoop+303 CALLrel _kokucon+0 BE5736C 0 0 0 _k2copnws+510 CALL??? 00000000 BE5736C 0 75A9CE8 0 _msqopnws+2847 CALLrel _k2copnws+0 BE5736C 0 2592718 0 _msqfun+4521 CALLrel _msqopnws+0 0 0 75A9CE8 0 0 _k2copnws+510 CALL??? 00000000 25924FC 0 75A9CB0 0 _msqopnws+2847 CALLrel _k2copnws+0 BE5736C 0 25924FC 0 _xplProjToText+716 CALLrel _msqopnws+0 0 0 75A9CB0 0 0 _xplMakeRow+501 CALLrel _xplProjToText+0 456A5658 1 456CEC24 BE57540 _xplFetchRow+97 CALLreg 00000000 BE50001 456A5658 1 BE5BC54 _kqlfgx+564 CALLrel _xplFetchRow+0 BE575AC 112CF10 BE5BC54 5FF40676 FC83CBFD C6755F0 _kgligi+24 CALLreg 00000000 0 C66E900 456D03D8 456CFE84 C669FB4 _kglic+591 CALLreg 00000000 451F430 C66E900 C669FB4 0 0 456D03D8 456D134C 55D2475C 0 159BEE0 C669FB4 _kqlfxp+281 CALLrel _kglic+0 451F430 C66E900 C669FB4 0 0 8 0 5FF40676 159BEE0 C669FB4 _qerfxFetch+2183 CALLreg 00000000 _qerjotFetch+153 CALL??? 00000000 _qerjoFetch+507 CALL??? 00000000 5322BE0C 0 0 1 _qerjoFetch+507 CALL??? 00000000 5322A254 0 0 1 _qergsFetch+1649 CALL??? 00000000 5322A664 AA2634 5322A784 7FFF _rwsfcd+100 CALL??? 00000000 5322A784 0 0 1 _insfch.103+98 CALL??? 00000000 532276B8 0 0 1 _insdrv.103+643 CALLrel _insfch.103+0 C66AF60 C664590 _inscovexe+1157 CALLrel _insdrv.103+0 C66AF60 _insExecStmtExecIni CALL??? 00000000 53268BEC 53227A00 BE5C6A8 Engine.103+54 _insexe+214 CALLrel _insExecStmtExecIni 53268BEC 53227A00 BE5C6A8 Engine.103+0 _opiexe+12473 CALLrel _insexe+0 53268FC8 BE5C6A8 _opipls+3120 CALLrel _opiexe+0 4 5 BE5CEE8 _opiodr+973 CALLreg 00000000 66 6 BE5D678 _rpidrus.66+150 CALLrel _opiodr+0 66 6 BE5D678 38 _rpidru+88 CALLrel _rpidrus.66+0 BE5D18C _rpiswu2+368 CALLreg 00000000 BE5D4C4 _rpidrv+310 CALLrel _rpiswu2+0 _psddr0.36+452 CALLrel _rpidrv+0 38 66 BE5D678 38 _psdnal+287 CALLrel _psddr0.36+0 _pevm_EXECC+386 CALLreg 00000000 _pfrinstr_EXECC+40 CALLrel _pevm_EXECC+0 _pfrrun_no_tool+51 CALL??? 00000000 _pfrrun+1834 CALLrel _pfrrun_no_tool+0 BF0E5A8 544C77E6 BF0E5E4 _plsql_run+1051 CALLrel _pfrrun+0 BF0E5A8 _peicnt+179 CALLrel _plsql_run+0 BF0E5A8 1 0 _kkxexe+486 CALLrel _peicnt+0 _opiexe+5058 CALLrel _kkxexe+0 534CF2A0 _opiodr+973 CALLreg 00000000 4 4 BE5E81C _rpidrus.66+150 CALLrel _opiodr+0 4 4 BE5E81C 5 _rpidru+88 CALLrel _rpidrus.66+0 BE5E3DC _rpiswu2+368 CALLreg 00000000 BE5E714 _rpidrv+310 CALLrel _rpiswu2+0 _rpiexe+70 CALLrel _rpidrv+0 5 4 BE5E81C A _kkjex1e+3549 CALLrel _rpiexe+0 5 _kkjsexe+317 CALLrel _kkjex1e+0 BE5EC38 25 0 BE5EC0C _kkjrdp+720 CALLrel _kkjsexe+0 _opirip+681 CALLrel _kkjrdp+0 _opidrv+517 CALLrel _opirip+0 32 0 0 _sou2o+45 CALLrel _opidrv+0 32 0 0 _opimai+314 CALLrel _sou2o+0 BE5FE28 32 0 0 _BackgroundThreadSt CALLrel _opimai+0 art@4+314 7C57B385 CALLreg 00000000 --------------------- Binary Stack Dump --------------------- The Ora-600 lookup tool didn't find the callstack to be very interesting. I wonder if I can make that my home page in Metalink? Paul > > On 10/21/05, Paul Drake <bdbafh@xxxxxxxxx> wrote: > > On 10/21/05, Paul Drake <bdbafh@xxxxxxxxx> wrote: > > > Has anyone come across a hang/spin condition with datapump exports? > > > > > > Standard Edition 10.1.0 patchset 2 (10.1.0.4) + 10.1.0.4 patch 5. > > > w2k adv server sp4 > > > quad xeon (PIII) > > > > > > pegged 4 cpus on single user database, single database on the server > > > running an expdp.exe job (full=y). > > > > > > Shared servers are configured for this database due to VPN issues, but > > > the expdp session is connected to a dedicated server process (thread). > > > > > > receiving ora-600 [504] [address] [32] [4] [row cache objects] [10] [2] > > > [0x0] > > > > > > Since this is testing I'll hold off until next week to open an iTAR. > > > > > > A hanganalyze did not yet complete. > > > The job still appears to be in progress, so its not completely fubared. > > > A trace file is being generated by a backround job that appears to be > > > a statspack snapshot. > > > Is it just me, or is statspack in 10g not something that is meant for > > > usage anymore? > > > (wide open hole for soliciting signatures for Niall's post concerning > > > ASH). > > > I've seen a perfstat.statspack.snap collide with AMM causing major > > > amounts of grief in 10.1.0.4 on win32 at least 3 separate times > > > (disabled both now) and have seen at least 2 other posters here > > > complain about shared_pool latching issues related to that > > > combination. > > > > > > This is not how I planned to spend my afternoon. > > > I had hoped to sneak out to a theater to catch DOOM (the movie) - not > > > doom, return of the ora-600. > > > > > > thanks. > > > > > > Paul > > > > > > > A failed background job executing a statspack.snap was the root cause. > > > > datapump exports now run fine without issue. > > > > The fix was brutally simple and did not require downloading a patch or > > regression testing: > > > > SQL> @?/rdbms/admin/spdrop.sql; > > > > I've concluded that scheduled statspack snapshots just plain do not > > belong in a production database, at least for win32 10.1.0.4, even > > with statistics_level='BASIC'; and AMM not in use. > > > > Paul > > -- > > //www.freelists.org/webpage/oracle-l > > > > > -- > Christo Kutrovsky > Database/System Administrator > The Pythian Group > -- #/etc/init.d/init.cssd stop # f=ma, divide by 1, convert to moles. -- //www.freelists.org/webpage/oracle-l