OracleBlog

Everybody learns their lessons, and so will you. The only variable is how expensive the lesson is. While there is no substitute for direct, first-hand experience, the cheapest way to learn a lesson is to benefit from the experience of others.

My favourite source of cheap lessons is Ask Tom. I've compiled a sample collection of Tom's Wisdom from just the articles updated in the past week. Review this list and consider how many free lessons are contained in the past 7 years of archived information!

The number next to every tip has a hyperlink to the appropriate question. I included this because I remember that Tom's credo is "Trust, but Verify." Trust in Tom's wisdom, but check the context of his advice so you can see if it applies to your situation, is still valid today, and that it wasn't one of those rare cases where he got it wrong.

As a bonus for those who enjoyed my last post about Tom's more entertaining side, I've included a bit more of his biting humour too.

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)

More Tom Humour!

Refresh on Materialized View

Q: see oracle bug no 2088032
A: And then scratch your head and ask "why am I reading this bug report which doesn't have anything to do with my problem?"

Update Statement
Not that I don't believe you -- but -- I don't believe you.

Oracle PL/SQL - Performance Tuning - Comments in Code
In response to someone saying their code review team told them to remove comments from the code for performance reasons. I just KNEW a good retort was coming.

Oh my gosh.

I have never heard of anything so - well, I won't be that blunt, fill in your favorite word.
...
get a new code review team, one that actually knows how to review code
...
umm, they are sort of "not correct"

DBMS_JOB Package
This is one of Tom's favourite comebacks to someone saying "it doesn't work."

My car won't start either. why not?

Umm, define "does not work"

String Concatenation
Q: Can you do the same thing in Microsoft SQL server 2000....
A: That would be a good question for askbill@ms.com....

Restoring a file stored in a database
hahahahaha, ouch. sorry I just fell out of my chair.

Hmm, the bigger the database, the slower it is. Interesting.

But completely and utterly wrong.

CPU Utilization
Q: How can I reduce CPU utilization of Oracle database?
A: shutdown abort works well ;)

Refresh Complete and Rollback
Q: I have read/heard/been told that snapshots are a lot of overhead.
A: I heard that business travelers better not goto bars cause there are people that will drug you and take out your kidneys to sell on the black market.

Rollback Segments
Q: insert /*+ append nologging */ into t select * from t_stging where country_code = 'US'
A: That insert is just the same as:
insert /*+ append hello world!!!! how you DOING? */ into t ......