Query keeps failing with ORA-01555 (snapshot is too old)

[Lily_Liu_2004]

DBAs.

Recently, one long query (duration around 1 hour) keeps failing with ora-01555. I increased the undo to 1.5g from 1g. It still gets the same error. Any good suggestion or work around to solve this problem?

Thanks!

[davey23uk]

set your undo retention higher (more than one hour) and make sure your undo tablespace is big enough to grow

[marist89]

While davey23uk has suggested a solution, let me throw this out. The majority of the time people get ORA-1555 errors because of improperly coded PL/SQL. They open up a cursor on table X and proceed to change X inside the cursor loop.

[gandolf989]

While davey23uk has suggested a solution, let me throw this out. The majority of the time people get ORA-1555 errors because of improperly coded PL/SQL. They open up a cursor on table X and proceed to change X inside the cursor loop.

So if that is your intent, then you should look at PL/SQL collections.

[Lily_Liu_2004]

Thanks for the response,

The part of query captured in the alert log is like this:
ORA-01555 caused by SQL statement below (Query Duration=1596 sec, SCN: 0x0000.0154dd03):
Mon Sep 26 14:15:13 2005
select hs.email as hsemail, info.* from ( select asi.pid,asi.name, asi.city, asi.state, asi.st
reet1, asi.street2, asi.code as ceeb, asi.zip, ab.address sstreet1, ab.email, ab.firstname,
ab.lastname, ab.city scity, ab.zipcode, ab.gender, asi.phone, ab.state sstate, au.pin, au.
userstatus, aa.award_status, dma.dmacode, dma.dmaname from actwendy_awardstatus aa, actwendy_s
choolinfo asi, actwendy_user au, actwendy_basicinfo ab,actwendy_zip az, actwendy_dma dma where au
.userid = ab.userid(+) and asi.pid (+)= au.pid and aa.userid(+) = au.userid and au.usertype=0 and
au.year=:1 and asi.state in (:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20
,:21,:22,:23,:24,:25,:26,:27,:28,:29,:30,:31,:32,:33,:34,:35,:36,:37,:38,:39,:40,:41,:42,:43,:44,
:45,:46,:47,:48,:49,:50,:51,:52) and asi.zip = az.zipcode(+) and dma.dmacode(+) = az.dmacode ) in
fo, (select abi.email,u.pid from actwendy_schoolinfo si, actwendy_user u, actwendy_basicinfo abi
where u.pid = si.pid and abi.userid = u.USERID and si.

It looks like just a long query.

[gandolf989]

I think you need to go back and completely rewrite this query. You are probably doing full tables scans one several tables, and probably doing two full table scans on actwendy_schoolinfo and actwendy_user. The joins are probably using nested loops. If those tables are large that could be quite a bit of IO. Try breaking the query into smaller chunks that don't overlap tables and then join those smaller chunks. You might also try using the new 9i join methods, i.e. LEFT OUTER JOIN, RIGHT OUTER JOIN, etc. Finally make sure that you are not creating a cartesean product.

select hs.email as hsemail, 
       info.* 
  from ( select asi.pid,                   asi.name, 
                asi.city,                  asi.state, 
                asi.st        as reet1,    asi.street2,
                asi.code      as ceeb,     asi.zip,
                ab.address    as sstreet1, ab.email, 
                ab.firstname,              ab.lastname, 
                ab.city       as scity,    ab.zipcode, 
                ab.gender,                 asi.phone, 
                ab.state      as sstate,   au.pin, 
                au.userstatus,             aa.award_status, 
                dma.dmacode,               dma.dmaname 
           from actwendy_awardstatus aa, 
              ( SELECT pid,            name, 
                       city,           state, 
                       st,             street2,
                       code,           zip,
                       phone
                  from actwendy_schoolinfo
                 where state in                     
                     (       :2,  :3,  :4,  :5,  :6,  :7,  :8,  :9,  :10,
                        :11, :12, :13, :14, :15, :16, :17, :18, :19, :20,
                        :21, :22, :23, :24, :25, :26, :27, :28, :29, :30,
                        :31, :32, :33, :34, :35, :36, :37, :38, :39, :40, 
                        :41, :42, :43, :44, :45, :46, :47, :48, :49, :50,
                        :51, :52 ))  asi, 
                actwendy_user        au, 
                actwendy_basicinfo   ab,
                actwendy_zip         az, 
                actwendy_dma         dma
          where au.userid = ab.userid(+) 
            and asi.pid     (+)= au.pid 
            and aa.userid   (+)= au.userid
            and au.usertype    =  0
            and au.year        = :1
            and asi.zip        = az.zipcode(+) 
            and dma.dmacode (+)= az.dmacode ) info, 
       ( select abi.email, u.pid 
           from actwendy_schoolinfo si, 
                actwendy_user       u, 
                actwendy_basicinfo  abi
 where u.pid      = si.pid 
   and abi.userid = u.USERID 
   and si.???;