A recent addition to my Oracle PL/SQL library is the book Oracle PL/SQL Performance Tuning Tips & Techniques by Michael Rosenblum and Dr. Paul Dorsey.
I agree with Steven Feuerstein’s review that “if you write PL/SQL or are responsible for tuning the PL/SQL code written by someone else, this book will give you a broader, deeper set of tools with which to achieve PL/SQL success”.
In the foreword of the book, Bryn Llewellyn writes:
The database module should be exposed by a PL/SQL API. And the details of the names and structures of the tables, and the SQL that manipulates them, should be securely hidden from the application server module. This paradigm is sometimes known as “thick database.” It sets the context for the discussion of when to use SQL and when to use PL/SQL. The only kind of SQL statement that the application server may issue is a PL/SQL anonymous block that invokes one of the API’s subprograms.
I subscribe to the thick database paradigm. The implementation details of how a transaction is processed and where the data is stored in the database should be hidden behind PL/SQL APIs. Java developers do not have to know how the data is manipulated or the tables where the data is persisted, they just have to call the API.
However, like Bryn, I have seen many projects where all calls to the database are implemented as SQL statements that directly manipulate the application’s database tables. The manipulation is usually done via an ORM framework such as Hibernate.
In the book, the authors share a particularly bad example of this design. A single request from a client machine generated 60,000 round-trips from the application server to the database. They explain the reason behind this large number:
Java developers who think of the database as nothing more than a place to store persistent copies of their classes use Getters and Setters to retrieve and/or update individual attributes of objects. This type of development can generate a round-trip for every attribute of every object in the database. This means that inserting a row into a table with 100 columns results in a single INSERT followed by 99 UPDATE statements. Retrieving this record from the database then requires 100 independent queries. In the application server.
Wow! That’s bad. Multiply this by a 100 concurrent requests and users will start complaining about a “slow database”. NoSQL to the rescue!2 Comments | Filed in Oracle | Tags: book, pl/sql, sql
Steven Feuerstein was dismayed when he found in a PL/SQL procedure a cursor FOR loop that contained an INSERT and an UPDATE statements.
That is a classic anti-pattern, a general pattern of coding that should be avoided. It should be avoided because the inserts and updates are changing the tables on a row-by-row basis, which maximizes the number of context switches (between SQL and PL/SQL) and consequently greatly slows the performance of the code. Fortunately, this classic antipattern has a classic, well-defined solution: use BULK COLLECT and FORALL to switch from row-by-row processing to bulk processing.Leave a comment | Filed in Oracle | Tags: pl/sql, sql
Tom is not not a fan of public synonyms, here is why:
- public synonyms pollute the namespace.
- public synonyms can lead to security issues.
- public synonyms can lead to a maintenance headache.
- public synonyms are public – no one owns them.
So, instead of public synonyms… Continue reading…2 Comments | Filed in Oracle | Tags: sql
SQL injection attacks are becoming significantly more popular amongst hackers, according to recent data. Between Q1 2012 and Q2 2012, there has been an estimated 69 percent increase of this attack type.
SQL injection can be easily avoided. How? Just use bind variables. Here is a quote from Tom Kyte:
If you use bind variables, you cannot be SQL Injected – this is true for PL/SQL, for Java, for any and all languages. If you use bind variables you cannot be SQL Injected – period. It is that simple, really and truly.
Tom also links to an excellent paper on this subject written by Bryn Llewellyn: How to write SQL injection proof PL/SQL [PDF].
It baffles me how such a simple and easy solution to such a big security problem is not implemented in the code base of all of these big companies that have recently been hacked.Comments Off | Filed in Oracle, Security | Tags: sql
I think if Oracle had extended its ANSI SQL support beyond SELECT to the other DML commands the argument over whether to use the new syntax would have died out years ago.
It would have helped for sure. I also think that if you work in an environment where ANSI SQL is not the “standard” it is hard to adopt it as your main approach to writing queries.Comments Off | Filed in Oracle | Tags: join, sql
Apparently somewhere around the version 10 of sqlplus they changed the autotrace command to use DBMS_XPLAN.DISPLAY to show the plan of a query run with set autotrace on. But, I’ve never taken advantage of this feature because I’m using a 9.2 version of the Oracle client on my laptop.Comments Off | Filed in Oracle | Tags: sql
I will be presenting a free Webinar, brought to you by ODTUG, on Tuesday, July 17, 9:00 AM – 10:00 AM PDT. I will be talking about and demonstrating SQL and PL/SQL caching techniques that will considerably improve the performance of your queries and programs. The Webinar will cover the following five features:
Click here to register.
The webinar went very well. I thank all of you who attended and ODTUG for hosting it.
Click here to download a zip file containing the following files:
1. Set based processing will likely be much faster than row based processing. Our experiment of processing 100K rows showed row based processing was 3700 times slower than set based processing. Not twice as slower or even 10 times slower… 3700 times slower!Comments Off | Filed in Oracle | Tags: pl/sql, sql
2. Compared to set based processing, row based processing times degrade much quicker than set based processing. That is, row based processing does not scale nearly as well as set based processing. We saw this in that the linear trend line for row based processing was 0.00259 compared to 0.00000 for set based processing.
ALTER TABLE sys.dual ADD (dummy2 VARCHAR2(1));
You never, never never never, as in never ever – touch any of the SYS tables – not to insert into them, not to delete from them, not to update them and especially not to alter them.Comments Off | Filed in Oracle, Tips | Tags: sql
in oracle 10 g , the below query results in alphabetical order , there is no order by caluse. but when i am running the same query in oracle 11g , it gives me in random order. what is the problem here?
People, it is really simple: You want an ordered result set? use ORDER BY.
Tom Kyte wrote about it in this blog post and in his books:
Comments Off | Filed in Oracle | Tags: sql
Do not ever count on the order of rows from a query unless you have an ORDER BY statement on your query!
The only way to retrieve data from the database in some sorted order is to include an ORDER BY on your query. There is no substitute for ORDER BY.