FAQ
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

Search Discussions

  • Paul Drake at Oct 21, 2005 at 3:50 pm

    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.

    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
  • Christo Kutrovsky at Oct 21, 2005 at 3:56 pm
    I am curious as to how a failed statspack job can cause this ?

    And if not for production monitoring, where would you run statspack :)

    Christo
    On 10/21/05, Paul Drake wrote:
    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.

    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
    --
    http://www.freelists.org/webpage/oracle-l
  • Paul Drake at Oct 21, 2005 at 4:23 pm

    On 10/21/05, Christo Kutrovsky 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 wrote:
    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.

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedOct 21, '05 at 2:37p
activeOct 21, '05 at 4:23p
posts4
users2
websiteoracle.com

2 users in discussion

Paul Drake: 3 posts Christo Kutrovsky: 1 post

People

Translate

site design / logo © 2022 Grokbase