Mohamed Houri’s Oracle Notes

February 8, 2007

Senior Oracle Developer interview I

Filed under: Oracle — hourim @ 12:12 pm

What are the most important skills a senior Oracle developer and a junior DBA must have? Can I hire this DBA/developer or not? Should I prefer to hire a high school graduate with 5 years of experience or a college graduate with 1 year of experience and an OCP (Oracle Certified Practice)? Those are kind of questions recruiters are faced to when they are in a process of hiring oracle consultant specialized in data base administrator and PLSQL developments.

Tom Kyte in his famous website emphasize that he considers people skills as the most important things he look for in a process of hiring. The ability to communicate, to keep an open mind and to realize that things change over time, he said, are at least as important as technical skills. How do we not agree with such assumption? This is a perfect first step hiring process.

The second step in the hiring process would be to test the candidate technical skills. Here below is a snapshot of questions that might serve to distinguish between a good, a bad or an indifferent candidate.
 
Question 1: Could you explain ORA-01555 rollback segment snapshot too old error?
If you understand the principle of oracle multi-versioning then you can easily explain ora-01555 error. Oracle multi-versioning is the ability of oracle to maintain simultaneously a multiple version of the same data. Let’s consider that and end user starts a query (q1) that selects tab.ides on table tab at time t1. At time t1+1 a transaction starts and updates tab.ides that are being selected by query q1. Suppose that the query q1 is still running and is still getting new tab.ides at time t1+1, what values of tab.ides query q1 will be retrieving? Values of tab.ides at time t1 or values of tab.ides at time t1+1?

The answer is that query q1 will be retrieving values of tab.ides at the time of the beginning of the query i.e t1. That is at time t1+1 oracle has two versions of the same data tab.ides values at time t1 and values at time t1+1. This is the multi-versioning process in oracle.

Then, what is the relation between multi-versioning and ora-01555 rollback segment snapshot too old error?

When query q1 starts oracle did not copy the content of this query in any place but instead when a modification occurs in data queried by query q1 oracle will preserve a copy of this data before the modification happen in a place called rollback segment. Oracle uses this rollback segment to reconstruct the read-consistent snapshot of the data. The data provided by query q1 is reconstructed from rollback segment and presented as it was when the query q1 began at time t1.

Consequently when you (or other session) are actively updating information you are reading through cursor or through sql query, oracle will be actively using its rollback segments in order to store those modifications aiming to provide you with a data read consistency. When oracle is not anymore able to restore a consistent data then you will receive the famous ORA-1555 rollback segment snapshot too old.

Question 2: What solutions you will advise in order to avoid ORA-01555 error?

In addition to what has been recommended here how to avoid ora-01555 you may also consider this
1. increase size of rollback segment
2  make your query run very quickly
3. make your transaction (or job) restartable so that when an ora-01555 error occurs you can re-run your job
4 schedule your job at time where there is less update activity on the data base.

More questions next time….

4 Comments »

  1. Hi
    i am working in core database team for CITIFINANCIAL BANK.
    here we are getting always this error .
    but when we execute our process in a time where there is less update activity on the data base ,process(job) executed successfully not getting this error.

    But here there are so many process which we have to schedule in pick time(more updation activity).what we should do to avoid this errro

    pls suggest

    Comment by shashi kumar jha — May 23, 2007 @ 5:56 pm | Reply

  2. Shashi,

    In order to avoid this error you must understand it. This error is raised during a select process. Each update, done on your data during your process by your process or by another one, Oracle will log the “before update” image in it’s rollback segment. When your process is slow, and when your data are beeing heavily updated oracle might not be able to have enough space to guarantie the consistency of the “before image”.

    In few words

    1. schedule your process in time where there is less concurrency
    2. Do not commit inside the loop

    3. If your process runs in less than 2 hours and if you don’t need to commit, then commit only once outside the loop

    4. If your process runs in several hours (5, 6 …) then you need, unfortunately to commit (always outside the loop) from time to time in order to avoid the ORA-0152

    5. Finally, you can also begin your process by filling up the content of your main cursor into a plsql table and then close your cursor. And Loop on the plsql table instead of your cursor

    Mohamed

    When Oracle is unable to restore a data as it was before the query begun then

    Comment by Houri — May 31, 2007 @ 10:34 am | Reply

  3. Hi,
    I would say make your query visit less blocks…

    Comment by yasir — August 8, 2012 @ 11:11 am | Reply

  4. Hi Yasir,

    Read consistency is the action that allows a session to re-construct an older version of a data for which it (the session) is not supposed to see the newer value updated by another session.
    The process of consistent data re-construction starts by visiting the real data block in which it finds a pointer to the undo records describing how to reconstruct the data as it was at the beginning of the query, it will reconstruct this data in the buffer cache and finishes by displaying it to the original query.
    If you make your query visiting less blocks than you reduce the risk of finding blocks of data with pointers to undo records necessitating a re-construction of a consistent image.

    Then yes, tune your query, make it visit less blocks and you will certainly reduce the likelihood apparition of the ora-01555 error

    Regards

    Comment by hourim — August 8, 2012 @ 12:07 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

%d bloggers like this: