Oracle and other RDBMS
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Oracle and other RDBMS

  1. #1
    Join Date
    Nov 2000
    Posts
    416

    Exclamation

    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.

    An ounce of prevention is worth a pound of cure

  2. #2
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    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
    Reddy,Sam

  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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

  4. #4
    Join Date
    Dec 2000
    Posts
    8
    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.

  5. #5
    Join Date
    Nov 2000
    Posts
    416
    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.]

    An ounce of prevention is worth a pound of cure

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width