10 January 2007
Oracle versions 9i-10gR2
I was an XML evangelist in one of my past incarnations, at an insurance company, and was in the ACORD XML Initiative mailing list in those days. XML is an admirable extension of the old idea of hierarchical data files to a new use: making data files self-defining and interchangeable. No more struggling with proprietary data formats!
Why then is XML such a problem in some projects? I have worked on two large projects where the influence of XML was nearly disastrous. The difficulties arise from misuse, and misuse of XML seems to arise from misapprehension of well-known principles of software engineering.
In both of the problem projects that I mentioned, the common data access method in the Java logic comprised the following steps.
In some cases the statements were prepared before execution, in others simply handed over to the database as raw strings. Sometimes the parsing and concatenation step was elaborate, other times simple. In any case, this data access method suffered from two weaknesses:
The first weakness applies to the Java logic as such. This document covers the second drawback in rather more detail.
2. Reading the XML File
The XML files in which SQL statements are stored are typically small (a thousand lines or less), so only a trivial performance hit is implied by reading and parsing one of these files. Nevertheless, it's an entirely unnecessary performance hit. It's also one that makes any experienced database guy flinch, since it's so well-known that hierarchical data files, of which XML is an example, can only be designed to support high performance of some one particular query. The file structure itself is inflexible and parsing it is an overhead cost, so that at the very beginning of the endeavor we already encounter an unnecessary problem.
3. Distributed Change Management, Yet Again
One of the great talking points of Web evangelists has always been that with a Web interface, the revisions to the source code distribute themselves. Anybody with a browser gets the latest version. That's great, and you can't have it.
That is, you can't have it if all your SQL is embodied in text files that have to be distributed to the application servers, just like the old days. On one of my past projects, the scripts that distributed the revised XML files broke down every couple of weeks. So the app servers often ran inconsistent sets of SQL, and the debugging was a nightmare.
Again, this is an entirely unnecessary problem. It has already been laid to rest by the use of a Web interface. It does not need to be brought back from the grave.
4. Parsing and Concatenating Strings: Oh the Tedium
It is hard to see why anyone wants to take the route of fetching bits of SQL out of an XML file and sticking them together. Instead of connecting to the database, passing in a set of arguments, and retrieving a pointer to a result set, the poor Java programmer has condemned himself or herself to weary hours of file parsing, string manipulation, and debugging. Of course string manipulation is less painful in Java than in (say) C, but it still isn’t any fun when you could be hitting the slopes. Many Java classes that I have read contain fifty or a hundred lines of string manipulation, when a few lines could invoke a SQL statement stored in the database. As a canonical sample, here is how Sun’s Java documentation demonstrates invoking a stored statement.
To any experienced programmer, those five lines must seem immensely preferable to fifty or a hundred lines of string manipulation.
5. Parsing and Concatenating Strings: Vendor Dependency
What about the SQL code that gets called? One of the strangest assertions made about pulling SQL out of XML files is that it decreases dependency on the RDBMS vendor. In fact it increases that dependency.
The reason lies in the history of SQL. When the small company that became Oracle pioneered a commercial database with a SQL front end, there was no competition and there were no industry standards at all. As other companies observed the runaway success of this first offering (notoriously buggy as it then was) and hurried to produce their competing products, each one came up with slightly differing standards of SQL syntax. The responsible ANSI and ISO committees lagged years behind, so that a feature as obvious as the outer join was only described in the SQL-92 standard. Consequently each vendor developed, and to a large extent still has, a distinct dialect of SQL.
This means that a Java or other front-end application that parses and concatenates strings to assemble SQL statements has to be partly aware of syntax rules that are unique to each vendor. For an obvious example, the two statements below are equivalent, one for Oracle and one for SQL Server:
A front-end application that needs to decide between function names and between adding and ignoring a ‘from’ clause just to get the system date is in some degree of trouble.
The way to minimize vendor dependency is to use a well-defined API, so that a single procedure call with a known set of arguments always returns the same result set, no matter whether the procedure contains T-SQL and is executing under SQL Server or PL/SQL under Oracle or whatever. The result is that you see something like this in the front end source code:
Again this must seem preferable to any experienced programmer. The obvious benefit is that the Java application does not even need to know what happens on the RDBMS side. It just calls a procedure and gets the result. Naturally this idea implies maintaining a set of callable procedures for each RDBMS vendor, but it’s the same set, with the same API, and there is no rework to the front end and no rework to the tier (front or middle) where business logic is stored.
By the way, I should add at this point that ORMs, such as Hibernate, exhibit the same idea. Using an ORM, you map a Java object to a SQL query behind the scenes, so the SQL is never invoked directly in the middle tier.
6. Drawbacks of Executing Dynamic SQL
Since I’m a database guy, let’s turn to the area where I actually know what I’m talking about: the database, particularly the Oracle RDBMS and the way it processes SQL. Dynamic SQL has the following limitations:
For these reasons, Oracle Corporation does not advocate the use of dynamic SQL except when necessary. The following warning, often in the same words, has appeared in the documentation for each release of Oracle since at least 8i.As a general rule you should use dynamic SQL only if you cannot use static SQL to accomplish your goals, or if using static SQL is too cumbersome.
For 10g, this sentence is at the top of page 8-3 in the Application Developer’s Guide—Fundamentals.
The following sections describe the limitations of dynamic SQL in more detail.
7. Dynamic SQL Must Be Parsed
When a SQL statement is submitted to the parsing engine as a string, Oracle parses it in at least three steps, called the ‘soft parse,’ and possibly in a total of six steps, called the ‘hard parse.’ The first parsing step is a syntax check, whereby the parsing engine verifies that ‘select’ is before ‘from’ and so forth. The second step is a semantics check, whereby the parsing engine verifies that the columns mentioned belong to the tables mentioned, that the submitting user has access to the tables, and so forth. This involves considerable execution of SQL against the data dictionary.
The third step is the ‘outline check.’ The parser now knows that the SQL is valid, so it is possible that this statement was executed previously. The parser tests for this by replacing bind variable names in the SQL text with placeholders, minimally reducing white space, and then hashing the resulting string. The hash value is then compared to the hash values in the in-memory data structures that describe recently processed SQL statements. If a matching hash value is found, then the parsing engine concludes that the SQL statement has already been executed and the hard parse need not be completed. The statement can use the same execution plan as before, with whatever different parameters are required.
With dynamic SQL, there is no way to avoid performing at least the soft parse at least for each preparation of each SQL statement. The best possible case is that the statement is prepared, which means soft-parsed, and that each preparation of a statement passes the syntax, semantics, and outline checks, so that no more than these three steps need be done for each invocation of the relevant Java class. This is because the Java method,
The worst case is that not only is the statement not prepared, but no individual submission of the statement passes the outline check (because, for example, bind variables were not used) so that the rest of SQL parsing must also be done for each submission (that is, generating the execution plans, costing them out and choosing the best, generating the binary representation of the chosen plan and storing it in the shared pool).
In profiling the behavior of databases in the two projects that I’ve mentioned, the persistent high rates of parsing were a continuing worry. These high rates result from every SQL statement being dynamic SQL, so that at the minimum every statement undergoes the soft parse during statement preparation.
8. The Side-Effect on Static SQL
As the chains of memory structures in the shared pool fill up, the least recently used statements are flushed. The higher the rate of parsing, the more statements will be dropped. Thus dynamic SQL tends to force shared static SQL out of the shared pool. So the dynamic SQL not only requires parsing overhead for itself, but also for statements that otherwise would have been shared.
9. Dynamic SQL Cannot Be Fully Traced and Profiled
As described above, the outline check is a brief and brutal process. This is for performance: a more intelligent check would affect the overall performance of the database. For static SQL, there is no downside (since static SQL by nature is the same literal string each time it runs), but for dynamic SQL there is a downside.
Two dynamic SQL statements, although logically identical, are often not an exact match in capitalization, white space, or parentheses, or the order of conditions in the ‘where’ clause. Then the hash values generated in the outline check are not identical, and Oracle sees not one SQL statement but two. This not only requires redundant hard parsing, but also makes performance tracing and profiling extremely difficult.
Any profiling tool has the same problem as the parsing engine: it cannot always match dynamic SQL statements that were meant to be the same. The performance analyst or DBA has a hard time matching SQL statements that the profiling tool cannot match. A human being is better at visually recognizing matches, but there are too many statements for a human being to read them all.
The upshot is that no one is ever completely sure that the measured performance of a dynamic SQL statement really represents every invocation of that statement.
10. Dynamic SQL Cannot Be Pinned
Static SQL can be stored in a PL/SQL package, which is simply a source code file that collects a group of PL/SQL procedures and functions. Many companies store their data access objects as SQL statements within PL/SQL procedures within PL/SQL packages. One of the advantages gained is that packages can be pinned in memory.
Pinning marks a package as non-swappable, meaning that after its initial load it remains available through the shared pool and does not age out. This is helpful for packages that are expensive to parse and are not used so frequently that you can be sure they won’t age out. Since the shared pool is very heavily accessed, surprisingly many packages qualify for this kind of management. Actually, Oracle has recommended in the past that some of their bundled utility packages, such as DBMS_STANDARD, should be pinned for best performance.
Pinning cannot be guaranteed to help for every application. However, it can be useful for static SQL, and it can never be used at all for dynamic SQL. This is a case where using dynamic SQL is willfully discarding a tool.
11. Dynamic SQL Cannot Be Precompiled
PL/SQL packages can be precompiled. In this context, precompilation means that the PL/SQL is converted into C source code and the system C compiler is invoked. The object code is then stored into the Oracle dynamically loadable kernel routines. In effect, it is possible to make the PL/SQL routine part of the database kernel. This has a great performance effect on PL/SQL that performs CPU-intensive operations. It is not guaranteed to help in other cases. It is another optimization that is available for static SQL and that can never be used for dynamic SQL.
12. Dynamic SQL Suffers from Network Overhead
Sometimes merely submitting dynamic SQL from an application is a performance problem. The bottleneck is the size of a UDP datagram. This is a configurable size, but 256 bytes is the size most commonly chosen. The trouble is that moderately complex SQL statements are often, maybe even usually, longer than 256 bytes. The consequence is that the network and the listeners become busy passing and assembling datagrams. Looking at the Oracle trace files, one sees that the database is waiting on the network more and more.
Of course, as network hardware improves, this is less of an issue. But like so many other problems with dynamic SQL, it is such an unnecessary problem.
13. Dynamic SQL Is Open to the SQL Injection Exploit and Other Security Exploits
Dynamic SQL is in itself a security breach. As Pete Finnigan writes:Identifying SQL that shouldn’t be there is not simple. The reason SQL injection is possible is because of the use of dynamic SQL in applications. This intended dynamic SQL means that the set of all legal SQL statements is harder if not impossible to define. If legal statements are impossible to define then so are the illegal ones.
The context is a paper on the specific exploit called SQL injection.
SQL injection consists of adding to otherwise innocent SQL a clause or phrase that defeats whatever security it was trying to impose. Imagine an application that dynamically constructs the following statement:
The Java code that executes this statement is supposed to encrypt the user’s password so that it can be established that the user has the right to see the sensitive data. However, if the SQL is constructed dynamically and the Java input routines are not careful enough, the user can type his or her password so that the following statement is generated:
Obviously the malicious user immediately gains full access, without having a valid password. This example is not an invented one; more than one commercial site has discovered what happens when a hacker types
It might seem that SQL injection is a well enough known exploit to be guarded against, but consider the possible forms listed by Pete Finnigan:The ideal list of all possible SQL injection types or signatures is impossible to define in totality but a good starting point would cover the following possibilities:
There are so many possible forms that dynamic SQL is an important security defect even in a shop where the security team is on the lookout.
14. In Conclusion
I hope it is clear that this extended list of faults with the misuse of XML files for SQL storage and of dynamic SQL is not a diatribe against either XML or dynamic SQL. XML is a major advance in data file interchange and has many inventive applications. Dynamic SQL (in all four of the available forms, Methods I through IV) is a terrific solution where it is the right solution. However, we're at the point where, as Philip Lambert remarks on his website, the misuse of these rather specialized techniques is "becoming the norm and not the exception."
And that's not a good thing.