Menu Bar

Friday, August 18, 2017

Knowing your optimizer


"Sure you won't change your mind?" / "Is there something wrong with the one I have?" - Dr. Gillian Taylor & Mr. Spock - Star Trek IV: The Voyage Home
Not so very long ago, I had one of the subject matter experts come and talk to me about what he considered to be the abysmal performance in a query against the ERP database we host. He was trying to find, via a SQL query, all open work orders from the database.

The WHAT

The query in question used two tables:
MSF600 - The equipment register table. This table contains all the pieces of equipment and is keyed on a column called EQUIP_NO
MSF620 - The work order table. This table contains all work orders, whether raised against equipment or not. It is keyed on WORK_ORDER but one of the many alternative indexes is based on WO_STATUS_M. Another alternative index is based upon the EQUIP_NO as well.

Work Orders have a column called WO_STATUS_M, which is the status of the work order. The valid values for status are:

A: Authorized
C: Closed
O: Open, not Authorized

This is going to address a specific query and the understanding of why it took a while to run. Long-running queries are not necessarily a bad thing, depending on what they are doing, however, this particular query seems like it should have run relatively quickly.

The SQL statement itself seemed fairly inoffensive:


select e.plant_no, e.equip_status, w.work_order, w.wo_desc, w.work_group, 
       w.raised_date, w.maint_sch_task, w.wo_status_m
from msf600 e
join msf620 w on w.equip_no = e.equip_no
where w.wo_status_m <> 'C'
order by raised_date
/

Why should an apparently simple query perform so badly? Well, let's find out. Sometimes the simplest looking queries can do the weirdest things.

Oracle has a statement EXPLAIN PLAN you can add to the start of any query to generate an ESTIMATED execution plan. This is what the optimizer THINKS it should be doing. The actual plan is something different but we won’t go into that today. Doing that here gives us:


SQL> explain plan for
  2  select e.plant_no, e.equip_status, w.work_order, w.wo_desc, w.work_group, 
  3         w.raised_date, w.maint_sch_task, w.wo_status_m
  4  from msf600 e
  5  join msf620 w on w.equip_no = e.equip_no
  6  where w.wo_status_m <> 'C'
  7  order by raised_date
  8  /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 2559632844

--------------------------------------------------------------------------------------
| Id  | Operation           | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |        | 20366 |  2684K|       | 57379   (1)| 00:00:03 |
|   1 |  SORT ORDER BY      |        | 20366 |  2684K|  2976K| 57379   (1)| 00:00:03 |
|*  2 |   HASH JOIN         |        | 20366 |  2684K|       | 56764   (1)| 00:00:03 |
|*  3 |    TABLE ACCESS FULL| MSF620 |  5006 |   430K|       | 55431   (1)| 00:00:03 |
|   4 |    TABLE ACCESS FULL| MSF600 | 38297 |  1757K|       |  1333   (1)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("W"."EQUIP_NO"="E"."EQUIP_NO")
   3 - filter("W"."WO_STATUS_M"<>'C')

17 rows selected.

SQL> 

What we can see from this is that with are joining both tables, no indexes and doing full table scans.

As far as reading the plan, in general, the further a line is indented, the earlier in the whole execution of the statement it is executed.

So this query as written is doing a TABLE ACCESS FULL on both MSF600 and MSF620. TABLE ACCESS FULL means start at the first record. Read EVERY record in the table and return it. So this query as written is reading ALL of MSF600, ALL of MSF620, picking up the records they want and matching them to MSF600. Then it matches all those records together, sorting them and returning it to the user.

The FIX


So, I modified the query just a little:
SQL> explain plan for
  2  select e.plant_no, e.equip_status, w.work_order, w.wo_desc, w.work_group, 
  3         w.raised_date, w.maint_sch_task, w.wo_status_m     
  4  from msf600 e
  5  join msf620 w on w.equip_no = e.equip_no
  6  where w.wo_status_m in ('A', 'O')
  7  order by raised_date
  8  /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
Plan hash value: 2508940585

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |            | 20364 |  2684K|       |  2954   (1)| 00:00:01 |
|   1 |  SORT ORDER BY                         |            | 20364 |  2684K|  2976K|  2954   (1)| 00:00:01 |
|*  2 |   HASH JOIN                            |            | 20364 |  2684K|       |  2339   (1)| 00:00:01 |
|   3 |    INLIST ITERATOR                     |            |       |       |       |            |          |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| MSF620     |  5005 |   430K|       |  1007   (1)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN                  | MSF62001I9 |  5005 |       |       |    16   (0)| 00:00:01 |
|   6 |    TABLE ACCESS FULL                   | MSF600     | 38297 |  1757K|       |  1333   (1)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("W"."EQUIP_NO"="E"."EQUIP_NO")
   5 - access("W"."WO_STATUS_M"='A' OR "W"."WO_STATUS_M"='O')

19 rows selected.

SQL> 

What happened here?

Look at our output now. There’s an index in there - this is the index I talked about at the top, the one that uses WO_STATUS_M as the access path to the data. An Index range scan is the optimizer telling us that it’s found an index and can search on a distinct range of values.

The Cost column is also a VERY abstract thing (represents the amount of effort the engine has to do to satisfy your query) and you shouldn’t go betting your house on it but notice the huge drop in the Cost of the query too.

So instead of matching reading ALL of MSF620 to find those 8,153 rows, if can go directly to the index, pick up the 8100+ out of the millions, match those to MSF600 (which will always be a full scan for a query like this) and return those. Huge reduction in I/O here.

There are other issues that can affect execution time for things like this as well - are the rows already in the buffer cache or do they need to be fetched from disk ? Has the statement previously been parsed and compiled and it's sitting in memory waiting to run again?

The WHY

The optimizer is both a beautiful thing and demon spawn all at the same time. It can only operate on what it knows and sometimes it makes a bad decision. The design of this particular status field (WO_STATUS_M) has 3 statuses – A & O which represent non-closed WO’s and C for closed. Which means you have a small number of A’s, then a bunch of C’s then the O’s. Which means you have data scattered all through the file.

The optimizer takes into consideration the distribution of the data as well.


 SQL> select wo_status_m, count(*)
  2  from msf620
  3  group by wo_status_m
  4  order by 1
  5  /

W   COUNT(*)
- ----------
A       4946
C    1175836
O         62

For this table we have 99.5% of the values are ‘C’. For whatever reason, the optimizer decided that since the range of values of “NOT C” were small it was more efficient to scan the whole table. I can’t tell you why. This is one of those bad decisions I told you about earlier.

When I changed it to be inclusive it turned around and said “OK, I know I have a few up front and a few at the end, let’s get them separately”.

It also doesn’t mean that this is always the case. There are ALWAYS exceptions.

I don’t have the time to go digging in the Oracle Documentation for this at the moment, but there are several blogs and articles I found elsewhere on the internet that backup these findings along the lines of “The Oracle Optimizer doesn’t necessarily like inequalities”:

http://oracleinaction.com/conditions-based-inequalities-cant-use-indexes-toresolve/
http://oracle.readthedocs.io/en/latest/sql/indexes/predicates-equality-before-inequality.html
http://grokbase.com/t/freelists.org/oracle-l/11bf3r6eky/strategies-for-dealing-with-not-equal-conditions-and-indexes

So bottom line here: if you’re trying to run a query with an inequality and getting bad performance, try an equality instead and see what you get.

Thursday, March 23, 2017

Back-Ups, Up and Away!


"Of all the things I've lost, I miss my mind the most" - apparently attributed to Mark Twain

Well, I've been a real slacker in adding anything resembling new content to this blog for the last, oh, 4 years. Time to try to make an effort to correct that.

Let's talk about backups.

The definition of backup, at least in respect to computers as defined in the Oxford English Dictionary is "A copy of a file or other item of data made in case the original is lost or damaged."

So, all we need to do is write a script to copy all the files that make up our database off to a secondary location, right ?

Right ?

No so much.

If the database is down, you could take a copy of all of the files that make up your database purely with a copy at the operating system level. This was historically called a "cold" backup. This, of course, incurs downtime. In years gone by, regular nightly downtime for things like backups was a common affair. I remember when I first got into IT as a computer operator (back at the dawn of time) - we'd start up the machines around 6AM, they'd go from 7AM to 6PM and then we'd shut down the applications, run backups and batch jobs and the like.

However that was another time. In our modern, internet world, users expect 99.99% uptime (Well they expect 100%, but lots of SLA's seem to float in the 99.99%+ area). This means that you don't have the liberty to shut things down for hours while you copy your database files to secondary disk or tape.

So why can't we just copy the database files to backup media with the rest of the files on our server and call it good ?

Oracle knows about changes to the database by a number called the SCN - System Change Number.

Every datafile in an Oracle database has a header block. This header block contains lots of information but among it all, recorded in every datafile, is the SCN at last checkpoint.

The database writer (DBWR) likes to take frequent power naps. If it's not doing anything it sleeps. It will wake up every 3 seconds if the database isn't really busy, or more frequently than that if it is and flush unwritten blocks to disk and the database will perform a checkpoint. As a part of that process, the header block in every file in the database is updated with the same SCN. As a part of making sure that database integrity is maintained in the database, the SCN's across the headers must be consistent.

So, if you're copying a database while the instance is active, the SCN in the datafile headers is constantly being updated. You copy the first file and the SCN is 1000, you copy the second datafile and the SCN is 1100 and the third is at 1200. So then when you try to restore from your backup, the headers are inconsistent and the database won't open.

There are several ways to implement backup strategies to handle this requirement.

Oracle defines any type of backup not done using RMAN as a User Managed Backup. They also provide an integrated backup tool, Recovery Manager (RMAN) to perform backups - and most DBA's will probalby want to go that route.

There are several ways to complicate user managed backups. If you use ASM - you really need to use RMAN for example.

I think I'll wrap up this first entry here, since we've covered the why of backups and the couple of different options for performing Oracle backups. In the next article, I'll cover user managed backups and how to take them.

Until next time.

Tuesday, July 9, 2013

T-SQL Tuesday #44 - Second Chances (or how not to be a Horse's Rear End)


"Mr Wazowski, what you lack simply cannot be taught. You're just not scary." - Dean Hardscrabble, 'Monsters University'
T-SQL TuesdayAfter a year of always realizing on the second Wednesday that T-SQL Tuesday was yesterday, I remembered this month and went searching early. This month I found Bradley Ball(blog|twitter) asking us to write about "Second Chances" - time to reflect upon a time we could have done something better if given a second chance or when we royally screwed up and wished we could have a "Do over".

Since my "infrequently updated blog" has been idle for over 6 months now, it is probably due for an update.

I've been working in IT for over 25 years. Screwing up, particularly in production can be pretty scary. My list of screw ups in that time is probably long and glorious. I'd like to say that I've never repeated the same screw up but I'm sure that I have.

Making a mistake (unless your "mistake" is something criminal or your sense of humor when developing test data ends up in production) probably isn't going to be the defining point of your career - these things are things you can learn from and grow from.

Looking back at a quarter of a century of assorted screw ups, I remember such things as:

  • The time I truncated a table in production - except it was the wrong table (all part of being an accidental DBA I suppose)
  • The time I defined the same physical disk on the SAN to two servers and wondered why I was having daisy chained DB failures
  • The time I realized that my backups really weren't quite as good as I thought they were after the aforementioned disk issue
There's more, I'm sure, but we'll leave it there.

Today's post is not just about learning from my mistakes, it's also writing about how the company I work for celebrates those mistakes.

Before I go into detail on that, I should note that I work for a small company. We all seem to get along very well together. Management truly has an open door policy - walk into the office of the President, CEO or anyone else and air concerns or just chat. Every two weeks we have a brief all hands conference call where successes are celebrated, updates on new business are given and at the end of the meeting, failures are also celebrated.

When you fail, you can expect to be the "proud" recipient of an "HA" award.

What is an HA you ask? HA stands for "Horse's A**" (since we're dealing with a blog post probably read in places of employment, I'll employ a censor to sanitize my post :-) ).

I've been with this company for over 5 years. I had done pretty well for the first 3 years and managed to avoid earning an HA of my very own.

I was doing some testing on one of the development servers. I forget what I was doing at the time - but it was something that required me to stop and restart the SQL Server service (you can see where this is going already, right?).

During the testing of whatever it was that I was doing, a call came in where I needed to jump onto the production server to check something out. I completed that and forgot to log out of the server.

I went back to where I had left off in my testing and stopped and restarted the SQL Server Service. Within a few seconds emails start coming in about a down production SQL Server as the monitoring system starts screaming and in short order after that the phone starts ringing as users call in wondering where the ERP system we support went.

Stopping the database also caused a couple of other failures downstream as other services that relied on the database also stopped because the database wasn't there anymore (and they maintained an active connection).

It wasn't a huge deal to correct it and systems were back up in a matter of minutes, but there was lots to do afterwards, since every outage requires a Root Cause Analysis to be presented to the customer etc.

I mentioned earlier about learning from your mistakes. This particular outage caused me to always take the time to be double sure when doing things like stopping services. Now I also try to reconfigure my desktop on production servers (is the Start Button on the left of the screen? Must be a production server).

Of course in the grand scheme of things, as far as second chances go, this is a minor thing. The main reason I wanted to write about this particular incident was to "show off" the little certificate that you can earn at my place of employment for those times when you wish you had a "Second Chance".

(For the record, these are awarded humorously and without ill intent, just in case you're wondering - and the transgression doesn't necessarily have to be huge to earn one, either. As Forrest Gump says, "It Happens" and when it happens to you here, you're probably getting one of these!).

Until next time - may you never find yourself facing the behind of the horse!

Saturday, November 24, 2012

FLASHBACK Technology in Oracle Databases (Part 2)


"By the way, did I mention it also travels in time?" - The Ninth Doctor, referring to the TARDIS
Last time we talked about using Flashback technology to run queries against our database as it looked in the recent past.

This can certainly be useful, but what if it just isn't enough. What if we need to get back a table we just accidentally dropped, or recover a data screw up so bad that recovery is our only option.

There are other aspects to Flashback technology that let us do just that.

What we looked at last time would be covered under the topic sub-titles "Flashback Query" and "Flashback Version Query". What other options do we have available to us?

Looking into the heart of our TARDIS (refer to Part 1 for the introduction of the Oracle TARDIS), we find the following options awaiting us:

  • Flashback Database - a physical level restore that restores the entire database to a specific point in time using flashback logs rather than doing a database recovery and rolling archive logs forward
  • Flashback Table - recover a table to a point in time
  • Flashback Drop - restores an accidentally dropped table as well as all related indexes, triggers and constraints
  • Flashback Transaction - rollback a single transaction and optionally, all transactions dependent upon the rolled back transaction
  • Flashback Transaction Query - see all the changes made by a specific transaction

Today we'll be talking about the first three on that list (we'll deal with Flashback Transaction in part 3 of the series).

Flashback Database

Let's start with Flashback Database. Much like a database recovery, Flashback Database is a physical level recovery, much like a database point in time recovery, up to and including the OPEN RESETLOGS at the end, but usually much faster because the initial recovery of the data files from backup isn't needed.

In order to use Flashback Database, the Fast Recovery Area needs to be configured in the database that is a candidate for Flashback.

This means that the db_recovery_file_dest and db_recovery_file_dest_size initialization parameters need to be set. Furthermore, in order to enable Flashback Database, db_flashback_retention_target also needs to be set. This parameter the number of minutes we can go back into the past and perform a Flashback Database.

Once this is configured, we must also make sure to issue ALTER DATABASE FLASHBACK ON.

What this will do is to start writing images of data blocks to the fast recovery area. The flashback logs are written sequentially and often in bulk. The database automatically creates, deletes and resizes these logs as needed. These logs are not archived.

The only thing that the DBA really needs to know about for the flashback logs is that they exist (from a sizing of the fast recovery area point of view) and that they are adding a performance overhead to the database.

The database also needs to be in Archivelog Mode for this functionality to work.

When doing a Flashback Database, the database uses information in these logs, as well as some information from archive logs, in order to perform the requested operation. It is because of this that you can't enable flashback after a failure and expect that you can flashback your database to before the failure.

Similar to the syntax used in Flashback Query, there are several options you can apply to Flashback Database:

  • TO SCN
  • TO SEQUENCE
  • TO TIMESTAMP
  • TO RESTORE POINT

Each of these also supports a TO BEFORE construct as well. Doing FLASHBACK TO will recover the database right up until the parameter specified. Doing FLASHBACK TO BEFORE will recover the database right up until immediately before the parameter specified.

Demo Time

First things first, let's check that our database is set up correctly:


SQL> show parameter db_recovery

NAME                        TYPE        VALUE                          
--------------------------- ----------- ------------------------------ 
db_recovery_file_dest       string      c:\oracle\flash_recovery_area  
db_recovery_file_dest_size  big integer 4977M                          

SQL> show parameter flashback;

NAME                          TYPE        VALUE
----------------------------- ----------- -----
db_flashback_retention_target integer     1440

SQL> select flashback_on from v$database;

FLASHBACK_ON                                               
------------------                                               
NO                                                               

SQL> alter database flashback on;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON                                                     
------------------                                               
YES                                                              

SQL> select instance_name, archiver from v$instance;

INSTANCE_NAME    ARCHIVE                              
---------------- -------                              
demo             STARTED

Next, let's check the status of the database as we left it after Part 1, then add a new table and populate it with some related data.


SQL> conn demo/demo
Connected.
SQL> alter session set nls_date_format = 'YYYY-MM-DD:HH24:MI:SS';

Session altered.

SQL> select table_name from user_tables;

TABLE_NAME                                                    
---------------                                               
PREZ                                                          

SQL> desc prez
 Name                                      Null?    Type 
------------------------------------------ -------- -----------
 NAME                                      VARCHAR2(25)
 ELECTED                                   VARCHAR2(4)

SQL> select * from prez;

NAME                      ELEC                     
------------------------- ----                                               
Barack Obama              2008                     
George W. Bush            2013                     
Bill Clinton              2013                                           
George H. W. Bush         2013                     
Ronald Reagan             2013                                           

SQL> select sysdate from dual;

SYSDATE                                                           
-------------------                   
2012-11-24:22:02:36                                               

SQL> create table viceprez (name varchar2(25), elected varchar2(4));

Table created.

SQL> insert into viceprez values ('Joe Biden', '2008');

1 row created.

SQL> insert into viceprez values ('Dick Cheney', '2000');

1 row created.

SQL> insert into viceprez values ('Al Gore', '1992');

1 row created.

SQL> insert into viceprez values ('Dan Quayle', '1988');

1 row created.

SQL> insert into viceprez values ('George H. W. Bush', '1980');

1 row created.

SQL> select * from viceprez;

NAME                      ELEC                                    
------------------------- ----                                           
Joe Biden                 2008                     
Dick Cheney               2000                     
Al Gore                   1992                     
Dan Quayle                1988                     
George H. W. Bush         1980                     

SQL> select sysdate from dual;

SYSDATE                                                                      
-------------------                                                          
2012-11-24:22:09:07                                                          

SQL> quit

Note the time stamp here. At the time that the timestamp is shown the rows have not been committed to the database. The commit does not happen until we quit from SQL*Plus, where the commit happens implicitly.

So, a user gets into a database and starts deleting rows. They suddenly realize that they are in prod and not in test!

The DBA comes to the rescue (after first realizing that he must shut down the database in order to perform the flashback).

I've also included listings here showing the current incarnation of the database from RMAN between steps.


C:\>rman target /
RMAN> list incarnation of database;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       DEMO     3543868088       CURRENT 1          19-NOV-12

RMAN>quit

C:\> sqlplus demo/demo
Connected.

SQL> delete from viceprez where elected< 2000;

3 rows deleted.

SQL> delete from prez where elected = 2013;

4 rows deleted.

SQL> commit;

Commit complete.

SQL> select * from prez;

NAME                      ELEC
------------------------- ----
Barack Obama              2008

SQL> select * from viceprez;

NAME                      ELEC
------------------------- ----
Joe Biden                 2008
Dick Cheney               2000

SQL>connect / as sysdba
Connected.


SQL> flashback database to timestamp to_timestamp('2012-11-24:22:09:07','YYYY-MM-DD:HH24:MI:SS');
flashback database to timestamp to_timestamp('2012-11-24:22:09:07','YYYY-MM-DD:HH24:MI:SS')
*
ERROR at line 1:
ORA-38757: Database must be mounted and not open to FLASHBACK. 

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> quit

C:\> sqlplus / as sysdba

SQL> startup mount
ORACLE instance started.

Total System Global Area  535662592 bytes                                       
Fixed Size                  1375792 bytes                                       
Variable Size             415236560 bytes                                       
Database Buffers          113246208 bytes                                       
Redo Buffers                5804032 bytes                                       
Database mounted.
SQL> flashback database to timestamp to_timestamp('2012-11-24:22:09:07','YYYY-MM-DD:HH24:MI:SS');

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> connect demo/demo
Connected.

SQL> select * from prez;

NAME                      ELEC                                                  
------------------------- ----                                                  
Barack Obama              2008                                                  
George W. Bush            2013                                                  
Bill Clinton              2013                                                  
George H. W. Bush         2013                                                  
Ronald Reagan             2013                                                  

SQL> select * from viceprez;

no rows selected

SQL> quit

C:\>rman target /
RMAN> list incarnation of database;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       DEMO     3543868088       PARENT  1          19-NOV-12
2       2       DEMO     3543868088       CURRENT 849611     24-NOV-12

RMAN>quit

This is where the earlier comment about the placement of the commit came into play (and also ties neatly into my earlier post this month about Transaction Isolation Levels in Oracle). Because the changes had not been committed at the timestamp specified, when the flashback database was performed, those rows are missing from the resultant database.

Let's assume that we now go back and recreate the data in the VICEPREZ table and that our same user goes and deletes the exact same records again. We'll pick it up at the delete and the subsequent rescue by the DBA:


SQL> select sysdate from dual;

SYSDATE                                                                         
-------------------                                                             
2012-11-24:22:22:11                                                             

SQL> delete from viceprez where elected < '2000';

3 rows deleted.

SQL> delete from prez where elected = '2013';

4 rows deleted.

SQL> commit;

Commit complete.

SQL> select * from prez;

NAME                      ELEC
------------------------- ----
Barack Obama              2008

SQL> select * from viceprez;

NAME                      ELEC
------------------------- ----
Joe Biden                 2008
Dick Cheney               2000

SQL> connect / as sysdba
Connected.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  535662592 bytes                                       
Fixed Size                  1375792 bytes                                       
Variable Size             415236560 bytes                                       
Database Buffers          113246208 bytes                                       
Redo Buffers                5804032 bytes                                       
Database mounted.
SQL> flashback database to timestamp to_timestamp('2012-11-24:22:22:11','YYYY-MM-DD:HH24:MI:SS');

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> connect demo/demo
Connected.

SQL> select * from prez;

NAME                      ELEC                                                  
------------------------- ----                                                  
Barack Obama              2008                                                  
George W. Bush            2013                                                  
Bill Clinton              2013                                                  
George H. W. Bush         2013                                                  
Ronald Reagan             2013                                                  

SQL> select * from viceprez;

NAME                      ELEC                                                  
------------------------- ----                                                  
Joe Biden                 2008                                                  
Dick Cheney               2000                                                  
Al Gore                   1992                                                  
Dan Quayle                1988                                                  
George H. W. Bush         1980                                                  

SQL> quit

C:\>rman target /
RMAN> list incarnation of database;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       DEMO     3543868088       PARENT  1          19-NOV-12
2       2       DEMO     3543868088       PARENT  849611     24-NOV-12
3       3       DEMO     3543868088       CURRENT 849931     24-NOV-12

RMAN>

The DBA is the hero of the hour, having managed to successfully recover the database back to pre-delete state without the large time investment of a complete database restore.

Of course, such functionality needs to be used with great care in a production environment to make sure that the rest of the data you lose isn't more of a pain than the data you've already lost.

One scenario that immediately comes to mind for the use of something like this would be in a large Enterprise application where Payroll is one of the subsystems in the database. The developers in this theoretical shop are in the process of making major functional changes to the code. They want to test on as close to live data as possible so they request a refresh of the test database.

Being a large Enterprise level database, it takes 2-3 hours to restore the test server from backup. The developers run their test payroll but it didn't work. They need another fresh copy of the production database in order to run their test again.

Before Flashback Database, another 2-3 hours would be wasted waiting for the refresh to complete.

If the DBA setup the test environment for Flashback and let the developers do their testing, if they needed a fresh copy of the database in test, a quick Flashback Database later and they have a fresh copy of the data in a fraction of the time.

Flashback Table

Our next scenario is for Flashback Table. With this feature you can flashback just one table in the database to a particular SCN, TIMESTAMP or RESTORE POINT. Creating a restore point is as simple as:


SQL> create restore point flashback_table;

Restore point created.

Again, let's assume that someone has run rampant over some table in the database. The table is stand alone, with no other dependencies on other objects, so it has been determined that it's safe to roll this one table back to a point in time in the past.

Demo Time


SQL> conn demo/demo
Connected.
SQL> select * from viceprez;

NAME                      ELEC
------------------------- ----
Joe Biden                 2008
Dick Cheney               2000
Al Gore                   1992
Dan Quayle                1988
George H. W. Bush         1980

SQL> select sysdate from dual;

SYSDATE            
-------------------
2012-11-24:22:32:14

SQL> delete from viceprez where elected = '2008';

1 row deleted.

SQL> select * from viceprez;

NAME                      ELEC
------------------------- ----
Dick Cheney               2000
Al Gore                   1992
Dan Quayle                1988
George H. W. Bush         1980

SQL> flashback table viceprez to restore point flashback_table;
flashback table viceprez to restore point flashback_table
                *
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled 

What does that mean? Row movement is a setting that tells Oracle it's OK to generate new ROWID's for rows being recovered in this manner (since there's no guarantee that the old ROWID is still unique).

Fortunately, this is a setting that can be enabled after the fact!


SQL> alter table viceprez enable row movement;

Table altered.

SQL> select * from viceprez;

NAME                      ELEC                                                  
------------------------- ----                                                  
Dick Cheney               2000                                                  
Al Gore                   1992                                                  
Dan Quayle                1988                                                  
George H. W. Bush         1980                                                  

SQL> flashback table viceprez to restore point flashback_table;

Flashback complete.

SQL> select * from viceprez;

NAME                      ELEC                                                  
------------------------- ----                                                  
Joe Biden                 2008                                                  
Dick Cheney               2000                                                  
Al Gore                   1992                                                  
Dan Quayle                1988                                                  
George H. W. Bush         1980                                                  

Flashback Drop

For our last scenario, we're going to look at recovering a table that has been dropped.

In this scenario (ignoring the fact that a user shouldn't have drop rights on a table anyway), a user inserts a row into the table, realizes their error and attempts to remove it. However, instead of removing the row, they drop the table in error.

The DBA puts on his red cape and once again comes to the rescue!

Demo Time


SQL> insert into viceprez values ('The Man in the Moon', '2013');

1 row created.

SQL> select * from viceprez;

NAME                      ELEC                                                  
------------------------- ----                                                  
Joe Biden                 2008                                                  
Dick Cheney               2000                                                  
Al Gore                   1992                                                  
Dan Quayle                1988                                                  
George H. W. Bush         1980                                                  
The Man in the Moon       2013                                                  

6 rows selected.

SQL> drop table viceprez;

Table dropped.

SQL> select * from viceprez;
select * from viceprez
              *
ERROR at line 1:
ORA-00942: table or view does not exist 


SQL> select table_name from user_tables;

TABLE_NAME                                                                      
------------------------------                                                  
PREZ                                                                            

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME          
---------------- ------------------------------ ------------ -------------------
VICEPREZ         BIN$bsEpu+DLQZWxoPngfpkZJQ==$0 TABLE        2012-11-24:22:40:18

SQL> flashback table viceprez to before drop;

Flashback complete.

SQL> show recyclebin;
SQL> select table_name from user_tables;

TABLE_NAME                                                                      
------------------------------                                                  
PREZ                                                                            
VICEPREZ                                                                        

SQL> select * from viceprez;

NAME                      ELEC                                                  
------------------------- ----                                                  
Joe Biden                 2008                                                  
Dick Cheney               2000                                                  
Al Gore                   1992                                                  
Dan Quayle                1988                                                  
George H. W. Bush         1980                                                  
The Man in the Moon       2013                                                  

6 rows selected.

SQL> delete from viceprez where elected = '2013';

1 row deleted.

SQL> select * from viceprez;

NAME                      ELEC                                                  
------------------------- ----                                                  
Joe Biden                 2008                                                  
Dick Cheney               2000                                                  
Al Gore                   1992                                                  
Dan Quayle                1988                                                  
George H. W. Bush         1980                                                  

Conclusion

Used with caution, the above Flashback options could save you hours of time in the event that you accidentally drop the wrong table, or have the need to perform repetitive testing on a test copy of the database where the testing causes mass updates to the data (such as in a payroll update).

There are many more options to these commands than what we've talked about here. For full information, please refer to Chapter 18 of the Oracle 11g Database Backup and Recovery User's Guide.

Next Time

Next time, we'll dig a little into Flashback Transaction and Flashback Transaction Query.

Tuesday, November 20, 2012

FLASHBACK Technology in Oracle Databases (Part 1)


"If it helps, I can travel in time as well" - The Tenth Doctor, referring to the TARDIS
Today we're talking about Time Travel in Oracle Databases.

I almost made a reference in the title and quote of this post about Mr. Peabody and the Way Back Machine but that would have perhaps shown my age - besides, quoting The Doctor is way cooler.

The Doctor and the Tardis

I recently found myself in a situation where I needed to go back in time. I was rolling forward a copy of a database to a particular point in time from a backup. I needed to stop at that particular point in time because the project manager wanted me to run row counts on the 1500 odd tables in the "before" and "after" databases and I had taken the "before" at that specific point in time.

During my recovery operation I fat fingered the UNTIL TIME clause and went past my stopping point.

After the expected cursing had died down I started wishing I had a TARDIS of my very own. Apart from the coolness factor of being able to zip back and forth through time at will, being able to go back about 15 minutes and fix my mistake would have been a miracle in and of itself.

It was then that I remembered FLASHBACK and it became my own TARDIS - Time and Relational Data in Schemas.

What is Flashback Query

First introduced in Oracle 9i, Flashback Query enables as to view the database as it existed at a point in time in the recent past. In Oracle 9i and onwards, this is implemented via the DBMS_FLASHBACK package.

This functionality was enhanced in Oracle 9i Release 2 with the addition of the "AS OF TIMESTAMP" clause to the SELECT statement.

Prerequisites and Limitations

There are some prerequisites to use Flashback Query

  • Initialization parameter requirement: undo_management = auto
  • Initialization parameter requirement: undo_retention = nnnn (maximum number of seconds to be able to query into the past)
  • Initialization parameter requirement: undo_tablespace = <undo_tablespace_name>
  • FLASHBACK (specific tables) or FLASHBACK ANY TABLE system privilege
  • EXECUTE privilege on DBMS_FLASHBACK

There are some restrictions that you should be aware of as well:

  • Prior to 11g, DDL that alters the structure of a table invalidates old UNDO data, rendering Flashback Query inoperative. Post 11g, most DDL is supported.
  • Timestamps map to an SCN (System Change Number) Value. These are internally updated every 5 minutes. If operating within that window, query results may be inaccurate if using timestamps (use SCN's for increased accuracy).
  • You must DISABLE/ENABLE the flashback mode before changing time windows.
  • Only data state is affected. Any table that has had a DDL change will reflect the most recent state of the table.
  • You cannot use Flashback Query on V$ views, but you can use it on catalog tables like USER_TABLES.
  • The SYS user cannot use DBMS_FLASHBACK but can use AS OF TIMESTAMP in a SELECT.
  • DML or DDL operations cannot be performed in Flashback mode. Open a cursor, fetch the data, end the flashback session and manipulate the data or use INSERT INTO ... SELECT AS OF TIMESTAMP constructs.
  • The undo_retention parameter is a target - it is not a guarantee of data availability.

Demo Time

The first thing we would need to do is to make sure that the user we're going to use has the correct permissions so we'd want to execute the following script:


create user demo identified by demo default tablespace users;
grant create session, flashback any table, create table to demo;
grant execute on dbms_flashback to demo;
alter user demo quota unlimited on users;

Next, we will check our parameters to make sure that automatic undo is turned on and that we're archiving:


SQL> connect / as sysdba
Connected.
SQL> show parameter undo

NAME                         TYPE        VALUE
---------------------------- ----------- ---------------
undo_management              string      AUTO
undo_retention               integer     3600
undo_tablespace              string      UNDOTBS1

SQL> select instance_name, archiver from v$instance;

INSTANCE_NAME    ARCHIVE                              
---------------- -------                              
demo             STARTED                              

After that, let's create a table and load a little data and validate what we have. At the end of the inserts, we'll commit the changes, grab our current SCN and our current date and time.


SQL> conn demo/demo
Connected.

SQL> create table prez (name varchar2(25), elected varchar2(4));

Table created.

SQL> insert into prez values ('Barack Obama', '2008');

1 row created.

SQL> insert into prez values ('George W. Bush', '2000');

1 row created.

SQL> insert into prez values ('Bill Clinton', '1992');

1 row created.

SQL> insert into prez values ('George H. W. Bush', '1988');

1 row created.

SQL> insert into prez values ('Ronald Reagan', '1980');

1 row created.

SQL> select * from Prez;

NAME                      ELEC
------------------------- ----
Barack Obama              2008
George W. Bush            2000
Bill Clinton              1992
George H. W. Bush         1988
Ronald Reagan             1980

SQL> commit;

Commit complete.

SQL> alter session set nls_date_Format = 'YYYY-MM-DD:HH24:MI:SS';

Session altered.

SQL> set serveroutput on
SQL> var scn number;
SQL> exec :scn := dbms_flashback.get_system_change_number;

PL/SQL procedure successfully completed.

SQL> exec dbms_output.put_line ('SCN = '||to_char(:scn));
SCN = 818801

PL/SQL procedure successfully completed.

SQL> select sysdate from dual;

SYSDATE 
-------------------
2012-11-20:22:49:06

Next, we'll run an update where we "accidentally" update all the rows in the table to an incorrect value. We'll also grab a timestamp here.


SQL> update prez set elected = '2013';

5 rows updated.

SQL> commit;

Commit complete.

SQL> select sysdate from dual;

SYSDATE
-------------------
2012-11-20:22:49:32

With that done, it's now time to check the table out with current values and values as they were at a specific SCN. The first way uses the DBMS_FLASHBACK package to view the data as it was at that first SCN we assigned to the variable :scn. Then, we do it again using SELECT ... AS OF SCN.


SQL> select * from prez;

NAME                      ELEC
------------------------- ----
Barack Obama              2013
George W. Bush            2013
Bill Clinton              2013
George H. W. Bush         2013
Ronald Reagan             2013

SQL> exec dbms_flashback.enable_at_system_change_number(:scn);

PL/SQL procedure successfully completed.

SQL> select * from prez;

NAME                      ELEC
------------------------- ----
Barack Obama              2008
George W. Bush            2000
Bill Clinton              1992
George H. W. Bush         1988
Ronald Reagan             1980

SQL> exec dbms_flashback.disable;

PL/SQL procedure successfully completed.

SQL> select * from prez;

NAME                      ELEC
------------------------- ----
Barack Obama              2013
George W. Bush            2013
Bill Clinton              2013
George H. W. Bush         2013
Ronald Reagan             2013

SQL> select * from prez as of scn :scn;

NAME                      ELEC
------------------------- ----
Barack Obama              2008
George W. Bush            2000
Bill Clinton              1992
George H. W. Bush         1988
Ronald Reagan             1980

For the next demo, we'll do effectively the same thing, using Timestamps instead of SCN's. I made sure to wait at least 5 minutes so that the internal SCN to TIMESTAMP mapping was updated correctly and we went and did round 2.


SQL> select * from prez;

NAME                      ELEC
------------------------- ----
Barack Obama              2013
George W. Bush            2013
Bill Clinton              2013
George H. W. Bush         2013
Ronald Reagan             2013

SQL> exec dbms_flashback.enable_at_time(to_date('2012-11-20:22:49:00','YYYY-MM-DD:HH24:MI:SS'));

PL/SQL procedure successfully completed.

SQL> select * from prez;

NAME                      ELEC
------------------------- ----
Barack Obama              2008
George W. Bush            2000
Bill Clinton              1992
George H. W. Bush         1988
Ronald Reagan             1980

SQL> exec dbms_flashback.disable;

PL/SQL procedure successfully completed.

SQL> select * from prez 
  2  as of timestamp 
  3  to_timestamp('2012-11-20:22:49:00','YYYY-MM-DD:HH24:MI:SS');

NAME                      ELEC
------------------------- ----
Barack Obama              2008
George W. Bush            2000
Bill Clinton              1992
George H. W. Bush         1988
Ronald Reagan             1980

SQL> select * from prez 
  2  as of timestamp 
  3  to_timestamp('2012-11-20:22:49:32','YYYY-MM-DD:HH24:MI:SS');

NAME                      ELEC
------------------------- ----
Barack Obama              2013
George W. Bush            2013
Bill Clinton              2013
George H. W. Bush         2013
Ronald Reagan             2013

Finally, there's another clause we can add to the SELECT statement to show what versions of a row may exist. You can specify SELECT ... VERSIONS BETWEEN.

We start by updating one row of the table, getting the new times and SCN's and then running the queries.


SQL> update prez set elected = '2008' where name = 'Barack Obama';

1 row updated.

SQL> select * from prez;

NAME                      ELEC
------------------------- ----
Barack Obama              2008
George W. Bush            2013
Bill Clinton              2013
George H. W. Bush         2013
Ronald Reagan             2013

SQL> commit;

Commit complete.

SQL> select sysdate from dual;

SYSDATE                                                           
-------------------                                               
2012-11-20:23:11:54                                               

SQL> exec dbms_output.put_line ('SCN = '||to_char(:scn));
SCN = 818801                                              

PL/SQL procedure successfully completed.

SQL> var scn2 number;
SQL> exec :scn2 := dbms_flashback.get_system_change_number;

PL/SQL procedure successfully completed.

SQL> exec dbms_output.put_line ('SCN = '||to_char(:scn2));
SCN = 820456                                         

PL/SQL procedure successfully completed.

SQL> select * from prez 
  2  versions between scn 818801 and 820456 
  3  where name = 'Barack Obama';

NAME                      ELEC
------------------------- ----
Barack Obama              2008
Barack Obama              2013
Barack Obama              2008

SQL> select sysdate from dual;

SYSDATE                                               
-------------------                                               
2012-11-20:23:19:24                                               

SQL> select * from prez 
  2  versions between timestamp 
  3  to_timestamp('2012-11-20:22:49:00','YYYY-MM-DD:HH24:MI:SS')
  4  and to_timestamp('2012-11-20:23:20:00','YYYY-MM-DD:HH24:MI:SS')
  5  where name = 'Barack Obama';

NAME                      ELEC
------------------------- ----
Barack Obama              2008
Barack Obama              2013
Barack Obama              2008

Conclusion

One of the important things to note here is that while you're in Flashback mode, no DML is allowed. One way to get around that is to use SELECT ... AS OF to create a temporary copy of the data you need and then work from there to correct it.

The following would work:


create table temp_prez as select * from prez as of scn :scn;

From there you'd be able to compare current and old data and update accordingly as needed.

For further information on Flashback Technology in Oracle Databases, be sure to check the Oracle Documentation.

Next Time

Next time, we'll dig a little deeper into the features added in Oracle 10g and later, specifically Flashback Table, Flashback Drop and Flashback Database.

Thursday, November 15, 2012

Question to self: Who am I ?


"I believe the appropriate metaphor here involves a river of excrement and a Native American water vessel without any means of propulsion." - Dr. Sheldon Cooper, The Big Bang Theory.
Today we'll talk about some Oracle stuff for a change.

I had one of my colleagues come to be a couple of weeks ago and say to me "How to I tell what database I'm connected to after I connect to the database" ?

I had to scratch my head a little on that one. When connected as SYSDBA, it's very simple to select from v$instance and you have your instance name.

However, if you're not connected as SYSDBA how do you do it.

Enter the SYS_CONTEXT function. SYS_CONTEXT allows you to extract parameter values associated with a particular namespace. Oracle provides a predefined namespace called USERENV which contains a wealth of information about the current session.

The following, for example would tell you which database you're currently connected to:


SELECT SYS_CONTEXT ('USERENV', 'INSTANCE_NAME') 
   FROM DUAL;

There's a full list of the predefined parameters in the Oracle SQL Language Reference Manual.

Wednesday, November 14, 2012

Isolation Levels in Oracle vs SQL Server


"I am somewhat preoccupied telling the Laws of Physics to shut up and sit down." -Vaarsuvius, Order of the Stick Webcomic
I sat in on a session at the PASS Summit in Seattle last week listening to a session delivered by Randy Knight talking about Isolation Levels in SQL Server.

Knowing that Oracle handles things differently to SQL Server made me want to research things a little closer.

As a DBA who spends time in both Oracle and SQL Server, it's important to understand Isolation Levels in these two RDBMS's and how they work, because their default behavior is very different.

According to the ANSI Standard, there are 4 default Isolation Levels in a Relational Database Management System, ordered below from least isolated to most isolated.

  1. Read Uncommitted
  2. Read Committed
  3. Repeatable Read
  4. Serializable

Going into the details of what each of these mean is outside the scope of what I want to write about today. There's a good write up at Wikipedia or any of a dozen or more blogs, just search "Isolation Levels" on your favorite search engine.

It should be noted that SQL Server implements all 4 of the ANSI Standard Isolation Levels plus one of its own (SNAPSHOT Isolation - which has 2 different levels in itself - Statement level and Transaction Level). As with the basic tenets, going into the details of snapshot isolation is outside of the scope of what I want to talk about here. To learn more about SNAPSHOT Isolation, refer to Books Online.

Oracle, on the other hand, does not implement Read Uncommitted and also does not implement Repeatable Read. Oracle adds in their own extra option called Read Only Isolation. You can read more about this option in the Oracle Documentation.

Because I work daily in both Oracle and SQL Server, what I really wanted to find out was what are the core effects of how SQL Server approached the READ COMMITTED Isolation level vs how Oracle handles it.

In basic terms, SQL Server has a "pessimistic" view of data concurrency by default. When in the middle of a transaction, SQL Server will block readers on any row that has changed until that row is committed.

Oracle, on the other hand has an "optimistic" view of concurrency by default. When in the middle of a transaction, Oracle will have readers see a version of the row as it existed at the start of the update transaction, thus not blocking readers.

I found an article written by Tom Kyte, Oracle's technology expert, where he talked about possible data inaccuracies when Read Committed is implemented the way that Microsoft does it in SQL Server and wanted to summarize it here as I used it for my own testing.

The basic setup is like this:

A bank account database has a table for balances that contains two columns, account and balance. The table has many hundreds of thousands rows.

A query is started to sum balances. The database begins reading pages and accumulating balances. After 200,000 records are read a transaction is done in the database. An account that has already been read is decremented by $400 and an account that has not yet been read (row number 300,000) is incremented by $400.

How do both database systems handle this ?

The timeline would go like this (I am going to represent the summing of balances by selecting one row at a time):

  1. Transaction 1: select balance where account = 1;
  2. Transaction 1: select balance where account = 2;

    ... etc ...

  3. Transaction 2: Decrement the value of account 1 by $400;
  4. Transaction 2: Increment the value of account 300000 by $400;

    ... etc ...

  5. Transaction 1: select balance where account = 300000;

    ... etc ...

  6. Transaction 2: Commit

Let's start by looking at how Oracle handles it.

I preload a table with three rows. Account 1 has a balance of $500, Account 2 has a balance of $200 and Account 300000 has a balance of $100.

Oracle's SQL*Plus, differently from SQL Server Management Studio, has an implicit transaction. You don't need to specify BEGIN TRANSACTION - a transaction is assumed from the moment you start until you quit SQL*Plus (implicit commit) or explicitly issue a commit or rollback.

In Session 1, we execute the following


SQL> select balance from accounts where account = 1;

   BALANCE
----------
       500

SQL> select balance from accounts where account = 2;

   BALANCE
----------
       200

SQL>

We do not commit the transaction.

In Session 2 we update the values of our accounts


SQL> update accounts set balance = 100 where account = 1;

1 row updated.

SQL> update accounts set balance = 500 where account = 300000;

1 row updated.

SQL>

Once again we leave the transaction open and do not commit.

We switch back to session 1:


SQL> select balance from accounts where account = 300000;

   BALANCE
----------
       100

SQL>

The query immediately returns the value of account 300000 as it was at the beginning of this current transaction, giving us a consistent view of all the balances as of the time that the query started.

Returning to Session 2 now, we can commit the transaction over there just to tidy up loose ends.


SQL> Commit;

Commit complete.

SQL> 

Having seen how Oracle does it (the optimistic view), let's have a look at SQL Server.

On SQL Server, we need to explicitly tell SQL Server Management Studio that we want a transaction.


begin transaction

select * from accounts where account = 1;
select * from accounts where account = 2;


account                                 balance
--------------------------------------- ---------------------------------------
1                                       500

(1 row(s) affected)

account                                 balance
--------------------------------------- ---------------------------------------
2                                       200

(1 row(s) affected)

So far so good.

Over to Session 2, we perform our balance transfer between accounts


begin transaction

update accounts set balance = 100 where account = 1;
update accounts set balance = 500 where account = 300000;

(1 row(s) affected)

(1 row(s) affected)

Returning to Session 1, we try to query Account 300000:


select * from accounts where account = 300000;

SSMS sits here and spins. It is waiting for the transaction from Session 2 to complete and commit the change to the database. A writer is blocking a reader now.

We swap back to session 2 and complete the transaction:


commit transaction

Immediately the query in Session 1 returns a value:


account                                 balance
--------------------------------------- ---------------------------------------
300000                                  500

(1 row(s) affected)

That's not right .... or is it ? I'd have to say no - it's not right - not from a pure data accuracy standpoint.

From the SQL Server Read Committed Isolation level and the way it's implemented that is the correct value - the value was read after it was committed to the database but the net result is that the $400 has been counted twice. It was counted in the balance of account 1 and then again in the balance of account 300000 - our query has, in fact, returned incorrect data!

If I was wanting an accurate picture of my account balances under this scenario, it appears to be that Snapshot Isolation is what's needed, but even then it can be a little tricky.

I ran through the scenario above again using Snapshot Isolation, using these scripts in the same order of command execution as presented above::

Session 1


set transaction isolation level snapshot
begin transaction
select * from accounts
select * from accounts where account = 300000
commit transaction

Session 2


set transaction isolation level snapshot
begin transaction
update accounts set balance = 100 where account = 1;
update accounts set balance = 500 where account = 300000
commit transaction

The value in the last query of Session 1 showed 100 before I committed Session 2, with an immediate return, just like Oracle did. However, even after I had committed Session 2 and ended it completely, the return from the query on the balance on account 300000 in Session 1 was still showing $100, until I ran the commit transaction in Session 1, when it turned back to the $500 value that was actually sitting in the database.

This actually makes sense, since we had done a begin transaction at the start of the query in Session 1, so it was showing us a picture of the data as it existed at the start of Session 1.

If we run through the example one more time, this time not putting the queries in Session 1 inside a transaction, as soon as Session 2 commits, the balance of account 300000 is showing the updated value immediately.

Microsoft provides an excellent example of the different behavior under snapshot isolation and read committed snapshot isolation on their website (that uses vacation days instead of a bank account as the example).

So, there's a definite "Gotcha!" sitting in there. If this kind of level of accuracy is important to you and your application, make sure you completely understand how Isolation levels work in the database system you're using.

I should re-iterate that the demonstration scenario used in this post was used in Tom Kyte's article as linked above and replicated here in order to compare and contrast the differences between the default behavior in both Oracle and SQL Server and how it can be modified by using other options. While he mentioned "other vendors" in his article, I wanted to see for myself how this scenario specifically played out in SQL Server (as I already knew for certain how it would behave in Oracle).