DA Morgan <damorgan_at_exesolutions.com> wrote in
news:3E6FE5AA.BA151C57_at_exesolutions.com:
> GC wrote:
>
>> On Wed, 12 Mar 2003 15:47:36 -0800, DA Morgan
>> <damorgan_at_exesolutions.com> wrote:
>>
>> >GC wrote:
>> >
>> >> Have a very strange ora-1555 error.
>> >>
>> >> In the simplest terms, here is what is happening:
>> >> 1. Long-running (~3 hours) query is run against table A, B, and C.
>> >>
>> >> 2. No data is updated, inserted, or deleted in tables A, B, or C,
>> >> thus no blocks are placed into rollback.
>> >>
>> >> 3. Since there is no data in rollback, Oracle does not need to
>> >> reference blocks in the rollback segments.
>> >>
>> >> 4. Table Z, completely unrelated to tables A, B, or C, is loaded
>> >> from table Z_tmp via the merge function in sql.
>> >>
>> >> 5. The query merrily runs against tables A, B, and C until this
>> >> merge job (on table Z) runs.
>> >>
>> >> 6. An ora 1555 is thrown and the query fails.
>> >>
>> >> Why would an ora 1555 be thrown on a query that is not referencing
>> >> any blocks in any rollback segments? I have a tar open and am
>> >> running systemstate and errorstack traces when this issue
>> >> replicates itself, but has anyone here had a similiar problem or
>> >> can fill what I may be missing?
>> >>
>> >> Database is a 9.0.1.4 64bit system running on hp-ux 11. Somewhat
>> >> large batch system with ample rollback space (20 rollback segs, 13
>> >> GB usable rollback space - perhaps overkill, but it was like this
>> >> when I got here and I don't want to give up disk space if I don't
>> >> have to :)
>> >>
>> >> Thanks,
>> >> Greg
>> >
>> >Almost undoubtedly nothing is running for 3+ hours and not using
>> >rollback. Why don't you post the SQL and let us take a look.
>>
>> Curious statement - why would a simple extract query (I'll have to
>> wait until morning to post it, if it comes to that) generate
>> rollback? Temp thrashing perhaps, memory sorts definitely, but
>> rollback? It's not even updating background data dictionary tables.
>> I'm not arguing per se, I'm just curious as to why rollback would be
>> generated in this situation.
>>
>> >Oh and get yourself out of 9.0.1. It was never intended for
>> >production and what you are hitting might be an early bug.
>>
>> 9.0.1 was bad, but the latest 9.0.1.4 patchset seems to have taken
>> care of many of the nasty bugs. Coworkers using 9.2.0.2 are having a
>> worse time than I :)
>>
>> Cheers,
>> Greg
>
> I can't answer that but here's one possibility.
>
> User A starts a long running SELECT.
> User B updates a record after A starts its query.
> The rollback segment holding the original of the record updated by B
> is reused.
>
> A's query gets to the point of needing the original version of the
> record updated by B and can't find it.
>
> I'm not sure A needs to be using any rollback for this to happen.
> Perhaps others like Jonathan and Howard
> can confirm my thought or point out the error in my thinking.
>
> This is just speculation ... but seems reasonable based on my
> understanding of how it might work.
>
> Daniel Morgan
>
>
Yes that is one possibility but the original poster has indicated in the
first post that there is no update/inserts/deletes happening on the
tables he is querying.
Okay another possibility is Delayed Block clean out. Maybe u did a Large
Load or Update and this select is being used to clean the blocks of the
ITLS but there is No RBS Entry as that has already been overwritten.
Just my few cents.
--
Best Regards,
Ganesh R
======================================
Live to Learn.. Forget and Learn Again
======================================
Received on Thu Mar 13 2003 - 10:44:50 CST
