Re: Oracle question from a SQL Server DBA

I'm biased of course :-) but ...

aa wrote in message <6dfscs$qk8$1_at_news.interlog.com>...
>I am an experienced SQL Server DBA starting to learn Oracle. I am comparing
>the two systems and would like comments on the following points.
>
>Table update locks:
>With SQL Server, if I run a query that updates every row in a table the
>entire table will be locked.

Which means that no-one else can do a thing ...

>If I simaultaneouly run a select query against
>the table the query will hold until the update either commits or rolls
 back.
>If I do the same in Oracle, the select query will return the old snapshot
 of
>the table ( from rollback segments) before the update is committed. The
 user
>is unaware of the fact someone else is updating the table and the Select
>results will be out of date as soon as the other update commits.

But the users query will be absolutely up to date with the point in time when they issued the query.

>The SQL Server user will know someone is running an update (due to the
>hourglass) and will try again later. He is always sure that any Select
>results he obtains are committed ones.

As is an Oracle user. A select will only ever return committed results.

>What are the pros and cons of these methods?

To me there are several big pluses to the Oracle approach

1. A user will never have a query with results which include both committed and uncommitted changes but (*importantly*)
2. Oracle doesn't require the over-use (IMHO) of table locks.

I think the concurrency, consistency balance of Oracle is brilliant. However, the big downer
is the dreaded 'Snapshot too old error' and the fact that you may have a query run for ages
and return absolutely nothing! (Then again, if a complete block on table reads had been
issued, the user wouldn't have had the chance to run the query at all ;->

>

>Cold sever backups:
>With Sql Server, I can copy *.DAT in MSSQL\DATA to another machine with
>SQL Server loaded and everything will be up instantly. All database
>configuration are stored in Master.dat.
>Can I do the same with Oracle? Would I have to re-create the database
>instances in the Registry? Please comment.

I'm no expert on this platform, but I suspect that Oracle would require some registry entries.

>
>Integrate Login:
>Can I authenticate an Oracle user with the NT login?
 

>
>Performance monitoring:
>Where can I get Oracle counters for perfomance monitor? SQL Server has a
>whole bunch of them and that facilitates tuning a great deal.
>

I'm not sure what you mean by counters, but I think you'll find what you need in
the following virtual tables - V$SYSSTAT (main one), V$SESSTAT, V$WAITSTAT and a
host of others. Try

    select table_name from dictionary where table_name like 'V$%'

Hope some of this helps. Trust me, once you get used to the undoubted differences,
you might be impressed. (Although in my experience DBAs are an extremely fickle
bunch!)

>
>
Received on Wed Mar 04 1998 - 00:00:00 CST