Technical Facts About Oracle

Technical Facts About Oracle
Published at Talus Software
http://www.talussoftware.com/
September 18, 2003


1
The Oracle database does not store numbers in IEEE format. IEEE is the lingua franca of CPUs. Therefore, even before you start laying out your tables' definitions, Oracle will make your performance suffer. Oracle must translate needless billions of numbers between it and your apps . . . and there is no work-around for this. The speed that you should have is not possible with Oracle.
2
Oracle page chains when a column's size increases. That means that a single row splits into uncontiguous fragments each time a column increases its size. Change a null to a string and the row has a new page chain. Change a column from "hello" to "hello world" and the row page chains again. Null, varchar and all number columns are subject to page chaining. Yes . . . even number columns--because Oracle stores all numbers as variable length columns (verifiable with vsize()). The larger the number, the more bytes required. Update a column with a smaller number--you waste space. Update a column with a larger number--you now have a page chain. Page chaining is a tremendous performance hit. If you have a 40,000 row table and one of its columns is null, then you update the columns to "hello world", your table now has 40,000 page chains. Oracle DBAs suggest that you make such columns char. You may add terabytes of blanks to your table, but you won't page chain.

As opposed to Oracle, Sybase never page chains (except for image or text data types). A single row is always contiguous. And all numbers are fixed length. Therefore, Sybase does not have to search and put together all the pieces of a single row. They are already together.
3
Oracle 8i does not support bit operators. Oracle 9i introduces the BITAND operator, but only the BITAND operator. Since Oracle does not store IEEE integer formats, every bit operation consumes tens of thousands of clock cycles instead of a single cycle. Yet another performance hit. If your application performs frequent bit comparisons and needs the results fast, it is imperative that you go with Sybase or Microsoft SQL Server. Sybase and Microsoft store standard IEEE datatypes. and hence use the standard bit operators like "update table set bitmask = bitmask 66536" Under the covers, Sybase and Microsoft are doing a simple single CPU instruction.I have written a set of PL/SQL bit functions as a tourniquet for Oracle. They are in DBPowerSuite under the ./dbaccess/scripts/oracle directory. However, because they are functions, the Oracle optimizer will be inefficient with them . . . but you have no choice with Oracle.
4
Oracle has at least three different languages: Server SQL, SQL*Plus and PL/SQL. The three languages imply functionality and "the most controllable database in the world." But that is a Oracle propaganda. The three languages stem only from the fact that the first language Oracle had invented failed to do what it must; therefore, Oracle developed a second. Since the second language was no good either, Oracle invented a third. Since Oracle could never get it right, they settled for a fusion of the three using bubblegum as the conduit. The bubblegum consists of "BIND", Oracle's weird "version" of dynamic SQL., and the DBMS_OUTPUT package. But even the conduit has major flawsSeveral Oracle fans e-mailed me saying "There is only one SQL language in Oracle. They are all the same." Another person, on the other hand, admitted the disparity and defended it. He said, "SQL*Plus and PL/SQL are a wonderful example of the dichotomy between I/O and the procedural . . . unlike the Sybase potpourri." To him I say, "The mouth and brain is a wonderful example of the dichotomy between I/O and the procedural. But unlike Oracle, the mouth and brain have seamless connections, do not require the user to build nerves and synapses, and do not require an elbow to make them work. "

The next several points illustrate this.

5
PL/SQL blocks within a SQL*Plus script ignore SQL*Plus's 'set autocommit on'. Committing transactions within PL/SQL is independent from the SQL*Plus in which the PL?SQL 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 definition 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's 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 which Oracle doesn't think exists.
7
You must have 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 statement in SQL*Plus. "if ..., then ...." 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 cannot do a standard select statement in PL/SQL. This tells you that Oracle does not support result sets. Oracle users will rebuke me, "Oracle does support result sets!" But when you read the Oracle manual, Oracle's definition of a result set is not the same. Oracle purposely introduced the term result set only so that Oracle fans can say, "We support result sets." It was a marketing ploy only. A result set implies nothing in Oracle. Oracle has no concept of parameter result sets, row result sets, cursor result sets, compute result sets, etc., nor the concept of result sets arriving in a stream to the client.
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. Oracle programmers will spend a day of programming just to simulate a simple Sybase print command.
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 a basic Oracle design 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. This is a throwback from the 7-bit ASCII days of the 1960s. 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 dictionarySybase 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, because 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 architecture.You can change this SQL*Plus default behavior by set sqlblanklines on.
18
The problem of not being able to drop the childmost table without the "cascade constraints" clause has been fixed in 9.2.
19
Oracle is incapable of truncating an parent table. A parent table is a table has incoming referential integrity constraints. It doesn't matter if the table is already empty. It doesn't matter if all its child tables are empty. You have to disable its RI constraints. The extra coding to disable and then reenable RI constraints before and after truncation, is substantial and a waste of time. The operation is logically unnecessary.
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. Oracle calls this a feature.
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. 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 desirable 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 adopted a math convention akin to 0 + 1 = 0.
24
Oracle is single-threaded. Look at your process list (Solaris). 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 are the trappings of home. To the Sybase user, they are museum relics from 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 does not inform you if you have run out of rollback segment while creating an index. It just hangs 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
Point 34 used to slam Oracle's inability to create indexes based on dictionary order. However, in the light of Oracle's function-based indexes, this is not a concern. For review, Sybase users can set up their servers to process search clauses, order by, and case senstivity by setting up server-wide collating sequences. While Oracle doesn't handle that type of in-built automation, Oracle does support function-based indexes. Though function-based indexes require more syntax and are probably never needed aside from collating sequences, you could say that Oracle beats Sybase in flexibility for this one aspect.
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 qualified 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 subjectThe 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 DBPowerSuite, 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 aficionados. 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.
47
Oracle's UPDATE and DELETE statements have no FROM clause. Therefore, you cannot join tables to restrict the set of rows you are updating. Instead of being consistent with the SELECT statement like Sybase, Oracle forces you to be inconsistent. It's work-around time. You now have to figure out a way to simulate a join. You have to think subqueries. Oracle forces you to write a non-intuitive subquery for each column you are setting. If you are setting 3 columns, you have to write 3 subqueries--even if your subqueries are all the same. That's 3 times the processing. The resulting syntax is so convoluted, you will have to refer to examples each time you try to do it. This single Oracle problem will cost your company thousands of dollars both in writing the original statements, and then maintaining them.
48
Oracle lets you create a temporary table, but then forbids you to use a storage clause. There is no reason for this. It is an annoyance which requires expensive work-arounds. For an app I wrote, I needed to create and populate a large temporary table. I declared a big minextentsso that the app would not abort hours later in case of insufficient space. I made the table temporary so that the table would automatically go away if the database connection abnormally went away. But Oracle forbids me to declare minextents for a temporary table. In order to get around this problem, I had to create permanent tables, and write a stored procedure that drops such permanent tables on demand. Another employee of the company had to write a GUI interface to stored procedure so that the customer could maintain his own database.