Oracle PL/SQL performance tuning crash course

Question

I am a Java guy who is familiar to basic SQL and PL/SQL. I can write simple procedures and functions. Now, I am expected to do some Oracle performance tuning. Can anyone help me with some crash course material? Thanks in advance.

--Siddharth

Answer 1

Along with the earlier suggestions (all excellent in their own rights) there are a few simple things you can do which will make you into an Instant Performance Guru (tm):

1. Carry a briefcase full of papers and books. Dog-eared books with titles like "Oracle Performance Tuning For Highly Effective People"+ and pieces of paper with boxes and arrows scribbled on them work well. If the books are for outdated versions of Oracle so much the better as it makes it look like you've been doing this for a while - plus, you can buy 'em cheap from the 'clearance' table at your local bookstore. For best effect the briefcase should be well worn - if you're forced to purchase a new briefcase you can get that weathered effect by backing over it with a car and/or tying a rope to the handle and dragging it through the sand/dirt/mud for ten minutes or so. This all helps to impress the natives. A bullet hole or two can be interesting conversation starters. You can also use the briefcase to carry your lunch and other important stuff like a towel.
2. Add full-key indexes for all queries.
3. Ensure all foreign keys have full-key indexes backing them.

This may give you the idea that "performance analysis" consists mostly of adding the indexes that the people who wrote the software never bothered to add because in their next-to-empty development database everything ran really fast. This is not correct. A complete fabrication. Utter nonsense. At best it's only about, like, 95% of it. Pay no attention to that man behind the curtain - he is of no importance whatsoever...

You now know the Secrets of the Oracle Performance Masters. (Well, most of it anyways, except for the Secret Handshake, which is tough to explain in a text message (and besides, you need six fingers on each hand), and the Hidden Mysteries, which consist principally of a lot of stuff about frogs which you don't technically need to know but which has been know to make well-informed people giggle a lot - which is not attractive...).

Go ye forth and do ye good works.

+This is actually slightly incorrect. The book you really want to have in your briefcase is "Oracle Performance Tuning For People Who Are Smarter Than 99.9% Of The Inhabitants Of This Planet". Since 99.99999%++ of the inhabitants of this planet are single-celled organisms or managers (and sometimes both) this is not difficult to accomplish.

++This is a real number. You can't just make this stuff up+++.

+++Actually, you can and I did. But it's not "lying" if you use an exact number - it's "creative re-imagining"++++.

++++That's "lying" in consultant-speak.