Hello,
As usual, I will preface my question with the fact that I'm not an Oracle expert - or even close! I will now attempt to lay out my problem in a sensical fashion.
Goal:
I need to improve the performance of our application. Pretty much everything about our application is in the database, so while there is also a lot of code around the database calls, I think the path of least resistance is to improve the performance of our database stored procedures.
Problems:
- I believe most - if not all - of our stored procedures are under-performing, but we don't know exactly how deep (i.e., the "extent" to which stored procedures under-performing) and widespread (i.e., "how many" stored procedures are under-performing) the problem is, and I need evidence to propose a revamp.
- We have no baseline (or benchmark) to which to compare our "current" results, but I believe a good starting point would be to understand which stored procedures are frequently run and how long they take.
- Finally (and related to #3, above), we currently have no process for collection of database performance metrics.
Sub-Goal (AKA, the GOAL as it relates to you guys and how you can help me):
Obviously, the first step in improving performance is understanding areas that are under-performing. In order to improve the performance of our stored procedures, I need to first collect performance metrics about them. At this point, I'm simply wondering what stored procedures are run, how often they are run, the parameters, and how long they take to execute. This should give me a good starting point for optimization.
Sub-Goal (Secondary):
Being that I'm not a DB/SQL expert (I'm a super-novice), I may have a very limited view as to the data points (as I've laid them out above) I want to collect. I'd love to hear suggestions, but - to be clear - that's a secondary thing. I need to find out how this data collection works in Oracle first.
Requirements:
I cannot do anything session-based. The performance metrics must be collected at a higher level. Our stored procedures are all run via RPC from our application. I can set up some basic scenarios to run in a session, but this seems too specific to me and won't capture the entire scope of the problem. Instead, I'd like to be able to run a process like...
- Start collecting DB metrics (say, on a timer - the scheduling will be up to me, so don't worry about it)
- Perform various functions within our application - this will cause various stored procedure calls.
- Stop the DB metric collection
- Generate a report
With SQL Server, I set up stored procedures to start data collection, stop collection, and generate a report, all of which I can easily schedule. How can I do something similar with Oracle? I've been googling and looking at various DBMS_* packages: MONITOR, STATS, PROFILER, TRACE, and others. What is a good starting point? There is a LOT of documentation for all of these, and so far I haven't been able to collect the data that I'd like to collect (probably due to a lack of understanding). Many of these tend to be session-based, as well, which as I mentioned doesn't quite fit our requirements. If someone could tell me which package to look at specifically, it would eliminate a lot of pain.
Thanks.