ORA-1555 on MV refresh (takes 16 seconds to complete)

Oracle Version 9.2.0.8 Platform AIX 5L
ORA-1555 after a call to refresh materialized view
Underlying table across link  contains only 23000 rows
One every four weeks or so this query returns ORA-1555
undo_retention=16hours
undo tablespace 30G

CREATE MATERIALIZED VIEW TEST_VIEW
TABLESPACE TEST_TBS
PCTUSED    0
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOCACHE
LOGGING
NOPARALLEL
BUILD IMMEDIATE
REFRESH COMPLETE
START WITH TO_DATE('17-Nov-2008 20:30:00','dd-mon-yyyy hh24:mi:ss')
NEXT TRUNC(SYSDATE + 1) + 20.5/24 
WITH PRIMARY KEY
AS 
SELECT *
  FROM te

Open in new window

mngong_rcAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mngong_rcAuthor Commented:
The last line is select * from  test_job_view@testdblink
This is the error that is returned

ORA-12012: error on auto execute of job 111
ORA-12008: error in materialized view refresh path
ORA-01555: snapshot too old: rollback segment number 4 with name "_SYSSMU4$" too small
ORA-02063: preceding line from CSS
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 820
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 877
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
0
woolmilkporcCommented:
Hi,
every four weeks?
One common cause for ORA-1555 are discrete transactions running in parallel to your job.
Could it be that someone on the remote side has scheduled a job to run every 4 weeks or so that is running under 'BEGIN_DISCRETE_TRANSACTION' and that is modifying data in the table underlying 'test_job_view'?
Sounds bizarre, but have a look ...
 
wmp
 
 
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mngong_rcAuthor Commented:
I  will look but it is kind of sporadic .Not exactly four weeks and not consistently .

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

sdstuberCommented:
How much rollback do you have?

That error is sort of self-explanatory,  you don't have sufficient rollback.

There are only 3 options to solve that.
-Add more rollback to handle the transaction load you encounter in these periodic spikes
-Change the application to do fewer transactions so you don't generate as much rollback
-Make the query execute faster so the window of transactions you will encounter is decreased.

0
mngong_rcAuthor Commented:
The specifics of the system is listed at the top of the page
undo_retention=16hours
undo tablespace 30G
The first two propositions are feasible and straightforward and I would have
gone with those anytime .
Considering the size of the UNDO TBS and the retention time it appeared there
was no shortage of either
It appears there is not much that can be done apart increasing TBS/undo_retention
My thanks

Mike
0
sdstuberCommented:
what is the code behind test_job_view?  How much underlying data must be kept in undo to support that view?


Also note  the last option doesn't always apply, in fact, is probably not applicable most of the time.

If 17 hours ago  I modify one row in your data and then never commit and then lots and lots of other transactions happen to fill up rollback so it can't be kept in undo.  Your whole app could run just fine as long as nobody needed that row, or if they needed it inside the 16 hour/30Gig undo window.  When your mv runs though, it needs everything and you must go back 17 hours and can't.  So you see the 1555 even if your query ran in 1 second.
0
mngong_rcAuthor Commented:
Like your point  even though I do not completely agree.
Are you talking about undo being overwritten .
That should  appear in v$undostat as unexpired  steal count .
For now the steal count is zero  when I query the database
a few hours after the error .
May be missing something obvious

0
sdstuberCommented:
Since it only happens once per month or so, it's probably some anomolous process (human or cpu or both) that causes it.  If you aren't experiencing 1555 other times, you wouldn't see it.
0
sdstuberCommented:
particularly if the user happens to be somebody that might use that mv,  then their session gets an error and they rollback/commit or otherwise resolve the situation so the mv works the next time
0
mngong_rcAuthor Commented:
The error also occurs across a database link .I am starting to feel like I should be worrying about the s ource database and not the database from where the query is run .


0
sdstuberCommented:
that could be too
0
woolmilkporcCommented:
... oh, I thought this was self-evident!
I always assumed your info about undo_tbs etc. were related to the underlying DB (see my first post).
 
wmp
 
 
 
 
 
0
sdstuberCommented:
Everything said above applies regardless of which side of the link the rollback issues occur

In this case, yes, we know it's from the remote side because the error message tells us so.

ORA-02063: preceding line from CSS   <<<--  This means the erros are from the remote side
0
mngong_rcAuthor Commented:
Have added 4000M worth of redo and will be monitoring.

Please share the points to the two contributors
0
sdstuberCommented:
to share the points, close the question, assign a grade and split the points between whatever posts you found helpful.
0
mngong_rcAuthor Commented:
Would accept the solution from woolmilkporc  (though it is yet to be tested) and since
the other answers have been  implemented without success.
Possibility of discrete transactions seems to be only possibility right now.
Please give 75% to woolmilkporc and 25% to sdstuber for consistency .
0
sdstuberCommented:
as the author, you're the one that closes a question
0
sdstuberCommented:
If you don't remember how here is the help link on closing your question

http://www.experts-exchange.com/help.jsp#hi407
0
woolmilkporcCommented:
Hi modus_operandi,

I think we should adhere to the author's intentions, as written in his comment # 23335091  above,
where he suggested to give 25% of the points to sdstuber and 75% to woolmilkporc (me).

So I recommend to accept sdstuber's comment # http:#a23004603 (63 points)
and woolmilkporc's (my) comment # http:#a22975569 (187 points).

As for grading - because I am one of the experts involved, I'd like to leave this decision to you.

Thanks a lot,

Cheers

woolmilkporc

 

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.