Query keeps failing with ORA-01555 (snapshot is too old)
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

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

  1. #1
    Join Date
    Mar 2004
    Location
    IA USA
    Posts
    257

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

    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!

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    set your undo retention higher (more than one hour) and make sure your undo tablespace is big enough to grow

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    Quote Originally Posted by 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.
    So if that is your intent, then you should look at PL/SQL collections.
    this space intentionally left blank

  5. #5
    Join Date
    Mar 2004
    Location
    IA USA
    Posts
    257
    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.

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    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.

    Code:
    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.???;
    this space intentionally left blank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width