Re: Sybase vs Oracle - which is better?

Hi!

>I'd agree ... sqlplus is more like isql and SQR tied together. However, as
>a Sybase DBA for 7 years I've never run into anything I could not 'do' with
>isql and some shell scripting.

but your scripts then becomes platform dependend?? The SQLPlus ones I make can run on any platform (NT, Unix, VMS, VM, AIX etc.)

>I like Sybase's organization of it's data dictionary via system tables.
>Sybase also has a better SQL 'trace' capability. Sybase also allows you to
>'select' results within a stored proc w/out requiring you to store the info
>into another table and/or local variables.
For an Oracle guy like me, could you elaporate on these capabilities? To me, it seems I have plenty of trace cabilities in Oracle, INCLUDING, tracing how much each section/path of a SQL takes of a query.

And no, you don't need to store the results of a query to pass it on to the procedure calling it. Use REF CURSORs and the result set can be returned without using an INTO.

>Oracle assumes that the user is not smart enough to figure out how to use
>it's system tables so it gives us more than 400 views to present data
>dictionary info ... and heaven help you if you try to join some of these
>denormalized views!

Ohh - 400 views huh? Nahh, your statement just shows how colored you get by using the same system over and over again. Oracle does not assume ANYTHING about the user - it provides the novice Oracle user with access to views (which experienced users uses too) and advanced users can gain access to the "real" dictionary "tables" (v$ etc.).

>Sybase's 'set statistics io on' provides a breakdown of logical and
>physical i/o's for *all* tables (including work tables) involved in a
>query. Oracle provides one big number for all i/o's for all tables
>involved in a query.

Depends on how you those to trace your query/DML. You can get all broken down to each query-path you need. Including logical and fysical IO, number of rows processed etc. And that includes of course any temporary allocation needed for sorting etc.

I have on many, many, many occasions used Sybase's
>detail i/o statistics to tune queries ... much nicer than Oracle's
>capabilities.

As I don't know Sybases - it might look "nicer" - it doesn't mean that you don't have the ability in Oracle.

>..... In Oracle all 'select' statements must put
>their results *into* something within the stored proc, i.e., you can not
>send the results of the 'select' directly back to the client ...

False - use REF CURSORs for this excat reason. Available since 7.3.

>Something else I don't like about Oracle is the way they handle their LONG
>datatypes. If you need to scan a table, or rebuild an index on said table,
>and there's a LONG datatype in the table, then Oracle also reads the pages
>that contain the LONG data.

Oracle lets YOU deside where and how to store data. That includes longs. If you choose to store your long for a picture with your customer data, then you have a problem. If you choose to store it in a sepate table with a reference to the customer table, then you have a solution to your problem. In other words, using this approach I as a DBA desides where, how and when to store a LONG. But maybe you do have the ability in Sybase to control where the TEXT datatype gets stored?

>There are other areas where I think Oracle, or Sybase, fall short when
>compared to the other guy ... but I'm running out of time and need to move
>on .... <g> ...

hehe - I'm very grateful for your walkthrough. It surely tought me a few things about Sybase that I didn't know before. And your way of using your assignments to justify the usability of different features is recommendable. It just shows that one tool doesn't fit everyones need. (hey we are all different so why shouldn't our tools be?)

>Overall, I'd agree that PL/SQL has a lot more features/functionality than
>T/SQL. In my opinion T/SQL is more efficient than PL/SQL. So it's a toss
>up ... which is more important in your area ... features/functionality or
>efficiency/performance?

What do you have to substanciate that statement? You have not (in the above) talked about features that makes T/SQL more efficient (better performance) than PL/SQL? Other than your misunderstanding about the return of resultsets, I havn't seen any thing that suggests that Oracle's PL/SQL should perform faster (or slower) than the same code for Sybase's T/SQL.

>Oracle supplies 3 utilities:
> export Oracle specific formats for getting data out (resultant file
>can only be used by 'import')
> import Oracle specific formats for getting data in (can only use
>resultant files generated by 'export')
> sqlloader Utility for getting ascii/text data files into an Oracle
>database

And SQL*Plus, Server Manager, and a misc. assements of tools to setup network and other "external" parts of the databse, like cartridges and SQL*Net

>Funny ... Oracle doesn't provide any sort of sql-un-loader ... they seem to
>think that you'll never need to get data out of an Oracle database into
>ascii/text format ... hmmm.

Totally agree :)) One thing that Oracle somehow didn't get from DB2. The SQL*Loader is 100% compatible (or was in version 7.2) with DB2, but the Unloader from DB2 has never to my knowledge existed in the Oracle toolset. But then, to unload data I usually use SQL*Plus (so it is possible with standard tools if your output will be ASCII,EBCDIC based).

>So, with Sybase I get to learn/use one utility for all data un/load req's
>... with Oracle I have to deal with 3 utilities as well as design my own
>utility if I want to get data out of Oracle into ascii/text format!

no, you need to learn ONE tool to get data in/out of Oracle in an external format. The IMP/EXP utilities are for DBA's that needs to move data between Oracle databases or make logical backups.

>The one thing I do like about the export/import utilities is that they can
>pull out not just data but several of the components that make up a
>tablespace, too. Nice for getting index, stored procs, permissions, etc.
Sounds like Sybase really has something to implement in their tools? Without the above ability I would find my data stuck in Oracle. Granted, Oracle has had it BIG share of bugs in the IMP/EXP utilties expecially after the procedural option was introduced (PL/SQL).

>What monitors are you talking about with Oracle? If you're talking about
>the Server Manager ... this is just a GUI representation of some of those
>400 views Oracle gives you into their data dictionary. Does Oracle have a
>monitoring capability for it's internal counters/values?

Yep .. in the same tables and from Server Manager. If you say that Server Manager shows a SUBSET and not all values, that's correct. Some values aren't even documented and are for internal use in Oracle only. Lots of freeware tools exists to extract these values, and as you say the values are present in the dictionary. One problem does exist and that's the "counters" as you call them are un-resetable. Hence they count from the day the databsae was started and on and on and on. You need to use utilities (also provided by Oracle) to get staticstics for a specific period in time.

>That's funny ... I'm now 'playing' with Oracle and I've discovered that the
>big issue is NOT 'row level locking'. The whole issue here is
>*CONCURRENCY* of your data access. The granularity of the lock (row vs.
>page) has nothing to do with why Oracle is 'better' than Sybase ...

Two sides of the same matter. Concurrency is a requirements, and locks is ONE of the options you have to implement this.

>Sybase requires you to wait until an exclusive lock is released before you
>can access the data that's locked ... whether you want to read or modify
>it.

ARGHH! Sorry, but now I understand why customers would REQUIRE Sybase to implement rowlocking mechanisms. You are never prevented from access to a table, unless a DLL operation is in progress (like creating an Index).

>Oracle also makes you wait for an exclusive lock if you want to modify the
>same data, however, if you wish only to read the data that is currently
>locked by another user you are allowed to read an 'old' copy of the data.
>In other words, Oracle goes the extra step to keep multiple copies of a
>data row so that if someone is currently updating a row then someone else
>can read the 'old' row.

Exclusive locks are used VERY rarely. They are normally only needed for big batch operations. Most operations use shared locks - and even more often row-locks. Hence you are not normally prevented from updating a row unless someone else has just updated it and hasn't committed his transaction yet.

>Oracle's concept of multiple copies of data is nice as long as you realize
>that you have 'old' or 'bad' data. It's also nice as long as you realize
>that the system now has to set aside additional resources (memory, disk and
>cpu cycles) to maintain these multiple copies of data.

Now you are putting words into peoples mind that makes them misread the concept as you have. I wouldn't use the word OLD, but CONSISTENT. What would you prefer - to read more current but inconsistent data, than less current (we are usually talking seconds old) but consistent ?? I know what I choose ... the consistent way.

>Sybase has gone to row-level locking because most of the RDBMS user
>community doesn't have the brain cells to investigate the *REAL* issues
blablablabla.
What a lot of crab. It seems to me that your failure to understand the rowlocking (hence only refering to exclusive locks which doesn't have ANYTHING to do with rowlocking) shows that you are the one with the dead brain cells. In OLTP applications using nothing less than rowlocking seems to assure problems. If you are using a dataware house I would say that rowlocking is not necessary.

But tell me how Sybase deals with consistency? If a module needs to update 4 different tables in one transaction does that mean that while this transaction is going on NOONE can update these tables? What if the update made by user B (waiting for user A's transaction) needs to select data from the table before updating it (by adding 10 to a number), he has to wait for his SELECT too?

It seems to me that a lot of time must be used on waiting in Sybase.

>> + Oracle can select data from another database on another server
>> (dblink) Can Sybase?
>Yep, you can do this with Sybase. This feature is available with 11.5 and
>11.9. It was available in earlier releases via a separate Sybase product.

What is your oppinion that this feature is a build in feature of the database? Does it require special syntax to retrive access from external data in Sybase? It doesn't in Oracle.

>I'd like to see Oracle include their partition option as part of the basic
>database binaries ... not as a separate product.

Partion option? That's a feature of Oracle8 Enterprise? Not a seperate product?
Having any gains from partions means having millions and billions of records. A few organizations have this requirements, so I do understand why only the enterprise version (the $$ as you call it) includes the abilities.

>Something else to think about ... I believe someone else already posted
>something similar to this ... Oracle uses a lot of the underlying OS's
>capabilities to handle connections (e.g., each connection grabs it's own
>memory, process slot, etc.) while Sybase maintains all of it's connections
>internally (e.g., one big chunk of memory, limited process slots).

Huh? I think this was true in the good old 80'ies and 91/92. SQLNet 2x fixed that "problem". SQLNet 2.x has existed since 93/94. All connections are handled by two central "dispatchers" - that's all.

>I once saw Oracle boast that they beat the socks off Sybase ... but if you
>read the fine print you found that Oracle was testing it's brand-new 7.x
>software,

Brand new 7.x ?? Oracle hasn't demonstrated any brand new 7.x in almost 2 years since 8.x came out???

>Yeah, I agree that Oracle and Sybase ... and Informix and Microsoft and DB2
>and Terradata and ???? ... continue to leap frog each other, i.e., yeah, on
>like hardware Oracle does, at times, beat Sybase ... and vice versa.

That's something I agree too. That's competition - and that's how I hope it continues in the future (heck, that's why I like these talks when kept at a proper level).

>The whole TPC benchmark comparison 'issue' is a non-issue when you finally
>compare apples to apples ... regardless of whether you're an Oracle or
>Sybase user.

The issues that needs to be compared are the issues required. You wont ever use all of the utilities/features of Oracle in your application - likewise with Sybase. So find out which features you need, and compare THOSE. Look at issues like development time, maintanance, support and future "devotion" from the vendor. Those are important "issues" - not whatever Oracle processed 100+ users more pr. second than Sybase or visa versa, if your organization has at maximum 100 users in total. Received on Thu Nov 26 1998 - 00:00:00 CST