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.
1 Answer
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.
