[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2008-11-17
22
Medium Priority
?
1,066 Views
Last Modified: 2013-12-19
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

0
Comment
Question by:mngong_rc
  • 9
  • 7
  • 3
19 Comments
 

Author Comment

by:mngong_rc
ID: 22975448
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
 
LVL 68

Accepted Solution

by:
woolmilkporc earned 500 total points
ID: 22975569
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
 

Author Comment

by:mngong_rc
ID: 22977122
I  will look but it is kind of sporadic .Not exactly four weeks and not consistently .

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
ID: 23004603
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
 

Author Comment

by:mngong_rc
ID: 23004754
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 23004983
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
 

Author Comment

by:mngong_rc
ID: 23006910
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 23006932
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 23006948
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
 

Author Comment

by:mngong_rc
ID: 23012853
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 23012873
that could be too
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 23012886
... 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
 
LVL 74

Expert Comment

by:sdstuber
ID: 23012960
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
 

Author Comment

by:mngong_rc
ID: 23104371
Have added 4000M worth of redo and will be monitoring.

Please share the points to the two contributors
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 23104494
to share the points, close the question, assign a grade and split the points between whatever posts you found helpful.
0
 

Author Comment

by:mngong_rc
ID: 23335091
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 23336721
as the author, you're the one that closes a question
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 23336741
If you don't remember how here is the help link on closing your question

http://www.experts-exchange.com/help.jsp#hi407
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 23635431
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

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to recover a database from a user managed backup
Suggested Courses
Course of the Month19 days, 9 hours left to enroll

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question