On 10/21/05, Christo Kutrovsky <kutrovsky.oracle_at_gmail.com> 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_at_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_at_gmail.com> wrote:
> > On 10/21/05, Paul Drake <bdbafh_at_gmail.com> 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
> > --
> > http://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.
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Oct 21 2005 - 16:23:54 CDT
