True or False – Undo

9 05 2010

May 9, 2010

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

Continuing the true or false blog article series with more questions that seem to have conflicting answers, if the answers to those questions depend on the source of the information found through Internet searches.  Today’s quiz is on the topic of Oracle Database’s undo functionality, and in some cases you may need to consult several of the suggested articles in order to correctly answer each of the questions.

When answering the questions, answer True or False, followed by a justification for the answer that you provided.  Yes, there are a lot of questions, but part of the entertainment in these types of exercises is reading an article and identifying what is logically right or wrong.  Of course, as with the previous quizzes, any answers that attempt to utilize logical fallacies will be scored as incorrect.

The Articles:

The Quiz:

1.  Rollback segment shrinks cause the next session to use the rollback segment to wait while the rollback segment is reduced in size to its optimal size.

2.  An OPTIMAL size for rollback segments should never be set because when the rollback segments are correctly sized, they should never increase in size.

3.  The ideal rollback segment size is partially determined by the values of the DB_WRITER_PROCESSES, LOG_BUFFER, AND LOG_CHECKPOINT_INTERVAL initialization parameters, as well as the COMMIT frequency.

4.  Large, long running batch jobs should be manually set to use a single, large rollback segment to reduce the risk of ORA-01555 (snapshot too old) errors.

5.  Adding a large undo segment when manual undo management is in use will effectively reduce the chances of sessions receiving ORA-01555 (snapshot too old) errors.

6.  When automatic undo management is used, ORA-01555 (snapshot too old) errors are triggered by Oracle Database creating many small undo segments in an undo tablespace that is too small.

7.  In a database with a single session connected, it is not possible to trigger an ORA-01555 (snapshot too old) error when the session is not modifying the tables that are being queried.

8.  When manual undo management is in use, only UPDATE transactions are able to automatically cause an undo segment to grow; SELECT statements do not cause the undo segments to grow, thus increasing the risk of ORA-01555 (snapshot too old) errors when the undo segments are small.

9.  When manual undo management is in use, it is a common practice for DBAs to assign a transaction to a specific rollback segment using the SET TRANSACTION command, but this is unnecessary with automatic undo management.

10. The automatically created undo segments in the SYSTEM tablespace will only be used when modifying objects residing in the SYSTEM tablespace.

 11. The UNDO_TABLESPACE parameter must be set to an undo tablespace’s name when automatic undo management is used.

12. The V$WAITSTAT view includes a block class that indicates the number of waits on the system undo header blocks.

13. The SESSIONS parameter should be set to a value no larger than the expected maximum number of user sessions since the SESSIONS parameter affects the number of undo segments created when automatic undo management is used.

14. When automatic undo management is used, as the number of sessions connected to the database instance increases, the instance dynamically adds additional (offline) undo segments in response to the sessions merely connecting.  The TRANSACTIONS_PER_ROLLBACK_SEGMENT parameter determines the number of additional sessions that must be connected before an additional undo segment is added.

15. SELECT type SQL statements do not generate redo nor undo.

16. In most databases, DML or DDL is performed in only 10% of all transactions in the database.

17. After a session changes data and issues a COMMIT, all sessions with access permissions to view the data are able to immediately see those changes.

18. Read consistency in Oracle means that data included in the query results will always be as of the moment the query was submitted.

19. Undo segments may be used to support flashback type operations.

20. The redo logs contain undo records, which allows Oracle Database to optimize the process of recovering from a shutdown abort, and also means that a future release of Oracle Database will no longer need dedicated undo tablespaces.

Note that a new blog article category has been added to quickly locate the quiz articles.  When attempting to answer the true or false questions, think about under what conditions the answer to the question will be true and under what conditions the answer to the question must be false.


Actions

Information

10 responses

10 05 2010
Charles Hooper

These questions must be a bit difficult to answer, so I will eliminate one of the easy questions (just to get the ball rolling).

Question #16 states “In most databases, DML or DDL is performed in only 10% of all transactions in the database.” This is a restatement of the following quote from article #1 that is listed above:
“Seems they forgot that generally speaking, only about 1 in 10 ‘transactions’ in most databases actually do DML/DDL and that 90% are usually SELECT.”

There are a couple of problems with the above:
1. In a general sense, a transaction begins when data in a table is inserted, updated, or deleted. Inserting, updating, and deleting data are all forms of DML. In this case, I would assume that about 10 in 10 (100%) of all transactions in a database perform some sort of DML.
2. Let’s assume that instead of “transaction” the article is describing the types of SQL statements submitted by sessions, that 90% of all SQL statements submitted to the database instance are SELECT type SQL statements. If we find a database instance where on average 90% of all SQL statements submitted are SELECT type statements, does that mean that roughly 10% of the SQL statements submitted are either DML or DDL? No. But why? Article #7 and this documentation reference explain why.

In short, SELECT statements are DML statements, so if 90% of the SQL statements submitted are SELECT type statements, at least 90% of the SQL statements submitted are DML statements. With this in mind, I would assume that the amount of submitted DDL that affects an average production database is quite small, which probably means that at least 99% of the SQL statements must be DML.

Question #16 is False.

11 05 2010
Charles Hooper

I will eliminate another one of the easy questions. Question #19 states “Undo segments may be used to support flashback type operations.” That question is True… on second thought, maybe it is False.

The Oracle Database Editions document states that Standard Edition and Standard Edition One do not support Flashback Table, Flashback Database, Flashback Transaction, nor Flashback Transaction Query. Testing shows that the Standard Edition does support FLASHBACK TABLE x TO BEFORE DROP, which uses the Oracle RECYCLEBIN. So, for the Standard Edition and Standard Edition One, undo segments are not used to support flashback type operations.

The answer is true for the Enterprise Edition, but of course that is not the full story. The documentation states that flashback is used to:
•Perform queries that return past data
•Perform queries that return metadata that shows a detailed history of changes to the database
•Recover tables or rows to a previous point in time
•Automatically track and archive transactional data changes
•Roll back a transaction and its dependent transactions while the database remains online

For instance, FLASHBACK DATABASE uses RMAN, flashback logs, and archived redo logs.

If the answer must be correct for Standard Edition and Enterprise Edition, then the question must state that flashback type operations use the Oracle RECYCLEBIN. If the answer just needs to be correct for the Enterprise Edition, the question could just as easily state that “RMAN may be used to support flashback type operations.”

11 05 2010
Charles Hooper

Time to eliminate another one of the easy questions.

Question #20, while long and “wordy”, like all automatically correct questions, is probably False. If you read article #2, you would probably answer “certainly False”, and what is so special about Oracle Database v6 anyway?

11 05 2010
Charles Hooper

Time to eliminate another one of the easy questions.

Question #7, False. A test case (I think) in article #9 (Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions) showed that it is possible for the only session connected to an Oracle database to generate an ORA-01555 (snapshot too old) error when the session is only querying tables, and has not modified the tables that are being queried. It might be interesting to see if a more recent release of Oracle Database would also trigger the same ORA-01555 error with the same conditions in place.

12 05 2010
Donatello Settembrino

Hi Charles,

question 15

15. SELECT type SQL statements do not generate redo nor undo.

False or perhaps we should say depends :)
I think there is much to discuss, there are several cases to consider

SQL>create table t (x int not null);

SQL>insert into t select rownum from dual connect by level commit;

dirty block in buffer cache

SQL>select v.dirty, count(*)
  2  from v$bh v
  3  where v.objd = (select object_id from user_objects where object_name = 'T')
  4  group by v.dirty;

D   COUNT(*)
- ----------
Y          8

stored current statistics

SQL>select n.name, m.value
  2  from  v$mystat m, v$statname n
  3  where m.STATISTIC# = n.STATISTIC#
  4  and   n.name in ('redo size', 'cleanouts only - consistent read gets', 'undo change vector size')
  5  order by n.name;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
cleanouts only - consistent read gets                                     4
redo size                                                             80728
undo change vector size                                               26316



SQL>select count(*) from t;

  COUNT(*)
----------
        10

at this point, Oracle performs a commit (fast) cleanout

SQL>select n.name, m.value
  2  from  v$mystat m, v$statname n
  3  where m.STATISTIC# = n.STATISTIC#
  4  and   n.name in ('redo size', 'cleanouts only - consistent read gets', 'undo change vector size')
  5  order by n.name;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
cleanouts only - consistent read gets                                     4
redo size                                                             80728
undo change vector size                                               26316

SQL statements do not generate redo nor undo, DBWR has not yet written any changes

if before committed (following the insert) another session clean the buffer cache (Eg Alter system flush buffer_cache) blocks were written to disk. A commit followed by a select generates redo The following actions in sequence:

Session 1

SQL>Drop table t purge;

SQL>create table t (x int not null);

SQL>insert into t select rownum from dual connect by level alter system flush buffer_cache;

Session1

SQL>commit;


SQL>select n.name, m.value
  2  from  v$mystat m, v$statname n
  3  where m.STATISTIC# = n.STATISTIC#
  4  and   n.name in ('redo size', 'cleanouts only - consistent read gets', 'undo change vector size')
  5  order by n.name;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
cleanouts only - consistent read gets                                     5
redo size                                                           3198412
undo change vector size                                              507916

following a delayed block cleanout

SQL>select count(*) from t;

  COUNT(*)
----------
    200000

SQL>select n.name, m.value
  2  from  v$mystat m, v$statname n
  3  where m.STATISTIC# = n.STATISTIC#
  4  and   n.name in ('redo size', 'cleanouts only - consistent read gets', 'undo change vector size')
  5  order by n.name;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
cleanouts only - consistent read gets                                   156
redo size                                                           3209460
undo change vector size                                              507916

as I said, this time redo is generated

12 05 2010
Timur Akhmadeev

Another example.

12 05 2010
Charles Hooper

Donatello,

Delayed block cleanout is the exception (the answer for the question being false) that I had in mind when I wrote question #15.

Here are a couple of more links:
http://forums.oracle.com/forums/thread.jspa?messageID=3718129&
http://jonathanlewis.wordpress.com/2009/06/16/clean-it-up/

If we take the test table from the forums.oracle.com that I provided, assume that selecting from the table generated the following output (this is actual output from running the query):

SET AUTOTRACE ON STATISTICS
 
SELECT
  *
FROM
  T10;
 
Statistics
---------------------------------------
     4  recursive calls
     0  db block gets
    10  consistent gets
     0  physical reads
     0  redo size
  9319  bytes sent via SQL*Net to client
   381  bytes received via SQL*Net from client
     2  SQL*Net roundtrips to/from client
     0  sorts (memory)
     0  sorts (disk)
  1000  rows processed

Now if a FOR UPDATE clause is added to the SELECT statement:

SELECT
  *
FROM
  T10
FOR UPDATE;
 
Statistics
---------------------------------------
      4  recursive calls
   1023  db block gets
     14  consistent gets
      0  physical reads
 221252  redo size
  53257  bytes sent via SQL*Net to client
    381  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
   1000  rows processed

We see that the SELECT statement has generated 221,252 bytes of redo.

Timur, thanks for posting a link to another example.

12 05 2010
Charles Hooper

Time to answer another one of the easy questions.
#11 “The UNDO_TABLESPACE parameter must be set to an undo tablespace’s name when automatic undo management is used.” False. Documentation Link #4 (10.2.0.x) states the following:
“When the instance starts, the database automatically selects the first available undo tablespace. If no undo tablespace is available, then the instance starts without an undo tablespace and stores undo records in the SYSTEM tablespace. This is not recommended in normal circumstances, and an alert message is written to the alert log file to warn that the system is running without an undo tablespace.

If the database contains multiple undo tablespaces, you can optionally specify at startup that you want to use a specific undo tablespace. This is done by setting the UNDO_TABLESPACE initialization parameter, as shown in this example:

UNDO_TABLESPACE = undotbs_01

In this case, if you have not already created the undo tablespace (in this example, undotbs_01), the STARTUP command fails. The UNDO_TABLESPACE parameter can be used to assign a specific undo tablespace to an instance in an Oracle Real Application Clusters environment.”


Two for the price of one – the above quote also answers question #10 “The automatically created undo segments in the SYSTEM tablespace will only be used when modifying objects residing in the SYSTEM tablespace.”.

13 05 2010
Charles Hooper

Time to answer another one of the easy questions.
#18 “Read consistency in Oracle means that data included in the query results will always be as of the moment the query was submitted.” False. It could mean that the data returned by the query will be as of the moment the session’s transaction started if ISOLATION_LEVEL=SERIALIZABLE is set for the session.

But, maybe there is more to read consistency than what happens when the value of the ISOLATION_LEVEL parameter changes, a feature introduced in Oracle Database Version 4. Yes, there is a bit more on the topic of what read consistency means in the documentation.

14 05 2010
Charles Hooper

Time to answer another one of the easy questions.

#1. “Rollback segment shrinks cause the next session to use the rollback segment to wait while the rollback segment is reduced in size to its optimal size.”
False. The question seems to imply (I did write the question, but not the source material that prompted the question) that it is the next session that uses the rollback segment that actually performs the shrink operation. Article #9, on page 174 of that book includes the following under the heading “SMON: The System Monitor”:
“Shrinks rollback segments: SMON will perform the automatic shrinking of rollback segments to its optimal size, if it is set.”

So, it is the SMON process that is responsible for shrinking the rollback segments, not the next session to use the rollback segment.

Possibly also interesting is the following quote:
“ORA-1595 and ORA-1594 9 October 1999
I’m getting the following intermittent error with various rollback segments.
ORA-01595: error freeing extent (1) of rollback segment (7)
ORA-01594: attempt to wrap into rollback segment (7) extent (1) which is being freed

Two factors are necessary for this to happen.
A rollback segment has extended beyond OPTIMAL.
There are two or more transactions sharing the rollback segment at the time of the shrink.
What happens is that the first process gets to the end of an extent, notices the need to shrink and begins the recursive transaction to do so. But the next transaction blunders past the end of that extent before the recursive transaction has been committed.
The preferred solution is to have sufficient rollback segments to eliminate the sharing of rollback segments between processes. Look in V$RESOURCE_LIMIT for the high-water-mark of transactions. That is the number of rollback segments you need. The alternative solution is to raise OPTIMAL to reduce the risk of the error.”

The above implies that it is the session that is using the rollback segment that begins a recursive transaction to perform the shrink. Of course the above quote is more than 10 years old, so there is a chance that things have changed since Oracle Database 8i.

Speaking of Oracle Database 8i, the following quote might be interesting:
“You should carefully assess the kind of transactions the system runs when setting the OPTIMAL parameter for each rollback segment. For a system that executes long-running transactions frequently, OPTIMAL should be large so that Oracle does not have to shrink and allocate extents frequently. Also, for a system that executes long queries on active data, OPTIMAL should be large to avoid ‘snapshot too old’ errors. OPTIMAL should be smaller for a system that mainly executes short transactions and queries so that the rollback segments remain small enough to be cached in memory, thus improving system performance.”

That quote just states that “Oracle” performs the shrink operation, but the mention of “snapshot too old” errors leads into some of the other questions. It also lead to the following quote:
“cause 2) the description:

user a logs in

user b logs in and deletes/updates/inserts X records commits in a loop.

MAY cause A to experience an ORA-1555 if A is performing a long running query against the object(s) B is modifying. It will not cause it for the reason you state. Rollback data is not preserved for SELECTS. As soon as B commits, his rollback might be overwritten. It is when it is overwritten that user A will get the ORA-1555.

An ORA-1555 is never about running out of rollback. It is about rollback that was generated being overwritten. A select statement will not cause rollback to be ‘held’. As soon as the transaction that generated the rollback commits – that rollback may be reused and if it is and it is needed by some query, you will get an ORA-1555.”

Of course that Asktom thread pointed to his one, also on the topic of “snapshot too old” errors.

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




Follow

Get every new post delivered to your Inbox.

Join 139 other followers

%d bloggers like this: