-
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!
-
set your undo retention higher (more than one hour) and make sure your undo tablespace is big enough to grow
-
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
-
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.
-
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.
-
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.???;
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|