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.