Hey Gang, I'm on a new project and have stored procedure that need to be optimized. I don't have access to OMS or DBA rights in Toad. I can only gain access to the SP as a user. And the time restraint are limited. Are there any product that you'll could suggest? I know if I had the Toad DBA right or 9i Dianostic pack access this would be a big help.
Presumably you can view execution plans in TOAD. If so then I would start by putting in some timing debug into the SP to identify the slow sql. Then run the sql within TOAD and look at the execution plan. From that you should be able to figure out how to optimise the sql or the tables/indexes.