Tuning Oracle DB

From Slashdot | Refactoring SQL Applications

I Only Know Oracle (Score:5, Informative)

by bloobamator (939353) on Wednesday March 11, @10:59PM (#27161495)

I only know Oracle but I’ve known it since version 5.0. Intimately. I haven’t read the book but I read the review. Here are a few tips I’ve learned over the decades that you might find useful, just in case they aren’t covered in the book:

1) You have to establish a general rule of thumb for each production db whereby any one sql that consumes more than x% of the db resources needs to be tuned. The value of x varies from db to db. If it cannot be tuned below x% then it needs to be refactored.
2) Learn to use stored outlines. If you can get them to work they will save your ass and make you look like a total hero.
3) Never turn your back on the optimizer. Really. Even for simple queries, even with the deepest stats.
4) Bind variables are a necessity for high-repetition sql. Bind variables are something you might want to avoid for reports queries for which the optimal plans depend on the user input values. This is because a sql’s plan is cached along with it the first time it is parsed, and if you use bind variables then the first plan you get is the plan you will always get so long as the sql remains in the shared pool.
(You can sometimes work around this issue by turning off bind variable peeking, but consider doing it on a per-session basis instead of changing it system-wide. Scary!)
5) Nowadays a 32GB SGA is no big thing. Get yourselves a ton o’ RAM and set up a keep pool in the buffer cache to pin your most important reporting indexes and tables. Partition your big reporting tables and maintain a sliding window of the most recent partition(s) in the keep pool.
6) No sorting to-disk. Ever. If you cannot let the session have the PGA it needs to sort the query in memory then the SQL needs to be “refactored”.
7) Once you have eliminated most of the physical disk reads it then becomes all about the buffer gets (BG’s). When disk reads are low the high-logical-BG queries immediately become the new top SQL. This is because logical BG’s are all CPU and your db is now cpu-bound, which is right where you want it. So from this point it’s back to item #1 and we prune and tune (thanks KG!)

I could go on all day. Perhaps I should write a book?

[Slashdot] [Digg] [Reddit] [del.icio.us] [Facebook] [Technorati] [Google] [StumbleUpon]

Comments are closed.