Oracle Query Performance, Indexes, and Optimization

I've got an issue that I'm sure the posters here can answer regarding Oracle databases. I know it is probably a silly issue, but I really need assistance.

I'm a MS SQL Server guru, which is all we've got in-house. Works great for our small and mid-sized apps.

For our accounting system, we've outsourced in an ASP model. The provider is using Oracle 9 for the back end. We're seeing a certain job (which consists of numerous SQL queries) which takes 45 minutes to run. IMO, it doesn't seem like it is the type of process that should take as long as it does.

However, the ASP says they do not have any tools to diagnose which of the queries are poorly performing, and can't figure out if the application would be enhanced by additional indexes, etc. I find that very hard to believe. In SQL Server, there are numerous tools to perform those tasks, most of which are extremely simple to use (i.e., Profiler, Idera's tools, Quest's tools, etc).

Do these types of tools exist for Oracle? Do they come with base Oracle database administration tools? Query analysis, index analysis, poorly performing queries, table scans, etc.

I need some information to be able to battle their DBA's, of which I'm doubting their sincerity as well as ability. I can't believe there aren't any tools out there that can help an Oracle DBA with their performance analysis.

Thanks in advance for your help. Received on Wed Mar 21 2007 - 11:39:00 CDT