Oracle and other RDBMS

[farrokhp]

As an Ortacle DBA guy, I'd like to have just a basic picture about how Database admin in a whole and mostly syntax wise handle in other major RDBMS as SQL Server, Sybase and DB2 UDB. Can you refer me to a site that have a side by side compare chart/table of DBA commands in these Database also may be a benchmark about thier freatures and performace comparision.

[sreddy]

My new job involve SQL server and Sybase ASE and started looking at those RDBMS from DBA Perspective. Answering your Q isn't that easy if you want to compare these RDBMS and outline the DBA commands. The guy who worked on each RDBMS for atleast one project Deployment only can tell. By any chance if I could get the opportunuty Iam working on, I would be able to answer your Q some time in future.

http://www.dbasupport.com/forums/sho...?threadid=6110

[chrisrlong]

SQLServer's stated goal for 7.0 was 100 servers / dba. Mind you, I haven't used SQLServer since 6.5, but let me tell you - Oracle is not even in the same ball-park as far as ease of administration is concerned. I'm not even sure it's in the same sport. Of course, their problem had always been a lack of *raw power*, although they seem to have left Oracle in the dust in that respect too lately:
http://www.tpc.org/tpcc/results/tpcc_perf_results.asp
Although I am impressed with the powerful/albeit expensive entry they made in the TPC-H 3000GB category:
http://www.tpc.org/tpch/results/h-results.idc
On the flip side, My un-educated take on DB2 is that it has generally had an abundance of raw power, (nothe their 1000GB entry on the same page) but was ridiculously difficult to use and maintain.

Anyway, the point is not that we are comparing FORTRAN and COBOL with command-by-command equality. We are comparing COBOL with VB with C++, where all 3 have *completely* different ways of looking at the world. You cannot get down to a statement-level comparison - the best you might be able to do is to get to an 'activity'-level comparison, but even that is extremely tricky.

Good luck,

- Chris

BTW - Was anybody as surprised as I was to see all the Informix entries in the benchmarks - Guess IBM wasn't that crazy after all
http://dailynews.yahoo.com/h/ap/2001...mix_ibm_2.html

[ramses71]

where I am I look after a couple of SQL Server 6.5 and 7 databases as well as Oracle 7.3.4 and 8i. We have some sybase floating around but will promoting those over to Oracle within the next year.

No easy way to get a strict comparison between all rdbms's. Just some overviews.

SQL Server - easy to administer as mentioned above but really lacks in power, memory mgmt (like all MS products), portability to other platforms (tried porting it to a more secure server lately?), and flexibility.

Sybase - lots of nice sutff before Oracle but a pig on resources and difficult to manage objects within the product (so I am told).

Informix - (my previous VP had been a VP at Informix), again lots of nice stuff but some of the key features (like outer joins) were really lacking.

That's about as much as I know.

[farrokhp]

This is an article from a Sybase guy first time come to Oracle world !! ( I am looking for some more interesting articles to spark the fundamental similarities / differences of RDBMS giants in my mind ) Anymore help ?

***************************************************************************************

I compiled the following "oracle cheatsheet" when working in an Oracle
environment for the first time (coming to it as a Sybase person). I'd
welcome comments/corrections...

boss

Oracle Knowledge
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
On NT specifically
- regedit, then HKEY_LOCAL_MACHINE\software\Oracle for a list of all
environment vars set for Oracle
- Ctrl-panel -> Services: see what services are running
(ps -fe | grep oracle)
- Oracle Enterprise Manager == SSM or another 3rd party tool used to
graphically manage the database. Its an administrator tool.

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Environment
- $ORACLE_HOME: directory where Oracle s/w is located. (Eqiv. to
$SYBASE)
- $ORACLE_HOME/database: start/stop scripts for database
($SYBASE/RUN scripts)
- Oracle typically stores all data and log devices in flat files
- Oracle stores the commands that created/loaded a table in "control
files." These can get large and are sometimes archived.
- When you startup, Oracle reads a "Parameter file" which i can't find.

[
$ORACLE_SID = $DSQUERY to point to the instance name you want to
access.
There are 2 files that Oracle reads at startup: init${ORACLE_SID}.ora
and config${ORACLE_SID}.ora
]

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Terminology versus Sybase

- Database == SQL Server; Datafiles, redo logs, control files
- Instance == SQL server: the combo of the SGA and the Oracle processes.
Once started, the Instance is associated w/ a Database (mounting).
- Schema = the collection of objects that are readable by the user
(somewhat akin to object owner (dbo e.g.) in that objects are associated
with a schema, which is directed by the user login)
- Table Space == database (physically located in datafiles).
- Redo Logs == Transaction Logs. Oracle records all changes to a
memory buffer (redo log buffer RLB). Has a job called LGWR that
periodically writes them to the Rollback disk segments. Then a
third optional process called the Archiver (ARCH) archives the redo
log info to a tape/backup medium.
- DBWR == Checkpoint process; writes modified data in memory to
disk asynchronously; manages the Database Buffer Cache
- Database buffer cache (DBC) == Default data cache: basic memory
containing data read from disk; has dirty and clean pages, uses
LRU queue strategy like Sybase.
- Rollback Segments == Physical Sybase log segments: database devices

Things that are just integrated into the Sybase Server that are
given seperate terminology in Oracle:
- SGA: System global Area: Shared memory pool, database buffer cache,
and redo log buffer.
- PGA: Program Global Area: non shared memory area allocated to each
user connection
- PMON, SMON: Process and System monitor: they are daemons that clean
up behind users, reclaiming resources.
- RECO: Recoverer process
- LCKN: Lock process: manages locks inter instance

- Table == table, column == column, View == view, Index == Index
- Blocks, extents, segments = same

- Sequence: a system table that stores a single unique number across
a database; like having a master identity value for a whole Server, not
just one particular table.
- Synonym: a public alias to a object; prevents the need to have to
type schema.table all the time.

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Datatypes

Char(x): max 255, it blank pads shorter entries up to defined column
size
Varchar2(x): max 2000: much larger than Sybase's max of 255
Number(s,p) == sybase's numeric(s,p)
Date = Sybase's datetime [sysdate = getdate() to get current date/time]
Long: variable length data upto 2gb in size; only one per table. Can't
be put in RI constraints or indexes. Kinda
like a Sybase text field, but less functional (Sybase has no text size
limit and can have more than one per table)
Raw = Sybase's binary/image datatype.
ROWNUM: store's a Row's address. No sybase system equivalent < 11.9.2
(however its not static; its internal only)

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
SQL*PLUS

- sql*plus == isql: edit calls up a Notepad-ish editor that will
pass the SQL you write back to the sql*Plus program.

- while retreiving, can define column masks, where to break in displays,
compute values on the fly.
- lots of set commands: reads from $ORACLE_HOME/dbs/login.sql for
preset set commands (or could be $SQLPATH variable). type "show all"
for a list of all current settings. Type "show var" to show the
setting for a variable

- can save to a file, get (retreive) a file, and spool (redirect)
output to a file. spool filename, spool off.

- l[ist] == history in sqsh; can recall previous commands by line
number.
* == last command, use numbers otherwise.
- set pause on/off == piping output through more in sqsh.
- set null "NULL" to have null values appear as NULL instead of blanks.

- login.sql (in orant/DBS): .sqshrc file; can preset values

- / re-executes the last known command (like hitting go again in sqsh)
- If you edit something, it will cut off your last char unless you put
in a line at the end consisting of "/" by itself.

- @filename.ext runs stuff just as if you did isql -i filename.ext

- Sql*plus defaults to autocommit off (where as Sybase defaults on; once
you hit "go" the statement is committed to the database). There is an
option t
hough to have autocommit on. however, if i drop an object its
immediately
commi
tted. Weird. AND, if i log out, it automatically commits
anything pending.

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
PL/SQL Info/Commands & Equivalents

- Strings must single quoted like in Informix; double quotes implies
a column-name
- SQL commands end with semicolon, not go (like Informix)
- column names are typically case insensitive; system data is mostly
uppercase (a mainframe habit?)

[ That's my guess. :-) ]

- Referencing an object
schema table.column@dblink == server.database.table.owner.column
(dblink == database link, akin to server. schema == owner?)

[ Yes, basically, schema == owner. All objects owned by user
"finance" are referred to as the finance schema. You can think of
schemas as similar to databases in that they are a collection of
objects (tables) that are related.

a DBLINK is another object like a table and is defined for a single
table in the remote instance/server. When it's created, you
associate a username and password with it. Also, query
optimization is done at the receiving instance (or at least it was
when I worked with it a couple years back), so the remote instance
will return ALL records from a table and then the WHERE clause will
be applied by the local/receiving instance. Could be a bummer.]

- You can have defined Synonyms (Aliases) in Oracle
- Metadata: you can store comments on a table and a column right in the
database. VERY nice. Imports to the comment field in Erwin too.

- Working w/ dates a bit clunky; Oracle stores the date and time
together like sybase but defaults to only show date. To get the
time, you must do something like to_char('date',mm-dd-yy hh:mm:ss)
to get the time out. Plus, when inserting any date value not in the
exact form 01-jan-99 you'll have to use a to_date reverse engineered
function b/c Oracle (unlike sybase) cannot interpret and convert
dates on the fly.

[ Also, times are only stored to the second -- no miliseconds. Not a
big
deal for many apps, but scientific applications frequently care. :-) ]

- sql*loader == bcp; Oracle's data loading tool. Very efficient
at getting data IN, but not so much getting it out.
- export/import: akin to bcp in binary mode; its Oracle-only format

- create (or replace) [object] feature: nice.
- create table statement has many different options compared to Sybase.
You can cap the size of a table w/ Maxextents option. Can
"create table as select.." == select into table creation.

[ Try not to think of the various SIZE and MAX parameters as
options, even though they are. If you don't define values, then
Oracle creates objects with the defaults set up for the tablespace,
which can cause performance problems or waste space.]

- there is no select into to dynamically create a table. You'll
have to grab the ddl. which requires a 3rd party tool/stored proc.
- alter table modify column feature (though you can't change datatype
or shrink the size unless its empty). You also cannot drop the
column this way in 8.0
- decode: a simulated if/then/else. Has for/loop and while/loop
constructs. When/then construct for errorhandling.
- dynamic sql: exists! very straightforward.

- grant, drop table, truncate, insert, update, delete all work the same
more or less; different options of course.
- drop table cascade constraints: very nice way to not worry about
the foreign key constraints on a table when dropping it.

- Create table; you specify space allocation at create table time.
As you extend, Oracle will allocate more space, but this leads to
extreme fragmentation.

- desc[ribe] == sp_help [tablename]
- show user == select user_name()
- password == sp_password (pre Oracle 8.0; alter user)
- alter table a rename to b OR rename a to b == sp_rename.
- select * from v$session == sp_who
- select * from user_view == sp_helpcode [view_name]
- prompt "text" == print "text" (shows text on the screen)

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Outstanding questions

- How do you get data OUT of Oracle? Can Sql*Loader do that? You can
spool file output in Sql*plus...but not exactly what we're looking for.
Sql*Loader says it can do this, but no examples and no documentation.
Oracle guru suggests cut-n-paste out of TOAD. Blake says exp.

[ Nope. Spooling the output from Sql*Plus is it. SQL*Loader is just
what
its name implies -- a loading utility. :-( ]

- learn more about the Oracle directory structure; where do important
files reside? where does the server start from?

[Start looking at $ORACLE_HOME and $ORACLE_BASE. Then check out the
$HOME
directory for the "oracle" user -- it is frequently different than
$ORACLE_HOME and $ORACLE_BASE. ]

- what is a key preserved table? I couldn't update through a
view b/c one of the tables wasn't 'key preserved.'

- How do you tell which query plan, which index is getting used?

[My biggest problem in an Oracle environment are the backups. Oracle
doesn't have a native backup utility and so still depends on operating
system commands to backup up the data files one at a time. Very
cumbersome. While the backup is happening, Oracle can't write to the
data
files, so data changes are getting written to the log files in datablock
chunks instead of rows, so you have to size your log files to handle
that
if you want online (hot) backups.

Another concern is that Oracle never deletes from indexes, so you have
to
rebuild your indexes periodically to reclaim space from deletes.]