Re: 10g expdp hang/spin win32 ora-600 [504] (in testing)

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