DEVELOPING SUCCESSFUL ORACLE APPLICATIONS in Objective-C

Encoding ECC200 in Objective-C DEVELOPING SUCCESSFUL ORACLE APPLICATIONS

ops$tkyte%ORA11GR2> select /* TAG */ substr( username, 1, 1 ) 2 from all_users au1 3 where rownum = 1; S S ops$tkyte%ORA11GR2> alter session set cursor_sharing=force; Session altered. ops$tkyte%ORA11GR2> select /* TAG */ substr( username, 1, 1 ) 2 from all_users au2 3 where rownum = 1; SUBSTR(USERNAME,1,1) -----------------------------S What happened there Why is the column reported by SQL*Plus suddenly so large for the second query, which is arguably the same query If we look at what the cursor sharing setting did for us, it (and something else) will become obvious: ops$tkyte%ORA11GR2> select sql_text from v$sql where sql_text like 'select /* TAG */ %'; SQL_TEXT ------------------------------------------------------------------------------select /* TAG */ substr( username, 1, 1 ) from all_users au1 where rownum = 1 select /* TAG */ substr( username, :"SYS_B_0", :"SYS_B_1" ) au2 where rownum = :"SYS_B_2" from all_users

The cursor sharing removed information from the query. It found every literal, including the substr constants we were using. It removed them from the query and replaced them with bind variables. The SQL engine no longer knows that the column is a substr of length 1 it is of indeterminate length. Also, you can see that where rownum = 1 is now bound as well. This seems like a good idea; however, the optimizer has just had some important information removed. It no longer knows that this query will retrieve a single row; it now believes this query will return the first N rows and N could be any number at all. This can have a negative impact on your generated query plans. Additionally, I have shown that while CURSOR_SHARING = FORCE runs much faster than parsing and optimizing lots of unique queries (refer to the section on bind variables above), I have also found it to be slower than using queries where the developer did the binding. This arises not from any inefficiency in the cursor-sharing code, but rather in inefficiencies in the program itself. In many cases, an application that does not use bind variables is not efficiently parsing and reusing cursors either. Since the application believes each query is unique (it built them as unique statements), it will never use a cursor more than once. The fact is that if the programmer had used bind variables in the first place, she could have parsed a query once and reused it many times. It is this overhead of parsing that decreases the overall potential performance. Basically, it is important to keep in mind that simply turning on CURSOR_SHARING = FORCE will not necessarily fix your problems. It may very well introduce new ones. CURSOR_SHARING is, in some cases, a very useful tool, but it is not a silver bullet. A well-developed application would never need it. In the long term, using bind variables where appropriate, and constants when needed, is the correct approach.

Note There are no silver bullets, none. If there were, they would be the default behavior and you would never

Even if there are some switches that can be thrown at the database level, and they are truly few and far between, problems relating to concurrency issues and poorly executing queries (due to poorly written queries or poorly structured data) can t be fixed with a switch. These situations require rewrites (and frequently a re-architecture). Moving data files around, adjusting parameters, and other databaselevel switches frequently have a minor impact on the overall performance of an application. Definitely not anywhere near the two, three, ... n times increase in performance you need to achieve to make the application acceptable. How many times has your application been 10 percent too slow 10 percent too slow, no one complains about. Five times too slow, people get upset. I repeat: you will not get a five times increase in performance by moving data files around. You will only achieve large increments in performance by fixing the application, perhaps by making it do significantly less I/O.

Note This is just to note how things change over time. I ve often written that you will not get a five times

increase in performance by moving data files around. With the advent of hardware solutions such as Oracle Exadata a storage area network device designed as an extension to the database you can in fact get a five times, ten times, fifty times or more decrease in response time by simply moving data files around. But that is more of a we completely changed our hardware architecture story than a we reorganized some of our storage.

Performance is something you have to design for, build to, and test for continuously throughout the development phase. It should never be something to be considered after the fact. I am amazed at how often people wait until the application has been shipped to the customer, put in place, and is actually running before they even start to tune it. I ve seen implementations where applications are shipped with nothing more than primary keys no other indexes whatsoever. The queries have never been tuned or stress-tested. The application has never been tried out with more than a handful of users. Tuning is considered to be part of the installation of the product. To me, that is an unacceptable approach. Your end users should be presented with a responsive, fully tuned system from day one. There will be enough product issues to deal with without having poor performance be the first thing users experience. Users expect a few bugs from a new application, but at least don t make the users wait a painfully long time for those bugs to appear on screen.