Site Meter Technology How Tos: 2007

Tuesday, October 2, 2007

How to Optimize Sql in Oracle

So the scenario is you have your business users crying foul over how slow the system is. It could be anything a web application batch process, some stored procedures running forever, hell even some JDBC calls.

You go to the DBA and he throws some blah at you about how overworked he is and then will try to fit you in but you never know when he's gonna get to it.


So how do you know what to look for and do it yourself or get someone to get it done.We'll use oracle as an example. Some simple items to look out for :

>Check the tables in question:

select table_name, last_analyzed

from user_tables

where table_name like '%your tablename%'


More often then not the dates will be old which suggests you got to get these tables analyzed.


> Simply Run analyze on the tables. This may take a while to run depending upon the size of the data in the table

Analyze Table your_tablename compute statistics


Look at the indexes on each of the tables.

Often you will find they do not have indexes or they do but not on the columns that queries use.

So there you go create indexes on the most queried columns in the sql


Create index ID1 on your_table_name(Column_name)

.
Bham ! This should increase the efficiency 75% off the time. Now you have everything working at a threshold that is tolerable.For further fine tuning break down the code and optimize each query to get the output you desire.


By this time the DBA should be able to poke around and find the bottlenecks if any, in the system.

All things Technical and then some

Being a techie I thought I would start writing about things that I work with and interest me and ofcourse technology seems to play a big role.

Starting today I thought I would like to write simple How Tos on Techie Stuff.

It's not going to be a tutorial, there are several Experts & Gurus out there for that. It's gonna be simple tricks for newbies who want to get something done when they are caught in a techy bind as they say.

So welcome aboard and watch out for some geeky stuff :)