40 Tips From Tom

1.Row-level locking has no overhead, not for 1 lock, not for 1 billion locks
2. Reads are never blocked, and reads don’t block
3. The best UI is a command line
4. Always use packages, for all good, real, production code
4a. Never use a standalone procedure except for demos, tests and standalone utilities that call nothing and are called by nothing.
4b. Packages break the dependency chain, support encapsulation, increase namespace, support overloading and session variables, and promote overall good coding techniques
5. Use the same source control techniques for PL/SQL as you would for any other language (C, Java, VB)
6. Use dbms_stats instead of Analyze
6a. Easier to automate, the stated/preferred way of collecting statistics, can analyze external tables, gathers statistics need for CBO (and nothing extra)
7. Serializable does not imply serial ordering
8. To be useful, know more than just the technology, know the business and the goals of your organisation
9. Joins are not evil. Databases were born to join.
10. Never compare strings to dates, and dates to strings. Compare dates to dates, strings to strings, numbers to numbers and never any other combination.
11. Beware of implicit conversions of dates and numbers. Avoid implicit like the plague, explicit is good
12. Never TO_NUMBER a number, never TO_DATE a date
13. Stop using YY in your date formatting, now and forever
14. Autonomous transactions as a feature was a huge mistake, they are rarely used in a manner that is safe.
15. Never copy online redolog when your database is in archivelog mode
16. Never restore the online redo log files
17. Rollback is not just for modifications, this is fundamental about Oracle
18. Stop committing until your transaction is complete
19. Don’t use a loop when a single query will do
19a. Use bulk processing instead of slow-by-slow processing
20. For performance reasons, call PL/SQL (or worse, Java) from SQL if and where there is quite simply no other way
21. Large databases are neither slow nor scary
22. Analytical functions are for those who crave, desire or just sometimes NEED speed.
23. The way to understand is to do
24. Soft parse percentage? 99% and above is good.
25. Select only the columns you need, not *
26. When designing a table, put the most frequently access columns first, those most likely to be NULL last, but don’t overnalyse it.
27. Disk is cheap. Data integrity, priceless.
28. You cannot put CPU in the bank and save it for later. If you are at 99.99% utilization — pat yourself on the back, you’ve done well.
29. Analytics rock. Analytics roll.
30. Don’t use the slower UNION when UNION ALL will do
31. Never never never do dynamically what you can do statitically, in PL/SQL.
32. You want to scale? Use static SQL in PL/SQL, all binds, all the time.
33. Triggers are evil.
34. Magic should be avoided. Experience tells me this.
35. Never create stuff as “sys” or “sysdba”, thats a special magical account not to be used by you or me.
36. Be careful doing anything non-transactional in a trigger. It won’t rollback when the transaction rolls back.
36a. Be very careful using any routine that starts with UTL_ in a trigger, they’re generally non-transactional
37. Use dbms_application_info package to allow you to show a procedure’s progress in v$session.
38. The sheer beauty of PL/SQL is that your concerns with other languages, like “parse once; execute many” and using bind variables are done automatically and transparently.
39. Cursor sharing is a bad thing, if you don’t need it
40. Not binding is actually harder to do than binding (and less secure)


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s