October 14, 2002 at 10:22 am
hbkdba,
It will be a reporting/OLAP style database with about 50 users, 20 concurrent on average. Daily feeds from the production equivalent.
October 14, 2002 at 11:15 am
I think the major point not addressed here is price. Oracle costs TWICE as much per processor as MS SQL Server. An $80k price difference per quad processor server is a pretty major factor for a lot of companies. Also factor in the cost of administration for an Oracle system compared to MS SQL.
Oracle is a stronger package then SQL Server, but not twice as strong. The following article from PCMag is a fair comparison - you might review it and present some of its points to your manager:
http://www.pcmag.com/article2/0,4149,7275,00.asp
Josh
October 15, 2002 at 12:39 am
acudlip,
some extra questions:
1) which reporting tool
2) expected growth of the OLAP DB
3) load method
4) source of the data
5) is hardware / O.S. choosen already ?
October 15, 2002 at 10:05 am
Interesting topics, however getting a little off topic.
acudlip,
Can you repost your question in a new thread?
Steve Jones
October 15, 2002 at 12:08 pm
It has been more than a year since I worked with Oracle 9i, so I hope anything I have to say about Oracle is not obsolete.
As a general remark: think of Oracle as a Rolls-Royce, and SQL Server as a Chevrolet. If technical excellence is the main criterion, I think Oracle is the obvious choice. However, there is another big issue: cost, which explains why you see more Cavaliers than Silver Shadows on the road. Not just licensing costs, either, but also administration costs. Oracle seems to need more DBAs to administer, and Oracle DBAs are generally paid better than SQL Server DBAs.
I advise anyone to migrate to Oracle from SQL Server only if the conversion is done by skilled Oracle people, and only if the gains in technology can be judged to outweigh the extra on-going costs. It only stands to reason that the larger the database, the more Oracle's advantages can be leveraged. However, having said that, I think often SQL Server gets a bum rap, ironically, due to one of its strengths: its ease of use. Oracle DBAs must have lot of skill, but a SQL Server DBA can often get by, 99.9% of the time, with less skill. When the crisis comes -- and it will -- two skilled Oracle DBAs are more likely to possess the experience level to deal with it than the lone SQL Server guy in a one-man DBA shop.
<con't, next post>
October 15, 2002 at 12:37 pm
I have time for a couple of point responses:
> "SQL Server is only operable on the Windows platform, and this is a major limitation for it to be an enterprise solution."
The first part of the statement is true, but the second part doesn't follow from the first. As long as your client boxes can access the server box on the network, the client shouldn't care what the DBMS runs on. In fact, most of the Oracle shops I've seen have placed Oracle on a Sun workstation in support applications on Windows client boxes; in other words, Oracle/Sun is the odd man out in such cases. It doesn't matter to most customers that Oracle can run on a 1980 vintage Data General minicomputer and SQL Server can't.
> "...SQL Server's locking scheme is much simpler (less mature) and will result in a lot of delays/waits in a heavy OLTP environment.
I think a point is scored for Oracle here. I've often wondered how much the differences between the DBMSs follow naturally from the simple manner in which the commits take place. With SQL Server, an INSERT/UPDATE/DELETE commits right away unless you tell it not to do so, while Oracle commits only when you tell it to (or when the session ends and you didn't tell it not to). Whatever the reason, Oracle can provide READ integrity without READ locks, by playing a little shell game with its ROLLBACK segments and REDO logs.
> "MISSING OBJECT TYPES IN SQL SERVER... No public or private synonyms"
True, but this goes into the B.F.D. pile. The bad news is SQL Server doesn't have synonyms, but the good news is that, due to its structure, doesn't really need them. Synonyms are more important in Oracle because there is one database per server/instance, whereas SQL Server allows many databases per server/instance. Schemas are usually delinateated by logins rather than by databases, and therefore synonyms acquire a level of importance.
> "c. no packages ie. collection of procedures and functions."
True, but now it's SQL Server's turn to win one: Oracle procedures do not return a results set. This seemingly minor little advantage of SQL Server's is extremely ergonomic, and means that, too often, programmers using Oracle spend more time imbedding query logic in their code.
> "a. Significant extensions to the ANSI SQL-92 standard in Sql Server..."
Pot calling the kettle black: Oracle too goes off the ANSI reservation.
> "Sql Server uses cryptic commands.... Oracle Sql and Pl/Sql... can do things more intuitively than Microsoft Transact-Sql..."
Let's talk about "cryptic" vs. "intuitive" for a minute. Oracle has no analog to bracketed pattern-matching. E.g., let's say you want to find rows where a column's first two bytes are alphabetic and next four bytes are numeric characters. Which query below looks more cryptic:
> SQL Server: SELECT * FROM account WHERE acctid LIKE '[A-Z][A-Z][0-9][0-9][0-9][0-9]'
> Oracle: SELECT * FROM account WHERE TRANSLATE (acctid, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', 'AAAAAAAAAAAAAAAAAAAAAAAAAA9999999999') = 'AA9999'
> "In Sql server, there is no ability to read/write from external files from a stored procedure. Oracle has this ability."
This just isn't true. Using xp_cmdshell, BCP, or BULK INSERT, you can pretty much have your way with external files in SQL Server.
But consider this: In Oracle PL/SQL, there is no way to execute an operating-system command from inside the SQL Engine. SQL Server has this ability.
Edited by - Lee Dise on 10/15/2002 12:38:31 PM
Edited by - Lee Dise on 10/15/2002 12:40:32 PM
October 20, 2002 at 7:49 pm
Do not limit your choices to just two Databases. With the new MS Licensing policy, Sybase can be a cheaper solution to SQL Server and has the robustness, maturity, configurability, performance and reliability of Oracle. Sybase is the de facto database of Wall Street businesses because these people does not trust their financial data to Microsoft's reliability and knows finance to well to be hoodwinked by Oracle.
October 21, 2002 at 6:25 am
Agree with everythign said. You can write an article from either point of view. It is interesting that the replication section is one paragraph, I understand that its not up to SQL 2000's replication.
SQL has the benefit that it is the same product from CE version upto enterprise version in terms of compatibility, you can't say the same for Oracle.
Final point is that the enterprise features in Oracle have benefited from time and experience where SQL has only just introduced them in SQL 2000. Partitioning for instance in SQL is a hack where as in Oracle it is a true feature that has its own commands.
I hope that Yukon will not just have better toys (tools) but better implement file, access, partitioning, data types.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
October 21, 2002 at 9:02 am
Sorry for the long post, but the original writer apparently removed this page from his web site...
It's an Oracle perspective from a Sybase DBA,
and I think that it fits as a good reply to the article above. Very funny.
Bottom line: it's all a matter of point of view.
Enjoy!
Oracle Facts
Buying Oracle is like buying a car "as is". It comes with 3 flat tires, a burnt-out headlight, 5% brakes left, and an engine that will blow a rod within 3 weeks. The engine looks like something out of the '70s. There is no room under the hood, and 90% of what you see are smog control devices. Upon closer inspection, half the sparks plugs and the carburetor are missing. A look at the driver's instruments is similarly interesting. Here you'll find not one, but many stick shifts each with 200 gears labelled in hexadecimal which somehow imply functionality. You will see a steering wheel the size of a barge's, as well as a built-in 8-track tape player. The CD player, an after-thought, is dangling from two wires under the glove box. The ignition switch is nowhere to be found.
"I had been a Sybase DBA for 8 years before I crossed paths with Oracle. It only took one hour for Oracle to sow seeds of despair. I hoped that I was just ignorant about Oracle and that the more I learned Oracle, the better I'd like it. Well, that turned out to be a false hope. It is now 3 years later and Oracle hasn't gotten any better.
"Here is a list of my grievances. I hope you find them interesting and useful. I have approached company Oracle gurus and Oracle tech support as I discover each problem. The response I get from the gurus is, "Well, that's true . . . but Oracle has market share." From Oracle tech support, I get, "No, that is not a problem. That is a feature." And so my conclusion is this: I don't think there is single company out there who has purchased Oracle based on Oracle's technical merit. For every company and every Oracle DBA whom I met, in the end confesses that their use of Oracle is solely based on "Oracle's market share.
"By the way, these problems are in all versions of Oracle. Regard the problems as genetic. The architectural flaws of Oracle just pass from release down to the next release. If anything, the more current the release, the more problems because mutations and inbreeding only produce more beastly manifestations."
1 Oracle has no provision to store numbers in the computer's own native format, namely IEEE. All float and integer numbers are stored as integers with an extra byte for sign and a specification for decimal place. As for float numbers, Oracle does not support exponents. The scientific community, therefore, must look elsewhere.
2 Computers carry out their arithmetic with IEEE formatted numbers only. Because Oracle does not store any of its numbers in IEEE, every single computation involving an Oracle number must be converted from its internal format to IEEE. When you store it back to Oracle, Oracle must convert it back to their own format. Better buy a faster CPU.
3 Oracle does not support bit operators. "update table set bitmask = bitmask | 66536" is a syntax error. The reason why Oracle can not do this most basic of functionality is because of the way Oracle incorrectly stores numbers in the first place. There is no meaning to "bit" in its own internal format. The '|' sign which means 'bit-wise or' in standard programming, means nothing in Oracle. If your application makes use of bit flags or masks, which most applications do or should, Oracle will stop you dead in your tracks.
I have written a set of Oracle PL/SQL bit operator substitution functions as a tourniquet to this Oracle pitfall. They are in BriansTools under the ./dbaccess/oracle_scripts directory. However, because they are functions, the Oracle optimizer will be far less efficient with them as Sybase would be to standard bit operations.
4 Oracle has at least three different languages: SQL, SQL*Plus and PL/SQL. The three languages deceitfully imply flexibility and functionality. But really, the presence of three languages exists because of a lack of forethought. Whereas Sybase has one language with a grammar which handles everything, not any one of Oracle's 3 SQL languages can speak the language of its database. Because of the inadequacy of any of the languages, Oracle then introduces interfaces between them. The clumsy interfaces are an addtional level of complexity which you have to learn. For example, getting results back to SQL*Plus from PL/SQL includes a wide assortment of BIND variables. This is unecessary in Sybase
Several Oracle people have e-mailed me on this point saying that "There is only one SQL language in Oracle. They are all the same." But I tell you the truth, the grammar rules change drastically between Server SQL, SQL*Plus and PL/SQL. The next several points illustrate this.
5 PL/SQL blocks within a SQL*Plus ignore SQL*Plus's 'set autocommit on'. Committing transactions within PL/SQL is independent from the SQL*Plus in which it is embedded. The commit syntax is also different.
6 You cannot write any DDL with PL/SQL. 'create table' is invalid with PL/SQL. To the Sybase user, that is like saying that you cannot create a table within a stored procedure. If you want to create tables in an Oracle stored procedure, you have to use dynamic SQL. The table defintion may be static, but you have to use dynamic SQL because PL/SQL cannot create tables to begin with. This is an up-front misuse of dynamic. Oracle dynamic SQL has a different and obtuse syntax. You will have to learn it. Also, any future DML statements in your PL/SQL stored procedure that manage that table will also have to be in Oracle dynamic syntax. After all, you cannot write insert statements into a table the compiler doesn't think exists.
7 You must have a special permission in Oracle to create a table in a PL/SQL dynamic statement. You must have CREATE ANY TABLE permission. You may already have the ability to "CREATE TABLE X", but if you use dynamic SQL to do the exact same thing, you don't have the permission.
8 There is no if statment in SQL*Plus. "if ..., then drop table ...." is impossible with Oracle. It is a standard in Oracle for a programmer to issue SQL*Plus DDL commands knowing that they will fail. You just let SQL*Plus generate an error. That is normal for Oracle. The problem is that "error" means error. One's attention is drawn to it. Each one requires examination. DBAs hate this stuff. Customers hate this stuff. Tech support hates this stuff. The typical tech support's response is "Yeah, that error is okay but that is normal for Oracle."
To attempt to handle the error, you can introduce the WHENEVER SQLERROR clause in SQL*Plus, but that is only a Band-Aid. That does not prevent the error to begin with. You can also spool out a SQL command generated from a prior select from USER_TABLES, and then execute this spooled file later. The latter method is the best choice to prevent errors. But the method is just another kludge. It introduces a timing problem for the transaction, and you will also find yourself executing empty commands . . . but that is normal for Oracle.
9 Even though you can do a "select col1, col2 . . . from table" in Server SQL, you can not do a standard select statement in PL/SQL. This tells you that Oracle does not support result sets.
10 You cannot print anything to your screen from within a PL/SQL block in real time. If you want to print out the current iteration of a cursor, just forget it. If you want to print the rows of a cursor as they are being selected, just forget it. Oracle is incapable of doing it. Oracle buffers all the output from a PL/SQL block for printing until after the PL/SQL block completes. Even at that, Oracle limits you to 1 MB of buffer after which SQL*Plus blows up. So, when you have a long running loop, there is no way to tell your user about the progress of your loop. To the user, it looks like your script doesn't work. But that is normal for Oracle.
11 In Oracle, you can only execute one SQL command at a time. Oracle has no concept of a batch. This is true in SQL*Plus as well as through ODBC. So, I assume this is an OCI limitation. In Sybase, you can send 100s of commands in a batch-- an operation which takes only a single network I/O. In Oracle, you must execute 100s of commands to accomplish the same thing. In Sybase, you load up 100 commands in your car and go to the shop and have them all serviced at one time. In Oracle, you must load you car 100 times, go to the shop 100 times, and have each command serviced one at a time.
12 Oracle does not support client-side timeouts. In Sybase, you can program your client to timeout if the server doesn't respond to a login request or to a query in a settable time. This mechanism allows your client to recover gracefully when the server or network has a problem. Oracle offers no such mechanism. In Oracle, you have to kill -9 your client. If you are a capable programmer and have an extra three man-months to spare, you can program threads into your own client which handle timeouts in lieu of Oracle's inability to do so for itself.
13 When creating a stored procedure using SQL*Plus, you may have errors in your procedure. The best error message Oracle can give you is "Procedure created with warnings or errors." One would think Oracle would be more specific. You actually have to go out of your way to request more information. You must additionally type SHOW ERRORS. Note that the error message implies that Oracle will create a procedure with errors in it.
14 Ever try to change your line width in SQL*Plus on Solaris? Do it and then select from the dictionary. Watch SQL*Plus core dump. SQL*Plus has been core dumping for years.
15 Oracle is inconsistent with the quoted identifier feature. Your company may literally waste man months of work over each instance of this problem. As you know, in Oracle you can use a reserved word for a table name if you put quotes around it. The problem is, is that Oracle will let you get by with a reserved word without quotes in one instance but blow up months later when you try to use it another. For example: create table CLUSTERS. This command works even though CLUSTERS is a reserved word. You can also select * from CLUSTERS. But try: . . .where CLUSTID not in (select CLUSTID from CLUSTERS) and watch Oracle blow up. While I can create a CLUSTERS table and select from it, I cannot refer to CLUSTERS in a subquery. The grammar rules of Oracle SQL change within Oracle SQL. A true linguistic nightmare.
16 The Oracle data dictionary stores all its object names in upper case. Oracle allows you to access the object in upper case, lower case and mixed case. That is fine. But that concept doesn't work for object names where you have used the quoted identifier. Given create table TEST, you can select * from test, but you cannot select * from "test". Oracle regards this as a feature. Any object-name within quotes will be copied exactly to the data dictionary; yet that is not so for normal unquoted objects. This is another major inconsistency. Because of the reserved word problem, it is tempting to just put quotes around everything. If you do that, however, then all the object names in your SQL scripts have to match exactly those in the data dictionary
Sybase is refreshing after this particular Oracle nightmare. Sybase is simply consistent. What you create is what you get.
17 A blank line within a SQL command in SQL*Plus is a syntax error. Actually, it is a feature. A blank line tells SQL*Plus to erase your last command. Any leftover SQL clause following your blank line therefore blows up. To the Oracle user this is normal; to the Sybase user this is stupid. Sybase ignores blank lines and reset is the command to erase your last command. The problem is this. You have to make sure all your SQL scripts do not have mid-command blank lines, becuase SQL*Plus will treat each blank line as an error. Some third party code generation programs are lax with Oracle's wonderful feature, and so those code generation programs will not work with Oracle. In Sybase this problem never happens because Sybase has an architecture.
18 Oracle is incapable of dropping a table with only outgoing referential integrity constraints. In other words, Oracle cannot drop the childmost table. This problem is just irritating. I wish that Oracle was logical. The work-around is easy. Just add "cascade constraints" to the drop statement. Nonetheless, Oracle should be able to drop the childmost table anyway since its RI constrainsts will never interfere with its disappearance. This is an 8i feature.
19 Oracle is incapable of truncating a table that has referential integrity constraints. It doesn't matter if the table is the childmost table. It doesn't matter even if the table is empty. You have to disable its RI constraints no matter if the table is empty or the table is the childmost table. The extra coding to disable and then reenable RI constraints before and after a truncation, is substantial and a total waste of time. The operation is logically unnecessary. Another 8i feature.
20 The system manager (Sybase equivalent of "sa") is not allowed to grant permissions on a user's objects to other users. In other words, the system manager does not have the permissions to manage the system. In Oracle, only the user himself can grant others permissions to his objects.
21 You cannot create a read-only view in your own schema which reads from tables in another user's schema. You may have select permissions on the other user's tables, but that doesn't matter to Oracle.
22 Avoid creating views based on large tables. Avoid creating views based on views. Oracle's optimizer is extremely lousy at finding the fastest way to the data. Whereas Sybase can return results of a third generation view based on a 20 million row table in 45 minutes, Oracle can never figure it out, even after days of processing, for a table that is 1/20th the size. This Oracle behavior will force you to create work tables, which is what you tried to avoid in the first place.
23 Oracle has screwed up the definition of the null set. NULL has a specific meaning in mathematics, yet Oracle gets it wrong. In math, the null set is the set containing nothing--the empty set. In Oracle, the null set also includes the set containing 0-length strings. Oracle's definition is an oxymoron. The ramifications are severe: 1) An application can no longer discern if a varchar2 column has been touched or not. (An empty string usually means that a user entered data even though the data has no length. A NULL means that the user never touched the column in the first place) and 2) You can longer use a varchar2 as one of the columns in a primary key if it is possible that the varchar2 value is "". The latter limitation will force the DB designer to use a heap table instead of the more desireable index-organized table. That will increase the size of the table significantly and thus will slow down access to it. All this slowness and excess bulk because Oracle has not grasped the fundamentals of math.
24 Oracle is single-threaded. Look at your process list. Every connection to Oracle has its own process. The listener is its own process. The writer has its own process. The monitor has its own process. Each connection has its own process. In Sybase, all connections and listeners are threads inside the dataserver.
25 Oracle has actually planned a core dump directory for itself. That should tell you something. Note that is it full all the time.
26 Oracle only supports one database per server. Sybase supports 32,767 databases per server.
27 Oracle uses the temporary tablespace to build the indexes of create index commands. If you are creating a large index, your temporary tablespace better be huge too. Hint: make sure your init.ora variable SORT_AREA_SIZE is about 20 MB and that you have turned off logging in the temporary tablespace. If you don't do these two things, Oracle may never complete your create index command.
28 Oracle will use rollback segments to create the indexes. The problem is, is that whether the index gets created or not, there is no need to log any of the rows of an index to the rollback segment or to the redo logs. Either you can create the index or not. So there is no reason to log individual rows of the index. Nonetheless, Oracle will consume gobs of time and resources to log them.
29 Oracle will use the redo logs to create an index.
30 Oracle will step over its own shared memory bounds set in the init.ora file when creating a large enough index. Once it steps over its bounds, no one can log in anymore because Oracle not only had overstepped its bounds, but also it had leaked all the shared memory.
31 To the person who knows only Oracle, core dump directories, index logging, single-threaded and one-database servers do not sound strange. To the Sybase user, this is Jurassic Park. Sybase does not log the rows of an index. Sybase does not use the temporary database to form permanent indexes. Sybase does not have a rollback segment to also log temporary transactions, as if you would want to do that in the first place. And so, the Sybase user is surprised when it takes Oracle 16 hours to build a unique index on a large table whereas to took 40 minutes to build the same index on the same table in Sybase. I became aware that all this was happening when I tried to build an index and saw Oracle run out of temporary tablespace, run out of rollback segment space (2.5 GB), while busily writing to the redo logs. There is a way to ease the pain of this logging. Shut off logging in the temporary tablespaces and in the rollback segments tablespaces.
32 Oracle will log every row in the index to its rollback segments. Oracle uses rollback segments for everything without exception, and even for objects in the temporary tablespace. An index will be logged twice--one for the rows being generated in the permanent tablespace and again for rows being temporary written to in the temporary tablespace.
33 SQL*Plus will not inform you if you have run out of rollback segment while creating an index. It will just hang forever. You have to suspect something is up. You have to continuously view the alert log. tail -f alert.log . . . is normal for Oracle.
34 Oracle has no ability to index columns in dictionary order. If you want to search on a column in dictionary order, you must have another column in the row with the same data but in upper case. This is a tremendous waste of space and I./O time as well as another feature which causes heart attacks in a Sybase user.
35 The network configuration assistant program (netca) on Solaris will erase your entire tnsnames.ora and listener.ora files when you tell it to "Cancel and discard your changes." To the Sybase user, that is like quitting sybsetup or asecfg and having it erase the entire interfaces file.
36 The create database command does not load all the necessary SQL scripts to make the database a database. In Sybase, the equivalent is issuing a create database command and finding that the created database does not have system tables.
37 In Oracle, a database is a server. Oracle only supports one database per server. For each database you want in Oracle, you must run the equivalent of asecfg; that is, dbassist. So, a Sybase DBA who is used to managing 5000 databases on a single server is going to have to run 5000 iterations of dbassist to create his 5000 databases.
Now dbassist is a trip in itself. Note that when dbassist generates a script to create the database, the script does not do any error checking. You can literally get thousands of errors and Oracle will say, "Database successfully installed." Remember that DDL errors are normal for Oracle. If you program Oracle, you do not even try to make your program error free because true DDL error handling is not part of Oracle's "architecture." Was the database installed correctly? Of course not.
38 Note that dbassist is incompatible with itself . After you ask dbassist to clean up after an unsuccessful generated script run, dbassist not only removes the database, but also removes the directory structure that your script initially requires. The script you just generated after spending an hour answering dbassist questions, no longer works. You have no choice but to answer all the questions again, even if you answer them the same way.
39 You cannot tell Oracle to use a specific rollback segment for an import. If you are importing a large table with a large index, you must first take all the small rollback segments offline so that Oracle must choose the large rollback segment when doing the import. Remember, Oracle even uses rollback segments to create an index. While you can bust up the import of the table data into smaller transactions with a COMMIT=Y, you cannot bust up the index creation into separate smaller transactions. (This index transaction limitation does not exist in Sybase since Sybase doesn't log the individual rows created during a create index.)
40 You cannot create an index organized table from a heap table or vice-versa. In Sybase lingo, you cannot create or drop a clustered index on an existing table. This has awful and severe consequences. When you need to import a lot of data into your clustered index table, it is always much faster (up to 50x faster) to import the data without the clustered index being present, even if it means the table being recopied. In Oracle, you are stuck. You cannot change an index organized table to a heap table for loading. Therefore, the load will take forever. The work-around is to create a non-clustered index instead. That will effectively double your table's lookup time. In addition, if your index contains most of the columns in your table, then you have doubled the size of your table as well.
41 Here's a major Oracle kludge: The LDAP server. The LDAP server is nothing more than a super thick carpet in order to cover up the severe problem that all users in an Oracle database cannot readily share the tables of another user. There is no concept or equivalent of 'dbo' in Oracle. In Sybase, every user in a Sybase database sees the database's dbo's schema. The dbo's schema is common to all users in a table, and is an addition to the user's own schema. As a poor attempt to provide dbo-like functionality, Oracle introduces the Band-Aid concept of synonyms. If you get tired of creating 10,000 synonyms for the 10,000 objects in your database, Oracle introduces an additional Band-Aid called the LDAP server. The LDAP server is a nightmare in itself.
42 Oracle allows you to create a user name with a "." (dot) in it. Dots are the ANSI standard SQL character to separate the parts of a fully qualified object name. For example, "user.mytable". So, Oracle's username's embedded dots conflict with the ANSI standard. If SQL*Plus runs into any dotted user names, it core dumps. FYI, Sybase does not let you "sp_addlogin" a username with a dot in it.
43 Which brings up another limitation of Oracle. In Sybase, a fully qualifed object name is: server.database.owner.object. In Oracle, a fully qualified object name is: owner.object. The conclusion is as bad as it is obvious. Oracle has no concept objects belonging to different databases or servers in their basic architecture. The Oracle fan will say "But you can query tables from remote servers in Oracle!" And they are right. You do it with synonyms. One synonym for every object. If you want to access 2000 tables in a remote server, you have to create and maintain 2000 synonyms. Oracle introduced the concept of synonyms to get around their architecture.
44 I mentioned this one in passing before: You cannot do "select col1, col2 . . . coln from mytable" in a stored procedure. In other words, Oracle stored procedures cannot return a result set. The Sybase user is instantly horrified and rightly so. Returning result sets from procs is so useful, common and easy that it is taken for granted in Sybase. But in Oracle, the way to simulate a row result set is fodder for PhD dissertations. The problem is formidable. Hordes of white papers have been sacrificed on this unholy alter. For giggles, go to google.com. Search on ODBC, Oracle and Stored Procedures. Look at all the material on the subject
The solution is this: You pass a reference cursor as a parameter to the procedure and then use a client-side fetch on the cursor. Your stored procedure also must be bundled in an Oracle package so that you can type define your cursor. The ODBC developer realizes, "Hey! There is no ODBC parameter type SQL_REFERENCE_CURSOR. I can't pass a parameter that's a reference cursor. " The response is, "Your ODBC driver vendor must provide the reference cursor mechanism inside the driver itself . . . and hide it from the ODBC layer." The company Merant does such a thing. Hats off to them. Their ODBC drivers provide this mechanism under the covers. You still have to go through the considerable pain of setting up these special stored procedures,. But Merant has given one the avenue to simulate result sets with Oracle. In BriansTools, I have included a couple of PL/SQL script examples of how to simulate row result sets in an Oracle proc given that the user is using Merant ODBC drivers.
You don't even think about result sets in Sybase. Result sets are a natural feature of the territory. Just like a sunrise, one takes it for granted in Sybase. However, if you use Oracle, the sun does not rise. You have to invent the sun and a rotating earth.
Program managers beware. Your database developers and DBAs will spend three times as long accomplishing a task in Oracle than they will in Sybase. That is normal for Oracle. Your developers must invent suns and rotate earths on a daily basis in Oracle. You will not find Oracle lacking functionality, but you will find its functionality convoluted, illogical, counter-intuitive, missing the mark and buried. Oracle's functionality comes this way because Oracle's architectural foundation is not adequate.
45 Oracle is in conflict over the meaning of ';' -- the semicolon. In SQL*Plus, the semicolon executes your command but in PL/SQL means the end of a SQL statement without executing it. To execute a PL/SQL inside SQL*Plus, you type '/' instead. This is a fundamental overlap conflict between execution commands and language syntax. It is a basic architectural flaw which one faces every day and hour when using Oracle. It drives everyone crazy, even Oracle afficionados. The Sybase user, who has grown accustomed to using good software, vomits at this behavior of Oracle. The Oracle user, who has grown accustomed to flaws and quirks, treats this as normal. In Oracle, one gets used to such bad things and gets desensitized after a while.
46 The import utility captures ctrl-C. Say you have 3000 tables in your dump. You get 10 tables into it and for whatever reason, you want to quit. You must type ctrl-C 2990 more times before import will exit. Consider kill -9.
If you have any comments, corrections or additions, please send an e-mail to Tech Support. My purpose is not to defame Oracle, but rather to point out the truth while venting my frustrations. Since I am not a DBA zealot, you can correct me if I am wrong. I promise I will not get upset. I will only be pleased to find out that I am wrong, because I too have to wrestle the Oracle devil every day.
October 21, 2002 at 11:52 am
I think most of the technical aspects have been covered in far greater detail than I am capable (or willing) to go into. However, I'd like to throw in a few general observations.
1. The funniest part of the post was "I'm not trying to start a religious war here but... " That's like saying "I'm not trying to start a fire but let's just soak these rags in gasoline and motor oil and sit them in a cardboard box between the furnace and that old space heater that periodically throws sparks."
2. Everyone is biased when it comes to choosing OS and DB. Unix people naturally like Oracle for it's low level configurabilty, pathetic GUIs and general anal retentiveness. "But look! You can configure this down to the electron spin direction!" On the other hand, Windows people tend to prefer the hands off "Works good enough for me" and "Don't fix it if it isn't broken" mentality. Yes, we're lazy. (Naturally I'm not including Steve Jones or anyone else who posted more than a thousand words on this topic in the lazy comment!)
For most mid-range DB applications I've run across (15-30GB.) SQL Server is good enough. I don't care that if we used oracle, hired an Oracle DBA or two at $80,000+/year to wring out an extra couple of seconds of response time. It's just not worth it to me. The end users of my database systems will blame me for problems regardless of whether it's caused by the database, application software, network, or kludged up workstation that they've filled with buggy screensavers, desktop themes, goofy cursors and games that they had their kids come in and load for them. After all I still have the woman down in accounting who blames the DB structure changes that I made for screwing up her email...
3. I think the single biggest deciding factor for which DB is chosen is based almost solely on which side of the fence the most senior decision maker in an organization is on.
The next most important factors are budget, available hardware and of course the requirements of the vendor.
In my organization there are a lot of simple things that could be done to increase performance, consistancy and backups that are not feasible due to outside factors. For instance, SQL Server recommends that you put the transaction log on a mirrored drive set and the data files on multiple raid five arrays. I presented this idea to our Network and Infrastructure folks (SA's) and they laughed and said "no, the entire drive array will be one raid 5 but we'd be happy to give you as many logical partitions as you want."
As a final parting shot I'd like to say that this whole thread sounds like a plot by Oracle to make some of our most capable SQL Server DBAs look bad. Apparantly they've put their entire workloads on hold to research and author long detailed accounts defending the honor of SQL Server.
"I met Larry Niven at ConClave 27...AND I fixed his computer. How cool is that?"
(Memoirs of a geek)
October 22, 2002 at 6:36 am
Regarding #11: They explain SQL Server's success in benchmarks as "only because it supports Federated Databases". That is like saying "The only reason you won the race is because you are faster".
November 18, 2002 at 12:57 pm
As a long time user of Oracle and a new user of Sql Server - my opinion depends on my last task. I have days that I marvel at SqlServer's ease of use - then I curse it's confusing admin language. I find most of the points of comparison irrelevant in relation to what I do day to day as a DBA. As long as I can get the job done...
Oracle's tech support isn't high on my list - I haven't experienced tech support for SqlServer.
November 20, 2002 at 2:56 am
As a cynic I would say that tech support for SQL Server is
in order of preference.
I showed an ORACLE developer a method for selected blocks of ten records, 1 to 10 of 100, 11 to 20 of 100 etc. His first question was "What does the TOP clause do?".
I have found MS products have one major advantage of their competitors and fortunately for MS that advantage is one that is an absolute killer.
EASE OF USE.
Bare in mind I started using Microsoft products when there was MSDOS 2.1 and Word 1.3.
You can get a hell of a long way in a short time with almost any Microsoft product. In fact, it is only when you REALLY push a Microsoft product that it falls down. I find that I can achieving 80% of a projects goals is relatively straight forward with an MS product, and users tend to be happy with 80%. Hence the success of Visual Basic.
It's a bit off topic, but I started looking at Lotus Smartsuite vs MS Office from a developers perspective. i.e. using LotusScript as opposed to VBA.
You can see that the non-Microsoft product tries to aim for the enterprise level stuff, however LotusScript, although similar to VBA, is not as easy to use.
I believe that the same sort of thing can be observed in the ORACLE / SQL Server debate.
I would be interested in a SQL Server Vs Sybase comparison, especially given that they were originally the same core product.
I would also be interested in a comparison with other alledgedly enterprise level databases.
November 21, 2002 at 5:52 am
acudlip,
My last company went through the following:
SQL 6.5 dual processor > SQL 7 quad processor with replicated machine for reporting >
SQL 7 quad processor replicated machine across 3 different cities via DS3 (45mbps) > SQL 7 on a UNISYS 16 processor 32 GB RAM terabyte environment.
We had MS on-site to help with the transition to the last and were able to send our data to them to have us see on-site at MS performance.
I am sure that if you contact Microsoft and Oracle they may both be able to arrange for some sort of performance analysis for your company to review to help make the decision.
Just my thoughts.
Thanks,
AJ
AJ Ahrens
SQL DBA
Custom Billing AT&T Labs
Good Hunting!
AJ Ahrens
webmaster@kritter.net
November 21, 2002 at 5:49 pm
In my humble opinion, the only way to truly compare Oracle and MSSQL server is NOT on paper nor simply on technical merits, but to actually work on production applications on both platforms and then compare real life day-to-day experiences. A man with an experience is never at the mercy of a man with simply an argument. The bottom line in all of this, is how much $$ did it cost your company to deploy a DB app? If the app works as designed, scales as designed and meets all the requirements for growth and stability, why does it matter what technology it is? Much of the rhetoric in the DB wars is based solely on perception and NOT experience.
We have both SQK2K and Oracle 8i and 9i apps in production at my shop, one of which we had to convert from SQL2K to Oracle because of a customer's preference. Both SQL2K and Oracle are stinking fast - that's a given, they run just fine. All DBA's here have 10+ years experience, some just Oracle, some SQL2K, some both (me). Personally, I like both DB's and feel very fortunate that I get to work on both. Regardless of all the technical advantages of either DB, our real life experiences, mistakes and all, proved, hands down, that SQL2K required significantly less headaches and Business $$ to build, maintain, and deploy. Senior level DBA's can make a database work, scale and perform, regardless of the DB vendor - that is a given and is fact. All of our DB applications work fine, they all run very fast and perform and scale as designed. But the bottom line is that my company paid significantly more $$ to build, maintain and deploy our Oracle apps. We logged more MetaLink bugs than I care to count (8.1.7.1, 2, 3 and 9i as well). We logged none for SQL2K. What ran at 50% CPU on SQL2K on two 4-way boxes, ran at 70% on an 8-way with half the throughput on Oracle 8.1.7.1. The Oracle app will certainly scale as fast as we need it, but it takes much more hardware to achieve this than SQL2K. Our tuning methodology was real simple. All stored procedures MUST run in under 100 milliseconds - period. Once that's bench marked, then we size and scale how much machine we'll need. I could care less if SQL2K is better or worse than Oracle - it DOES NOT matter to our CEO. What matters is whether we're paying $100K to deploy an Oracle app or $30K to deploy a SQL2K app that meets all the requirements. I'm sure like everyone else, we did things wrong in both Oracle and SQL2K, but then again, that's real life - right? My 2-cents.
Bob Moffat
Bob Moffat
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply