I'm an application developer. We have some reporting solution for our client on Oracle database 10g. They would like to speed up execution of some calculation stored procedures. Ok, I know about query plans & optimizations, but also I need to give cost-effective hardware recommendation. Do they need to add more RAM, or more CPU (server is virtual, so it is easy), or maybe they need better storage solution ? How do I know where Oracle hits the wall, what parameters should I measure during the execution of my procedures? Thanks for any advice.
closed as too localized by Chris S♦ Jan 20 '13 at 14:26
This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center. If this question can be reworded to fit the rules in the help center, please edit the question.
You will have to dig into the DBA hills. Oracle 10g has several mechanisms which tell you where it is constrained, like ADDM, AWR, ...
If you have enterprise license (and your server has enough RAM), then you are allowed to take advantage of the web tool Enterprise Manager where there you will see graphically what is used and where there should be more (RAM, CPU, disk, disk I/O, ...).
Take a look at database management and configuration management. Its not so hard even for a novice, but do discuss it with some DBA.