Michael Rosenblum and I are writing a book about PL/SQL performance tuning for Oracle Press. I started thinking about the “big picture” ideas that everyone should know. The following is the list that I came up with. If you have any other top tips that I missed, please let me know.
1) You can’t fix performance problems if you don’t ever measure performance in the first place.
2) You have to re-measure performance from time to time.
3) You can’t fix bad system performance if you never ask the question: Why is the system performing badly?
4) Don’t simply accept bad system performance.
5) Follow your instincts. There is no reason why creating one purchase order with 10 details based on an existing quote should take ten seconds, even if that is an acceptable time lag.
6) PL/SQL is pretty fast, but anything that needs to be done a million times is going to take a while.
7) If the application is making 10,000 calls to a PL/SQL package in the database from the application server and performance is bad, the right approach is NOT to tune the PL/SQL. You need to figure out why the application is making 10,000 calls.
8) The best detailed tuning techniques in any version of the DBMS are not the same as the best techniques in any other version of the DBMS. Even point releases of the DBMS can change best practices.
9) Because of 8), every time you want to upgrade the DBMS (or OS, or app server), you have to perform full regression testing on the system.
10) Hints and using undocumented Oracle parameters are some of the most sensitive parts of your code that can change in a version upgrade. Avoid them when possible.
11) Adding hardware to fix a performance problem is almost always a bad idea. A scalable architecture does not mean that you can add hardware as you add more users or data. It means that you don’t need to add hardware as data and number of users increase.
12) We have much better tools for measuring performance problems than we used to. You need to keep up-to-date.
13) Context switching between SQL and PL/SQL is bad, context switching between the application server and PL/SQL in the database is 1000 times worse.
14) You have to test production data volumes and production user loads; otherwise, you are just hoping that your system is not going to crash.
15) Don’t try to tune the PL/SQL until you have determined that it is the cause of the problem. Usually, when there is a web application performance issue, it is not the PL/SQL. The cause is most likely one of the following:
- Lots of round trips from the application server to the database
- Slow JavaScript execution on the client
- A bad SQL query in the code
- Moving a large page to the client from the application server
16) Good performance tuning goes hand-in-hand with good architecture. Trying to performance tune a terrible architecture will only produce limited results.
17) Instrumentation has to be built into the architecture from the ground up. In most application systems, the only way to figure out what a particular operation does is to run only that operation in the test environment. In a well instrumented architecture, you can find out exactly what each operation does by monitoring the production environment.
18) Poorly written PL/SQL can result in performance issues that are undetectable in development and very hard to see when testing the system. For example, some issues are caused by database locks due to PL/SQL code.
19) Running a routine in a package the first time frequently takes MUCH longer than the second time because the package only gets loaded into memory once.
20) PL/SQL tuning can’t take place in isolation. You have to simultaneously worry about database tuning as well as SQL tuning.
A few thoughts:
– BULK COLLECT with LIMIT as applicable, is your friend.
– Cached cursors are your friend too.
– If it can be done in SQL don’t do it in PL/SQL instead.
– Don’t write code in PL/SQL to do tasks that are already built in to the database. It may sound obvious, but I have a system with MIN() MAX() GREATER() and such like, as separate PL/SQL utilities. Sigh!
Number 17 above, instrumentation, is excellent. Glad you included it.
Hope you find this useful. Looking forward to the book already!
Cheers,
Norm.
Item # 13 shouldn’t discourage developers from using SQL in PL/SQL. A well formed SQL statement that takes advantage of set processing is usually faster than row by row processing.
Items #7 and #11 might seem obvious but unfortunately they aren’t.
Are you going to cover specific aspects of PL/SQL like parallel enabled pipelined functions and other features that improve performance?
Looking forward to buying the book.
Best Practices Aren’t.
(And Norman beat me to the don’t use PL if you don’t have to)