0What is Performance in general terms?
Throughput per Input. For Eg: we keep saying, this bike has so and so much of mileage and all that. That is one of the parameters in measuring the performance of a vehile. Similarly, in the computer programming world, performance is a key factor like in any other industry.
To keep it simple, let us take an example that you have written a program for your client based on his requiremetns and you are giving the desired results. But, as the volume of transactions increases, your program starts performaing poorly. It starts taking 2 mins to give the results, which was 1 sec earlier. Now, there is another competitor and has a solution for it and his program gives the same result in less than a sec inspite of increased volumes for the same set of hardware configuration as you were using. This hardware part is very important. This is our input/investment, which involves things like RAM, no. of cores of cpu etc.
You also have a solution to the above problem and you suggest your client to increas the RAM by 2 times. Hardware and especially what we call as Memory (RAM), is very costly. Why would customer agree to your solution? Here comes the need of performance tuning.
So, what it actually means is, to tune you program to use the hardware efficiently. So, there is a base to refer and it a set of hardware. If there are two versions of a program written. To compare them, we whould run them on the same set of hardware components.
For eg: Lets say, you are doing this statetement in your plsql code.
Select emp_name from emp where emp_id=1120;
which is fine. But what if you want to access the same information for all or 1000 employees. Definitely, you would use a cursor and run it under a loop and process each employee's data at a time.
So, what's happening here. Let me concentrate on context switch only in this case. There are 1000 context swicthes between plsql engine and Sql statement executor. So, if you use Bulk collect, these 1000 would be replaced by a single context switch, which is an anormous amount of perforamance gain. This is just an example. You would have 100s of columns to select and crores of rows in reality.
Let me ask you one question. Why Oracle is the leading RDBMS? Because of the same fact that it has given us lots of perforamnce tuning features like bulk collect, indexing, Materialized views, Partitioning, Clustering, bind variables, HINTS, AWR reports, ASH reports and so on. But, it's our responsibilty to understand them and apply it in our applications.
It was an attempt to make you understand what is performance tuning exactly and why is it needed.To conclude, let me put two important things in performance tuning-
1. It's mainly about reducing the I/O and hence make use of the memory effectively.
2. Writing effective Sql statements solves 80% of the problems and ofcourse you are required to tune other parts of plsql code too.
