Best Oracle SQL query analyze tool

Question

I'm looking for a tool (either free or paid) capable of visualization oracle SQL query performance.

The ultimate goal is to have a tool, that can be easily read (eg. not EXPLAIN PLAN result) and that I can use to detect slow parts of query (eg. what join with what condition is actually adding the most to the whole query cost). I do not need automatic query optimization, I just want to see why does a query has too high cost. Also, it is best if the analyzer can run without special privileges.

I've tried Quest SQL Optimizer for Oracle, which looks quite ok, but I'm missing the option to see join conditions (much like SQL Developer). Also, I'm not able to easily find what join takes up most of the query cost.

What are your experiences? Is there a tool for this?

Solution

SQL Developer has built-in support for running Oracle's SQL Tuning Advisor, which is very much the kind of thing you're looking for.

Long term though, you're best bet is to get familiar with the Explain Plan output. You can spot the lowest hanging fruit by first making sure there are no Cartesian joins (always bad, usually a bug in the SQL) and then checking for full scans on big tables, which means that the joins aren't relying on indexes.