-
Background:
---------------
My current client has a sizeable Oracle database in production. They have had many optimization passes done to all the SQL. My investigations have shown that they have stability issues between and within the various servers. Once this is resolved, I want to make sweeping changes to the SQL, including index changes, additions of histograms, DDL changes, etc.
What I need
--------------
First, I am working with the assumption that I can gather all the SQL (or a very representative sample) for the application.
I am then looking for a database utility that will allow me to:
1. Store all these SQL statements
2. Store parameters for the bind variables for these statements
3. Hopefully, run these statements for me, although this is not an absolute requirement
4. Store the statistical results from these runs (particularly the Logical Reads, but CPU, Memory, Timing as well)
5. Compare these results to previous runs
I need to be able to make these sweeping changes and then see exactly which statements I helped and which I hurt. It seems to me that this should be a fairly standard tool in a DBA's toolbox. While I have written such a utility before (different platform, different client...), I would really like to purchase one this time around.
Please let me know if you know of such a utility.
Thanks,
Chris
-
I believe SQL Trace/tkprof will do all except #2 and #3.
-
Thanks, but
I haven't finished looking into that utility, as I can't seem to get the proper pieces running yet, but I'm not so sure about its ability to do #5 cleanly either.
I need to be able to easily compare hundreds of SQL statements and see clearly which ones had their LR go up or down. Unfortunately, I have also found Oracle's utilities to be generally very klunky and of limited use, so I must confess to having limited interest in figuring out if that will work.
Any other ideas?
-
use SQL Anaylyzer, it wont do any of this but it gives the idea, the plan, the route the query uses
