On 10/21/05, Christo Kutrovsky wrote:
I am curious as to how a failed statspack job can cause this ?
I am curious as to how a failed statspack job can cause this ?
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 HeisenbergUncertainty 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.trcThu 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 wrote:
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
On 10/21/05, Paul Drake 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.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
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
