It didn't make any sense to me either. After he showed me his "current" code
(as opposed to the original code he sent me) we discovered the ORA-1555 was
not coming from the query inside the loop at all. He was trying to query
min and max PK values in a single query at the start of the program. That
caused a full index scan and that's why we were getting ORA-1555. I told him
to get the min and max values in two separate queries and the ORA-1555 went
away. Thanks to all who helped.
--
Chuck
"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message
news:apclm7$ghm$2$8300dec7_at_news.demon.co.uk...
>
> Chuck.
> The developer hasn't done
> 'set transaction read only'
> before the loop has he ?
>
> I can't imagine any way that you can get
> ORA-1555 if you are correct in your statement
> that
>
> > I know the RBS is not wrapping back on itself
> > in that short of a time and it is not shrinking
> > either.
>
>
> --
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Next Seminar dates:
> (see http://www.jlcomp.demon.co.uk/seminar.html )
>
> ____USA__________November 7/9 (Detroit)
> ____USA__________November 19/21 (Dallas)
> ____England______November 12/14
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
>
>
>
> Chuck wrote in message ...
> >One of my developers is trying to query a 45g table that is
> constantly being
> >insert into. He wants to get a count of rows that meet a certain
> criteria.
> >Of couse he got ORA-1555 snapshot too old. I told him to query
> smaller
> >sections of the table and total up the counts for the individual
> sections so
> >he wrote a PL/SQL block to do that. It loops through table selecting
> 1000
> >rows at a time. Each of these queries finishes in seconds but he is
> still
> >getting ora-1555 though. Why? According to Oracle's documentation
> under the
> >"set transaction" command they use"statement-level read consistency".
> Isn't
> >each separate execution of the select a new statement? I know the RBS
> is not
> >wrapping back on itself in that short of a time and it is not
> shrinking
> >either.
> >
>
>
>
Received on Mon Oct 28 2002 - 09:39:36 CST
