Thursday, December 15, 2005

Oracle Rawks My Sawks!

I've been blogging about tweaking XP so much, im kinda bored of it. Okay, so maybe 4 posts on XP can't quantify as "So Much".. but then again, it's my blog so i'm gonna say it's enough for now. ;) Besides, i can't think of anything to write today on tweaking Windows.. maybe something will pop into my head tomorrow.

What's fresh in my head though, is Oracle 9i Tuning. Personally, i find Oracle to be one of the most kick-ass RDBMS. PostgreSQL is cool cause it's free but for enterprise deployment, i'll put all my money in Oracle. MS SQL? ermmmm... moving on. =p

Alot of DBAs deploy database and then forget about it.. until users start calling and asking why does it take 1/2 hour to print report 'X' when it used to take 3 mins? Data grows baby and if you don't manage it, you're digging your own grave. Before moving on to more technical tips on database performance tuning, I'd like to cover some of the basic tuning phases every dba should know or cover appropriately.

In a nutshell, tuning can be divided into 4 phases:
  1. Application design and programming.
  2. Database Configuration.
  3. Adding a new application to an existing database.
  4. Troubleshooting and tuning.

1#: Application design and programming

The fundamental of all tuning. 75% of the time, your users are breathing down your neck on performance due to this problem. The best way to do this, run statspack and see what kinda statements are being parsed. Statspack is god's gift to DBA's. Use it. (Will cover more on this)

2#: Database configuration

Always plan data configuration to ensure shorter recovery time and faster data access.

3#: Adding new application to an existing database

Workload changes when new apps are added to an existing system. You must accompany any major change in the workload with performance monitoring.

4#: Troubleshooting and Tuning

Use tools to identify bottlenecks. Examine this data to form a hypothesis. Develop a solution from the hypothesis and implement it. Run a test load to see if it solves your problems. Common performance problems are Bad session management, bad cursor management, and bad relational designs.

Remember, always collect a Baseline set of statistics. This should be when the system is optimal. From there, collect statistics on a scheduled basis and monitor for degration.

Some tools you can use to tune your database....
  1. Oracle Enterprise Manager
  2. Diagnostics and tuning packs
  3. Statspacks
  4. Dynamic troubleshooting views
  5. dba_xxxxxx dictionary views
  6. Oracle wait events
  7. utlbstat.sql and utlestat.sql scripts

I'll drill down more into using SQL Scripts and tools to manage and tune your database. But not today ;) Cause i have to sleep. Most of you probably have left this site reading the first few lines.. Those of you who did manage to reach this far.. thanks for reading my textbook bullshit ;)

Tomorrow, i hope to drill down to the more techi shit. Those who do return to read it, well i hope it helps you and if it doesn't, thanks for wasting your time at techrawks!


Blogger KY said...

hey, pretty good write up. will be good if u actually link to where we can find the tools. like a download site or something. :)

11:13 AM  
Blogger TechTalks said...

point noted my friend ;)
I'll try to add more tuning tools and scripts as i go along..
Thanks for stopping by..

5:38 PM  

Post a Comment

<< Home