Last Wednesday Quest organized a seminar in Amsterdam on the performance of the Oracle database and the programs you write for it.
There were three presentations.
- Application Testing
- Database maintenance
The main issue in this presentation was that you should care about performance as early as possible in your development. It is much cheaper (time and money wise) to tune your code early in the process than when itâ€™s all on production and when thereâ€™s lots of lines of code to check. 60% of the performance issues can be found in the SQL statements used. Another 30% can be found in the indexes used. That makes up 90% of the performance issues.
Quest has a couple of tools available that can help you with solving these issues. You can use the code Xpert or the profiler (and their front-end to it). But you can of course also ask the database (inspect the SGA) or, if all else fails, ask your users or your DBA. Another tool they promote is the SQL optimizer for Oracle. This tool rewrites the SQL statement you provide it (more times than you could do yourself in the same time) and looks for the best execution plan (it doesnâ€™t actually execute the statements). Chances are this will come up with a better statement than the one you wrote.
This was mainly a presentation on Firefighting performance problems in production. Not really testing your application, but responding to issues raised by the users, like â€˜The application is slowâ€™, or (worse) â€˜The application doesnâ€™t workâ€™ (donâ€™t be too specific, pleaseâ€¦). What is mostly done is monitoring alarms, thresholds being exceeded, for instance high CPU usage or the amount of memory available is low. For this Quest offers different tools too, for instance Spotlight on Oracle.
Then there was a presentation on what a presentation on what a â€˜hobby DBAâ€™ can use to trouble shoot a production application. You donâ€™t have to be an expert on Oracle DBA tasks to be able to see where a bottle neck is present. As long as you have the right (monitoring) tools in place. This way, everybody can identify such issues and take appropriate action, that is, have the right people do their thing.
All in all it was mainly a bit of a sales rap, but I think developers (I am not a DBA) need to know about these tools (and be able to view the results) and there are probably more tools available than just the Quest ones, but if you donâ€™t know what to look for, how can you find the right ones.