Sunday, January 15, 2006
Oracle DO NOTs
Here is another interesting discussion taking place on AskTom about Oracle DO NOTs:
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:53901314988148
After Tom responded, a lot of people got into it. I had some ideas myself, but decided not to make one of my famous Top 20 Lists after all, since it wound up similar to this one:
http://thinkoracle.blogspot.com/2005/12/20-plsql-coding-tips.html
For those of you who are curious, here is more or less what was covered by Tom or his guests:
Tom:
1. Don't accept string literals from end users and concatenate them into your SQL. In other words, use binds. This isn't just for efficiency, its more to prevent execution of malicious code. Yes, I know I did this one when demonstrating DBMS_PIPE.http://thinkoracle.blogspot.com/2005/11/dbmspipe.html
2. Don't test on an empty or near-empty database (you need real volumes)
3. Don't test with a single user (you'll miss scalability issues)
4. Don't forget to use a source control system.
5. Don't wing it as you go along, design in advance.
One responded (our buddy Bill) that you shouldn't do anything without a spec.
6. Don't take advice from experts without testing to see if it applies to you.
7. Don't optimize by hypothesize. Test!
From Others:
8. Don't reinvent the wheel, use built-in packages
9. Don't ignore the Oracle documentation, there is a wealth of information there.
10. Don't use technologies because they are cool.
11. Don't hesitate to throw away bad code, rewriting can be better than refactoring.
This actually resulted in some debate. Sometimes you want to let sleeping dogs lie. That is, if the code works, don't spend days re-factoring it unless you need to.
12. Don't write code without documentation/comments
Here is a great language-neutral link: http://tlug.up.ac.za/old/htwuc/unmain.html
13. Don't name variables arbitrarily.
14. Don't skip your unit-testing
15. Don't comment your SQL to override the optimizer plan unless you are sure.
16. Don't forget to update comments when you update code.
17. Don't forget to instrument your code.
What is instrumentation? Well, read Mogens Norgaard, or start with this link:
http://tkyte.blogspot.com/2005/06/instrumentation.html
18. Don't forget to mention dependencies when commenting code.
19. Don't expect the same database on two different hosts to work exactly the same.
20. Don't modify init.ora parameters without documenting the originals.
21. Don't neglect to collect histogram data when you run stats.
Well this may have turned into a Top 20 (well, 21) list, but none of these are my own. If you find this interesting, the link to the discussion is at the top.
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:53901314988148
After Tom responded, a lot of people got into it. I had some ideas myself, but decided not to make one of my famous Top 20 Lists after all, since it wound up similar to this one:
http://thinkoracle.blogspot.com/2005/12/20-plsql-coding-tips.html
For those of you who are curious, here is more or less what was covered by Tom or his guests:
Tom:
1. Don't accept string literals from end users and concatenate them into your SQL. In other words, use binds. This isn't just for efficiency, its more to prevent execution of malicious code. Yes, I know I did this one when demonstrating DBMS_PIPE.http://thinkoracle.blogspot.com/2005/11/dbmspipe.html
2. Don't test on an empty or near-empty database (you need real volumes)
3. Don't test with a single user (you'll miss scalability issues)
4. Don't forget to use a source control system.
5. Don't wing it as you go along, design in advance.
One responded (our buddy Bill) that you shouldn't do anything without a spec.
6. Don't take advice from experts without testing to see if it applies to you.
7. Don't optimize by hypothesize. Test!
From Others:
8. Don't reinvent the wheel, use built-in packages
9. Don't ignore the Oracle documentation, there is a wealth of information there.
10. Don't use technologies because they are cool.
11. Don't hesitate to throw away bad code, rewriting can be better than refactoring.
This actually resulted in some debate. Sometimes you want to let sleeping dogs lie. That is, if the code works, don't spend days re-factoring it unless you need to.
12. Don't write code without documentation/comments
Here is a great language-neutral link: http://tlug.up.ac.za/old/htwuc/unmain.html
13. Don't name variables arbitrarily.
14. Don't skip your unit-testing
15. Don't comment your SQL to override the optimizer plan unless you are sure.
16. Don't forget to update comments when you update code.
17. Don't forget to instrument your code.
What is instrumentation? Well, read Mogens Norgaard, or start with this link:
http://tkyte.blogspot.com/2005/06/instrumentation.html
18. Don't forget to mention dependencies when commenting code.
19. Don't expect the same database on two different hosts to work exactly the same.
20. Don't modify init.ora parameters without documenting the originals.
21. Don't neglect to collect histogram data when you run stats.
Well this may have turned into a Top 20 (well, 21) list, but none of these are my own. If you find this interesting, the link to the discussion is at the top.
