Re: Sybase vs Oracle - which is better?

Karl Zdero wrote:

> The usual techie debate continues,
> Is Sybase better than Oracle or is Oracle better than Sybase?
>
> When it comes to benchmarks, the www.sybase.com site says that Sybase
> holds the world record, but www.oracle.com says it holds the record. I
> see in www.tpc.org that the fastest tpc is with Oracle!
>
> Now apart from benchmarks, what makes either database better?
>
> I like Oracle as I've used it the most, heres why.
> + sqlplus is superior than isql, at least I can check return error
> codes.

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.

>
> + PL/SQL is more powerful than TRANSACT/SQL

I do like the concepts of Oracle packages (to include user defined global variables), arrays (PL/SQL tables), exception/error handling method, user-defined functions, 'before' triggers, access to data by ROWID, 'create or replace' of sp's/views/triggers/packages, etc.

Packages and functions provide *power* in terms of functionality but I'm not sure about performance. 'Before' Triggers are definitely a performance saver if you find that your triggers are rolling back, or modifying, the base SQL statement. User-defined functions are *powerful* in terms of functionality but watch out for performance issues if not designed properly and/or used inefficiently. The exception/error handling in Oracle is nice for it's simplicity in terms of defining your exception/error handling in one place. Accessing your data via ROWID is great for speedier access (just make sure you double check that the ROWID is still 'good').

The concept of 'create or replace' when defining stored procs, triggers, views and packages is really nice. This means you don't lose your permissions when you re-define an object. Sybase should definitely use this feature (Sybase currently requires you to 'drop' an object before you can 're/create' it ... thus losing your permissions).

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.

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!

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. I have on many, many, many occasions used Sybase's detail i/o statistics to tune queries ... much nicer than Oracle's capabilities.

As for the way Sybase handles 'select' in stored procs ... if I require that my developers use stored procs to retrieve result sets from the database ... no problem ... just have the stored proc(s) generate the desired 'select' statements with the knowledge that the results are passed through/back to the client. 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 ... you have to send results back via 'out' variables or run a cursor to 'print' (via DBMS_OUTPUT?) your data back to the client one row at a time. Sybase's method is more efficient in terms of getting 'select' results back to the client asap.

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. With Sybase the TEXT datatype (similar to Oracle's LONG) is stored separately and thus not accessed in situations where you don't need to access the TEXT data (e.g., index rebuilds, scans of the non-TEXT data). This can be a big time saver when it comes to handling a huge table that's mostly LONG/TEXT data. (Yeah, you can split your LONG data out into another table .... but why doesn't the Oracle database handle this 'behind the scenes' for you?)

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> ...

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?

>
> + I can see qll SQL activity for a particular session (SQLAREA),
> can't do it in Sybase?

If you mean you can see the actual 'SQL' code ... you can (supposedly) do this in the latest releases of Sybase. (Yeah, Sybase should have provided this capability years ago!).

So, how did I track down 'bad' code in the past with Sybase? If you have a SQL sniffer (e.g., Cyrano) you have no problems. If I didn't have a SQL sniffer I was able to look at sysprocesses and syslocks to get an idea of what tables a particular query was going against, tied with the knowledge of what the user was doing (screen and functionality) I could track down the source code.

Yeah, being able to see the actual 'SQL' code would be a time saver!

>
> + Oracle comes standard with many utilities, whereas Sybase doesn't

Such as? What specific utilities are you looking for?

Sybase has one utility for getting data in and out of the database, namely, 'bcp'. This can handle Sybase specific formats or ascii/text formats ... in and out of the database. bcp allows me to target specific table partitions as well as specific rows in a table (or file).

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

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.

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!

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.

>
> + Oracle has more options with triggers than Sybase. e.g. per row
> triggers and per statement

Yep, I agree that this is good.

>
> (In Sybase you must write a cursor loop to action a multiple row
> insert for example)
> + Monitors are standard with Oracle. (Sybase dont think so)

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?

Sybase provides a stored proc called sp_sysmon which gives you tons of info. Combine this with the Monitor/Historical server tool (which I believe is standard with 11.5/11.9) and you can track down a ton of stuff based on internal counters/values that are not readily available via system tables.

>
> + Row level locking been for years in Oracle, only recently in
> Sybase.

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 ...

Row vs. Page locking granularity ... after 7 years with Sybase I have yet to run into an application where page locking was an issue. If there have been locking issues it has arisen from poor database and/or application/SQL design. The granularity of the Sybase locks (at the page level) have never been a problem. (My last Sybase environment was running 1500 concurrent users against a 140GB OLTP database ... and the only lock problems were poor application design!)

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.

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.

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.

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 behind 'row-level' locking, i.e., they don't understand that lock granularity doesn't have anything to do with it, i.e., they don't understand that Oracle's concurrency model is a resource hog (and thus a performance degrader), i.e., they don't understand that if an application *HAS* to have row level locking then there's probably an issue of poor database and/or application design. Sybase has gone to row-level locking because the (less than observant) RDBMS user community demands that they need this (regardless of the fact that they don't know what they're talking about).

>
> + 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.

I'd like to see Oracle include their partition option as part of the basic database binaries ... not as a separate product. Sybase provides their partition capability as a built-in part of their binary ... you don't have to pay Sybase more money for the partition functionality ... you have to pay Oracle more $$ for the same functionality.

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). This is one of the reasons why Oracle has a hard time handling more than a few hundred connections into it's database without purchasing additional hardware and/or software (e.g., Oracle Parallel Server, Oracle Multi-threaded server). As I've mentioned before ... I just came from a client where the plain vanilla Sybase dataserver was able to handle 1500 concurrent users (in fact, we did a benchmark ... actually, John McVicker did the benchmark ... where we got over 3,000 concurrent users into the dataserver using the same hardware ... an HP T-600 with 10 processors and 1.5GB of RAM).

Why do I bring this up? I'd suggest you go back to the TPC benchmarks and take a look at the fine print (if you can find it). Make sure you are comparing like versions of Oracle and Sybase engines ... make sure you are comparing like hardware and OS's ... make sure you look at the total costs involved in terms of hardware, software, and maintenance of the test environments.

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, with the additional Oracle Parallel Server product (mucho more dinero), running on a brand new Alpha 64-bit server ... against a Sybase 10.x server running on a Sparc 2000. Forget for the minute that Oracle failed to run their test against Sybase's 11.x server (10.x was an old product) ... forget that Oracle failed to run their test on like hardware (the Alpha was the latest/greatest with 64-bit technology while the Sparc 2000 was a couple years old and running 32-bit software) ... forget that Oracle decided to run their MPP software against Sybase's SMP software ... forget that the Oracle environment cost several times more than the Sybase environment ... forget that Oracle was comparing a new Oracle TPC benchmark against an old Sybase TPC benchmark ... in the end the Oracle TPC benchmarks were about 10% faster than the Sybase TPC benchmarks (not a whole hell of a lot when you start remembering all the differences in the comparison!?!?!).

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.

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.

--
Mark A. Parsons
Iron Horse, Inc.